[Home] [Help]
4: eOutPath IN varchar2, mvspcName IN varchar2 default null,indspcName IN varchar2 default null) IS
5:
6: -- This it the Procedure to create a MV script for a given Dimension. This procedure writes to a
7: -- file.
8: filehandle UTL_FILE.FILE_TYPE;
9:
10: eDimName varchar2(30);
11: eOwnerName varchar2(30);
12: eMVName varchar2(30);
275: -- eeOutPath:= '/users/mrangwal';
276: -- eOutFile:= 'tete';
277: eDimName:= UPPER(exDimName);
278: eOwnerName:= UPPER(exOwnerName);
279: IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
280: filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
281: END IF;
282: -- tempName:= '';
283: IF NOT curDispName%ISOPEN THEN
276: -- eOutFile:= 'tete';
277: eDimName:= UPPER(exDimName);
278: eOwnerName:= UPPER(exOwnerName);
279: IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
280: filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
281: END IF;
282: -- tempName:= '';
283: IF NOT curDispName%ISOPEN THEN
284: OPEN curDispName;
289: END LOOP;
290: CLOSE curDispName;
291:
292: IF tempName IS NULL THEN
293: RAISE UTL_FILE.INTERNAL_ERROR;
294: END IF;
295: dimList.EXTEND;
296: i:= i + 1;
297: dimList(i).dimName:= eDimName;
324: dimHierList(j).dimName := eDimName;
325: dimHierList(j).dimNum := i;
326: dimHierList(j).hierName:= NULL;
327: dimHierList(j).hierNum:= k;
328: RAISE UTL_FILE.INVALID_OPERATION;
329: END IF;
330: dimList(i).hierCount:= k;
331: END LOOP;
332: j:= 0;
391: createMV:= TRUE;
392: eMVName:= SUBSTR(eDimName,0,5) || '_' || SUBSTR(mvdimList(i).hierName,0,5);
393: eMVName:= eMVName || '_OLAP';
394: --UTL_PUT(filehandle,buffer);
395: UTL_FILE.PUT(filehandle,'create materialized view ');
396: UTL_FILE.PUT_LINE(filehandle,eMVName);
397: UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
398: o:= 0;
399: FOR j in 1..mvdimList.Count LOOP
392: eMVName:= SUBSTR(eDimName,0,5) || '_' || SUBSTR(mvdimList(i).hierName,0,5);
393: eMVName:= eMVName || '_OLAP';
394: --UTL_PUT(filehandle,buffer);
395: UTL_FILE.PUT(filehandle,'create materialized view ');
396: UTL_FILE.PUT_LINE(filehandle,eMVName);
397: UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
398: o:= 0;
399: FOR j in 1..mvdimList.Count LOOP
400: IF mvdimList(j).mvNum= i THEN
393: eMVName:= eMVName || '_OLAP';
394: --UTL_PUT(filehandle,buffer);
395: UTL_FILE.PUT(filehandle,'create materialized view ');
396: UTL_FILE.PUT_LINE(filehandle,eMVName);
397: UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
398: o:= 0;
399: FOR j in 1..mvdimList.Count LOOP
400: IF mvdimList(j).mvNum= i THEN
401: eedimName:= mvdimList(j).dimName;
477: -- IF k < 800 THEN
478: -- indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
479: --
480: -- END IF;
481: UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
482: UTL_FILE.NEW_LINE(filehandle,1);
483: END IF;
484: END LOOP;
485: -- indexString:= indexString || ' partition values less than(MAXVALUE))';
478: -- indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
479: --
480: -- END IF;
481: UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
482: UTL_FILE.NEW_LINE(filehandle,1);
483: END IF;
484: END LOOP;
485: -- indexString:= indexString || ' partition values less than(MAXVALUE))';
486: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
482: UTL_FILE.NEW_LINE(filehandle,1);
483: END IF;
484: END LOOP;
485: -- indexString:= indexString || ' partition values less than(MAXVALUE))';
486: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
487: IF mvspcName IS NOT NULL THEN
488: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
489: UTL_FILE.NEW_LINE(filehandle,1);
490: END IF;
484: END LOOP;
485: -- indexString:= indexString || ' partition values less than(MAXVALUE))';
486: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
487: IF mvspcName IS NOT NULL THEN
488: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
489: UTL_FILE.NEW_LINE(filehandle,1);
490: END IF;
491: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
485: -- indexString:= indexString || ' partition values less than(MAXVALUE))';
486: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
487: IF mvspcName IS NOT NULL THEN
488: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
489: UTL_FILE.NEW_LINE(filehandle,1);
490: END IF;
491: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
487: IF mvspcName IS NOT NULL THEN
488: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
489: UTL_FILE.NEW_LINE(filehandle,1);
490: END IF;
491: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
488: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
489: UTL_FILE.NEW_LINE(filehandle,1);
490: END IF;
491: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
489: UTL_FILE.NEW_LINE(filehandle,1);
490: END IF;
491: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
490: END IF;
491: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
498: UTL_FILE.PUT_LINE(filehandle,'SELECT');
491: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
498: UTL_FILE.PUT_LINE(filehandle,'SELECT');
499: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
492: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
498: UTL_FILE.PUT_LINE(filehandle,'SELECT');
499: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
500: UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
493: UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
498: UTL_FILE.PUT_LINE(filehandle,'SELECT');
499: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
500: UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
501:
494: UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
498: UTL_FILE.PUT_LINE(filehandle,'SELECT');
499: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
500: UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
501:
502:
495: UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
498: UTL_FILE.PUT_LINE(filehandle,'SELECT');
499: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
500: UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
501:
502:
503: bufString:= bufString || 'SELECT ';
496: UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
497: UTL_FILE.PUT_LINE(filehandle,'AS');
498: UTL_FILE.PUT_LINE(filehandle,'SELECT');
499: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
500: UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
501:
502:
503: bufString:= bufString || 'SELECT ';
504: bufString:= bufString || 'COUNT(*) COUNT_STAR, ';
527: FETCH curTabName into eTabName;
528: EXIT WHEN curTabName%NOTFOUND;
529: END LOOP;
530: CLOSE curTabName;
531: UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
532: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;
533:
534: bufString:= bufString
535: || dbms_assert.enquote_name(eTabName)
542: || dbms_assert.enquote_name(eLevelColName);
543:
544: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
545: bufString:= bufString || ', ';
546: UTL_FILE.PUT_LINE(filehandle,',');
547: END IF;
548: ELSIF hierLevelList(l).dimName= eeDimName AND
549: hierLevelList(l).hierName= NULL THEN
550: eLevelName:= hierLevelList(l).levelName;
564: FETCH curTabName into eTabName;
565: EXIT WHEN curTabName%NOTFOUND;
566: END LOOP;
567: CLOSE curTabName;
568: UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
569: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;
570:
571: bufString:= bufString
572: || dbms_assert.enquote_name(eTabName)
579: || dbms_assert.enquote_name(eLevelColName);
580:
581: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
582: bufString:= bufString || ', ';
583: UTL_FILE.PUT_LINE(filehandle,',');
584: END IF;
585: END IF;
586: END LOOP;
587: END IF;
585: END IF;
586: END LOOP;
587: END IF;
588: END LOOP;
589: UTL_FILE.PUT_LINE(filehandle,') gid,');
590: bufString:= bufString || ') gid, ';
591: FOR j in 1..mvdimList.Count LOOP
592: IF mvdimList(j).mvNum= i THEN
593: eeDimName:= mvdimList(j).dimName;
627: || dbms_assert.enquote_name(eLevelColName)
628: || '_AR'
629: || ',';
630:
631: -- UTL_FILE.PUT_LINE(filehandle,') gid');
632: UTL_FILE.PUTF(filehandle,'max(%s.%s) %s%s_AR', eTabName,eLevelColName,eLevelColName,UPPER(SUBSTR(eAttrName,0,5)));
633: UTL_FILE.PUT_LINE(filehandle,',');
634: END LOOP;
635: CLOSE curAttrName;
628: || '_AR'
629: || ',';
630:
631: -- UTL_FILE.PUT_LINE(filehandle,') gid');
632: UTL_FILE.PUTF(filehandle,'max(%s.%s) %s%s_AR', eTabName,eLevelColName,eLevelColName,UPPER(SUBSTR(eAttrName,0,5)));
633: UTL_FILE.PUT_LINE(filehandle,',');
634: END LOOP;
635: CLOSE curAttrName;
636: END IF;
629: || ',';
630:
631: -- UTL_FILE.PUT_LINE(filehandle,') gid');
632: UTL_FILE.PUTF(filehandle,'max(%s.%s) %s%s_AR', eTabName,eLevelColName,eLevelColName,UPPER(SUBSTR(eAttrName,0,5)));
633: UTL_FILE.PUT_LINE(filehandle,',');
634: END LOOP;
635: CLOSE curAttrName;
636: END IF;
637: END LOOP;
660: FETCH curTabName into eTabName;
661: EXIT WHEN curTabName%NOTFOUND;
662: END LOOP;
663: CLOSE curTabName;
664: UTL_FILE.PUTF(filehandle,'%s.%s %s', eTabName,eLevelColName,eLevelColName);
665: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
666:
667: bufString:= bufString
668: ||dbms_assert.enquote_name(eTabName)
673:
674: -- bufString:= bufString || eLevelColName || ' ' || eLevelColName;
675: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
676: bufString:= bufString || ', ';
677: UTL_FILE.PUT_LINE(filehandle,', ');
678: ELSE
679: UTL_FILE.PUT_LINE(filehandle,'');
680: END IF;
681: ELSIF hierLevelList(l).dimName= eDimName AND
675: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
676: bufString:= bufString || ', ';
677: UTL_FILE.PUT_LINE(filehandle,', ');
678: ELSE
679: UTL_FILE.PUT_LINE(filehandle,'');
680: END IF;
681: ELSIF hierLevelList(l).dimName= eDimName AND
682: hierLevelList(l).hierName= NULL THEN
683: eLevelName:= hierLevelList(l).levelName;
696: FETCH curTabName into eTabName;
697: EXIT WHEN curTabName%NOTFOUND;
698: END LOOP;
699: CLOSE curTabName;
700: UTL_FILE.PUTF(filehandle,'%s.%s %s', eTabName,eLevelColName,eLevelColName);
701: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
702:
703: bufString:= bufString
704: || dbms_assert.enquote_name(eTabName)
709:
710: -- bufString:= bufString || eLevelColName || ' ' || eLevelColName;
711: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
712: bufString:= bufString || ', ';
713: UTL_FILE.PUT_LINE(filehandle,', ');
714: ELSE
715: UTL_FILE.PUT_LINE(filehandle,'');
716: END IF;
717: END IF;
711: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
712: bufString:= bufString || ', ';
713: UTL_FILE.PUT_LINE(filehandle,', ');
714: ELSE
715: UTL_FILE.PUT_LINE(filehandle,'');
716: END IF;
717: END IF;
718: END LOOP;
719: END IF;
718: END LOOP;
719: END IF;
720: END LOOP;
721: bufString:= bufString || ' FROM ';
722: UTL_FILE.PUT_LINE(filehandle,'FROM ');
723: o:= 0;
724: FOR j in 1..mvdimList.Count LOOP
725: IF mvdimList(j).mvNum= i THEN
726: eeDimName:= mvdimList(j).dimName;
745: FETCH curTabName into eTabName;
746: EXIT WHEN curTabName%NOTFOUND;
747: END LOOP;
748: CLOSE curTabName;
749: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
750: -- UTL_FILE.PUT_LINE(filehandle,' ');
751: -- bufString:= bufString || eTabName || ' ';
752: o:= o + 1;
753: mvtableList.EXTEND;
746: EXIT WHEN curTabName%NOTFOUND;
747: END LOOP;
748: CLOSE curTabName;
749: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
750: -- UTL_FILE.PUT_LINE(filehandle,' ');
751: -- bufString:= bufString || eTabName || ' ';
752: o:= o + 1;
753: mvtableList.EXTEND;
754: mvtableList(o).tabName:= eTabName;
753: mvtableList.EXTEND;
754: mvtableList(o).tabName:= eTabName;
755: mvtableList(o).ownerName:= eOwnerName;
756: tableCount:= mvtableList.Count;
757: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
758: -- UTL_FILE.PUT_LINE(filehandle,' ');
759: -- bufString:= bufString || eTabName || ' ';
760: ELSIF hierLevelList(l).dimName= eedimName AND
761: hierLevelList(l).hierName= NULL AND
754: mvtableList(o).tabName:= eTabName;
755: mvtableList(o).ownerName:= eOwnerName;
756: tableCount:= mvtableList.Count;
757: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
758: -- UTL_FILE.PUT_LINE(filehandle,' ');
759: -- bufString:= bufString || eTabName || ' ';
760: ELSIF hierLevelList(l).dimName= eedimName AND
761: hierLevelList(l).hierName= NULL AND
762: mvdimList(j).lvlCount= hierLevelList(l).levelNum THEN
776: FETCH curTabName into eTabName;
777: EXIT WHEN curTabName%NOTFOUND;
778: END LOOP;
779: CLOSE curTabName;
780: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
781: -- UTL_FILE.PUT_LINE(filehandle,' ');
782: -- bufString:= bufString || eTabName || ' ';
783: o:= o + 1;
784: mvtableList.EXTEND;
777: EXIT WHEN curTabName%NOTFOUND;
778: END LOOP;
779: CLOSE curTabName;
780: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
781: -- UTL_FILE.PUT_LINE(filehandle,' ');
782: -- bufString:= bufString || eTabName || ' ';
783: o:= o + 1;
784: mvtableList.EXTEND;
785: mvtableList(o).tabName:= eTabName;
784: mvtableList.EXTEND;
785: mvtableList(o).tabName:= eTabName;
786: mvtableList(o).ownerName:= eOwnerName;
787: tableCount:= mvtableList.Count;
788: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
789: -- UTL_FILE.PUT_LINE(filehandle,' ');
790: -- bufString:= bufString || eTabName || ' ';
791: END IF;
792: END LOOP;
785: mvtableList(o).tabName:= eTabName;
786: mvtableList(o).ownerName:= eOwnerName;
787: tableCount:= mvtableList.Count;
788: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
789: -- UTL_FILE.PUT_LINE(filehandle,' ');
790: -- bufString:= bufString || eTabName || ' ';
791: END IF;
792: END LOOP;
793: END IF;
893: mvtableList.EXTEND;
894: mvtableList(o).tabName:= eTabName;
895: mvtableList(o).ownerName:= eOwnerName;
896: END IF;
897: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
898: -- UTL_FILE.PUT_LINE(filehandle,' ');
899: -- bufString:= bufString || eTabName || ' ';
900: END IF;
901: ELSIF hierLevelList(l).dimName= edimName AND
894: mvtableList(o).tabName:= eTabName;
895: mvtableList(o).ownerName:= eOwnerName;
896: END IF;
897: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
898: -- UTL_FILE.PUT_LINE(filehandle,' ');
899: -- bufString:= bufString || eTabName || ' ';
900: END IF;
901: ELSIF hierLevelList(l).dimName= edimName AND
902: hierLevelList(l).hierName= NULL THEN
945: mvtableList.EXTEND;
946: mvtableList(o).tabName:= eTabName;
947: mvtableList(o).ownerName:= eOwnerName;
948: END IF;
949: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
950: -- UTL_FILE.PUT_LINE(filehandle,' ');
951: -- bufString:= bufString || eTabName || ' ';
952: END IF;
953:
946: mvtableList(o).tabName:= eTabName;
947: mvtableList(o).ownerName:= eOwnerName;
948: END IF;
949: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
950: -- UTL_FILE.PUT_LINE(filehandle,' ');
951: -- bufString:= bufString || eTabName || ' ';
952: END IF;
953:
954: END IF;
966: || ' '
967: || dbms_assert.enquote_name(mvtableList(o).tabName)
968: || ' ';
969:
970: UTL_FILE.PUTF(filehandle,'%s.%s %s ', mvtableList(o).ownerName,mvtableList(o).tabName,
971: mvtableList(o).tabName);
972:
973: IF o < mvtableList.Count THEN
974: bufString:= bufString || ', ';
971: mvtableList(o).tabName);
972:
973: IF o < mvtableList.Count THEN
974: bufString:= bufString || ', ';
975: UTL_FILE.PUT(filehandle,',');
976: END IF;
977: UTL_FILE.PUT_LINE(filehandle,' ');
978: END LOOP;
979: mvtableList.DELETE;
973: IF o < mvtableList.Count THEN
974: bufString:= bufString || ', ';
975: UTL_FILE.PUT(filehandle,',');
976: END IF;
977: UTL_FILE.PUT_LINE(filehandle,' ');
978: END LOOP;
979: mvtableList.DELETE;
980:
981: -- bufString:= bufString || ' ' || 'WHERE ';
1019: childString:= eparLevelName;
1020: IF childString <> parentString THEN
1021: IF whereFlag = FALSE THEN
1022: bufString:= bufString || ' ' || 'WHERE ';
1023: UTL_FILE.PUT_LINE(filehandle,' WHERE');
1024: whereFlag:= TRUE;
1025: ELSE
1026: bufString:= bufString || ' ' || ' AND ';
1027: UTL_FILE.PUT_LINE(filehandle,' AND');
1023: UTL_FILE.PUT_LINE(filehandle,' WHERE');
1024: whereFlag:= TRUE;
1025: ELSE
1026: bufString:= bufString || ' ' || ' AND ';
1027: UTL_FILE.PUT_LINE(filehandle,' AND');
1028: END IF;
1029: UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
1030: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1031:
1025: ELSE
1026: bufString:= bufString || ' ' || ' AND ';
1027: UTL_FILE.PUT_LINE(filehandle,' AND');
1028: END IF;
1029: UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
1030: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1031:
1032: bufString:= bufString
1033: || '('
1070: childString:= eparLevelName;
1071: IF childString <> parentString THEN
1072: IF whereFlag = FALSE THEN
1073: bufString:= bufString || ' ' || 'WHERE ';
1074: UTL_FILE.PUT_LINE(filehandle,' WHERE');
1075: whereFlag:= TRUE;
1076: ELSE
1077: bufString:= bufString || ' ' || ' AND ';
1078: UTL_FILE.PUT_LINE(filehandle,' AND');
1074: UTL_FILE.PUT_LINE(filehandle,' WHERE');
1075: whereFlag:= TRUE;
1076: ELSE
1077: bufString:= bufString || ' ' || ' AND ';
1078: UTL_FILE.PUT_LINE(filehandle,' AND');
1079: END IF;
1080: UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
1081: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1082:
1076: ELSE
1077: bufString:= bufString || ' ' || ' AND ';
1078: UTL_FILE.PUT_LINE(filehandle,' AND');
1079: END IF;
1080: UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
1081: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1082:
1083: bufString:= bufString
1084: || '('
1091: END LOOP;
1092: END IF;
1093: END LOOP;
1094:
1095: UTL_FILE.PUT_LINE(filehandle,'GROUP BY ');
1096: bufString:= bufString || 'GROUP BY ';
1097: FOR j in 1..mvdimList.Count LOOP
1098: IF mvdimList(j).mvNum= i THEN
1099: mvnum:= 0;
1128: EXIT WHEN curTabName%NOTFOUND;
1129: END LOOP;
1130: CLOSE curTabName;
1131: IF ePosition < poscount AND ePosition = 1 THEN
1132: UTL_FILE.PUT(filehandle,'(');
1133: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1134: UTL_FILE.PUT(filehandle,', ');
1135: END IF;
1136: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1130: CLOSE curTabName;
1131: IF ePosition < poscount AND ePosition = 1 THEN
1132: UTL_FILE.PUT(filehandle,'(');
1133: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1134: UTL_FILE.PUT(filehandle,', ');
1135: END IF;
1136: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1137: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1138:
1132: UTL_FILE.PUT(filehandle,'(');
1133: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1134: UTL_FILE.PUT(filehandle,', ');
1135: END IF;
1136: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1137: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1138:
1139: bufString:= bufString
1140: || dbms_assert.enquote_name(eTabName)
1145: -- bufString:= bufString || eLevelColName || ' ';
1146: END LOOP;
1147: CLOSE curColName;
1148: IF ePosition <> 1 THEN
1149: UTL_FILE.PUT(filehandle,')');
1150: END IF;
1151: IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
1152: UTL_FILE.PUT(filehandle,', ROLLUP( ');
1153: bufString:= bufString || ', ROLLUP( ';
1148: IF ePosition <> 1 THEN
1149: UTL_FILE.PUT(filehandle,')');
1150: END IF;
1151: IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
1152: UTL_FILE.PUT(filehandle,', ROLLUP( ');
1153: bufString:= bufString || ', ROLLUP( ';
1154: ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
1155: UTL_FILE.PUT(filehandle,', ');
1156: bufString:= bufString || ', ';
1151: IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
1152: UTL_FILE.PUT(filehandle,', ROLLUP( ');
1153: bufString:= bufString || ', ROLLUP( ';
1154: ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
1155: UTL_FILE.PUT(filehandle,', ');
1156: bufString:= bufString || ', ';
1157: ELSIF mvdimList(j).lvlCount= mvnum THEN
1158: UTL_FILE.PUT_LINE(filehandle,') ');
1159: bufString:= bufString || ') ';
1154: ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
1155: UTL_FILE.PUT(filehandle,', ');
1156: bufString:= bufString || ', ';
1157: ELSIF mvdimList(j).lvlCount= mvnum THEN
1158: UTL_FILE.PUT_LINE(filehandle,') ');
1159: bufString:= bufString || ') ';
1160: END IF;
1161: ELSIF hierLevelList(l).dimName= edimName AND
1162: hierLevelList(l).hierName= NULL THEN
1186: EXIT WHEN curTabName%NOTFOUND;
1187: END LOOP;
1188: CLOSE curTabName;
1189: -- IF ePosition < poscount AND ePosition = 1 THEN
1190: -- UTL_FILE.PUT(filehandle,'(');
1191: -- ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1192: -- UTL_FILE.PUT(filehandle,', ');
1193: -- END IF;
1194: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1188: CLOSE curTabName;
1189: -- IF ePosition < poscount AND ePosition = 1 THEN
1190: -- UTL_FILE.PUT(filehandle,'(');
1191: -- ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1192: -- UTL_FILE.PUT(filehandle,', ');
1193: -- END IF;
1194: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1195: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1196:
1190: -- UTL_FILE.PUT(filehandle,'(');
1191: -- ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1192: -- UTL_FILE.PUT(filehandle,', ');
1193: -- END IF;
1194: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1195: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1196:
1197: bufString:= bufString
1198: || dbms_assert.enquote_name(eTabName)
1203: -- bufString:= bufString || eLevelColName || ' ';
1204: END LOOP;
1205: CLOSE curColDimName;
1206: -- IF ePosition <> 1 THEN
1207: -- UTL_FILE.PUT(filehandle,')');
1208: -- END IF;
1209: -- IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
1210: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
1211: -- bufString:= bufString || ', ROLLUP( ';
1206: -- IF ePosition <> 1 THEN
1207: -- UTL_FILE.PUT(filehandle,')');
1208: -- END IF;
1209: -- IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
1210: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
1211: -- bufString:= bufString || ', ROLLUP( ';
1212: -- ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
1213: -- UTL_FILE.PUT(filehandle,', ');
1214: -- bufString:= bufString || ', ';
1209: -- IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
1210: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
1211: -- bufString:= bufString || ', ROLLUP( ';
1212: -- ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
1213: -- UTL_FILE.PUT(filehandle,', ');
1214: -- bufString:= bufString || ', ';
1215: -- ELSIF mvdimList(j).lvlCount= mvnum THEN
1216: -- UTL_FILE.PUT_LINE(filehandle,') ');
1217: -- bufString:= bufString || ') ';
1212: -- ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
1213: -- UTL_FILE.PUT(filehandle,', ');
1214: -- bufString:= bufString || ', ';
1215: -- ELSIF mvdimList(j).lvlCount= mvnum THEN
1216: -- UTL_FILE.PUT_LINE(filehandle,') ');
1217: -- bufString:= bufString || ') ';
1218: -- END IF;
1219: END IF;
1220: END LOOP;
1219: END IF;
1220: END LOOP;
1221: END IF;
1222: END LOOP;
1223: UTL_FILE.PUT(filehandle,';');
1224:
1225: query_length:= LENGTH(bufString);
1226: IF NOT checkName%ISOPEN THEN
1227: OPEN checkName;
1252: -- (runId,i+100,eOwnerName,indexString);
1253: eTempMV:= '';
1254: bufString:= '';
1255:
1256: UTL_FILE.NEW_LINE(filehandle,2);
1257: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
1258: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1259: -- UTL_FILE.NEW_LINE(filehandle,1);
1260: IF createMV = TRUE THEN
1253: eTempMV:= '';
1254: bufString:= '';
1255:
1256: UTL_FILE.NEW_LINE(filehandle,2);
1257: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
1258: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1259: -- UTL_FILE.NEW_LINE(filehandle,1);
1260: IF createMV = TRUE THEN
1261: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1255:
1256: UTL_FILE.NEW_LINE(filehandle,2);
1257: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
1258: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1259: -- UTL_FILE.NEW_LINE(filehandle,1);
1260: IF createMV = TRUE THEN
1261: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1262: eOwnerName);
1263: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1257: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
1258: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1259: -- UTL_FILE.NEW_LINE(filehandle,1);
1260: IF createMV = TRUE THEN
1261: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1262: eOwnerName);
1263: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1264: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
1265: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1259: -- UTL_FILE.NEW_LINE(filehandle,1);
1260: IF createMV = TRUE THEN
1261: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1262: eOwnerName);
1263: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1264: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
1265: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1266: -- UTL_FILE.NEW_LINE(filehandle,1);
1267:
1260: IF createMV = TRUE THEN
1261: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1262: eOwnerName);
1263: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1264: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
1265: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1266: -- UTL_FILE.NEW_LINE(filehandle,1);
1267:
1268: -- UTL_FILE.NEW_LINE(filehandle,2);
1262: eOwnerName);
1263: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1264: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
1265: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1266: -- UTL_FILE.NEW_LINE(filehandle,1);
1267:
1268: -- UTL_FILE.NEW_LINE(filehandle,2);
1269: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1270: UTL_FILE.NEW_LINE(filehandle,2);
1264: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
1265: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1266: -- UTL_FILE.NEW_LINE(filehandle,1);
1267:
1268: -- UTL_FILE.NEW_LINE(filehandle,2);
1269: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1270: UTL_FILE.NEW_LINE(filehandle,2);
1271: END IF;
1272: FOR j in 1..mvdimList.Count LOOP
1265: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
1266: -- UTL_FILE.NEW_LINE(filehandle,1);
1267:
1268: -- UTL_FILE.NEW_LINE(filehandle,2);
1269: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1270: UTL_FILE.NEW_LINE(filehandle,2);
1271: END IF;
1272: FOR j in 1..mvdimList.Count LOOP
1273: IF mvdimList(j).mvNum= i THEN
1266: -- UTL_FILE.NEW_LINE(filehandle,1);
1267:
1268: -- UTL_FILE.NEW_LINE(filehandle,2);
1269: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1270: UTL_FILE.NEW_LINE(filehandle,2);
1271: END IF;
1272: FOR j in 1..mvdimList.Count LOOP
1273: IF mvdimList(j).mvNum= i THEN
1274: mvnum:= 0;
1297: END LOOP;
1298: CLOSE curTabName;
1299: IF hierLevelList(l).levelNum <> 1 THEN
1300: IF CreateMV = TRUE THEN
1301: UTL_FILE.NEW_LINE(filehandle,2);
1302: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1303: UTL_FILE.NEW_LINE(filehandle,1);
1304: IF indspcName IS NOT NULL THEN
1305: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1298: CLOSE curTabName;
1299: IF hierLevelList(l).levelNum <> 1 THEN
1300: IF CreateMV = TRUE THEN
1301: UTL_FILE.NEW_LINE(filehandle,2);
1302: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1303: UTL_FILE.NEW_LINE(filehandle,1);
1304: IF indspcName IS NOT NULL THEN
1305: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1306: UTL_FILE.NEW_LINE(filehandle,1);
1299: IF hierLevelList(l).levelNum <> 1 THEN
1300: IF CreateMV = TRUE THEN
1301: UTL_FILE.NEW_LINE(filehandle,2);
1302: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1303: UTL_FILE.NEW_LINE(filehandle,1);
1304: IF indspcName IS NOT NULL THEN
1305: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1306: UTL_FILE.NEW_LINE(filehandle,1);
1307: END IF;
1301: UTL_FILE.NEW_LINE(filehandle,2);
1302: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1303: UTL_FILE.NEW_LINE(filehandle,1);
1304: IF indspcName IS NOT NULL THEN
1305: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1306: UTL_FILE.NEW_LINE(filehandle,1);
1307: END IF;
1308: -- bufString:= bufString || 'INDEX MV_';
1309: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1302: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1303: UTL_FILE.NEW_LINE(filehandle,1);
1304: IF indspcName IS NOT NULL THEN
1305: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1306: UTL_FILE.NEW_LINE(filehandle,1);
1307: END IF;
1308: -- bufString:= bufString || 'INDEX MV_';
1309: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1310: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1306: UTL_FILE.NEW_LINE(filehandle,1);
1307: END IF;
1308: -- bufString:= bufString || 'INDEX MV_';
1309: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1310: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1311: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1312: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1313: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1314: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1307: END IF;
1308: -- bufString:= bufString || 'INDEX MV_';
1309: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1310: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1311: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1312: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1313: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1314: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1315: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1308: -- bufString:= bufString || 'INDEX MV_';
1309: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1310: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1311: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1312: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1313: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1314: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1315: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1316: UTL_FILE.NEW_LINE(filehandle,2);
1309: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1310: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1311: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1312: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1313: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1314: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1315: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1316: UTL_FILE.NEW_LINE(filehandle,2);
1317: END IF;
1310: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1311: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1312: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1313: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1314: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1315: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1316: UTL_FILE.NEW_LINE(filehandle,2);
1317: END IF;
1318: END IF;
1311: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1312: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1313: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1314: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1315: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1316: UTL_FILE.NEW_LINE(filehandle,2);
1317: END IF;
1318: END IF;
1319: --Bug 8915090 bufString:= bufString || 'INDEX_MV_' || eLevelColName;
1312: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1313: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1314: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1315: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1316: UTL_FILE.NEW_LINE(filehandle,2);
1317: END IF;
1318: END IF;
1319: --Bug 8915090 bufString:= bufString || 'INDEX_MV_' || eLevelColName;
1320:
1353: END LOOP;
1354: CLOSE curTabName;
1355: IF hierLevelList(l).levelNum <> 1 THEN
1356: IF CreateMV = TRUE THEN
1357: UTL_FILE.NEW_LINE(filehandle,2);
1358: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1359: UTL_FILE.NEW_LINE(filehandle,1);
1360: IF indspcName IS NOT NULL THEN
1361: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1354: CLOSE curTabName;
1355: IF hierLevelList(l).levelNum <> 1 THEN
1356: IF CreateMV = TRUE THEN
1357: UTL_FILE.NEW_LINE(filehandle,2);
1358: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1359: UTL_FILE.NEW_LINE(filehandle,1);
1360: IF indspcName IS NOT NULL THEN
1361: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1362: UTL_FILE.NEW_LINE(filehandle,1);
1355: IF hierLevelList(l).levelNum <> 1 THEN
1356: IF CreateMV = TRUE THEN
1357: UTL_FILE.NEW_LINE(filehandle,2);
1358: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1359: UTL_FILE.NEW_LINE(filehandle,1);
1360: IF indspcName IS NOT NULL THEN
1361: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1362: UTL_FILE.NEW_LINE(filehandle,1);
1363: END IF;
1357: UTL_FILE.NEW_LINE(filehandle,2);
1358: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1359: UTL_FILE.NEW_LINE(filehandle,1);
1360: IF indspcName IS NOT NULL THEN
1361: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1362: UTL_FILE.NEW_LINE(filehandle,1);
1363: END IF;
1364: -- bufString:= bufString || 'INDEX MV_';
1365: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1358: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
1359: UTL_FILE.NEW_LINE(filehandle,1);
1360: IF indspcName IS NOT NULL THEN
1361: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1362: UTL_FILE.NEW_LINE(filehandle,1);
1363: END IF;
1364: -- bufString:= bufString || 'INDEX MV_';
1365: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1366: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1362: UTL_FILE.NEW_LINE(filehandle,1);
1363: END IF;
1364: -- bufString:= bufString || 'INDEX MV_';
1365: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1366: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1367: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1368: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1369: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1370: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1363: END IF;
1364: -- bufString:= bufString || 'INDEX MV_';
1365: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1366: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1367: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1368: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1369: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1370: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1371: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1364: -- bufString:= bufString || 'INDEX MV_';
1365: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1366: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1367: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1368: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1369: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1370: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1371: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1372: UTL_FILE.NEW_LINE(filehandle,2);
1365: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1366: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1367: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1368: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1369: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1370: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1371: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1372: UTL_FILE.NEW_LINE(filehandle,2);
1373: END IF;
1366: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1367: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1368: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1369: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1370: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1371: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1372: UTL_FILE.NEW_LINE(filehandle,2);
1373: END IF;
1374: END IF;
1367: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1368: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1369: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1370: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1371: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1372: UTL_FILE.NEW_LINE(filehandle,2);
1373: END IF;
1374: END IF;
1375: --Bug 8915090 bufString:= bufString || 'INDEX_MV_' || eLevelColName;
1368: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1369: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1370: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1371: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1372: UTL_FILE.NEW_LINE(filehandle,2);
1373: END IF;
1374: END IF;
1375: --Bug 8915090 bufString:= bufString || 'INDEX_MV_' || eLevelColName;
1376:
1393: END LOOP;
1394: FOR j in 1..mvdimList.Count LOOP
1395: IF mvdimList(j).mvNum= i THEN
1396: IF CreateMV = TRUE THEN
1397: UTL_FILE.NEW_LINE(filehandle,2);
1398: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
1399: UTL_FILE.NEW_LINE(filehandle,1);
1400: IF indspcName IS NOT NULL THEN
1401: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1394: FOR j in 1..mvdimList.Count LOOP
1395: IF mvdimList(j).mvNum= i THEN
1396: IF CreateMV = TRUE THEN
1397: UTL_FILE.NEW_LINE(filehandle,2);
1398: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
1399: UTL_FILE.NEW_LINE(filehandle,1);
1400: IF indspcName IS NOT NULL THEN
1401: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1402: UTL_FILE.NEW_LINE(filehandle,1);
1395: IF mvdimList(j).mvNum= i THEN
1396: IF CreateMV = TRUE THEN
1397: UTL_FILE.NEW_LINE(filehandle,2);
1398: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
1399: UTL_FILE.NEW_LINE(filehandle,1);
1400: IF indspcName IS NOT NULL THEN
1401: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1402: UTL_FILE.NEW_LINE(filehandle,1);
1403: END IF;
1397: UTL_FILE.NEW_LINE(filehandle,2);
1398: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
1399: UTL_FILE.NEW_LINE(filehandle,1);
1400: IF indspcName IS NOT NULL THEN
1401: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1402: UTL_FILE.NEW_LINE(filehandle,1);
1403: END IF;
1404: -- bufString:= bufString || 'INDEX MV_';
1405: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1398: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
1399: UTL_FILE.NEW_LINE(filehandle,1);
1400: IF indspcName IS NOT NULL THEN
1401: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1402: UTL_FILE.NEW_LINE(filehandle,1);
1403: END IF;
1404: -- bufString:= bufString || 'INDEX MV_';
1405: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1406: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1402: UTL_FILE.NEW_LINE(filehandle,1);
1403: END IF;
1404: -- bufString:= bufString || 'INDEX MV_';
1405: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1406: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1407: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1408: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1409: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1410: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1403: END IF;
1404: -- bufString:= bufString || 'INDEX MV_';
1405: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1406: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1407: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1408: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1409: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1410: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1411: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1404: -- bufString:= bufString || 'INDEX MV_';
1405: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1406: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1407: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1408: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1409: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1410: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1411: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1412: UTL_FILE.NEW_LINE(filehandle,2);
1405: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1406: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1407: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1408: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1409: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1410: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1411: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1412: UTL_FILE.NEW_LINE(filehandle,2);
1413: END IF;
1406: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1407: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1408: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1409: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1410: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1411: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1412: UTL_FILE.NEW_LINE(filehandle,2);
1413: END IF;
1414: END IF;
1407: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1408: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1409: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1410: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1411: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1412: UTL_FILE.NEW_LINE(filehandle,2);
1413: END IF;
1414: END IF;
1415: END LOOP;
1408: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1409: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1410: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1411: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1412: UTL_FILE.NEW_LINE(filehandle,2);
1413: END IF;
1414: END IF;
1415: END LOOP;
1416: m:= 0;
1437: FETCH curTabName into eTabName;
1438: EXIT WHEN curTabName%NOTFOUND;
1439: END LOOP;
1440: CLOSE curTabName;
1441: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
1442: m:= m + 1;
1443: tabcol.EXTEND;
1444: -- tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;
1445:
1462: FETCH curTabName into eTabName;
1463: EXIT WHEN curTabName%NOTFOUND;
1464: END LOOP;
1465: CLOSE curTabName;
1466: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
1467: m:= m + 1;
1468: tabcol.EXTEND;
1469: -- tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;
1470:
1472: END IF;
1473: END LOOP;
1474: END IF;
1475: END LOOP;
1476: UTL_FILE.NEW_LINE(filehandle,2);
1477: IF CreateMV = TRUE THEN
1478: UTL_FILE.NEW_LINE(filehandle,2);
1479: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
1480: UTL_FILE.NEW_LINE(filehandle,1);
1474: END IF;
1475: END LOOP;
1476: UTL_FILE.NEW_LINE(filehandle,2);
1477: IF CreateMV = TRUE THEN
1478: UTL_FILE.NEW_LINE(filehandle,2);
1479: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
1480: UTL_FILE.NEW_LINE(filehandle,1);
1481: -- bufString:= bufString || 'INDEX MV_';
1482: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1475: END LOOP;
1476: UTL_FILE.NEW_LINE(filehandle,2);
1477: IF CreateMV = TRUE THEN
1478: UTL_FILE.NEW_LINE(filehandle,2);
1479: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
1480: UTL_FILE.NEW_LINE(filehandle,1);
1481: -- bufString:= bufString || 'INDEX MV_';
1482: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1483: UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');
1476: UTL_FILE.NEW_LINE(filehandle,2);
1477: IF CreateMV = TRUE THEN
1478: UTL_FILE.NEW_LINE(filehandle,2);
1479: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
1480: UTL_FILE.NEW_LINE(filehandle,1);
1481: -- bufString:= bufString || 'INDEX MV_';
1482: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1483: UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');
1484:
1479: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
1480: UTL_FILE.NEW_LINE(filehandle,1);
1481: -- bufString:= bufString || 'INDEX MV_';
1482: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1483: UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');
1484:
1485: --Bug 8915090 bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;
1486:
1487: bufString:= bufString
1495: || dbms_assert.enquote_name(eMVName);
1496:
1497: bufString:= bufString || '( (CASE SYS_OP_PARGID(GID';
1498: FOR n in 1..times LOOP
1499: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1500: --Bug 8915090 bufString:= bufString || tabCol(n).colName;
1501:
1502: bufString:= bufString
1503: || dbms_assert.enquote_name(tabCol(n).colName);
1502: bufString:= bufString
1503: || dbms_assert.enquote_name(tabCol(n).colName);
1504:
1505: END LOOP;
1506: UTL_FILE.PUT_LINE(filehandle,')');
1507: -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
1508: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
1509: -- bufString:= bufString || 'PARALLEL NOLOGGING; ';
1510:
1513: FOR k in REVERSE 1..times LOOP
1514: IF k = times THEN
1515: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1516: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1517: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1518: UTL_FILE.NEW_LINE(filehandle,1);
1519: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1520: FOR n in 1..times LOOP
1521: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1514: IF k = times THEN
1515: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1516: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1517: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1518: UTL_FILE.NEW_LINE(filehandle,1);
1519: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1520: FOR n in 1..times LOOP
1521: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1522: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1515: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1516: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1517: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1518: UTL_FILE.NEW_LINE(filehandle,1);
1519: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1520: FOR n in 1..times LOOP
1521: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1522: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1523:
1517: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1518: UTL_FILE.NEW_LINE(filehandle,1);
1519: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1520: FOR n in 1..times LOOP
1521: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1522: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1523:
1524: bufString:= bufString
1525: || ','
1525: || ','
1526: || dbms_assert.enquote_name(tabCol(n).colName);
1527:
1528: END LOOP;
1529: UTL_FILE.PUT_LINE(filehandle,'))');
1530: bufString:= bufString || '))';
1531: ELSIF k = 1 THEN
1532: bufString:= bufString || ' ELSE ';
1533: bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1530: bufString:= bufString || '))';
1531: ELSIF k = 1 THEN
1532: bufString:= bufString || ' ELSE ';
1533: bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1534: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR(SYS_OP_PAR(%s, GID', m);
1535: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1536: -- UTL_FILE.NEW_LINE(filehandle,1);
1537: FOR n in 1..times LOOP
1538: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1531: ELSIF k = 1 THEN
1532: bufString:= bufString || ' ELSE ';
1533: bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1534: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR(SYS_OP_PAR(%s, GID', m);
1535: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1536: -- UTL_FILE.NEW_LINE(filehandle,1);
1537: FOR n in 1..times LOOP
1538: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1539: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1532: bufString:= bufString || ' ELSE ';
1533: bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1534: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR(SYS_OP_PAR(%s, GID', m);
1535: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1536: -- UTL_FILE.NEW_LINE(filehandle,1);
1537: FOR n in 1..times LOOP
1538: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1539: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1540:
1534: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR(SYS_OP_PAR(%s, GID', m);
1535: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1536: -- UTL_FILE.NEW_LINE(filehandle,1);
1537: FOR n in 1..times LOOP
1538: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1539: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1540:
1541: bufString:= bufString
1542: || ','
1542: || ','
1543: || dbms_assert.enquote_name(tabCol(n).colName);
1544:
1545: END LOOP;
1546: UTL_FILE.PUT_LINE(filehandle,')) END) )');
1547: bufString:= bufString || ')) END) )';
1548: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1549: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1550: IF indspcName IS NOT NULL THEN
1544:
1545: END LOOP;
1546: UTL_FILE.PUT_LINE(filehandle,')) END) )');
1547: bufString:= bufString || ')) END) )';
1548: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1549: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1550: IF indspcName IS NOT NULL THEN
1551: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1552: UTL_FILE.NEW_LINE(filehandle,1);
1545: END LOOP;
1546: UTL_FILE.PUT_LINE(filehandle,')) END) )');
1547: bufString:= bufString || ')) END) )';
1548: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1549: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1550: IF indspcName IS NOT NULL THEN
1551: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1552: UTL_FILE.NEW_LINE(filehandle,1);
1553: END IF;
1547: bufString:= bufString || ')) END) )';
1548: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1549: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1550: IF indspcName IS NOT NULL THEN
1551: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1552: UTL_FILE.NEW_LINE(filehandle,1);
1553: END IF;
1554: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1555: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1548: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1549: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1550: IF indspcName IS NOT NULL THEN
1551: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1552: UTL_FILE.NEW_LINE(filehandle,1);
1553: END IF;
1554: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1555: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1556: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1550: IF indspcName IS NOT NULL THEN
1551: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1552: UTL_FILE.NEW_LINE(filehandle,1);
1553: END IF;
1554: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1555: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1556: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1557: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1558: UTL_FILE.NEW_LINE(filehandle,2);
1551: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1552: UTL_FILE.NEW_LINE(filehandle,1);
1553: END IF;
1554: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1555: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1556: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1557: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1558: UTL_FILE.NEW_LINE(filehandle,2);
1559: ELSE
1552: UTL_FILE.NEW_LINE(filehandle,1);
1553: END IF;
1554: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1555: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1556: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1557: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1558: UTL_FILE.NEW_LINE(filehandle,2);
1559: ELSE
1560: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1553: END IF;
1554: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1555: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1556: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1557: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1558: UTL_FILE.NEW_LINE(filehandle,2);
1559: ELSE
1560: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1561: -- bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(GID)';
1554: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1555: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1556: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1557: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1558: UTL_FILE.NEW_LINE(filehandle,2);
1559: ELSE
1560: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1561: -- bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(GID)';
1562: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1559: ELSE
1560: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1561: -- bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(GID)';
1562: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1563: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1564: UTL_FILE.NEW_LINE(filehandle,1);
1565: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1560: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1561: -- bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(GID)';
1562: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1563: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1564: UTL_FILE.NEW_LINE(filehandle,1);
1565: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1561: -- bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(GID)';
1562: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1563: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1564: UTL_FILE.NEW_LINE(filehandle,1);
1565: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1569: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
1562: bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
1563: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1564: UTL_FILE.NEW_LINE(filehandle,1);
1565: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1569: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
1570: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1563: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1564: UTL_FILE.NEW_LINE(filehandle,1);
1565: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1569: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
1570: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1571: FOR n in 1..times LOOP
1564: UTL_FILE.NEW_LINE(filehandle,1);
1565: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1569: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
1570: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1571: FOR n in 1..times LOOP
1572: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1565: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1569: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
1570: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1571: FOR n in 1..times LOOP
1572: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1573: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1566: -- UTL_FILE.NEW_LINE(filehandle,1);
1567: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1569: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
1570: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1571: FOR n in 1..times LOOP
1572: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1573: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1574:
1568: -- UTL_FILE.NEW_LINE(filehandle,1);
1569: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
1570: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
1571: FOR n in 1..times LOOP
1572: UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
1573: --Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;
1574:
1575: bufString:= bufString
1576: || ','
1576: || ','
1577: || dbms_assert.enquote_name(tabCol(n).colName);
1578:
1579: END LOOP;
1580: UTL_FILE.PUT_LINE(filehandle,'))');
1581: bufString:= bufString || '))';
1582: END IF;
1583: m:= m + 1;
1584: END LOOP;
1584: END LOOP;
1585: END IF;
1586: bufString:= ' ';
1587: IF CreateMV= TRUE THEN
1588: UTL_FILE.NEW_LINE(filehandle,2);
1589: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
1590: UTL_FILE.NEW_LINE(filehandle,1);
1591:
1592: -- bufString:= bufString || 'INDEX MV_';
1585: END IF;
1586: bufString:= ' ';
1587: IF CreateMV= TRUE THEN
1588: UTL_FILE.NEW_LINE(filehandle,2);
1589: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
1590: UTL_FILE.NEW_LINE(filehandle,1);
1591:
1592: -- bufString:= bufString || 'INDEX MV_';
1593: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1586: bufString:= ' ';
1587: IF CreateMV= TRUE THEN
1588: UTL_FILE.NEW_LINE(filehandle,2);
1589: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
1590: UTL_FILE.NEW_LINE(filehandle,1);
1591:
1592: -- bufString:= bufString || 'INDEX MV_';
1593: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1594: UTL_FILE.PUT_LINE(filehandle,'( (CASE GID');
1590: UTL_FILE.NEW_LINE(filehandle,1);
1591:
1592: -- bufString:= bufString || 'INDEX MV_';
1593: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
1594: UTL_FILE.PUT_LINE(filehandle,'( (CASE GID');
1595:
1596:
1597: --Bug 8915090 bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;
1598:
1622: bufString:= bufString
1623: || 'TO_CHAR('
1624: || dbms_assert.enquote_name(tabCol(m).colName)
1625: || ') END) )';
1626: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
1627: UTL_FILE.NEW_LINE(filehandle,1);
1628: UTL_FILE.PUT_LINE(filehandle,')');
1629: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1630: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1623: || 'TO_CHAR('
1624: || dbms_assert.enquote_name(tabCol(m).colName)
1625: || ') END) )';
1626: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
1627: UTL_FILE.NEW_LINE(filehandle,1);
1628: UTL_FILE.PUT_LINE(filehandle,')');
1629: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1630: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1631:
1624: || dbms_assert.enquote_name(tabCol(m).colName)
1625: || ') END) )';
1626: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
1627: UTL_FILE.NEW_LINE(filehandle,1);
1628: UTL_FILE.PUT_LINE(filehandle,')');
1629: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1630: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1631:
1632: IF indspcName IS NOT NULL THEN
1625: || ') END) )';
1626: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
1627: UTL_FILE.NEW_LINE(filehandle,1);
1628: UTL_FILE.PUT_LINE(filehandle,')');
1629: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1630: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1631:
1632: IF indspcName IS NOT NULL THEN
1633: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1626: UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
1627: UTL_FILE.NEW_LINE(filehandle,1);
1628: UTL_FILE.PUT_LINE(filehandle,')');
1629: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1630: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1631:
1632: IF indspcName IS NOT NULL THEN
1633: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1634: UTL_FILE.NEW_LINE(filehandle,1);
1629: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
1630: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1631:
1632: IF indspcName IS NOT NULL THEN
1633: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1634: UTL_FILE.NEW_LINE(filehandle,1);
1635: END IF;
1636: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1637: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1630: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
1631:
1632: IF indspcName IS NOT NULL THEN
1633: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1634: UTL_FILE.NEW_LINE(filehandle,1);
1635: END IF;
1636: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1637: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1638: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1632: IF indspcName IS NOT NULL THEN
1633: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1634: UTL_FILE.NEW_LINE(filehandle,1);
1635: END IF;
1636: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1637: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1638: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1639: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1640: UTL_FILE.NEW_LINE(filehandle,2);
1633: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1634: UTL_FILE.NEW_LINE(filehandle,1);
1635: END IF;
1636: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1637: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1638: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1639: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1640: UTL_FILE.NEW_LINE(filehandle,2);
1641: ELSE
1634: UTL_FILE.NEW_LINE(filehandle,1);
1635: END IF;
1636: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1637: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1638: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1639: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1640: UTL_FILE.NEW_LINE(filehandle,2);
1641: ELSE
1642: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1635: END IF;
1636: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1637: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1638: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1639: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1640: UTL_FILE.NEW_LINE(filehandle,2);
1641: ELSE
1642: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1643: --Bug 8915090 bufString:= bufString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';
1636: UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
1637: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1638: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1639: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1640: UTL_FILE.NEW_LINE(filehandle,2);
1641: ELSE
1642: bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
1643: --Bug 8915090 bufString:= bufString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';
1644:
1645: bufString:= bufString
1646: || 'THEN TO_CHAR('
1647: || dbms_assert.enquote_name(tabCol(m).colName)
1648: || ') ';
1649: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1650: UTL_FILE.NEW_LINE(filehandle,1);
1651: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1652: UTL_FILE.NEW_LINE(filehandle,1);
1653: END IF;
1646: || 'THEN TO_CHAR('
1647: || dbms_assert.enquote_name(tabCol(m).colName)
1648: || ') ';
1649: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1650: UTL_FILE.NEW_LINE(filehandle,1);
1651: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1652: UTL_FILE.NEW_LINE(filehandle,1);
1653: END IF;
1654: m:= m + 1;
1647: || dbms_assert.enquote_name(tabCol(m).colName)
1648: || ') ';
1649: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1650: UTL_FILE.NEW_LINE(filehandle,1);
1651: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1652: UTL_FILE.NEW_LINE(filehandle,1);
1653: END IF;
1654: m:= m + 1;
1655: END LOOP;
1648: || ') ';
1649: UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
1650: UTL_FILE.NEW_LINE(filehandle,1);
1651: UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
1652: UTL_FILE.NEW_LINE(filehandle,1);
1653: END IF;
1654: m:= m + 1;
1655: END LOOP;
1656: END IF;
1658:
1659: partList.DELETE;
1660: parnum.DELETE;
1661: tabCol.DELETE;
1662: UTL_FILE.NEW_LINE(filehandle,2);
1663: IF CreateMV = TRUE THEN
1664: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1665: eOwnerName);
1666: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1660: parnum.DELETE;
1661: tabCol.DELETE;
1662: UTL_FILE.NEW_LINE(filehandle,2);
1663: IF CreateMV = TRUE THEN
1664: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1665: eOwnerName);
1666: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1667: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
1668: UTL_FILE.NEW_LINE(filehandle,1);
1662: UTL_FILE.NEW_LINE(filehandle,2);
1663: IF CreateMV = TRUE THEN
1664: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1665: eOwnerName);
1666: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1667: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
1668: UTL_FILE.NEW_LINE(filehandle,1);
1669: END IF;
1670: END LOOP;
1663: IF CreateMV = TRUE THEN
1664: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1665: eOwnerName);
1666: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1667: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
1668: UTL_FILE.NEW_LINE(filehandle,1);
1669: END IF;
1670: END LOOP;
1671:
1664: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1665: eOwnerName);
1666: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1667: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
1668: UTL_FILE.NEW_LINE(filehandle,1);
1669: END IF;
1670: END LOOP;
1671:
1672:
1672:
1673: -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
1674: -- INDEX_TEXT) VALUES
1675: -- (runid,i+ 100,eOwnerName,bufString);
1676: UTL_FILE.FCLOSE(filehandle);
1677: commit;
1678: EXCEPTION
1679: WHEN UTL_FILE.INVALID_PATH THEN
1680: UTL_FILE.FCLOSE(filehandle);
1675: -- (runid,i+ 100,eOwnerName,bufString);
1676: UTL_FILE.FCLOSE(filehandle);
1677: commit;
1678: EXCEPTION
1679: WHEN UTL_FILE.INVALID_PATH THEN
1680: UTL_FILE.FCLOSE(filehandle);
1681: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1682: WHEN UTL_FILE.INVALID_OPERATION THEN
1683: UTL_FILE.FCLOSE(filehandle);
1676: UTL_FILE.FCLOSE(filehandle);
1677: commit;
1678: EXCEPTION
1679: WHEN UTL_FILE.INVALID_PATH THEN
1680: UTL_FILE.FCLOSE(filehandle);
1681: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1682: WHEN UTL_FILE.INVALID_OPERATION THEN
1683: UTL_FILE.FCLOSE(filehandle);
1684: DBMS_OUTPUT.PUT_LINE('MV not created as only one level in Dimension ');
1678: EXCEPTION
1679: WHEN UTL_FILE.INVALID_PATH THEN
1680: UTL_FILE.FCLOSE(filehandle);
1681: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1682: WHEN UTL_FILE.INVALID_OPERATION THEN
1683: UTL_FILE.FCLOSE(filehandle);
1684: DBMS_OUTPUT.PUT_LINE('MV not created as only one level in Dimension ');
1685: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1686: UTL_FILE.FCLOSE(filehandle);
1679: WHEN UTL_FILE.INVALID_PATH THEN
1680: UTL_FILE.FCLOSE(filehandle);
1681: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1682: WHEN UTL_FILE.INVALID_OPERATION THEN
1683: UTL_FILE.FCLOSE(filehandle);
1684: DBMS_OUTPUT.PUT_LINE('MV not created as only one level in Dimension ');
1685: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1686: UTL_FILE.FCLOSE(filehandle);
1687: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1681: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1682: WHEN UTL_FILE.INVALID_OPERATION THEN
1683: UTL_FILE.FCLOSE(filehandle);
1684: DBMS_OUTPUT.PUT_LINE('MV not created as only one level in Dimension ');
1685: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1686: UTL_FILE.FCLOSE(filehandle);
1687: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1688: WHEN UTL_FILE.WRITE_ERROR THEN
1689: UTL_FILE.FCLOSE(filehandle);
1682: WHEN UTL_FILE.INVALID_OPERATION THEN
1683: UTL_FILE.FCLOSE(filehandle);
1684: DBMS_OUTPUT.PUT_LINE('MV not created as only one level in Dimension ');
1685: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1686: UTL_FILE.FCLOSE(filehandle);
1687: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1688: WHEN UTL_FILE.WRITE_ERROR THEN
1689: UTL_FILE.FCLOSE(filehandle);
1690: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1684: DBMS_OUTPUT.PUT_LINE('MV not created as only one level in Dimension ');
1685: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1686: UTL_FILE.FCLOSE(filehandle);
1687: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1688: WHEN UTL_FILE.WRITE_ERROR THEN
1689: UTL_FILE.FCLOSE(filehandle);
1690: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1691: WHEN UTL_FILE.INTERNAL_ERROR THEN
1692: UTL_FILE.FCLOSE(filehandle);
1685: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1686: UTL_FILE.FCLOSE(filehandle);
1687: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1688: WHEN UTL_FILE.WRITE_ERROR THEN
1689: UTL_FILE.FCLOSE(filehandle);
1690: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1691: WHEN UTL_FILE.INTERNAL_ERROR THEN
1692: UTL_FILE.FCLOSE(filehandle);
1693: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1687: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1688: WHEN UTL_FILE.WRITE_ERROR THEN
1689: UTL_FILE.FCLOSE(filehandle);
1690: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1691: WHEN UTL_FILE.INTERNAL_ERROR THEN
1692: UTL_FILE.FCLOSE(filehandle);
1693: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1694: WHEN others THEN
1695: UTL_FILE.FCLOSE(filehandle);
1688: WHEN UTL_FILE.WRITE_ERROR THEN
1689: UTL_FILE.FCLOSE(filehandle);
1690: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1691: WHEN UTL_FILE.INTERNAL_ERROR THEN
1692: UTL_FILE.FCLOSE(filehandle);
1693: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1694: WHEN others THEN
1695: UTL_FILE.FCLOSE(filehandle);
1696: -- RAISE;
1691: WHEN UTL_FILE.INTERNAL_ERROR THEN
1692: UTL_FILE.FCLOSE(filehandle);
1693: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1694: WHEN others THEN
1695: UTL_FILE.FCLOSE(filehandle);
1696: -- RAISE;
1697: END ;
1698:
1699: PROCEDURE CreateDimTab(runId IN NUMBER, exOwnerName IN varchar2,exDimName IN varchar2) IS
1699: PROCEDURE CreateDimTab(runId IN NUMBER, exOwnerName IN varchar2,exDimName IN varchar2) IS
1700:
1701: -- This it the Procedure to create a MV script for a given Dimension. This procedure writes to a
1702: -- table for OEM to pick up.
1703: -- filehandle UTL_FILE.FILE_TYPE;
1704:
1705: eDimName varchar2(30);
1706: eOwnerName varchar2(30);
1707: eMVName varchar2(30);
1973: -- eeOutPath:= '/users/mrangwal';
1974: -- eOutFile:= 'tete';
1975: eDimName:= UPPER(exDimName);
1976: eOwnerName:= UPPER(exOwnerName);
1977: -- IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
1978: -- filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
1979: -- END IF;
1980: -- tempName:= '';
1981: IF NOT curDispName%ISOPEN THEN
1974: -- eOutFile:= 'tete';
1975: eDimName:= UPPER(exDimName);
1976: eOwnerName:= UPPER(exOwnerName);
1977: -- IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
1978: -- filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
1979: -- END IF;
1980: -- tempName:= '';
1981: IF NOT curDispName%ISOPEN THEN
1982: OPEN curDispName;
1987: END LOOP;
1988: CLOSE curDispName;
1989:
1990: IF tempName IS NULL THEN
1991: RAISE UTL_FILE.INTERNAL_ERROR;
1992: END IF;
1993: dimList.EXTEND;
1994: i:= i + 1;
1995: dimList(i).dimName:= eDimName;
2089: eMVName:= '';
2090: eMVName:= SUBSTR(eDimName,0,5) || '_' || SUBSTR(mvdimList(i).hierName,0,5);
2091: eMVName:= eMVName || '_OLAP';
2092: --UTL_PUT(filehandle,buffer);
2093: -- UTL_FILE.PUT(filehandle,'create materialized view ');
2094: -- UTL_FILE.PUT_LINE(filehandle,eMVName);
2095: -- UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
2096: indexString:= '';
2097: indexString:= indexString || 'partition by range (gid) (';
2090: eMVName:= SUBSTR(eDimName,0,5) || '_' || SUBSTR(mvdimList(i).hierName,0,5);
2091: eMVName:= eMVName || '_OLAP';
2092: --UTL_PUT(filehandle,buffer);
2093: -- UTL_FILE.PUT(filehandle,'create materialized view ');
2094: -- UTL_FILE.PUT_LINE(filehandle,eMVName);
2095: -- UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
2096: indexString:= '';
2097: indexString:= indexString || 'partition by range (gid) (';
2098: o:= 0;
2091: eMVName:= eMVName || '_OLAP';
2092: --UTL_PUT(filehandle,buffer);
2093: -- UTL_FILE.PUT(filehandle,'create materialized view ');
2094: -- UTL_FILE.PUT_LINE(filehandle,eMVName);
2095: -- UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
2096: indexString:= '';
2097: indexString:= indexString || 'partition by range (gid) (';
2098: o:= 0;
2099: FOR j in 1..mvdimList.Count LOOP
2177: IF k < 800 THEN
2178: indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
2179:
2180: END IF;
2181: -- UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
2182: -- UTL_FILE.NEW_LINE(filehandle,1);
2183: END IF;
2184: END LOOP;
2185: indexString:= indexString || ' partition values less than(MAXVALUE))';
2178: indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
2179:
2180: END IF;
2181: -- UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
2182: -- UTL_FILE.NEW_LINE(filehandle,1);
2183: END IF;
2184: END LOOP;
2185: indexString:= indexString || ' partition values less than(MAXVALUE))';
2186: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
2182: -- UTL_FILE.NEW_LINE(filehandle,1);
2183: END IF;
2184: END LOOP;
2185: indexString:= indexString || ' partition values less than(MAXVALUE))';
2186: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
2187: -- IF mvspcName IS NOT NULL THEN
2188: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
2189: -- UTL_FILE.NEW_LINE(filehandle,1);
2190: -- END IF;
2184: END LOOP;
2185: indexString:= indexString || ' partition values less than(MAXVALUE))';
2186: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
2187: -- IF mvspcName IS NOT NULL THEN
2188: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
2189: -- UTL_FILE.NEW_LINE(filehandle,1);
2190: -- END IF;
2191: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2185: indexString:= indexString || ' partition values less than(MAXVALUE))';
2186: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
2187: -- IF mvspcName IS NOT NULL THEN
2188: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
2189: -- UTL_FILE.NEW_LINE(filehandle,1);
2190: -- END IF;
2191: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2187: -- IF mvspcName IS NOT NULL THEN
2188: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
2189: -- UTL_FILE.NEW_LINE(filehandle,1);
2190: -- END IF;
2191: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2188: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
2189: -- UTL_FILE.NEW_LINE(filehandle,1);
2190: -- END IF;
2191: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2189: -- UTL_FILE.NEW_LINE(filehandle,1);
2190: -- END IF;
2191: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2190: -- END IF;
2191: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2198: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2191: -- UTL_FILE.PUT(filehandle,'TABLESPACE');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2198: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2199: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
2192: -- UTL_FILE.PUT_LINE(filehandle,eOwnerName);
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2198: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2199: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
2200: -- UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
2193: -- UTL_FILE.PUT_LINE(filehandle,'BUILD IMMEDIATE');
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2198: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2199: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
2200: -- UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
2201:
2194: -- UTL_FILE.PUT_LINE(filehandle,'USING NO INDEX');
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2198: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2199: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
2200: -- UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
2201:
2202:
2195: -- UTL_FILE.PUT_LINE(filehandle,'REFRESH FORCE');
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2198: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2199: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
2200: -- UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
2201:
2202:
2203: bufString:= bufString || 'SELECT ';
2196: -- UTL_FILE.PUT_LINE(filehandle,'ENABLE QUERY REWRITE');
2197: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2198: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2199: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_STAR,');
2200: -- UTL_FILE.PUT(filehandle,'GROUPING_ID( ');
2201:
2202:
2203: bufString:= bufString || 'SELECT ';
2204: bufString:= bufString || 'COUNT(*) COUNT_STAR, ';
2227: FETCH curTabName into eTabName;
2228: EXIT WHEN curTabName%NOTFOUND;
2229: END LOOP;
2230: CLOSE curTabName;
2231: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
2232: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;
2233:
2234: bufString:= bufString
2235: || dbms_assert.enquote_name(eTabName)
2238:
2239: -- bufString:= bufString || eLevelColName;
2240: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
2241: bufString:= bufString || ', ';
2242: -- UTL_FILE.PUT_LINE(filehandle,',');
2243: END IF;
2244:
2245: ELSIF hierLevelList(l).dimName = eDimName AND
2246: hierLevelList(l).hierName IS NULL THEN
2261: FETCH curTabName into eTabName;
2262: EXIT WHEN curTabName%NOTFOUND;
2263: END LOOP;
2264: CLOSE curTabName;
2265: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
2266: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;
2267:
2268: bufString:= bufString
2269: || dbms_assert.enquote_name(eTabName)
2272:
2273: -- bufString:= bufString || eLevelColName;
2274: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
2275: bufString:= bufString || ', ';
2276: -- UTL_FILE.PUT_LINE(filehandle,',');
2277: END IF;
2278:
2279:
2280: END IF;
2280: END IF;
2281: END LOOP;
2282: END IF;
2283: END LOOP;
2284: -- UTL_FILE.PUT_LINE(filehandle,') gid,');
2285: bufString:= bufString || ') gid, ';
2286: FOR j in 1..mvdimList.Count LOOP
2287: IF mvdimList(j).mvNum= i THEN
2288: eeDimName:= mvdimList(j).dimName;
2322: || UPPER(SUBSTR(dbms_assert.enquote_name(eAttrName),0,5))
2323: || '_AR'
2324: || ',';
2325:
2326: -- UTL_FILE.PUT_LINE(filehandle,') gid');
2327: -- UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
2328: -- UTL_FILE.PUT_LINE(filehandle,',');
2329: END LOOP;
2330: CLOSE curAttrName;
2323: || '_AR'
2324: || ',';
2325:
2326: -- UTL_FILE.PUT_LINE(filehandle,') gid');
2327: -- UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
2328: -- UTL_FILE.PUT_LINE(filehandle,',');
2329: END LOOP;
2330: CLOSE curAttrName;
2331: ELSIF hierLevelList(l).dimName = eDimName AND
2324: || ',';
2325:
2326: -- UTL_FILE.PUT_LINE(filehandle,') gid');
2327: -- UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
2328: -- UTL_FILE.PUT_LINE(filehandle,',');
2329: END LOOP;
2330: CLOSE curAttrName;
2331: ELSIF hierLevelList(l).dimName = eDimName AND
2332: hierLevelList(l).hierName IS NULL THEN
2361: || dbms_assert.enquote_name(eLevelColName)
2362: || '_AR'
2363: || ',';
2364:
2365: -- UTL_FILE.PUT_LINE(filehandle,') gid');
2366: -- UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
2367: -- UTL_FILE.PUT_LINE(filehandle,',');
2368: END LOOP;
2369: CLOSE curAttrName;
2362: || '_AR'
2363: || ',';
2364:
2365: -- UTL_FILE.PUT_LINE(filehandle,') gid');
2366: -- UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
2367: -- UTL_FILE.PUT_LINE(filehandle,',');
2368: END LOOP;
2369: CLOSE curAttrName;
2370: END IF;
2363: || ',';
2364:
2365: -- UTL_FILE.PUT_LINE(filehandle,') gid');
2366: -- UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
2367: -- UTL_FILE.PUT_LINE(filehandle,',');
2368: END LOOP;
2369: CLOSE curAttrName;
2370: END IF;
2371: END LOOP;
2394: FETCH curTabName into eTabName;
2395: EXIT WHEN curTabName%NOTFOUND;
2396: END LOOP;
2397: CLOSE curTabName;
2398: -- UTL_FILE.PUTF(filehandle,'%s.%s %s', eTabName,eLevelColName,eLevelColName);
2399: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
2400: bufString:= bufString
2401: || dbms_assert.enquote_name(eTabName)
2402: || '.'
2405: || dbms_assert.enquote_name(eLevelColName);
2406: -- bufString:= bufString || eLevelColName || ' ' || eLevelColName;
2407: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
2408: bufString:= bufString || ', ';
2409: -- UTL_FILE.PUT_LINE(filehandle,', ');
2410: -- ELSE
2411: -- UTL_FILE.PUT_LINE(filehandle,'');
2412: -- bufString:= bufString || ' ';
2413: END IF;
2407: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
2408: bufString:= bufString || ', ';
2409: -- UTL_FILE.PUT_LINE(filehandle,', ');
2410: -- ELSE
2411: -- UTL_FILE.PUT_LINE(filehandle,'');
2412: -- bufString:= bufString || ' ';
2413: END IF;
2414: ELSIF hierLevelList(l).dimName = eDimName AND
2415: hierLevelList(l).hierName IS NULL THEN
2429: FETCH curTabName into eTabName;
2430: EXIT WHEN curTabName%NOTFOUND;
2431: END LOOP;
2432: CLOSE curTabName;
2433: -- UTL_FILE.PUTF(filehandle,'%s.%s %s', eTabName,eLevelColName,eLevelColName);
2434: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
2435: bufString:= bufString
2436: || dbms_assert.enquote_name(eTabName)
2437: || '.'
2441:
2442: -- bufString:= bufString || eLevelColName || ' ' || eLevelColName;
2443: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
2444: bufString:= bufString || ', ';
2445: -- UTL_FILE.PUT_LINE(filehandle,', ');
2446: -- ELSE
2447: -- UTL_FILE.PUT_LINE(filehandle,'');
2448: -- bufString:= bufString || ' ';
2449: END IF;
2443: IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
2444: bufString:= bufString || ', ';
2445: -- UTL_FILE.PUT_LINE(filehandle,', ');
2446: -- ELSE
2447: -- UTL_FILE.PUT_LINE(filehandle,'');
2448: -- bufString:= bufString || ' ';
2449: END IF;
2450: END IF;
2451: END LOOP;
2451: END LOOP;
2452: END IF;
2453: END LOOP;
2454: bufString:= bufString || ' FROM ';
2455: -- UTL_FILE.PUT_LINE(filehandle,'FROM ');
2456: o:= 0;
2457: FOR j in 1..mvdimList.Count LOOP
2458: IF mvdimList(j).mvNum= i THEN
2459: eeDimName:= mvdimList(j).dimName;
2478: FETCH curTabName into eTabName;
2479: EXIT WHEN curTabName%NOTFOUND;
2480: END LOOP;
2481: CLOSE curTabName;
2482: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2483: -- UTL_FILE.PUT_LINE(filehandle,' ');
2484: -- bufString:= bufString || eTabName || ' ';
2485: o:= o + 1;
2486: mvtableList.EXTEND;
2479: EXIT WHEN curTabName%NOTFOUND;
2480: END LOOP;
2481: CLOSE curTabName;
2482: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2483: -- UTL_FILE.PUT_LINE(filehandle,' ');
2484: -- bufString:= bufString || eTabName || ' ';
2485: o:= o + 1;
2486: mvtableList.EXTEND;
2487: mvtableList(o).tabName:= eTabName;
2486: mvtableList.EXTEND;
2487: mvtableList(o).tabName:= eTabName;
2488: mvtableList(o).ownerName:= eOwnerName;
2489: tableCount:= mvtableList.Count;
2490: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2491: -- UTL_FILE.PUT_LINE(filehandle,' ');
2492: -- bufString:= bufString || eTabName || ' ';
2493:
2494: ELSIF hierLevelList(l).dimName = eDimName AND
2487: mvtableList(o).tabName:= eTabName;
2488: mvtableList(o).ownerName:= eOwnerName;
2489: tableCount:= mvtableList.Count;
2490: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2491: -- UTL_FILE.PUT_LINE(filehandle,' ');
2492: -- bufString:= bufString || eTabName || ' ';
2493:
2494: ELSIF hierLevelList(l).dimName = eDimName AND
2495: hierLevelList(l).hierName IS NULL THEN
2510: EXIT WHEN curTabName%NOTFOUND;
2511: END LOOP;
2512: CLOSE curTabName;
2513:
2514: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2515: -- UTL_FILE.PUT_LINE(filehandle,' ');
2516: -- bufString:= bufString || eTabName || ' ';
2517: o:= o + 1;
2518: mvtableList.EXTEND;
2511: END LOOP;
2512: CLOSE curTabName;
2513:
2514: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2515: -- UTL_FILE.PUT_LINE(filehandle,' ');
2516: -- bufString:= bufString || eTabName || ' ';
2517: o:= o + 1;
2518: mvtableList.EXTEND;
2519: mvtableList(o).tabName:= eTabName;
2518: mvtableList.EXTEND;
2519: mvtableList(o).tabName:= eTabName;
2520: mvtableList(o).ownerName:= eOwnerName;
2521: tableCount:= mvtableList.Count;
2522: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2523: -- UTL_FILE.PUT_LINE(filehandle,' ');
2524: -- bufString:= bufString || eTabName || ' ';
2525:
2526: END IF;
2519: mvtableList(o).tabName:= eTabName;
2520: mvtableList(o).ownerName:= eOwnerName;
2521: tableCount:= mvtableList.Count;
2522: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2523: -- UTL_FILE.PUT_LINE(filehandle,' ');
2524: -- bufString:= bufString || eTabName || ' ';
2525:
2526: END IF;
2527: END LOOP;
2627: mvtableList.EXTEND;
2628: mvtableList(o).tabName:= eTabName;
2629: mvtableList(o).ownerName:= eOwnerName;
2630: END IF;
2631: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2632: -- UTL_FILE.PUT_LINE(filehandle,' ');
2633: -- bufString:= bufString || eTabName || ' ';
2634: END IF;
2635: ELSIF hierLevelList(l).dimName = eDimName AND
2628: mvtableList(o).tabName:= eTabName;
2629: mvtableList(o).ownerName:= eOwnerName;
2630: END IF;
2631: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2632: -- UTL_FILE.PUT_LINE(filehandle,' ');
2633: -- bufString:= bufString || eTabName || ' ';
2634: END IF;
2635: ELSIF hierLevelList(l).dimName = eDimName AND
2636: hierLevelList(l).hierName IS NULL THEN
2678: mvtableList.EXTEND;
2679: mvtableList(o).tabName:= eTabName;
2680: mvtableList(o).ownerName:= eOwnerName;
2681: END IF;
2682: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2683: -- UTL_FILE.PUT_LINE(filehandle,' ');
2684: -- bufString:= bufString || eTabName || ' ';
2685: END IF;
2686: END IF;
2679: mvtableList(o).tabName:= eTabName;
2680: mvtableList(o).ownerName:= eOwnerName;
2681: END IF;
2682: -- UTL_FILE.PUTF(filehandle,'%s,', eTabName);
2683: -- UTL_FILE.PUT_LINE(filehandle,' ');
2684: -- bufString:= bufString || eTabName || ' ';
2685: END IF;
2686: END IF;
2687: END LOOP;
2698: || ' '
2699: || dbms_assert.enquote_name(mvtableList(o).tabName)
2700: || ' ';
2701:
2702: -- UTL_FILE.PUTF(filehandle,'%s.%s %s ', mvtableList(o).ownerName,mvtableList(o).tabName,
2703: -- mvtableList(o).tabName);
2704:
2705: IF o < mvtableList.Count THEN
2706: bufString:= bufString || ', ';
2703: -- mvtableList(o).tabName);
2704:
2705: IF o < mvtableList.Count THEN
2706: bufString:= bufString || ', ';
2707: -- UTL_FILE.PUT(filehandle,',');
2708: END IF;
2709: -- UTL_FILE.PUT_LINE(filehandle,' ');
2710: bufString:= bufString || ' ';
2711: END LOOP;
2705: IF o < mvtableList.Count THEN
2706: bufString:= bufString || ', ';
2707: -- UTL_FILE.PUT(filehandle,',');
2708: END IF;
2709: -- UTL_FILE.PUT_LINE(filehandle,' ');
2710: bufString:= bufString || ' ';
2711: END LOOP;
2712: mvtableList.DELETE;
2713:
2752: childString:= eparLevelName;
2753: IF childString <> parentString THEN
2754: IF whereFlag = FALSE THEN
2755: bufString:= bufString || ' ' || 'WHERE ';
2756: -- UTL_FILE.PUT_LINE(filehandle,' WHERE');
2757: whereFlag:= TRUE;
2758: ELSE
2759: bufString:= bufString || ' ' || ' AND ';
2760: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2756: -- UTL_FILE.PUT_LINE(filehandle,' WHERE');
2757: whereFlag:= TRUE;
2758: ELSE
2759: bufString:= bufString || ' ' || ' AND ';
2760: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2761: END IF;
2762: -- UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
2763: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2764:
2758: ELSE
2759: bufString:= bufString || ' ' || ' AND ';
2760: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2761: END IF;
2762: -- UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
2763: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2764:
2765: bufString:= bufString
2766: || '('
2802: childString:= eparLevelName;
2803: IF childString <> parentString THEN
2804: IF whereFlag = FALSE THEN
2805: bufString:= bufString || ' ' || 'WHERE ';
2806: -- UTL_FILE.PUT_LINE(filehandle,' WHERE');
2807: whereFlag:= TRUE;
2808: ELSE
2809: bufString:= bufString || ' ' || ' AND ';
2810: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2806: -- UTL_FILE.PUT_LINE(filehandle,' WHERE');
2807: whereFlag:= TRUE;
2808: ELSE
2809: bufString:= bufString || ' ' || ' AND ';
2810: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2811: END IF;
2812: -- UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
2813: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2814:
2808: ELSE
2809: bufString:= bufString || ' ' || ' AND ';
2810: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2811: END IF;
2812: -- UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
2813: --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2814:
2815: bufString:= bufString
2816: || '('
2824: END LOOP;
2825: END IF;
2826: END LOOP;
2827:
2828: -- UTL_FILE.PUT_LINE(filehandle,'GROUP BY ');
2829: bufString:= bufString || 'GROUP BY ';
2830: FOR j in 1..mvdimList.Count LOOP
2831: IF mvdimList(j).mvNum= i THEN
2832: mvnum:= 0;
2861: EXIT WHEN curTabName%NOTFOUND;
2862: END LOOP;
2863: CLOSE curTabName;
2864: IF ePosition < poscount AND ePosition = 1 THEN
2865: -- UTL_FILE.PUT(filehandle,'(');
2866: bufString:= bufString || '(';
2867: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
2868: -- UTL_FILE.PUT(filehandle,', ');
2869: bufString:= bufString || ', ';
2864: IF ePosition < poscount AND ePosition = 1 THEN
2865: -- UTL_FILE.PUT(filehandle,'(');
2866: bufString:= bufString || '(';
2867: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
2868: -- UTL_FILE.PUT(filehandle,', ');
2869: bufString:= bufString || ', ';
2870: END IF;
2871: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
2872: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ;
2867: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
2868: -- UTL_FILE.PUT(filehandle,', ');
2869: bufString:= bufString || ', ';
2870: END IF;
2871: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
2872: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ;
2873:
2874: bufString:= bufString
2875: || dbms_assert.enquote_name(eTabName)
2880: END LOOP;
2881: CLOSE curColName;
2882: IF ePosition <> 1 THEN
2883: bufString:= bufString || ')';
2884: -- UTL_FILE.PUT(filehandle,')');
2885: END IF;
2886: IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
2887: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
2888: bufString:= bufString || ', ROLLUP( ';
2883: bufString:= bufString || ')';
2884: -- UTL_FILE.PUT(filehandle,')');
2885: END IF;
2886: IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
2887: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
2888: bufString:= bufString || ', ROLLUP( ';
2889: ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
2890: -- UTL_FILE.PUT(filehandle,', ');
2891: bufString:= bufString || ', ';
2886: IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
2887: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
2888: bufString:= bufString || ', ROLLUP( ';
2889: ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
2890: -- UTL_FILE.PUT(filehandle,', ');
2891: bufString:= bufString || ', ';
2892: ELSIF mvdimList(j).lvlCount= mvnum THEN
2893: -- UTL_FILE.PUT_LINE(filehandle,') ');
2894: bufString:= bufString || ') ';
2889: ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
2890: -- UTL_FILE.PUT(filehandle,', ');
2891: bufString:= bufString || ', ';
2892: ELSIF mvdimList(j).lvlCount= mvnum THEN
2893: -- UTL_FILE.PUT_LINE(filehandle,') ');
2894: bufString:= bufString || ') ';
2895: END IF;
2896: ELSIF hierLevelList(l).dimName = eDimName AND
2897: hierLevelList(l).hierName IS NULL THEN
2921: EXIT WHEN curTabName%NOTFOUND;
2922: END LOOP;
2923: CLOSE curTabName;
2924: -- IF ePosition < poscount AND ePosition = 1 THEN
2925: -- UTL_FILE.PUT(filehandle,'(');
2926: -- bufString:= bufString || '(';
2927: -- ELSIF ePosition <= poscount AND ePosition <> 1 THEN
2928: -- UTL_FILE.PUT(filehandle,', ');
2929: -- bufString:= bufString || ', ';
2924: -- IF ePosition < poscount AND ePosition = 1 THEN
2925: -- UTL_FILE.PUT(filehandle,'(');
2926: -- bufString:= bufString || '(';
2927: -- ELSIF ePosition <= poscount AND ePosition <> 1 THEN
2928: -- UTL_FILE.PUT(filehandle,', ');
2929: -- bufString:= bufString || ', ';
2930: -- END IF;
2931: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
2932: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ;
2927: -- ELSIF ePosition <= poscount AND ePosition <> 1 THEN
2928: -- UTL_FILE.PUT(filehandle,', ');
2929: -- bufString:= bufString || ', ';
2930: -- END IF;
2931: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
2932: --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ;
2933:
2934: bufString:= bufString
2935: || dbms_assert.enquote_name(eTabName)
2943: END LOOP;
2944: END IF;
2945: END LOOP;
2946: -- rankNum:= 0;
2947: -- UTL_FILE.PUT(filehandle,';');
2948: -- SELECT nvl(max(rank#), 100) INTO rankNum FROM SYSTEM.MVIEW$_ADV_OUTPUT
2949: -- WHERE RUNID# = runid;
2950: rankNum:= rankNum + 1;
2951: query_length:= LENGTH(bufString);
2981: -- (runId,i+100,eOwnerName,indexString);
2982: eTempMV:= '';
2983: bufString:= '';
2984:
2985: -- UTL_FILE.NEW_LINE(filehandle,2);
2986: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
2987: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2988: -- UTL_FILE.NEW_LINE(filehandle,1);
2989: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
2982: eTempMV:= '';
2983: bufString:= '';
2984:
2985: -- UTL_FILE.NEW_LINE(filehandle,2);
2986: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
2987: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2988: -- UTL_FILE.NEW_LINE(filehandle,1);
2989: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
2990: -- eOwnerName);
2984:
2985: -- UTL_FILE.NEW_LINE(filehandle,2);
2986: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
2987: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2988: -- UTL_FILE.NEW_LINE(filehandle,1);
2989: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
2990: -- eOwnerName);
2991: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
2992: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
2985: -- UTL_FILE.NEW_LINE(filehandle,2);
2986: -- UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
2987: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2988: -- UTL_FILE.NEW_LINE(filehandle,1);
2989: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
2990: -- eOwnerName);
2991: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
2992: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
2993: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2987: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2988: -- UTL_FILE.NEW_LINE(filehandle,1);
2989: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
2990: -- eOwnerName);
2991: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
2992: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
2993: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2994: -- UTL_FILE.NEW_LINE(filehandle,1);
2995:
2988: -- UTL_FILE.NEW_LINE(filehandle,1);
2989: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
2990: -- eOwnerName);
2991: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
2992: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
2993: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2994: -- UTL_FILE.NEW_LINE(filehandle,1);
2995:
2996: -- UTL_FILE.NEW_LINE(filehandle,2);
2990: -- eOwnerName);
2991: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
2992: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
2993: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2994: -- UTL_FILE.NEW_LINE(filehandle,1);
2995:
2996: -- UTL_FILE.NEW_LINE(filehandle,2);
2997: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
2998: -- UTL_FILE.NEW_LINE(filehandle,2);
2992: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree,method_opt=>''for all columns size skewonly'') ;');
2993: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2994: -- UTL_FILE.NEW_LINE(filehandle,1);
2995:
2996: -- UTL_FILE.NEW_LINE(filehandle,2);
2997: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
2998: -- UTL_FILE.NEW_LINE(filehandle,2);
2999:
3000:
2993: -- bufString:= bufString || 'ANALYZE TABLE COMPUTE STATISTICS; ';
2994: -- UTL_FILE.NEW_LINE(filehandle,1);
2995:
2996: -- UTL_FILE.NEW_LINE(filehandle,2);
2997: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
2998: -- UTL_FILE.NEW_LINE(filehandle,2);
2999:
3000:
3001: -- SELECT nvl(max(rank#), 100) INTO rankNum FROM SYSTEM.MVIEW$_ADV_INDEX
2994: -- UTL_FILE.NEW_LINE(filehandle,1);
2995:
2996: -- UTL_FILE.NEW_LINE(filehandle,2);
2997: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
2998: -- UTL_FILE.NEW_LINE(filehandle,2);
2999:
3000:
3001: -- SELECT nvl(max(rank#), 100) INTO rankNum FROM SYSTEM.MVIEW$_ADV_INDEX
3002: -- WHERE RUNID# = runid;
3028: EXIT WHEN curTabName%NOTFOUND;
3029: END LOOP;
3030: CLOSE curTabName;
3031: IF hierLevelList(l).levelNum <> 1 THEN
3032: -- UTL_FILE.NEW_LINE(filehandle,2);
3033: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3034: -- UTL_FILE.NEW_LINE(filehandle,1);
3035: -- IF indspcName IS NOT NULL THEN
3036: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3029: END LOOP;
3030: CLOSE curTabName;
3031: IF hierLevelList(l).levelNum <> 1 THEN
3032: -- UTL_FILE.NEW_LINE(filehandle,2);
3033: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3034: -- UTL_FILE.NEW_LINE(filehandle,1);
3035: -- IF indspcName IS NOT NULL THEN
3036: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3037: -- UTL_FILE.NEW_LINE(filehandle,1);
3030: CLOSE curTabName;
3031: IF hierLevelList(l).levelNum <> 1 THEN
3032: -- UTL_FILE.NEW_LINE(filehandle,2);
3033: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3034: -- UTL_FILE.NEW_LINE(filehandle,1);
3035: -- IF indspcName IS NOT NULL THEN
3036: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3037: -- UTL_FILE.NEW_LINE(filehandle,1);
3038: -- END IF;
3032: -- UTL_FILE.NEW_LINE(filehandle,2);
3033: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3034: -- UTL_FILE.NEW_LINE(filehandle,1);
3035: -- IF indspcName IS NOT NULL THEN
3036: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3037: -- UTL_FILE.NEW_LINE(filehandle,1);
3038: -- END IF;
3039: -- bufString:= bufString || 'INDEX MV_';
3040: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3033: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3034: -- UTL_FILE.NEW_LINE(filehandle,1);
3035: -- IF indspcName IS NOT NULL THEN
3036: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3037: -- UTL_FILE.NEW_LINE(filehandle,1);
3038: -- END IF;
3039: -- bufString:= bufString || 'INDEX MV_';
3040: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3041: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3037: -- UTL_FILE.NEW_LINE(filehandle,1);
3038: -- END IF;
3039: -- bufString:= bufString || 'INDEX MV_';
3040: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3041: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3042: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3043: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3044: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3045: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3038: -- END IF;
3039: -- bufString:= bufString || 'INDEX MV_';
3040: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3041: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3042: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3043: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3044: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3045: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3046: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3039: -- bufString:= bufString || 'INDEX MV_';
3040: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3041: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3042: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3043: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3044: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3045: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3046: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3047: -- UTL_FILE.NEW_LINE(filehandle,2);
3040: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3041: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3042: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3043: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3044: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3045: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3046: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3047: -- UTL_FILE.NEW_LINE(filehandle,2);
3048:
3041: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3042: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3043: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3044: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3045: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3046: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3047: -- UTL_FILE.NEW_LINE(filehandle,2);
3048:
3049: --Bug 8915090 bufString:= bufString || 'MV_' || eLevelColName || SUBSTR(mvdimList(j).hierName,0,2) || 'BI_' || counter;
3042: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3043: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3044: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3045: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3046: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3047: -- UTL_FILE.NEW_LINE(filehandle,2);
3048:
3049: --Bug 8915090 bufString:= bufString || 'MV_' || eLevelColName || SUBSTR(mvdimList(j).hierName,0,2) || 'BI_' || counter;
3050:
3043: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3044: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3045: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3046: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3047: -- UTL_FILE.NEW_LINE(filehandle,2);
3048:
3049: --Bug 8915090 bufString:= bufString || 'MV_' || eLevelColName || SUBSTR(mvdimList(j).hierName,0,2) || 'BI_' || counter;
3050:
3051: bufString:= bufString
3083: EXIT WHEN curTabName%NOTFOUND;
3084: END LOOP;
3085: CLOSE curTabName;
3086: IF hierLevelList(l).levelNum <> 1 THEN
3087: -- UTL_FILE.NEW_LINE(filehandle,2);
3088: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3089: -- UTL_FILE.NEW_LINE(filehandle,1);
3090: -- IF indspcName IS NOT NULL THEN
3091: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3084: END LOOP;
3085: CLOSE curTabName;
3086: IF hierLevelList(l).levelNum <> 1 THEN
3087: -- UTL_FILE.NEW_LINE(filehandle,2);
3088: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3089: -- UTL_FILE.NEW_LINE(filehandle,1);
3090: -- IF indspcName IS NOT NULL THEN
3091: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3092: -- UTL_FILE.NEW_LINE(filehandle,1);
3085: CLOSE curTabName;
3086: IF hierLevelList(l).levelNum <> 1 THEN
3087: -- UTL_FILE.NEW_LINE(filehandle,2);
3088: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3089: -- UTL_FILE.NEW_LINE(filehandle,1);
3090: -- IF indspcName IS NOT NULL THEN
3091: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3092: -- UTL_FILE.NEW_LINE(filehandle,1);
3093: -- END IF;
3087: -- UTL_FILE.NEW_LINE(filehandle,2);
3088: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3089: -- UTL_FILE.NEW_LINE(filehandle,1);
3090: -- IF indspcName IS NOT NULL THEN
3091: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3092: -- UTL_FILE.NEW_LINE(filehandle,1);
3093: -- END IF;
3094: -- bufString:= bufString || 'INDEX MV_';
3095: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3088: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
3089: -- UTL_FILE.NEW_LINE(filehandle,1);
3090: -- IF indspcName IS NOT NULL THEN
3091: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3092: -- UTL_FILE.NEW_LINE(filehandle,1);
3093: -- END IF;
3094: -- bufString:= bufString || 'INDEX MV_';
3095: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3096: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3092: -- UTL_FILE.NEW_LINE(filehandle,1);
3093: -- END IF;
3094: -- bufString:= bufString || 'INDEX MV_';
3095: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3096: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3097: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3098: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3099: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3100: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3093: -- END IF;
3094: -- bufString:= bufString || 'INDEX MV_';
3095: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3096: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3097: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3098: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3099: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3100: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3101: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3094: -- bufString:= bufString || 'INDEX MV_';
3095: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3096: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3097: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3098: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3099: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3100: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3101: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3102: -- UTL_FILE.NEW_LINE(filehandle,2);
3095: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3096: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3097: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3098: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3099: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3100: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3101: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3102: -- UTL_FILE.NEW_LINE(filehandle,2);
3103:
3096: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3097: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3098: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3099: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3100: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3101: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3102: -- UTL_FILE.NEW_LINE(filehandle,2);
3103:
3104: IF createMV = TRUE THEN
3097: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3098: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3099: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3100: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3101: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3102: -- UTL_FILE.NEW_LINE(filehandle,2);
3103:
3104: IF createMV = TRUE THEN
3105:
3098: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3099: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3100: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3101: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3102: -- UTL_FILE.NEW_LINE(filehandle,2);
3103:
3104: IF createMV = TRUE THEN
3105:
3106: mvnum:= mvnum + 1;
3120: tpcount:= i;
3121:
3122: END IF;
3123:
3124: -- UTL_FILE.NEW_LINE(filehandle,1);
3125: -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
3126: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
3127: -- bufString:= bufString || 'PARALLEL NOLOGGING; ';
3128: -- IF dupdimmvFlag= TRUE THEN
3138: END LOOP;
3139:
3140: FOR j in 1..mvdimList.Count LOOP
3141: IF mvdimList(j).mvNum= i THEN
3142: -- UTL_FILE.NEW_LINE(filehandle,2);
3143: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
3144: -- UTL_FILE.NEW_LINE(filehandle,1);
3145: --Bug 8915090 bufString:= bufString || 'MV_GID_' || SUBSTR(mvdimList(j).hierName,0,2) || '_BI_' || counter;
3146:
3139:
3140: FOR j in 1..mvdimList.Count LOOP
3141: IF mvdimList(j).mvNum= i THEN
3142: -- UTL_FILE.NEW_LINE(filehandle,2);
3143: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
3144: -- UTL_FILE.NEW_LINE(filehandle,1);
3145: --Bug 8915090 bufString:= bufString || 'MV_GID_' || SUBSTR(mvdimList(j).hierName,0,2) || '_BI_' || counter;
3146:
3147: bufString:= bufString
3140: FOR j in 1..mvdimList.Count LOOP
3141: IF mvdimList(j).mvNum= i THEN
3142: -- UTL_FILE.NEW_LINE(filehandle,2);
3143: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
3144: -- UTL_FILE.NEW_LINE(filehandle,1);
3145: --Bug 8915090 bufString:= bufString || 'MV_GID_' || SUBSTR(mvdimList(j).hierName,0,2) || '_BI_' || counter;
3146:
3147: bufString:= bufString
3148: || 'MV_GID_'
3150: || '_BI_'
3151: || counter;
3152:
3153: -- IF indspcName IS NOT NULL THEN
3154: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3155: -- UTL_FILE.NEW_LINE(filehandle,1);
3156: -- END IF;
3157: -- bufString:= bufString || 'INDEX MV_';
3158: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3151: || counter;
3152:
3153: -- IF indspcName IS NOT NULL THEN
3154: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3155: -- UTL_FILE.NEW_LINE(filehandle,1);
3156: -- END IF;
3157: -- bufString:= bufString || 'INDEX MV_';
3158: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3159: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3155: -- UTL_FILE.NEW_LINE(filehandle,1);
3156: -- END IF;
3157: -- bufString:= bufString || 'INDEX MV_';
3158: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3159: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3160: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3161: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3162: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3163: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3156: -- END IF;
3157: -- bufString:= bufString || 'INDEX MV_';
3158: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3159: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3160: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3161: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3162: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3163: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3164: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3157: -- bufString:= bufString || 'INDEX MV_';
3158: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3159: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3160: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3161: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3162: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3163: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3164: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3165: -- UTL_FILE.NEW_LINE(filehandle,2);
3158: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3159: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3160: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3161: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3162: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3163: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3164: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3165: -- UTL_FILE.NEW_LINE(filehandle,2);
3166:
3159: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3160: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3161: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3162: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3163: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3164: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3165: -- UTL_FILE.NEW_LINE(filehandle,2);
3166:
3167:
3160: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3161: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3162: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3163: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3164: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3165: -- UTL_FILE.NEW_LINE(filehandle,2);
3166:
3167:
3168: IF createMV = TRUE THEN
3161: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3162: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3163: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3164: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3165: -- UTL_FILE.NEW_LINE(filehandle,2);
3166:
3167:
3168: IF createMV = TRUE THEN
3169: mvnum:= mvnum + 1;
3198: FETCH curTabName into eTabName;
3199: EXIT WHEN curTabName%NOTFOUND;
3200: END LOOP;
3201: CLOSE curTabName;
3202: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
3203: m:= m + 1;
3204: tabcol.EXTEND;
3205: -- tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;
3206:
3223: FETCH curTabName into eTabName;
3224: EXIT WHEN curTabName%NOTFOUND;
3225: END LOOP;
3226: CLOSE curTabName;
3227: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
3228: m:= m + 1;
3229: tabcol.EXTEND;
3230: -- tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;
3231:
3234: END LOOP;
3235: END IF;
3236: END LOOP;
3237:
3238: --UTL_FILE.NEW_LINE(filehandle,2);
3239: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
3240: -- UTL_FILE.NEW_LINE(filehandle,1);
3241: -- bufString:= bufString || 'INDEX MV_';
3242: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3235: END IF;
3236: END LOOP;
3237:
3238: --UTL_FILE.NEW_LINE(filehandle,2);
3239: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
3240: -- UTL_FILE.NEW_LINE(filehandle,1);
3241: -- bufString:= bufString || 'INDEX MV_';
3242: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3243: -- UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');
3236: END LOOP;
3237:
3238: --UTL_FILE.NEW_LINE(filehandle,2);
3239: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
3240: -- UTL_FILE.NEW_LINE(filehandle,1);
3241: -- bufString:= bufString || 'INDEX MV_';
3242: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3243: -- UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');
3244:
3239: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
3240: -- UTL_FILE.NEW_LINE(filehandle,1);
3241: -- bufString:= bufString || 'INDEX MV_';
3242: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3243: -- UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');
3244:
3245: --Bug 8915090 bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;
3246:
3247: bufString:= bufString
3250:
3251: -- bufString:= bufString || 'PREL_FI ON ' || eMVName;
3252: indexString:= indexString || '( (CASE SYS_OP_PARGID(GID';
3253: FOR n in 1..times LOOP
3254: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3255: indexString:= indexString || ', ' || tabCol(n).colName;
3256: END LOOP;
3257: -- UTL_FILE.PUT_LINE(filehandle,')');
3258: -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
3253: FOR n in 1..times LOOP
3254: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3255: indexString:= indexString || ', ' || tabCol(n).colName;
3256: END LOOP;
3257: -- UTL_FILE.PUT_LINE(filehandle,')');
3258: -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
3259: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
3260: -- bufString:= bufString || 'PARALLEL NOLOGGING; ';
3261:
3264: FOR k in REVERSE 1..times LOOP
3265: IF k = times THEN
3266: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3267: indexString:= indexString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3268: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3269: -- UTL_FILE.NEW_LINE(filehandle,1);
3270: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3271: FOR n in 1..times LOOP
3272: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3265: IF k = times THEN
3266: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3267: indexString:= indexString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3268: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3269: -- UTL_FILE.NEW_LINE(filehandle,1);
3270: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3271: FOR n in 1..times LOOP
3272: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3273: indexString:= indexString || ', ' || tabCol(n).colName;
3266: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3267: indexString:= indexString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3268: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3269: -- UTL_FILE.NEW_LINE(filehandle,1);
3270: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3271: FOR n in 1..times LOOP
3272: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3273: indexString:= indexString || ', ' || tabCol(n).colName;
3274: END LOOP;
3268: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3269: -- UTL_FILE.NEW_LINE(filehandle,1);
3270: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3271: FOR n in 1..times LOOP
3272: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3273: indexString:= indexString || ', ' || tabCol(n).colName;
3274: END LOOP;
3275: -- UTL_FILE.PUT_LINE(filehandle,'))');
3276: indexString:= indexString || '))';
3271: FOR n in 1..times LOOP
3272: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3273: indexString:= indexString || ', ' || tabCol(n).colName;
3274: END LOOP;
3275: -- UTL_FILE.PUT_LINE(filehandle,'))');
3276: indexString:= indexString || '))';
3277: ELSIF k = 1 THEN
3278: indexString:= indexString || ' ELSE ';
3279: -- indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(GID)';
3277: ELSIF k = 1 THEN
3278: indexString:= indexString || ' ELSE ';
3279: -- indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(GID)';
3280: indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3281: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3282: -- UTL_FILE.NEW_LINE(filehandle,1);
3283: -- UTL_FILE.PUT(filehandle,'(ELSE TO_CHAR(CASE SYS_OP_PAR(GID');
3284: FOR n in 1..times LOOP
3285: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3278: indexString:= indexString || ' ELSE ';
3279: -- indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(GID)';
3280: indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3281: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3282: -- UTL_FILE.NEW_LINE(filehandle,1);
3283: -- UTL_FILE.PUT(filehandle,'(ELSE TO_CHAR(CASE SYS_OP_PAR(GID');
3284: FOR n in 1..times LOOP
3285: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3286: indexString:= indexString || ',' || tabCol(n).colName;
3279: -- indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(GID)';
3280: indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3281: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3282: -- UTL_FILE.NEW_LINE(filehandle,1);
3283: -- UTL_FILE.PUT(filehandle,'(ELSE TO_CHAR(CASE SYS_OP_PAR(GID');
3284: FOR n in 1..times LOOP
3285: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3286: indexString:= indexString || ',' || tabCol(n).colName;
3287: END LOOP;
3281: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3282: -- UTL_FILE.NEW_LINE(filehandle,1);
3283: -- UTL_FILE.PUT(filehandle,'(ELSE TO_CHAR(CASE SYS_OP_PAR(GID');
3284: FOR n in 1..times LOOP
3285: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3286: indexString:= indexString || ',' || tabCol(n).colName;
3287: END LOOP;
3288: -- UTL_FILE.PUT_LINE(filehandle,'))' END);
3289: indexString:= indexString || ')) END))';
3284: FOR n in 1..times LOOP
3285: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3286: indexString:= indexString || ',' || tabCol(n).colName;
3287: END LOOP;
3288: -- UTL_FILE.PUT_LINE(filehandle,'))' END);
3289: indexString:= indexString || ')) END))';
3290: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3291: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3292: -- IF indspcName IS NOT NULL THEN
3286: indexString:= indexString || ',' || tabCol(n).colName;
3287: END LOOP;
3288: -- UTL_FILE.PUT_LINE(filehandle,'))' END);
3289: indexString:= indexString || ')) END))';
3290: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3291: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3292: -- IF indspcName IS NOT NULL THEN
3293: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3294: -- UTL_FILE.NEW_LINE(filehandle,1);
3287: END LOOP;
3288: -- UTL_FILE.PUT_LINE(filehandle,'))' END);
3289: indexString:= indexString || ')) END))';
3290: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3291: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3292: -- IF indspcName IS NOT NULL THEN
3293: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3294: -- UTL_FILE.NEW_LINE(filehandle,1);
3295: -- END IF;
3289: indexString:= indexString || ')) END))';
3290: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3291: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3292: -- IF indspcName IS NOT NULL THEN
3293: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3294: -- UTL_FILE.NEW_LINE(filehandle,1);
3295: -- END IF;
3296: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3297: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3290: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3291: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3292: -- IF indspcName IS NOT NULL THEN
3293: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3294: -- UTL_FILE.NEW_LINE(filehandle,1);
3295: -- END IF;
3296: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3297: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3298: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3292: -- IF indspcName IS NOT NULL THEN
3293: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3294: -- UTL_FILE.NEW_LINE(filehandle,1);
3295: -- END IF;
3296: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3297: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3298: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3299: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3300: -- UTL_FILE.NEW_LINE(filehandle,2);
3293: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3294: -- UTL_FILE.NEW_LINE(filehandle,1);
3295: -- END IF;
3296: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3297: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3298: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3299: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3300: -- UTL_FILE.NEW_LINE(filehandle,2);
3301: ELSE
3294: -- UTL_FILE.NEW_LINE(filehandle,1);
3295: -- END IF;
3296: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3297: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3298: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3299: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3300: -- UTL_FILE.NEW_LINE(filehandle,2);
3301: ELSE
3302: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3295: -- END IF;
3296: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3297: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3298: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3299: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3300: -- UTL_FILE.NEW_LINE(filehandle,2);
3301: ELSE
3302: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3303: -- indexString:= indexString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';
3296: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3297: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3298: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3299: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3300: -- UTL_FILE.NEW_LINE(filehandle,2);
3301: ELSE
3302: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3303: -- indexString:= indexString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';
3304: indexString:= indexString || 'ELSE TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3302: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3303: -- indexString:= indexString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';
3304: indexString:= indexString || 'ELSE TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3305:
3306: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3307: -- UTL_FILE.NEW_LINE(filehandle,1);
3308: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3303: -- indexString:= indexString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';
3304: indexString:= indexString || 'ELSE TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3305:
3306: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3307: -- UTL_FILE.NEW_LINE(filehandle,1);
3308: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3304: indexString:= indexString || 'ELSE TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
3305:
3306: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3307: -- UTL_FILE.NEW_LINE(filehandle,1);
3308: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3312: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
3305:
3306: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3307: -- UTL_FILE.NEW_LINE(filehandle,1);
3308: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3312: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
3313: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3306: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3307: -- UTL_FILE.NEW_LINE(filehandle,1);
3308: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3312: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
3313: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3314: FOR n in 1..times LOOP
3307: -- UTL_FILE.NEW_LINE(filehandle,1);
3308: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3312: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
3313: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3314: FOR n in 1..times LOOP
3315: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3308: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3312: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
3313: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3314: FOR n in 1..times LOOP
3315: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3316: indexString:= indexString || ',' || tabCol(n).colName;
3309: -- UTL_FILE.NEW_LINE(filehandle,1);
3310: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3312: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
3313: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3314: FOR n in 1..times LOOP
3315: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3316: indexString:= indexString || ',' || tabCol(n).colName;
3317: END LOOP;
3311: -- UTL_FILE.NEW_LINE(filehandle,1);
3312: -- UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
3313: -- UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
3314: FOR n in 1..times LOOP
3315: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3316: indexString:= indexString || ',' || tabCol(n).colName;
3317: END LOOP;
3318: -- UTL_FILE.PUT_LINE(filehandle,'))');
3319: indexString:= indexString || '))';
3314: FOR n in 1..times LOOP
3315: -- UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
3316: indexString:= indexString || ',' || tabCol(n).colName;
3317: END LOOP;
3318: -- UTL_FILE.PUT_LINE(filehandle,'))');
3319: indexString:= indexString || '))';
3320: END IF;
3321: m:= m + 1;
3322: END LOOP;
3329: (runid,rankNum,mvnum,1,bufString,eLevelColName,eOwnerName,indexString);
3330: END IF;
3331: bufString:= ' ';
3332: indexString:= ' ';
3333: -- UTL_FILE.NEW_LINE(filehandle,2);
3334: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
3335: -- UTL_FILE.NEW_LINE(filehandle,1);
3336:
3337: -- bufString:= bufString || 'INDEX MV_';
3330: END IF;
3331: bufString:= ' ';
3332: indexString:= ' ';
3333: -- UTL_FILE.NEW_LINE(filehandle,2);
3334: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
3335: -- UTL_FILE.NEW_LINE(filehandle,1);
3336:
3337: -- bufString:= bufString || 'INDEX MV_';
3338: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3331: bufString:= ' ';
3332: indexString:= ' ';
3333: -- UTL_FILE.NEW_LINE(filehandle,2);
3334: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
3335: -- UTL_FILE.NEW_LINE(filehandle,1);
3336:
3337: -- bufString:= bufString || 'INDEX MV_';
3338: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3339: -- UTL_FILE.PUT_LINE(filehandle,'( (CASE GID');
3335: -- UTL_FILE.NEW_LINE(filehandle,1);
3336:
3337: -- bufString:= bufString || 'INDEX MV_';
3338: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
3339: -- UTL_FILE.PUT_LINE(filehandle,'( (CASE GID');
3340:
3341:
3342: -- bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;
3343: --Bug 8915090 bufString:= bufString || 'MV_' || eMVName || '_ET_FI';
3358: FOR k in REVERSE 1..times LOOP
3359: IF k = 1 THEN
3360: indexString:= indexString || ' ELSE ';
3361: indexString:= indexString || 'TO_CHAR(' || tabCol(m).colName || ') END) )';
3362: -- UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
3363: -- UTL_FILE.NEW_LINE(filehandle,1);
3364: -- UTL_FILE.PUT_LINE(filehandle,')');
3365: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3366: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3359: IF k = 1 THEN
3360: indexString:= indexString || ' ELSE ';
3361: indexString:= indexString || 'TO_CHAR(' || tabCol(m).colName || ') END) )';
3362: -- UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
3363: -- UTL_FILE.NEW_LINE(filehandle,1);
3364: -- UTL_FILE.PUT_LINE(filehandle,')');
3365: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3366: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3367:
3360: indexString:= indexString || ' ELSE ';
3361: indexString:= indexString || 'TO_CHAR(' || tabCol(m).colName || ') END) )';
3362: -- UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
3363: -- UTL_FILE.NEW_LINE(filehandle,1);
3364: -- UTL_FILE.PUT_LINE(filehandle,')');
3365: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3366: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3367:
3368: -- IF indspcName IS NOT NULL THEN
3361: indexString:= indexString || 'TO_CHAR(' || tabCol(m).colName || ') END) )';
3362: -- UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
3363: -- UTL_FILE.NEW_LINE(filehandle,1);
3364: -- UTL_FILE.PUT_LINE(filehandle,')');
3365: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3366: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3367:
3368: -- IF indspcName IS NOT NULL THEN
3369: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3362: -- UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
3363: -- UTL_FILE.NEW_LINE(filehandle,1);
3364: -- UTL_FILE.PUT_LINE(filehandle,')');
3365: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3366: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3367:
3368: -- IF indspcName IS NOT NULL THEN
3369: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3370: -- UTL_FILE.NEW_LINE(filehandle,1);
3365: -- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
3366: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3367:
3368: -- IF indspcName IS NOT NULL THEN
3369: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3370: -- UTL_FILE.NEW_LINE(filehandle,1);
3371: -- END IF;
3372: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3373: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3366: -- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
3367:
3368: -- IF indspcName IS NOT NULL THEN
3369: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3370: -- UTL_FILE.NEW_LINE(filehandle,1);
3371: -- END IF;
3372: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3373: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3374: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3368: -- IF indspcName IS NOT NULL THEN
3369: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3370: -- UTL_FILE.NEW_LINE(filehandle,1);
3371: -- END IF;
3372: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3373: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3374: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3375: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3376: -- UTL_FILE.NEW_LINE(filehandle,2);
3369: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3370: -- UTL_FILE.NEW_LINE(filehandle,1);
3371: -- END IF;
3372: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3373: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3374: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3375: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3376: -- UTL_FILE.NEW_LINE(filehandle,2);
3377: ELSE
3370: -- UTL_FILE.NEW_LINE(filehandle,1);
3371: -- END IF;
3372: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3373: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3374: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3375: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3376: -- UTL_FILE.NEW_LINE(filehandle,2);
3377: ELSE
3378: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3371: -- END IF;
3372: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3373: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3374: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3375: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3376: -- UTL_FILE.NEW_LINE(filehandle,2);
3377: ELSE
3378: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3379: indexString:= indexString || ' THEN TO_CHAR(' || tabCol(m).colName || ') ';
3372: -- UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
3373: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3374: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3375: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3376: -- UTL_FILE.NEW_LINE(filehandle,2);
3377: ELSE
3378: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3379: indexString:= indexString || ' THEN TO_CHAR(' || tabCol(m).colName || ') ';
3380: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3376: -- UTL_FILE.NEW_LINE(filehandle,2);
3377: ELSE
3378: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3379: indexString:= indexString || ' THEN TO_CHAR(' || tabCol(m).colName || ') ';
3380: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3381: -- UTL_FILE.NEW_LINE(filehandle,1);
3382: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3383: -- UTL_FILE.NEW_LINE(filehandle,1);
3384: END IF;
3377: ELSE
3378: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3379: indexString:= indexString || ' THEN TO_CHAR(' || tabCol(m).colName || ') ';
3380: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3381: -- UTL_FILE.NEW_LINE(filehandle,1);
3382: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3383: -- UTL_FILE.NEW_LINE(filehandle,1);
3384: END IF;
3385: m:= m + 1;
3378: indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
3379: indexString:= indexString || ' THEN TO_CHAR(' || tabCol(m).colName || ') ';
3380: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3381: -- UTL_FILE.NEW_LINE(filehandle,1);
3382: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3383: -- UTL_FILE.NEW_LINE(filehandle,1);
3384: END IF;
3385: m:= m + 1;
3386: END LOOP;
3379: indexString:= indexString || ' THEN TO_CHAR(' || tabCol(m).colName || ') ';
3380: -- UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
3381: -- UTL_FILE.NEW_LINE(filehandle,1);
3382: -- UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
3383: -- UTL_FILE.NEW_LINE(filehandle,1);
3384: END IF;
3385: m:= m + 1;
3386: END LOOP;
3387: eLevelColName:= '';
3396: indexString:= ' ';
3397: partList.DELETE;
3398: parnum.DELETE;
3399: tabCol.DELETE;
3400: -- UTL_FILE.NEW_LINE(filehandle,2);
3401: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3402: -- eOwnerName);
3403: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3404: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
3397: partList.DELETE;
3398: parnum.DELETE;
3399: tabCol.DELETE;
3400: -- UTL_FILE.NEW_LINE(filehandle,2);
3401: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3402: -- eOwnerName);
3403: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3404: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
3405: -- UTL_FILE.NEW_LINE(filehandle,1);
3399: tabCol.DELETE;
3400: -- UTL_FILE.NEW_LINE(filehandle,2);
3401: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3402: -- eOwnerName);
3403: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3404: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
3405: -- UTL_FILE.NEW_LINE(filehandle,1);
3406:
3407: END LOOP;
3400: -- UTL_FILE.NEW_LINE(filehandle,2);
3401: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3402: -- eOwnerName);
3403: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3404: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
3405: -- UTL_FILE.NEW_LINE(filehandle,1);
3406:
3407: END LOOP;
3408: -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
3401: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3402: -- eOwnerName);
3403: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3404: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'') ;');
3405: -- UTL_FILE.NEW_LINE(filehandle,1);
3406:
3407: END LOOP;
3408: -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
3409: -- INDEX_TEXT) VALUES
3407: END LOOP;
3408: -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
3409: -- INDEX_TEXT) VALUES
3410: -- (runid,i+ 100,eOwnerName,bufString);
3411: -- UTL_FILE.FCLOSE(filehandle);
3412: commit;
3413: EXCEPTION
3414: -- WHEN UTL_FILE.INVALID_PATH THEN
3415: -- UTL_FILE.FCLOSE(filehandle);
3410: -- (runid,i+ 100,eOwnerName,bufString);
3411: -- UTL_FILE.FCLOSE(filehandle);
3412: commit;
3413: EXCEPTION
3414: -- WHEN UTL_FILE.INVALID_PATH THEN
3415: -- UTL_FILE.FCLOSE(filehandle);
3416: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3417: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3418: -- UTL_FILE.FCLOSE(filehandle);
3411: -- UTL_FILE.FCLOSE(filehandle);
3412: commit;
3413: EXCEPTION
3414: -- WHEN UTL_FILE.INVALID_PATH THEN
3415: -- UTL_FILE.FCLOSE(filehandle);
3416: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3417: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3418: -- UTL_FILE.FCLOSE(filehandle);
3419: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3413: EXCEPTION
3414: -- WHEN UTL_FILE.INVALID_PATH THEN
3415: -- UTL_FILE.FCLOSE(filehandle);
3416: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3417: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3418: -- UTL_FILE.FCLOSE(filehandle);
3419: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3420: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3421: -- UTL_FILE.FCLOSE(filehandle);
3414: -- WHEN UTL_FILE.INVALID_PATH THEN
3415: -- UTL_FILE.FCLOSE(filehandle);
3416: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3417: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3418: -- UTL_FILE.FCLOSE(filehandle);
3419: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3420: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3421: -- UTL_FILE.FCLOSE(filehandle);
3422: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3416: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3417: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3418: -- UTL_FILE.FCLOSE(filehandle);
3419: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3420: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3421: -- UTL_FILE.FCLOSE(filehandle);
3422: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3423: -- WHEN UTL_FILE.WRITE_ERROR THEN
3424: -- UTL_FILE.FCLOSE(filehandle);
3417: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3418: -- UTL_FILE.FCLOSE(filehandle);
3419: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3420: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3421: -- UTL_FILE.FCLOSE(filehandle);
3422: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3423: -- WHEN UTL_FILE.WRITE_ERROR THEN
3424: -- UTL_FILE.FCLOSE(filehandle);
3425: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3419: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3420: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3421: -- UTL_FILE.FCLOSE(filehandle);
3422: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3423: -- WHEN UTL_FILE.WRITE_ERROR THEN
3424: -- UTL_FILE.FCLOSE(filehandle);
3425: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3426: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3427: -- UTL_FILE.FCLOSE(filehandle);
3420: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3421: -- UTL_FILE.FCLOSE(filehandle);
3422: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3423: -- WHEN UTL_FILE.WRITE_ERROR THEN
3424: -- UTL_FILE.FCLOSE(filehandle);
3425: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3426: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3427: -- UTL_FILE.FCLOSE(filehandle);
3428: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3422: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3423: -- WHEN UTL_FILE.WRITE_ERROR THEN
3424: -- UTL_FILE.FCLOSE(filehandle);
3425: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3426: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3427: -- UTL_FILE.FCLOSE(filehandle);
3428: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3429: WHEN others THEN
3430: -- UTL_FILE.FCLOSE(filehandle);
3423: -- WHEN UTL_FILE.WRITE_ERROR THEN
3424: -- UTL_FILE.FCLOSE(filehandle);
3425: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3426: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3427: -- UTL_FILE.FCLOSE(filehandle);
3428: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3429: WHEN others THEN
3430: -- UTL_FILE.FCLOSE(filehandle);
3431: RAISE;
3426: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3427: -- UTL_FILE.FCLOSE(filehandle);
3428: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3429: WHEN others THEN
3430: -- UTL_FILE.FCLOSE(filehandle);
3431: RAISE;
3432: END ;
3433:
3434: END;