eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

OLAPSYS.OLAPFACTVIEW dependencies on UTL_FILE

Line 9: filehandle UTL_FILE.FILE_TYPE;

5: -- This it the Procedure to Create Cube MV in ROLLUP form for a Cube. This procedure is used to write to a file
6: -- To run it do the following
7:
8:
9: filehandle UTL_FILE.FILE_TYPE;
10: eOwnerName varchar2(30);
11: eCubeName varchar2(30);
12: eMVName varchar2(30);
13: -- eOutPath varchar2(30);

Line 259: IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN

255: -- eOutPath:= '/users/oracle';
256: cwm2_olap_metadata_refresh.mr_refresh;
257: eOwnerName:= UPPER(exOwnerName);
258: eCubeName:= UPPER(exCubeName);
259: IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
260: filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
261: END IF;
262: IF NOT curFactName%ISOPEN THEN
263: OPEN curFactName;

Line 260: filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');

256: cwm2_olap_metadata_refresh.mr_refresh;
257: eOwnerName:= UPPER(exOwnerName);
258: eCubeName:= UPPER(exCubeName);
259: IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
260: filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
261: END IF;
262: IF NOT curFactName%ISOPEN THEN
263: OPEN curFactName;
264: LOOP

Line 271: RAISE UTL_FILE.INTERNAL_ERROR;

267: END LOOP;
268: CLOSE CurFactName;
269: END IF;
270: IF eFactName IS NULL THEN
271: RAISE UTL_FILE.INTERNAL_ERROR;
272: END IF;
273: IF NOT curFactName%ISOPEN THEN
274: OPEN curFactName;
275: LOOP

Line 553: UTL_FILE.PUT_LINE(filehandle,'create materialized view');

549: -- (runid,1);
550: FOR i in 1..numofMV LOOP
551: --UTL_PUT(filehandle,buffer)
552: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
553: UTL_FILE.PUT_LINE(filehandle,'create materialized view');
554: UTL_FILE.PUT(filehandle,eMVName);
555: UTL_FILE.NEW_LINE(filehandle,1);
556: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
557: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 554: UTL_FILE.PUT(filehandle,eMVName);

550: FOR i in 1..numofMV LOOP
551: --UTL_PUT(filehandle,buffer)
552: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
553: UTL_FILE.PUT_LINE(filehandle,'create materialized view');
554: UTL_FILE.PUT(filehandle,eMVName);
555: UTL_FILE.NEW_LINE(filehandle,1);
556: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
557: -- UTL_FILE.NEW_LINE(filehandle,1);
558: -- UTL_FILE.PUT_LINE(filehandle,'parallel');

Line 555: UTL_FILE.NEW_LINE(filehandle,1);

551: --UTL_PUT(filehandle,buffer)
552: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
553: UTL_FILE.PUT_LINE(filehandle,'create materialized view');
554: UTL_FILE.PUT(filehandle,eMVName);
555: UTL_FILE.NEW_LINE(filehandle,1);
556: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
557: -- UTL_FILE.NEW_LINE(filehandle,1);
558: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
559:

Line 556: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);

552: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
553: UTL_FILE.PUT_LINE(filehandle,'create materialized view');
554: UTL_FILE.PUT(filehandle,eMVName);
555: UTL_FILE.NEW_LINE(filehandle,1);
556: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
557: -- UTL_FILE.NEW_LINE(filehandle,1);
558: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
559:
560: dupmvFlag:= FALSE;

Line 557: -- UTL_FILE.NEW_LINE(filehandle,1);

553: UTL_FILE.PUT_LINE(filehandle,'create materialized view');
554: UTL_FILE.PUT(filehandle,eMVName);
555: UTL_FILE.NEW_LINE(filehandle,1);
556: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
557: -- UTL_FILE.NEW_LINE(filehandle,1);
558: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
559:
560: dupmvFlag:= FALSE;
561: partition:= TRUE;

Line 558: -- UTL_FILE.PUT_LINE(filehandle,'parallel');

554: UTL_FILE.PUT(filehandle,eMVName);
555: UTL_FILE.NEW_LINE(filehandle,1);
556: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
557: -- UTL_FILE.NEW_LINE(filehandle,1);
558: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
559:
560: dupmvFlag:= FALSE;
561: partition:= TRUE;
562: IF partitioning = TRUE THEN

Line 568: UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');

564: partnum:= 0;
565: o:= 0;
566: indexString:= '';
567: indexString:= indexString || 'partition by range (gid) (';
568: UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
569: FOR j in 1..mvListdec.Count LOOP
570: IF mvListdec(j).mvNum= i THEN
571: edimName:= mvListdec(j).dimName;
572: ehierName:= mvListdec(j).hierName;

Line 696: UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);

692: IF k < 800 THEN
693: indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
694:
695: END IF;
696: UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
697: UTL_FILE.NEW_LINE(filehandle,1);
698: END IF;
699: END LOOP;
700: indexString:= indexString || ' partition values less than(MAXVALUE))';

Line 697: UTL_FILE.NEW_LINE(filehandle,1);

693: indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
694:
695: END IF;
696: UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
697: UTL_FILE.NEW_LINE(filehandle,1);
698: END IF;
699: END LOOP;
700: indexString:= indexString || ' partition values less than(MAXVALUE))';
701: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');

Line 701: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');

697: UTL_FILE.NEW_LINE(filehandle,1);
698: END IF;
699: END LOOP;
700: indexString:= indexString || ' partition values less than(MAXVALUE))';
701: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
702: partList.DELETE;
703: parnum.DELETE;
704: END IF;
705: UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');

Line 705: UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');

701: UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
702: partList.DELETE;
703: parnum.DELETE;
704: END IF;
705: UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
706: IF mvspcName IS NOT NULL THEN
707: UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
708: UTL_FILE.NEW_LINE(filehandle,1);
709: END IF;

Line 707: UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);

703: parnum.DELETE;
704: END IF;
705: UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
706: IF mvspcName IS NOT NULL THEN
707: UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
708: UTL_FILE.NEW_LINE(filehandle,1);
709: END IF;
710: UTL_FILE.PUT_LINE(filehandle,'build immediate');
711: UTL_FILE.PUT_LINE(filehandle,'using no index');

Line 708: UTL_FILE.NEW_LINE(filehandle,1);

704: END IF;
705: UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
706: IF mvspcName IS NOT NULL THEN
707: UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
708: UTL_FILE.NEW_LINE(filehandle,1);
709: END IF;
710: UTL_FILE.PUT_LINE(filehandle,'build immediate');
711: UTL_FILE.PUT_LINE(filehandle,'using no index');
712: UTL_FILE.PUT_LINE(filehandle,'refresh force');

Line 710: UTL_FILE.PUT_LINE(filehandle,'build immediate');

706: IF mvspcName IS NOT NULL THEN
707: UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
708: UTL_FILE.NEW_LINE(filehandle,1);
709: END IF;
710: UTL_FILE.PUT_LINE(filehandle,'build immediate');
711: UTL_FILE.PUT_LINE(filehandle,'using no index');
712: UTL_FILE.PUT_LINE(filehandle,'refresh force');
713: UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
714: UTL_FILE.PUT_LINE(filehandle,'AS');

Line 711: UTL_FILE.PUT_LINE(filehandle,'using no index');

707: UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
708: UTL_FILE.NEW_LINE(filehandle,1);
709: END IF;
710: UTL_FILE.PUT_LINE(filehandle,'build immediate');
711: UTL_FILE.PUT_LINE(filehandle,'using no index');
712: UTL_FILE.PUT_LINE(filehandle,'refresh force');
713: UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
714: UTL_FILE.PUT_LINE(filehandle,'AS');
715: UTL_FILE.PUT_LINE(filehandle,'SELECT');

Line 712: UTL_FILE.PUT_LINE(filehandle,'refresh force');

708: UTL_FILE.NEW_LINE(filehandle,1);
709: END IF;
710: UTL_FILE.PUT_LINE(filehandle,'build immediate');
711: UTL_FILE.PUT_LINE(filehandle,'using no index');
712: UTL_FILE.PUT_LINE(filehandle,'refresh force');
713: UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
714: UTL_FILE.PUT_LINE(filehandle,'AS');
715: UTL_FILE.PUT_LINE(filehandle,'SELECT');
716: bufString:= bufString || 'SELECT ';

Line 713: UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');

709: END IF;
710: UTL_FILE.PUT_LINE(filehandle,'build immediate');
711: UTL_FILE.PUT_LINE(filehandle,'using no index');
712: UTL_FILE.PUT_LINE(filehandle,'refresh force');
713: UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
714: UTL_FILE.PUT_LINE(filehandle,'AS');
715: UTL_FILE.PUT_LINE(filehandle,'SELECT');
716: bufString:= bufString || 'SELECT ';
717:

Line 714: UTL_FILE.PUT_LINE(filehandle,'AS');

710: UTL_FILE.PUT_LINE(filehandle,'build immediate');
711: UTL_FILE.PUT_LINE(filehandle,'using no index');
712: UTL_FILE.PUT_LINE(filehandle,'refresh force');
713: UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
714: UTL_FILE.PUT_LINE(filehandle,'AS');
715: UTL_FILE.PUT_LINE(filehandle,'SELECT');
716: bufString:= bufString || 'SELECT ';
717:
718: UTL_FILE.PUT(filehandle,'GROUPING_ID(');

Line 715: UTL_FILE.PUT_LINE(filehandle,'SELECT');

711: UTL_FILE.PUT_LINE(filehandle,'using no index');
712: UTL_FILE.PUT_LINE(filehandle,'refresh force');
713: UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
714: UTL_FILE.PUT_LINE(filehandle,'AS');
715: UTL_FILE.PUT_LINE(filehandle,'SELECT');
716: bufString:= bufString || 'SELECT ';
717:
718: UTL_FILE.PUT(filehandle,'GROUPING_ID(');
719: bufString:= bufString || 'GROUPING_ID( ';

Line 718: UTL_FILE.PUT(filehandle,'GROUPING_ID(');

714: UTL_FILE.PUT_LINE(filehandle,'AS');
715: UTL_FILE.PUT_LINE(filehandle,'SELECT');
716: bufString:= bufString || 'SELECT ';
717:
718: UTL_FILE.PUT(filehandle,'GROUPING_ID(');
719: bufString:= bufString || 'GROUPING_ID( ';
720: FOR j in 1..mvListinc.Count LOOP
721: IF mvListinc(j).mvNum= i THEN
722: edimName:= mvListinc(j).dimName;

Line 744: UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);

740: FETCH curTabName into eTabName;
741: EXIT WHEN curTabName%NOTFOUND;
742: END LOOP;
743: CLOSE curTabName;
744: UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
745: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
746:
747: bufString:= bufString
748: || dbms_assert.enquote_name(eTabName)

Line 754: UTL_FILE.PUT_LINE(filehandle,',');

750: || dbms_assert.enquote_name(eLevelColName)
751: || ' ';
752:
753: IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
754: UTL_FILE.PUT_LINE(filehandle,',');
755: bufString:= bufString || ', ';
756: END IF;
757:
758: ELSIF hierLevelList(l).dimName = eDimName AND

Line 777: UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);

773: FETCH curTabName into eTabName;
774: EXIT WHEN curTabName%NOTFOUND;
775: END LOOP;
776: CLOSE curTabName;
777: UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
778: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
779:
780: bufString:= bufString
781: || dbms_assert.enquote_name(eTabName)

Line 787: UTL_FILE.PUT_LINE(filehandle,',');

783: || dbms_assert.enquote_name(eLevelColName)
784: || ' ';
785:
786: IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
787: UTL_FILE.PUT_LINE(filehandle,',');
788: bufString:= bufString || ', ';
789: END IF;
790: END IF;
791: END LOOP;

Line 793: UTL_FILE.PUT_LINE(filehandle,',');

789: END IF;
790: END IF;
791: END LOOP;
792: IF mvListinc(j).highestLvl = FALSE THEN
793: UTL_FILE.PUT_LINE(filehandle,',');
794: bufString:= bufString || ', ';
795: END IF;
796: END IF;
797: END LOOP;

Line 798: UTL_FILE.PUT_LINE(filehandle,') gid,');

794: bufString:= bufString || ', ';
795: END IF;
796: END IF;
797: END LOOP;
798: UTL_FILE.PUT_LINE(filehandle,') gid,');
799: bufString:= bufString || ') gid, ';
800: IF NOT curUnitName%ISOPEN THEN
801: OPEN curUnitName;
802: LOOP

Line 805: UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);

801: OPEN curUnitName;
802: LOOP
803: FETCH curUnitName INTO eUnitName;
804: EXIT WHEN curUnitName%NOTFOUND;
805: UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
806: UTL_FILE.NEW_LINE(filehandle,1);
807: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
808: -- UTL_FILE.NEW_LINE(filehandle,1);
809: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';

Line 806: UTL_FILE.NEW_LINE(filehandle,1);

802: LOOP
803: FETCH curUnitName INTO eUnitName;
804: EXIT WHEN curUnitName%NOTFOUND;
805: UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
806: UTL_FILE.NEW_LINE(filehandle,1);
807: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
808: -- UTL_FILE.NEW_LINE(filehandle,1);
809: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
810:

Line 807: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);

803: FETCH curUnitName INTO eUnitName;
804: EXIT WHEN curUnitName%NOTFOUND;
805: UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
806: UTL_FILE.NEW_LINE(filehandle,1);
807: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
808: -- UTL_FILE.NEW_LINE(filehandle,1);
809: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
810:
811: bufString:= bufString

Line 808: -- UTL_FILE.NEW_LINE(filehandle,1);

804: EXIT WHEN curUnitName%NOTFOUND;
805: UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
806: UTL_FILE.NEW_LINE(filehandle,1);
807: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
808: -- UTL_FILE.NEW_LINE(filehandle,1);
809: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
810:
811: bufString:= bufString
812: || 'SUM('

Line 830: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');

826: -- bufString:= bufString || 'COUNT_OF_' || eUnitName || ', ';
827: END LOOP;
828: CLOSE CurUnitName;
829: END IF;
830: UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');
831: bufString:= bufString || 'COUNT(*) COUNT_OF_STAR, ';
832:
833: FOR j in 1..mvListdec.Count LOOP
834: IF mvListdec(j).mvNum= i THEN

Line 857: UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);

853: FETCH curTabName into eTabName;
854: EXIT WHEN curTabName%NOTFOUND;
855: END LOOP;
856: CLOSE curTabName;
857: UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
858: -- UTL_FILE.NEW_LINE(filehandle,1);
859: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
860:
861: bufString:= bufString

Line 858: -- UTL_FILE.NEW_LINE(filehandle,1);

854: EXIT WHEN curTabName%NOTFOUND;
855: END LOOP;
856: CLOSE curTabName;
857: UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
858: -- UTL_FILE.NEW_LINE(filehandle,1);
859: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
860:
861: bufString:= bufString
862: || dbms_assert.enquote_name(eTabName)

Line 869: UTL_FILE.PUT_LINE(filehandle,',');

865: || ' '
866: || dbms_assert.enquote_name(eLevelColName);
867:
868: IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
869: UTL_FILE.PUT_LINE(filehandle,',');
870: bufString:= bufString || ', ';
871: END IF;
872: ELSIF hierLevelList(l).dimName = eDimName AND
873: hierLevelList(l).hierName IS NULL THEN

Line 891: UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);

887: FETCH curTabName into eTabName;
888: EXIT WHEN curTabName%NOTFOUND;
889: END LOOP;
890: CLOSE curTabName;
891: UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
892: -- UTL_FILE.NEW_LINE(filehandle,1);
893: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
894:
895: bufString:= bufString

Line 892: -- UTL_FILE.NEW_LINE(filehandle,1);

888: EXIT WHEN curTabName%NOTFOUND;
889: END LOOP;
890: CLOSE curTabName;
891: UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
892: -- UTL_FILE.NEW_LINE(filehandle,1);
893: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
894:
895: bufString:= bufString
896: || dbms_assert.enquote_name(eTabName)

Line 903: UTL_FILE.PUT_LINE(filehandle,',');

899: || ' '
900: || dbms_assert.enquote_name(eLevelColName);
901:
902: IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
903: UTL_FILE.PUT_LINE(filehandle,',');
904: bufString:= bufString || ', ';
905: END IF;
906: END IF;
907: END LOOP;

Line 909: UTL_FILE.PUT_LINE(filehandle,',');

905: END IF;
906: END IF;
907: END LOOP;
908: IF mvListdec(j).highestLvl = FALSE THEN
909: UTL_FILE.PUT_LINE(filehandle,',');
910: bufString:= bufString || ', ';
911: END IF;
912: END IF;
913: END LOOP;

Line 914: UTL_FILE.NEW_LINE(filehandle,1);

910: bufString:= bufString || ', ';
911: END IF;
912: END IF;
913: END LOOP;
914: UTL_FILE.NEW_LINE(filehandle,1);
915: UTL_FILE.PUT_LINE(filehandle,'FROM');
916: o:= 0;
917: bufString:= bufString || 'FROM ';
918: FOR j in 1..mvListinc.Count LOOP

Line 915: UTL_FILE.PUT_LINE(filehandle,'FROM');

911: END IF;
912: END IF;
913: END LOOP;
914: UTL_FILE.NEW_LINE(filehandle,1);
915: UTL_FILE.PUT_LINE(filehandle,'FROM');
916: o:= 0;
917: bufString:= bufString || 'FROM ';
918: FOR j in 1..mvListinc.Count LOOP
919: IF mvListinc(j).mvNum= i THEN

Line 947: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);

943: o:= o + 1;
944: mvtableList.EXTEND;
945: mvtableList(o).tabName:= eTabName;
946: mvtableList(o).ownerName:= eOwnerName;
947: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
948: -- UTL_FILE.PUT_LINE(filehandle,' ');
949: -- bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
950: ELSIF hierLevelList(l).dimName = eDimName AND
951: hierLevelList(l).hierName IS NULL THEN

Line 948: -- UTL_FILE.PUT_LINE(filehandle,' ');

944: mvtableList.EXTEND;
945: mvtableList(o).tabName:= eTabName;
946: mvtableList(o).ownerName:= eOwnerName;
947: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
948: -- UTL_FILE.PUT_LINE(filehandle,' ');
949: -- bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
950: ELSIF hierLevelList(l).dimName = eDimName AND
951: hierLevelList(l).hierName IS NULL THEN
952: eLevelName:= hierLevelList(l).levelName;

Line 1031: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

1027: mvtableList(o).tabName:= eTabName;
1028: mvtableList(o).ownerName:= eOwnerName;
1029: END IF;
1030: -- bufString:= bufString || ' AND ';
1031: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1032: -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1033: END IF;
1034: ELSIF hierLevelList(l).dimName= edimName AND
1035: hierLevelList(l).hierName= NULL THEN

Line 1083: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

1079: mvtableList(o).tabName:= eTabName;
1080: mvtableList(o).ownerName:= eOwnerName;
1081: END IF;
1082: -- bufString:= bufString || ' AND ';
1083: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1084: -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1085: END IF;
1086: END IF;
1087: END LOOP;

Line 1101: UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,

1097: || dbms_assert.enquote_name(mvtableList(o).tabName)
1098: || ' '
1099: || dbms_assert.enquote_name(mvtableList(o).tabName)
1100: || ', ';
1101: UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
1102: mvtableList(o).tabName);
1103: UTL_FILE.PUT_LINE(filehandle,' ');
1104: END LOOP;
1105: mvtableList.DELETE;

Line 1103: UTL_FILE.PUT_LINE(filehandle,' ');

1099: || dbms_assert.enquote_name(mvtableList(o).tabName)
1100: || ', ';
1101: UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
1102: mvtableList(o).tabName);
1103: UTL_FILE.PUT_LINE(filehandle,' ');
1104: END LOOP;
1105: mvtableList.DELETE;
1106: UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
1107: UTL_FILE.PUT_LINE(filehandle,' ');

Line 1106: UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);

1102: mvtableList(o).tabName);
1103: UTL_FILE.PUT_LINE(filehandle,' ');
1104: END LOOP;
1105: mvtableList.DELETE;
1106: UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
1107: UTL_FILE.PUT_LINE(filehandle,' ');
1108: UTL_FILE.PUT_LINE(filehandle,'WHERE');
1109: whereFlag:= FALSE;
1110: --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';

Line 1107: UTL_FILE.PUT_LINE(filehandle,' ');

1103: UTL_FILE.PUT_LINE(filehandle,' ');
1104: END LOOP;
1105: mvtableList.DELETE;
1106: UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
1107: UTL_FILE.PUT_LINE(filehandle,' ');
1108: UTL_FILE.PUT_LINE(filehandle,'WHERE');
1109: whereFlag:= FALSE;
1110: --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';
1111:

Line 1108: UTL_FILE.PUT_LINE(filehandle,'WHERE');

1104: END LOOP;
1105: mvtableList.DELETE;
1106: UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
1107: UTL_FILE.PUT_LINE(filehandle,' ');
1108: UTL_FILE.PUT_LINE(filehandle,'WHERE');
1109: whereFlag:= FALSE;
1110: --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';
1111:
1112: bufString:= bufString

Line 1165: UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

1161: FETCH curLeavName into eLeavColName ;
1162: EXIT WHEN curLeavName%FOUND;
1163: END LOOP;
1164: CLOSE curLeavName;
1165: UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1166: --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || '=' || eFactName || '.' || eLeavColName || ') ';
1167:
1168: bufString:= bufString
1169: || '('

Line 1180: UTL_FILE.PUT_LINE(filehandle,' AND');

1176: || dbms_assert.enquote_name(eLeavColName)
1177: || ') ';
1178:
1179: IF ePosition < poscount THEN
1180: UTL_FILE.PUT_LINE(filehandle,' AND');
1181: bufString:= bufString || ' AND ';
1182: END IF;
1183: END LOOP;
1184: CLOSE curColName;

Line 1221: -- UTL_FILE.PUT_LINE(filehandle,' AND');

1217: FETCH curLeavName into eLeavColName ;
1218: EXIT WHEN curLeavName%NOTFOUND;
1219: END LOOP;
1220: CLOSE curLeavName;
1221: -- UTL_FILE.PUT_LINE(filehandle,' AND');
1222: UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1223: --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || '=' || eFactName || '.' || eLeavColName || ') ';
1224:
1225: bufString:= bufString

Line 1222: UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

1218: EXIT WHEN curLeavName%NOTFOUND;
1219: END LOOP;
1220: CLOSE curLeavName;
1221: -- UTL_FILE.PUT_LINE(filehandle,' AND');
1222: UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1223: --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || '=' || eFactName || '.' || eLeavColName || ') ';
1224:
1225: bufString:= bufString
1226: || '('

Line 1237: UTL_FILE.PUT_LINE(filehandle,' AND');

1233: || dbms_assert.enquote_name(eLeavColName)
1234: || ') ';
1235:
1236: IF ePosition < poscount THEN
1237: UTL_FILE.PUT_LINE(filehandle,' AND');
1238: bufString:= bufString || ' AND ';
1239: END IF;
1240: END LOOP;
1241: CLOSE curColDimName;

Line 1245: UTL_FILE.PUT_LINE(filehandle,' AND');

1241: CLOSE curColDimName;
1242: END IF;
1243: END LOOP;
1244: IF mvnum < dimList.Count THEN
1245: UTL_FILE.PUT_LINE(filehandle,' AND');
1246: bufString:= bufString || ' AND ';
1247: END IF;
1248: END IF;
1249: END LOOP;

Line 1286: UTL_FILE.PUT_LINE(filehandle,' AND');

1282: CLOSE curParentName;
1283: childString:= eparLevelName;
1284: IF childString <> parentString THEN
1285: bufString:= bufString || ' AND ';
1286: UTL_FILE.PUT_LINE(filehandle,' AND');
1287: UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
1288: --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1289: bufString:= bufString
1290: || '('

Line 1287: UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);

1283: childString:= eparLevelName;
1284: IF childString <> parentString THEN
1285: bufString:= bufString || ' AND ';
1286: UTL_FILE.PUT_LINE(filehandle,' AND');
1287: UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
1288: --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1289: bufString:= bufString
1290: || '('
1291: || dbms_assert.enquote_name(childString)

Line 1330: UTL_FILE.PUT_LINE(filehandle,' AND');

1326: CLOSE curParentName;
1327: childString:= eparLevelName;
1328: IF childString <> parentString THEN
1329: bufString:= bufString || ' AND ';
1330: UTL_FILE.PUT_LINE(filehandle,' AND');
1331: UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
1332: --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1333:
1334: bufString:= bufString

Line 1331: UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);

1327: childString:= eparLevelName;
1328: IF childString <> parentString THEN
1329: bufString:= bufString || ' AND ';
1330: UTL_FILE.PUT_LINE(filehandle,' AND');
1331: UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
1332: --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1333:
1334: bufString:= bufString
1335: || '('

Line 1348: UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');

1344: END LOOP;
1345: END IF;
1346: END LOOP;
1347:
1348: UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');
1349: bufString:= bufString || 'GROUP BY ';
1350: FOR j in 1..mvListinc.Count LOOP
1351: IF mvListinc(j).mvNum= i THEN
1352: mvnum:= 0;

Line 1385: UTL_FILE.PUT(filehandle,'(');

1381: EXIT WHEN curTabName%NOTFOUND;
1382: END LOOP;
1383: CLOSE curTabName;
1384: IF ePosition < poscount AND ePosition = 1 THEN
1385: UTL_FILE.PUT(filehandle,'(');
1386: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1387: UTL_FILE.PUT(filehandle,', ');
1388: END IF;
1389: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);

Line 1387: UTL_FILE.PUT(filehandle,', ');

1383: CLOSE curTabName;
1384: IF ePosition < poscount AND ePosition = 1 THEN
1385: UTL_FILE.PUT(filehandle,'(');
1386: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1387: UTL_FILE.PUT(filehandle,', ');
1388: END IF;
1389: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1390: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1391:

Line 1389: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);

1385: UTL_FILE.PUT(filehandle,'(');
1386: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1387: UTL_FILE.PUT(filehandle,', ');
1388: END IF;
1389: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1390: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1391:
1392: bufString:= bufString
1393: || dbms_assert.enquote_name(eTabName)

Line 1401: UTL_FILE.PUT(filehandle,')');

1397:
1398: END LOOP;
1399: CLOSE curColName;
1400: IF ePosition <> 1 THEN
1401: UTL_FILE.PUT(filehandle,')');
1402: END IF;
1403: IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
1404: UTL_FILE.PUT(filehandle,', ROLLUP( ');
1405: bufString:= bufString || ', ROLLUP( ';

Line 1404: UTL_FILE.PUT(filehandle,', ROLLUP( ');

1400: IF ePosition <> 1 THEN
1401: UTL_FILE.PUT(filehandle,')');
1402: END IF;
1403: IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
1404: UTL_FILE.PUT(filehandle,', ROLLUP( ');
1405: bufString:= bufString || ', ROLLUP( ';
1406: ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
1407: UTL_FILE.PUT_LINE(filehandle,', ');
1408: bufString:= bufString || ', ';

Line 1407: UTL_FILE.PUT_LINE(filehandle,', ');

1403: IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
1404: UTL_FILE.PUT(filehandle,', ROLLUP( ');
1405: bufString:= bufString || ', ROLLUP( ';
1406: ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
1407: UTL_FILE.PUT_LINE(filehandle,', ');
1408: bufString:= bufString || ', ';
1409: ELSIF mvListinc(j).lvlCount= mvnum THEN
1410: UTL_FILE.PUT(filehandle,') ');
1411: bufString:= bufString || ') ';

Line 1410: UTL_FILE.PUT(filehandle,') ');

1406: ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
1407: UTL_FILE.PUT_LINE(filehandle,', ');
1408: bufString:= bufString || ', ';
1409: ELSIF mvListinc(j).lvlCount= mvnum THEN
1410: UTL_FILE.PUT(filehandle,') ');
1411: bufString:= bufString || ') ';
1412: END IF;
1413: ELSIF hierLevelList(l).dimName = eDimName AND
1414: hierLevelList(l).hierName IS NULL THEN

Line 1442: UTL_FILE.PUT(filehandle,'(');

1438: EXIT WHEN curTabName%FOUND;
1439: END LOOP;
1440: CLOSE curTabName;
1441: IF ePosition < poscount AND ePosition <> 1 THEN
1442: UTL_FILE.PUT(filehandle,'(');
1443: ELSIF ePosition <= poscount THEN
1444: UTL_FILE.PUT(filehandle,',');
1445: END IF;
1446: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);

Line 1444: UTL_FILE.PUT(filehandle,',');

1440: CLOSE curTabName;
1441: IF ePosition < poscount AND ePosition <> 1 THEN
1442: UTL_FILE.PUT(filehandle,'(');
1443: ELSIF ePosition <= poscount THEN
1444: UTL_FILE.PUT(filehandle,',');
1445: END IF;
1446: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1447: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1448:

Line 1446: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);

1442: UTL_FILE.PUT(filehandle,'(');
1443: ELSIF ePosition <= poscount THEN
1444: UTL_FILE.PUT(filehandle,',');
1445: END IF;
1446: UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1447: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1448:
1449: bufString:= bufString
1450: || dbms_assert.enquote_name(eTabName)

Line 1458: UTL_FILE.PUT(filehandle,' )');

1454:
1455: END LOOP;
1456: CLOSE curColDimName;
1457: IF ePosition <> 1 THEN
1458: UTL_FILE.PUT(filehandle,' )');
1459: END IF;
1460: END IF;
1461: END LOOP;
1462: IF mvListinc(j).highestLvl = FALSE THEN

Line 1463: UTL_FILE.PUT(filehandle,', ');

1459: END IF;
1460: END IF;
1461: END LOOP;
1462: IF mvListinc(j).highestLvl = FALSE THEN
1463: UTL_FILE.PUT(filehandle,', ');
1464: bufString:= bufString || ', ';
1465: ELSE
1466: UTL_FILE.PUT(filehandle,';');
1467: -- bufString:= bufString || ';';

Line 1466: UTL_FILE.PUT(filehandle,';');

1462: IF mvListinc(j).highestLvl = FALSE THEN
1463: UTL_FILE.PUT(filehandle,', ');
1464: bufString:= bufString || ', ';
1465: ELSE
1466: UTL_FILE.PUT(filehandle,';');
1467: -- bufString:= bufString || ';';
1468: END IF;
1469: END IF;
1470: END LOOP;

Line 1497: UTL_FILE.NEW_LINE(filehandle,2);

1493: EXIT WHEN checkName%NOTFOUND;
1494: END LOOP;
1495: CLOSE checkName;
1496:
1497: UTL_FILE.NEW_LINE(filehandle,2);
1498: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1499: eOwnerName);
1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');

Line 1498: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',

1494: END LOOP;
1495: CLOSE checkName;
1496:
1497: UTL_FILE.NEW_LINE(filehandle,2);
1498: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1499: eOwnerName);
1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
1502: UTL_FILE.NEW_LINE(filehandle,1);

Line 1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);

1496:
1497: UTL_FILE.NEW_LINE(filehandle,2);
1498: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1499: eOwnerName);
1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
1502: UTL_FILE.NEW_LINE(filehandle,1);
1503: UTL_FILE.NEW_LINE(filehandle,2);
1504: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);

Line 1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');

1497: UTL_FILE.NEW_LINE(filehandle,2);
1498: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1499: eOwnerName);
1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
1502: UTL_FILE.NEW_LINE(filehandle,1);
1503: UTL_FILE.NEW_LINE(filehandle,2);
1504: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1505:

Line 1502: UTL_FILE.NEW_LINE(filehandle,1);

1498: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1499: eOwnerName);
1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
1502: UTL_FILE.NEW_LINE(filehandle,1);
1503: UTL_FILE.NEW_LINE(filehandle,2);
1504: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1505:
1506: eTempMV:= '';

Line 1503: UTL_FILE.NEW_LINE(filehandle,2);

1499: eOwnerName);
1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
1502: UTL_FILE.NEW_LINE(filehandle,1);
1503: UTL_FILE.NEW_LINE(filehandle,2);
1504: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1505:
1506: eTempMV:= '';
1507: bufString:= '';

Line 1504: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);

1500: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501: UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
1502: UTL_FILE.NEW_LINE(filehandle,1);
1503: UTL_FILE.NEW_LINE(filehandle,2);
1504: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1505:
1506: eTempMV:= '';
1507: bufString:= '';
1508: UTL_FILE.NEW_LINE(filehandle,1);

Line 1508: UTL_FILE.NEW_LINE(filehandle,1);

1504: UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1505:
1506: eTempMV:= '';
1507: bufString:= '';
1508: UTL_FILE.NEW_LINE(filehandle,1);
1509: mvnum:= 0;
1510: tpnum:= 0;
1511: FOR j in 1..mvListinc.Count LOOP
1512: IF mvListinc(j).mvNum= i THEN

Line 1537: UTL_FILE.NEW_LINE(filehandle,2);

1533: END LOOP;
1534: CLOSE curTabName;
1535: tpnum:= j * l;
1536: IF hierLevelList(l).levelNum <> 1 THEN
1537: UTL_FILE.NEW_LINE(filehandle,2);
1538: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
1539: UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
1540: UTL_FILE.NEW_LINE(filehandle,1);
1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');

Line 1538: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);

1534: CLOSE curTabName;
1535: tpnum:= j * l;
1536: IF hierLevelList(l).levelNum <> 1 THEN
1537: UTL_FILE.NEW_LINE(filehandle,2);
1538: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
1539: UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
1540: UTL_FILE.NEW_LINE(filehandle,1);
1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

Line 1539: UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);

1535: tpnum:= j * l;
1536: IF hierLevelList(l).levelNum <> 1 THEN
1537: UTL_FILE.NEW_LINE(filehandle,2);
1538: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
1539: UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
1540: UTL_FILE.NEW_LINE(filehandle,1);
1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1543: IF indspcName IS NOT NULL THEN

Line 1540: UTL_FILE.NEW_LINE(filehandle,1);

1536: IF hierLevelList(l).levelNum <> 1 THEN
1537: UTL_FILE.NEW_LINE(filehandle,2);
1538: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
1539: UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
1540: UTL_FILE.NEW_LINE(filehandle,1);
1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1543: IF indspcName IS NOT NULL THEN
1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

Line 1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');

1537: UTL_FILE.NEW_LINE(filehandle,2);
1538: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
1539: UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
1540: UTL_FILE.NEW_LINE(filehandle,1);
1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1543: IF indspcName IS NOT NULL THEN
1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1545: UTL_FILE.NEW_LINE(filehandle,1);

Line 1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

1538: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
1539: UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
1540: UTL_FILE.NEW_LINE(filehandle,1);
1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1543: IF indspcName IS NOT NULL THEN
1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1545: UTL_FILE.NEW_LINE(filehandle,1);
1546: END IF;

Line 1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

1540: UTL_FILE.NEW_LINE(filehandle,1);
1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1543: IF indspcName IS NOT NULL THEN
1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1545: UTL_FILE.NEW_LINE(filehandle,1);
1546: END IF;
1547: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1548: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

Line 1545: UTL_FILE.NEW_LINE(filehandle,1);

1541: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1543: IF indspcName IS NOT NULL THEN
1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1545: UTL_FILE.NEW_LINE(filehandle,1);
1546: END IF;
1547: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1548: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1549: UTL_FILE.NEW_LINE(filehandle,2);

Line 1547: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');

1543: IF indspcName IS NOT NULL THEN
1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1545: UTL_FILE.NEW_LINE(filehandle,1);
1546: END IF;
1547: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1548: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1549: UTL_FILE.NEW_LINE(filehandle,2);
1550: END IF;
1551: mvnum:= mvnum + 1;

Line 1548: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

1544: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1545: UTL_FILE.NEW_LINE(filehandle,1);
1546: END IF;
1547: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1548: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1549: UTL_FILE.NEW_LINE(filehandle,2);
1550: END IF;
1551: mvnum:= mvnum + 1;
1552: --Bug 8915150 bufString:= bufString || 'BMPIDX_' || eLevelColName;

Line 1549: UTL_FILE.NEW_LINE(filehandle,2);

1545: UTL_FILE.NEW_LINE(filehandle,1);
1546: END IF;
1547: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1548: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1549: UTL_FILE.NEW_LINE(filehandle,2);
1550: END IF;
1551: mvnum:= mvnum + 1;
1552: --Bug 8915150 bufString:= bufString || 'BMPIDX_' || eLevelColName;
1553:

Line 1595: UTL_FILE.NEW_LINE(filehandle,2);

1591: || 'BMPIDX_'
1592: || dbms_assert.enquote_name(eLevelColName);
1593:
1594: IF hierLevelList(l).levelNum <> 1 THEN
1595: UTL_FILE.NEW_LINE(filehandle,2);
1596: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
1597: UTL_FILE.NEW_LINE(filehandle,1);
1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

Line 1596: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);

1592: || dbms_assert.enquote_name(eLevelColName);
1593:
1594: IF hierLevelList(l).levelNum <> 1 THEN
1595: UTL_FILE.NEW_LINE(filehandle,2);
1596: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
1597: UTL_FILE.NEW_LINE(filehandle,1);
1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1600: IF indspcName IS NOT NULL THEN

Line 1597: UTL_FILE.NEW_LINE(filehandle,1);

1593:
1594: IF hierLevelList(l).levelNum <> 1 THEN
1595: UTL_FILE.NEW_LINE(filehandle,2);
1596: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
1597: UTL_FILE.NEW_LINE(filehandle,1);
1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1600: IF indspcName IS NOT NULL THEN
1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

Line 1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');

1594: IF hierLevelList(l).levelNum <> 1 THEN
1595: UTL_FILE.NEW_LINE(filehandle,2);
1596: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
1597: UTL_FILE.NEW_LINE(filehandle,1);
1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1600: IF indspcName IS NOT NULL THEN
1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1602: UTL_FILE.NEW_LINE(filehandle,1);

Line 1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

1595: UTL_FILE.NEW_LINE(filehandle,2);
1596: UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
1597: UTL_FILE.NEW_LINE(filehandle,1);
1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1600: IF indspcName IS NOT NULL THEN
1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1602: UTL_FILE.NEW_LINE(filehandle,1);
1603: END IF;

Line 1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

1597: UTL_FILE.NEW_LINE(filehandle,1);
1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1600: IF indspcName IS NOT NULL THEN
1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1602: UTL_FILE.NEW_LINE(filehandle,1);
1603: END IF;
1604: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1605: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

Line 1602: UTL_FILE.NEW_LINE(filehandle,1);

1598: UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599: UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1600: IF indspcName IS NOT NULL THEN
1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1602: UTL_FILE.NEW_LINE(filehandle,1);
1603: END IF;
1604: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1605: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1606: UTL_FILE.NEW_LINE(filehandle,2);

Line 1604: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');

1600: IF indspcName IS NOT NULL THEN
1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1602: UTL_FILE.NEW_LINE(filehandle,1);
1603: END IF;
1604: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1605: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1606: UTL_FILE.NEW_LINE(filehandle,2);
1607: END IF;
1608: -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';

Line 1605: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

1601: UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1602: UTL_FILE.NEW_LINE(filehandle,1);
1603: END IF;
1604: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1605: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1606: UTL_FILE.NEW_LINE(filehandle,2);
1607: END IF;
1608: -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
1609: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';

Line 1606: UTL_FILE.NEW_LINE(filehandle,2);

1602: UTL_FILE.NEW_LINE(filehandle,1);
1603: END IF;
1604: UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1605: UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1606: UTL_FILE.NEW_LINE(filehandle,2);
1607: END IF;
1608: -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
1609: -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
1610: -- bufString:= bufString || 'PARALLEL NOLOGGING; ';

Line 1666: -- UTL_FILE.NEW_LINE(filehandle,2);

1662: ehierName:= partList(j).hierName;
1663: m:= 0;
1664:
1665: tpnum:= j * i;
1666: -- UTL_FILE.NEW_LINE(filehandle,2);
1667: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
1668: -- UTL_FILE.NEW_LINE(filehandle,1);
1669: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1670: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');

Line 1667: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);

1663: m:= 0;
1664:
1665: tpnum:= j * i;
1666: -- UTL_FILE.NEW_LINE(filehandle,2);
1667: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
1668: -- UTL_FILE.NEW_LINE(filehandle,1);
1669: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1670: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
1671: -- UTL_FILE.PUT_LINE(filehandle,' ( ');

Line 1668: -- UTL_FILE.NEW_LINE(filehandle,1);

1664:
1665: tpnum:= j * i;
1666: -- UTL_FILE.NEW_LINE(filehandle,2);
1667: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
1668: -- UTL_FILE.NEW_LINE(filehandle,1);
1669: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1670: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
1671: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1672: FOR l in 1..hierLevelList.Count LOOP

Line 1669: -- UTL_FILE.PUT_LINE(filehandle,' ( ');

1665: tpnum:= j * i;
1666: -- UTL_FILE.NEW_LINE(filehandle,2);
1667: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
1668: -- UTL_FILE.NEW_LINE(filehandle,1);
1669: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1670: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
1671: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1672: FOR l in 1..hierLevelList.Count LOOP
1673: IF hierLevelList(l).dimName= eDimName AND

Line 1670: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');

1666: -- UTL_FILE.NEW_LINE(filehandle,2);
1667: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
1668: -- UTL_FILE.NEW_LINE(filehandle,1);
1669: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1670: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
1671: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1672: FOR l in 1..hierLevelList.Count LOOP
1673: IF hierLevelList(l).dimName= eDimName AND
1674: hierLevelList(l).hierName= eHierName THEN

Line 1671: -- UTL_FILE.PUT_LINE(filehandle,' ( ');

1667: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
1668: -- UTL_FILE.NEW_LINE(filehandle,1);
1669: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1670: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
1671: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1672: FOR l in 1..hierLevelList.Count LOOP
1673: IF hierLevelList(l).dimName= eDimName AND
1674: hierLevelList(l).hierName= eHierName THEN
1675: eLevelName:= hierLevelList(l).levelName;

Line 1694: -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);

1690: END LOOP;
1691: CLOSE curTabName;
1692: times:= times - 1;
1693: m:= m + 1;
1694: -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
1695: -- IF m < partList(j).levelCount THEN
1696:
1697: -- UTL_FILE.PUT(filehandle,',');
1698: -- END IF;

Line 1697: -- UTL_FILE.PUT(filehandle,',');

1693: m:= m + 1;
1694: -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
1695: -- IF m < partList(j).levelCount THEN
1696:
1697: -- UTL_FILE.PUT(filehandle,',');
1698: -- END IF;
1699: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
1700: -- UTL_FILE.NEW_LINE(filehandle,1);
1701: END IF;

Line 1699: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);

1695: -- IF m < partList(j).levelCount THEN
1696:
1697: -- UTL_FILE.PUT(filehandle,',');
1698: -- END IF;
1699: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
1700: -- UTL_FILE.NEW_LINE(filehandle,1);
1701: END IF;
1702: END LOOP;
1703: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

Line 1700: -- UTL_FILE.NEW_LINE(filehandle,1);

1696:
1697: -- UTL_FILE.PUT(filehandle,',');
1698: -- END IF;
1699: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
1700: -- UTL_FILE.NEW_LINE(filehandle,1);
1701: END IF;
1702: END LOOP;
1703: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1704: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

Line 1703: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

1699: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
1700: -- UTL_FILE.NEW_LINE(filehandle,1);
1701: END IF;
1702: END LOOP;
1703: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1704: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1705: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1706: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1707: -- IF indspcName IS NOT NULL THEN

Line 1704: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

1700: -- UTL_FILE.NEW_LINE(filehandle,1);
1701: END IF;
1702: END LOOP;
1703: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1704: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1705: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1706: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1707: -- IF indspcName IS NOT NULL THEN
1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

Line 1705: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');

1701: END IF;
1702: END LOOP;
1703: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1704: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1705: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1706: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1707: -- IF indspcName IS NOT NULL THEN
1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1709: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 1706: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

1702: END LOOP;
1703: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1704: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1705: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1706: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1707: -- IF indspcName IS NOT NULL THEN
1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1709: -- UTL_FILE.NEW_LINE(filehandle,1);
1710: -- END IF;

Line 1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

1704: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1705: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1706: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1707: -- IF indspcName IS NOT NULL THEN
1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1709: -- UTL_FILE.NEW_LINE(filehandle,1);
1710: -- END IF;
1711: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1712: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

Line 1709: -- UTL_FILE.NEW_LINE(filehandle,1);

1705: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1706: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1707: -- IF indspcName IS NOT NULL THEN
1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1709: -- UTL_FILE.NEW_LINE(filehandle,1);
1710: -- END IF;
1711: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1712: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1713: -- UTL_FILE.NEW_LINE(filehandle,2);

Line 1711: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');

1707: -- IF indspcName IS NOT NULL THEN
1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1709: -- UTL_FILE.NEW_LINE(filehandle,1);
1710: -- END IF;
1711: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1712: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1713: -- UTL_FILE.NEW_LINE(filehandle,2);
1714: END LOOP;
1715: partList.DELETE;

Line 1712: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

1708: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1709: -- UTL_FILE.NEW_LINE(filehandle,1);
1710: -- END IF;
1711: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1712: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1713: -- UTL_FILE.NEW_LINE(filehandle,2);
1714: END LOOP;
1715: partList.DELETE;
1716: UTL_FILE.NEW_LINE(filehandle,2);

Line 1713: -- UTL_FILE.NEW_LINE(filehandle,2);

1709: -- UTL_FILE.NEW_LINE(filehandle,1);
1710: -- END IF;
1711: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1712: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1713: -- UTL_FILE.NEW_LINE(filehandle,2);
1714: END LOOP;
1715: partList.DELETE;
1716: UTL_FILE.NEW_LINE(filehandle,2);
1717: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',

Line 1716: UTL_FILE.NEW_LINE(filehandle,2);

1712: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1713: -- UTL_FILE.NEW_LINE(filehandle,2);
1714: END LOOP;
1715: partList.DELETE;
1716: UTL_FILE.NEW_LINE(filehandle,2);
1717: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1718: eOwnerName);
1719: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1720: 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'' , granularity=>''GLOBAL'') ;');

Line 1717: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',

1713: -- UTL_FILE.NEW_LINE(filehandle,2);
1714: END LOOP;
1715: partList.DELETE;
1716: UTL_FILE.NEW_LINE(filehandle,2);
1717: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1718: eOwnerName);
1719: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1720: 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'' , granularity=>''GLOBAL'') ;');
1721: UTL_FILE.NEW_LINE(filehandle,2);

Line 1719: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);

1715: partList.DELETE;
1716: UTL_FILE.NEW_LINE(filehandle,2);
1717: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1718: eOwnerName);
1719: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1720: 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'' , granularity=>''GLOBAL'') ;');
1721: UTL_FILE.NEW_LINE(filehandle,2);
1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723: eOwnerName);

Line 1720: 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'' , granularity=>''GLOBAL'') ;');

1716: UTL_FILE.NEW_LINE(filehandle,2);
1717: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1718: eOwnerName);
1719: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1720: 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'' , granularity=>''GLOBAL'') ;');
1721: UTL_FILE.NEW_LINE(filehandle,2);
1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723: eOwnerName);
1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);

Line 1721: UTL_FILE.NEW_LINE(filehandle,2);

1717: UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1718: eOwnerName);
1719: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1720: 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'' , granularity=>''GLOBAL'') ;');
1721: UTL_FILE.NEW_LINE(filehandle,2);
1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723: eOwnerName);
1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
1725: UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');

Line 1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',

1718: eOwnerName);
1719: UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1720: 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'' , granularity=>''GLOBAL'') ;');
1721: UTL_FILE.NEW_LINE(filehandle,2);
1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723: eOwnerName);
1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
1725: UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');
1726: -- 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'' , granularity=>''GLOBAL'') ;');

Line 1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);

1720: 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'' , granularity=>''GLOBAL'') ;');
1721: UTL_FILE.NEW_LINE(filehandle,2);
1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723: eOwnerName);
1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
1725: UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');
1726: -- 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'' , granularity=>''GLOBAL'') ;');
1727: UTL_FILE.NEW_LINE(filehandle,3);
1728:

Line 1725: UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');

1721: UTL_FILE.NEW_LINE(filehandle,2);
1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723: eOwnerName);
1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
1725: UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');
1726: -- 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'' , granularity=>''GLOBAL'') ;');
1727: UTL_FILE.NEW_LINE(filehandle,3);
1728:
1729:

Line 1726: -- 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'' , granularity=>''GLOBAL'') ;');

1722: UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723: eOwnerName);
1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
1725: UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');
1726: -- 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'' , granularity=>''GLOBAL'') ;');
1727: UTL_FILE.NEW_LINE(filehandle,3);
1728:
1729:
1730: END LOOP;

Line 1727: UTL_FILE.NEW_LINE(filehandle,3);

1723: eOwnerName);
1724: UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
1725: UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');
1726: -- 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'' , granularity=>''GLOBAL'') ;');
1727: UTL_FILE.NEW_LINE(filehandle,3);
1728:
1729:
1730: END LOOP;
1731:

Line 1734: UTL_FILE.FCLOSE(filehandle);

1730: END LOOP;
1731:
1732:
1733: -- commit;
1734: UTL_FILE.FCLOSE(filehandle);
1735: -- EXCEPTION
1736: -- WHEN others THEN
1737: -- UTL_FILE.PUT('others');
1738:

Line 1737: -- UTL_FILE.PUT('others');

1733: -- commit;
1734: UTL_FILE.FCLOSE(filehandle);
1735: -- EXCEPTION
1736: -- WHEN others THEN
1737: -- UTL_FILE.PUT('others');
1738:
1739: --END ;
1740:
1741: commit;

Line 1743: WHEN UTL_FILE.INVALID_PATH THEN

1739: --END ;
1740:
1741: commit;
1742: EXCEPTION
1743: WHEN UTL_FILE.INVALID_PATH THEN
1744: UTL_FILE.FCLOSE(filehandle);
1745: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1746: WHEN UTL_FILE.INVALID_OPERATION THEN
1747: UTL_FILE.FCLOSE(filehandle);

Line 1744: UTL_FILE.FCLOSE(filehandle);

1740:
1741: commit;
1742: EXCEPTION
1743: WHEN UTL_FILE.INVALID_PATH THEN
1744: UTL_FILE.FCLOSE(filehandle);
1745: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1746: WHEN UTL_FILE.INVALID_OPERATION THEN
1747: UTL_FILE.FCLOSE(filehandle);
1748: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');

Line 1746: WHEN UTL_FILE.INVALID_OPERATION THEN

1742: EXCEPTION
1743: WHEN UTL_FILE.INVALID_PATH THEN
1744: UTL_FILE.FCLOSE(filehandle);
1745: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1746: WHEN UTL_FILE.INVALID_OPERATION THEN
1747: UTL_FILE.FCLOSE(filehandle);
1748: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
1749: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1750: UTL_FILE.FCLOSE(filehandle);

Line 1747: UTL_FILE.FCLOSE(filehandle);

1743: WHEN UTL_FILE.INVALID_PATH THEN
1744: UTL_FILE.FCLOSE(filehandle);
1745: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1746: WHEN UTL_FILE.INVALID_OPERATION THEN
1747: UTL_FILE.FCLOSE(filehandle);
1748: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
1749: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1750: UTL_FILE.FCLOSE(filehandle);
1751: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');

Line 1749: WHEN UTL_FILE.INVALID_FILEHANDLE THEN

1745: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1746: WHEN UTL_FILE.INVALID_OPERATION THEN
1747: UTL_FILE.FCLOSE(filehandle);
1748: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
1749: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1750: UTL_FILE.FCLOSE(filehandle);
1751: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1752: WHEN UTL_FILE.WRITE_ERROR THEN
1753: UTL_FILE.FCLOSE(filehandle);

Line 1750: UTL_FILE.FCLOSE(filehandle);

1746: WHEN UTL_FILE.INVALID_OPERATION THEN
1747: UTL_FILE.FCLOSE(filehandle);
1748: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
1749: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1750: UTL_FILE.FCLOSE(filehandle);
1751: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1752: WHEN UTL_FILE.WRITE_ERROR THEN
1753: UTL_FILE.FCLOSE(filehandle);
1754: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');

Line 1752: WHEN UTL_FILE.WRITE_ERROR THEN

1748: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
1749: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1750: UTL_FILE.FCLOSE(filehandle);
1751: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1752: WHEN UTL_FILE.WRITE_ERROR THEN
1753: UTL_FILE.FCLOSE(filehandle);
1754: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1755: WHEN UTL_FILE.INTERNAL_ERROR THEN
1756: UTL_FILE.FCLOSE(filehandle);

Line 1753: UTL_FILE.FCLOSE(filehandle);

1749: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1750: UTL_FILE.FCLOSE(filehandle);
1751: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1752: WHEN UTL_FILE.WRITE_ERROR THEN
1753: UTL_FILE.FCLOSE(filehandle);
1754: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1755: WHEN UTL_FILE.INTERNAL_ERROR THEN
1756: UTL_FILE.FCLOSE(filehandle);
1757: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');

Line 1755: WHEN UTL_FILE.INTERNAL_ERROR THEN

1751: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1752: WHEN UTL_FILE.WRITE_ERROR THEN
1753: UTL_FILE.FCLOSE(filehandle);
1754: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1755: WHEN UTL_FILE.INTERNAL_ERROR THEN
1756: UTL_FILE.FCLOSE(filehandle);
1757: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1758: WHEN others THEN
1759: -- UTL_FILE.FCLOSE(filehandle);

Line 1756: UTL_FILE.FCLOSE(filehandle);

1752: WHEN UTL_FILE.WRITE_ERROR THEN
1753: UTL_FILE.FCLOSE(filehandle);
1754: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1755: WHEN UTL_FILE.INTERNAL_ERROR THEN
1756: UTL_FILE.FCLOSE(filehandle);
1757: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1758: WHEN others THEN
1759: -- UTL_FILE.FCLOSE(filehandle);
1760: RAISE;

Line 1759: -- UTL_FILE.FCLOSE(filehandle);

1755: WHEN UTL_FILE.INTERNAL_ERROR THEN
1756: UTL_FILE.FCLOSE(filehandle);
1757: DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1758: WHEN others THEN
1759: -- UTL_FILE.FCLOSE(filehandle);
1760: RAISE;
1761: END ;
1762:
1763: PROCEDURE CreateFactTab(runId IN NUMBER,exOwnerName IN varchar2,exCubeName IN varchar2,partitioning IN BOOLEAN) IS

Line 1767: -- filehandle UTL_FILE.FILE_TYPE;

1763: PROCEDURE CreateFactTab(runId IN NUMBER,exOwnerName IN varchar2,exCubeName IN varchar2,partitioning IN BOOLEAN) IS
1764: -- This it the Procedure to Create Cube MV in ROLLUP form for a Cube. This procedure is used to write to a table for OEM
1765: -- Example CreateFatMVTable(89,'XADEMO','ANALYTIC_CUBE','ANALYTIC_MV');
1766:
1767: -- filehandle UTL_FILE.FILE_TYPE;
1768: eOwnerName varchar2(30);
1769: eCubeName varchar2(30);
1770: eMVName varchar2(30);
1771: -- eOutPath varchar2(30);

Line 2017: -- IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN

2013: -- eOutPath:= '/users/oracle';
2014: eOwnerName:= UPPER(exOwnerName);
2015: eCubeName:= UPPER(exCubeName);
2016: cwm2_olap_metadata_refresh.mr_refresh;
2017: -- IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
2018: -- filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
2019: -- END IF;
2020: IF NOT curFactName%ISOPEN THEN
2021: OPEN curFactName;

Line 2018: -- filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');

2014: eOwnerName:= UPPER(exOwnerName);
2015: eCubeName:= UPPER(exCubeName);
2016: cwm2_olap_metadata_refresh.mr_refresh;
2017: -- IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
2018: -- filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
2019: -- END IF;
2020: IF NOT curFactName%ISOPEN THEN
2021: OPEN curFactName;
2022: LOOP

Line 2029: RAISE UTL_FILE.INTERNAL_ERROR;

2025: END LOOP;
2026: CLOSE CurFactName;
2027: END IF;
2028: IF eFactName IS NULL THEN
2029: RAISE UTL_FILE.INTERNAL_ERROR;
2030: END IF;
2031: IF NOT curFactName%ISOPEN THEN
2032: OPEN curFactName;
2033: LOOP

Line 2311: -- UTL_FILE.PUT_LINE(filehandle,'create materialized view');

2307: -- (runid,1);
2308: FOR i in 1..numofMV LOOP
2309: --UTL_PUT(filehandle,buffer)
2310: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
2311: -- UTL_FILE.PUT_LINE(filehandle,'create materialized view');
2312: -- UTL_FILE.PUT(filehandle,eMVName);
2313: -- UTL_FILE.NEW_LINE(filehandle,1);
2314: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
2315: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 2312: -- UTL_FILE.PUT(filehandle,eMVName);

2308: FOR i in 1..numofMV LOOP
2309: --UTL_PUT(filehandle,buffer)
2310: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
2311: -- UTL_FILE.PUT_LINE(filehandle,'create materialized view');
2312: -- UTL_FILE.PUT(filehandle,eMVName);
2313: -- UTL_FILE.NEW_LINE(filehandle,1);
2314: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
2315: -- UTL_FILE.NEW_LINE(filehandle,1);
2316: -- UTL_FILE.PUT_LINE(filehandle,'parallel');

Line 2313: -- UTL_FILE.NEW_LINE(filehandle,1);

2309: --UTL_PUT(filehandle,buffer)
2310: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
2311: -- UTL_FILE.PUT_LINE(filehandle,'create materialized view');
2312: -- UTL_FILE.PUT(filehandle,eMVName);
2313: -- UTL_FILE.NEW_LINE(filehandle,1);
2314: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
2315: -- UTL_FILE.NEW_LINE(filehandle,1);
2316: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
2317:

Line 2314: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);

2310: eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
2311: -- UTL_FILE.PUT_LINE(filehandle,'create materialized view');
2312: -- UTL_FILE.PUT(filehandle,eMVName);
2313: -- UTL_FILE.NEW_LINE(filehandle,1);
2314: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
2315: -- UTL_FILE.NEW_LINE(filehandle,1);
2316: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
2317:
2318: dupmvFlag:= FALSE;

Line 2315: -- UTL_FILE.NEW_LINE(filehandle,1);

2311: -- UTL_FILE.PUT_LINE(filehandle,'create materialized view');
2312: -- UTL_FILE.PUT(filehandle,eMVName);
2313: -- UTL_FILE.NEW_LINE(filehandle,1);
2314: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
2315: -- UTL_FILE.NEW_LINE(filehandle,1);
2316: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
2317:
2318: dupmvFlag:= FALSE;
2319: -- partition:= TRUE;

Line 2316: -- UTL_FILE.PUT_LINE(filehandle,'parallel');

2312: -- UTL_FILE.PUT(filehandle,eMVName);
2313: -- UTL_FILE.NEW_LINE(filehandle,1);
2314: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
2315: -- UTL_FILE.NEW_LINE(filehandle,1);
2316: -- UTL_FILE.PUT_LINE(filehandle,'parallel');
2317:
2318: dupmvFlag:= FALSE;
2319: -- partition:= TRUE;
2320: IF partitioning = TRUE THEN

Line 2326: -- UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');

2322: partnum:= 0;
2323: o:= 0;
2324: indexString:= '';
2325: indexString:= indexString || 'partition by range (gid) (';
2326: -- UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
2327: FOR j in 1..mvListdec.Count LOOP
2328: IF mvListdec(j).mvNum= i THEN
2329: edimName:= mvListdec(j).dimName;
2330: ehierName:= mvListdec(j).hierName;

Line 2454: -- UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);

2450: IF k < 800 THEN
2451: indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
2452:
2453: END IF;
2454: -- UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
2455: -- UTL_FILE.NEW_LINE(filehandle,1);
2456: END IF;
2457: END LOOP;
2458: indexString:= indexString || ' partition values less than(MAXVALUE))';

Line 2455: -- UTL_FILE.NEW_LINE(filehandle,1);

2451: indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
2452:
2453: END IF;
2454: -- UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
2455: -- UTL_FILE.NEW_LINE(filehandle,1);
2456: END IF;
2457: END LOOP;
2458: indexString:= indexString || ' partition values less than(MAXVALUE))';
2459: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');

Line 2459: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');

2455: -- UTL_FILE.NEW_LINE(filehandle,1);
2456: END IF;
2457: END LOOP;
2458: indexString:= indexString || ' partition values less than(MAXVALUE))';
2459: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
2460: partList.DELETE;
2461: parnum.DELETE;
2462: END IF;
2463: -- UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');

Line 2463: -- UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');

2459: -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
2460: partList.DELETE;
2461: parnum.DELETE;
2462: END IF;
2463: -- UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
2464: -- IF mvspcName IS NOT NULL THEN
2465: -- UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
2466: -- UTL_FILE.NEW_LINE(filehandle,1);
2467: -- END IF;

Line 2465: -- UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);

2461: parnum.DELETE;
2462: END IF;
2463: -- UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
2464: -- IF mvspcName IS NOT NULL THEN
2465: -- UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
2466: -- UTL_FILE.NEW_LINE(filehandle,1);
2467: -- END IF;
2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');

Line 2466: -- UTL_FILE.NEW_LINE(filehandle,1);

2462: END IF;
2463: -- UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
2464: -- IF mvspcName IS NOT NULL THEN
2465: -- UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
2466: -- UTL_FILE.NEW_LINE(filehandle,1);
2467: -- END IF;
2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');

Line 2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');

2464: -- IF mvspcName IS NOT NULL THEN
2465: -- UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
2466: -- UTL_FILE.NEW_LINE(filehandle,1);
2467: -- END IF;
2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');
2471: -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');

Line 2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');

2465: -- UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
2466: -- UTL_FILE.NEW_LINE(filehandle,1);
2467: -- END IF;
2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');
2471: -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2473: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');

Line 2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');

2466: -- UTL_FILE.NEW_LINE(filehandle,1);
2467: -- END IF;
2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');
2471: -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2473: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2474: bufString:= bufString || 'SELECT ';

Line 2471: -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');

2467: -- END IF;
2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');
2471: -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2473: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2474: bufString:= bufString || 'SELECT ';
2475:

Line 2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');

2468: -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');
2471: -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2473: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2474: bufString:= bufString || 'SELECT ';
2475:
2476: -- UTL_FILE.PUT(filehandle,'GROUPING_ID(');

Line 2473: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');

2469: -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470: -- UTL_FILE.PUT_LINE(filehandle,'refresh force');
2471: -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2473: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2474: bufString:= bufString || 'SELECT ';
2475:
2476: -- UTL_FILE.PUT(filehandle,'GROUPING_ID(');
2477: bufString:= bufString || 'GROUPING_ID(';

Line 2476: -- UTL_FILE.PUT(filehandle,'GROUPING_ID(');

2472: -- UTL_FILE.PUT_LINE(filehandle,'AS');
2473: -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2474: bufString:= bufString || 'SELECT ';
2475:
2476: -- UTL_FILE.PUT(filehandle,'GROUPING_ID(');
2477: bufString:= bufString || 'GROUPING_ID(';
2478: FOR j in 1..mvListinc.Count LOOP
2479: IF mvListinc(j).mvNum= i THEN
2480: edimName:= mvListinc(j).dimName;

Line 2502: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);

2498: FETCH curTabName into eTabName;
2499: EXIT WHEN curTabName%NOTFOUND;
2500: END LOOP;
2501: CLOSE curTabName;
2502: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
2503: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName;
2504:
2505: bufString:= bufString
2506: || dbms_assert.enquote_name(eTabName)

Line 2511: -- UTL_FILE.PUT_LINE(filehandle,',');

2507: || '.'
2508: || dbms_assert.enquote_name(eLevelColName);
2509:
2510: IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
2511: -- UTL_FILE.PUT_LINE(filehandle,',');
2512: bufString:= bufString || ', ';
2513: END IF;
2514:
2515: ELSIF hierLevelList(l).dimName = eDimName AND

Line 2534: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);

2530: FETCH curTabName into eTabName;
2531: EXIT WHEN curTabName%NOTFOUND;
2532: END LOOP;
2533: CLOSE curTabName;
2534: -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
2535: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName;
2536:
2537: bufString:= bufString
2538: || dbms_assert.enquote_name(eTabName)

Line 2543: -- UTL_FILE.PUT_LINE(filehandle,',');

2539: || '.'
2540: || dbms_assert.enquote_name(eLevelColName);
2541:
2542: IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
2543: -- UTL_FILE.PUT_LINE(filehandle,',');
2544: bufString:= bufString || ', ';
2545: END IF;
2546: END IF;
2547: END LOOP;

Line 2549: -- UTL_FILE.PUT_LINE(filehandle,',');

2545: END IF;
2546: END IF;
2547: END LOOP;
2548: IF mvListinc(j).highestLvl = FALSE THEN
2549: -- UTL_FILE.PUT_LINE(filehandle,',');
2550: bufString:= bufString || ', ';
2551: END IF;
2552: END IF;
2553: END LOOP;

Line 2554: -- UTL_FILE.PUT_LINE(filehandle,') gid,');

2550: bufString:= bufString || ', ';
2551: END IF;
2552: END IF;
2553: END LOOP;
2554: -- UTL_FILE.PUT_LINE(filehandle,') gid,');
2555: bufString:= bufString || ') gid, ';
2556: IF NOT curUnitName%ISOPEN THEN
2557: OPEN curUnitName;
2558: LOOP

Line 2561: -- UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);

2557: OPEN curUnitName;
2558: LOOP
2559: FETCH curUnitName INTO eUnitName;
2560: EXIT WHEN curUnitName%NOTFOUND;
2561: -- UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
2562: -- UTL_FILE.NEW_LINE(filehandle,1);
2563: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
2564: -- UTL_FILE.NEW_LINE(filehandle,1);
2565: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';

Line 2562: -- UTL_FILE.NEW_LINE(filehandle,1);

2558: LOOP
2559: FETCH curUnitName INTO eUnitName;
2560: EXIT WHEN curUnitName%NOTFOUND;
2561: -- UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
2562: -- UTL_FILE.NEW_LINE(filehandle,1);
2563: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
2564: -- UTL_FILE.NEW_LINE(filehandle,1);
2565: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
2566:

Line 2563: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);

2559: FETCH curUnitName INTO eUnitName;
2560: EXIT WHEN curUnitName%NOTFOUND;
2561: -- UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
2562: -- UTL_FILE.NEW_LINE(filehandle,1);
2563: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
2564: -- UTL_FILE.NEW_LINE(filehandle,1);
2565: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
2566:
2567: bufString:= bufString

Line 2564: -- UTL_FILE.NEW_LINE(filehandle,1);

2560: EXIT WHEN curUnitName%NOTFOUND;
2561: -- UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
2562: -- UTL_FILE.NEW_LINE(filehandle,1);
2563: -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
2564: -- UTL_FILE.NEW_LINE(filehandle,1);
2565: --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
2566:
2567: bufString:= bufString
2568: || 'SUM('

Line 2586: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');

2582: -- bufString:= bufString || 'COUNT_OF_' || eUnitName || ', ';
2583: END LOOP;
2584: CLOSE CurUnitName;
2585: END IF;
2586: -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');
2587: bufString:= bufString || 'COUNT(*) COUNT_OF_STAR, ';
2588:
2589: FOR j in 1..mvListdec.Count LOOP
2590: IF mvListdec(j).mvNum= i THEN

Line 2613: -- UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);

2609: FETCH curTabName into eTabName;
2610: EXIT WHEN curTabName%NOTFOUND;
2611: END LOOP;
2612: CLOSE curTabName;
2613: -- UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
2614: -- UTL_FILE.NEW_LINE(filehandle,1);
2615:
2616: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName || '_' || l;
2617:

Line 2614: -- UTL_FILE.NEW_LINE(filehandle,1);

2610: EXIT WHEN curTabName%NOTFOUND;
2611: END LOOP;
2612: CLOSE curTabName;
2613: -- UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
2614: -- UTL_FILE.NEW_LINE(filehandle,1);
2615:
2616: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName || '_' || l;
2617:
2618: bufString:= bufString

Line 2628: -- UTL_FILE.PUT_LINE(filehandle,',');

2624: || '_'
2625: || l;
2626:
2627: IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
2628: -- UTL_FILE.PUT_LINE(filehandle,',');
2629: bufString:= bufString || ', ';
2630: END IF;
2631: ELSIF hierLevelList(l).dimName = eDimName AND
2632: hierLevelList(l).hierName IS NULL THEN

Line 2650: -- UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);

2646: FETCH curTabName into eTabName;
2647: EXIT WHEN curTabName%NOTFOUND;
2648: END LOOP;
2649: CLOSE curTabName;
2650: -- UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
2651: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName || '_' || l ;
2652:
2653: bufString:= bufString
2654: || dbms_assert.enquote_name(eTabName)

Line 2663: -- UTL_FILE.PUT_LINE(filehandle,',');

2659: || '_'
2660: || l ;
2661:
2662: IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
2663: -- UTL_FILE.PUT_LINE(filehandle,',');
2664: bufString:= bufString || ', ';
2665: END IF;
2666: END IF;
2667: END LOOP;

Line 2669: -- UTL_FILE.PUT_LINE(filehandle,',');

2665: END IF;
2666: END IF;
2667: END LOOP;
2668: IF mvListdec(j).highestLvl = FALSE THEN
2669: -- UTL_FILE.PUT_LINE(filehandle,',');
2670: bufString:= bufString || ', ';
2671: END IF;
2672: END IF;
2673: END LOOP;

Line 2674: -- UTL_FILE.NEW_LINE(filehandle,1);

2670: bufString:= bufString || ', ';
2671: END IF;
2672: END IF;
2673: END LOOP;
2674: -- UTL_FILE.NEW_LINE(filehandle,1);
2675: -- UTL_FILE.PUT_LINE(filehandle,'FROM');
2676: o:= 0;
2677: bufString:= bufString || ' FROM ';
2678: FOR j in 1..mvListinc.Count LOOP

Line 2675: -- UTL_FILE.PUT_LINE(filehandle,'FROM');

2671: END IF;
2672: END IF;
2673: END LOOP;
2674: -- UTL_FILE.NEW_LINE(filehandle,1);
2675: -- UTL_FILE.PUT_LINE(filehandle,'FROM');
2676: o:= 0;
2677: bufString:= bufString || ' FROM ';
2678: FOR j in 1..mvListinc.Count LOOP
2679: IF mvListinc(j).mvNum= i THEN

Line 2707: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);

2703: o:= o + 1;
2704: mvtableList.EXTEND;
2705: mvtableList(o).tabName:= eTabName;
2706: mvtableList(o).ownerName:= eOwnerName;
2707: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
2708: -- UTL_FILE.PUT_LINE(filehandle,' ');
2709: -- bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
2710: ELSIF hierLevelList(l).dimName = eDimName AND
2711: hierLevelList(l).hierName IS NULL THEN

Line 2708: -- UTL_FILE.PUT_LINE(filehandle,' ');

2704: mvtableList.EXTEND;
2705: mvtableList(o).tabName:= eTabName;
2706: mvtableList(o).ownerName:= eOwnerName;
2707: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
2708: -- UTL_FILE.PUT_LINE(filehandle,' ');
2709: -- bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
2710: ELSIF hierLevelList(l).dimName = eDimName AND
2711: hierLevelList(l).hierName IS NULL THEN
2712: eLevelName:= hierLevelList(l).levelName;

Line 2791: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

2787: mvtableList(o).tabName:= eTabName;
2788: mvtableList(o).ownerName:= eOwnerName;
2789: END IF;
2790: -- bufString:= bufString || ' AND ';
2791: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2792: -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2793: END IF;
2794: ELSIF hierLevelList(l).dimName= edimName AND
2795: hierLevelList(l).hierName= NULL THEN

Line 2843: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

2839: mvtableList(o).tabName:= eTabName;
2840: mvtableList(o).ownerName:= eOwnerName;
2841: END IF;
2842: -- bufString:= bufString || ' AND ';
2843: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2844: -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2845: END IF;
2846: END IF;
2847: END LOOP;

Line 2861: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,

2857: || dbms_assert.enquote_name(mvtableList(o).tabName)
2858: || ' '
2859: || dbms_assert.enquote_name(mvtableList(o).tabName)
2860: || ', ';
2861: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
2862: -- mvtableList(o).tabName);
2863: -- UTL_FILE.PUT_LINE(filehandle,' ');
2864: END LOOP;
2865: mvtableList.DELETE;

Line 2863: -- UTL_FILE.PUT_LINE(filehandle,' ');

2859: || dbms_assert.enquote_name(mvtableList(o).tabName)
2860: || ', ';
2861: -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
2862: -- mvtableList(o).tabName);
2863: -- UTL_FILE.PUT_LINE(filehandle,' ');
2864: END LOOP;
2865: mvtableList.DELETE;
2866: -- UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
2867: -- UTL_FILE.PUT_LINE(filehandle,' ');

Line 2866: -- UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);

2862: -- mvtableList(o).tabName);
2863: -- UTL_FILE.PUT_LINE(filehandle,' ');
2864: END LOOP;
2865: mvtableList.DELETE;
2866: -- UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
2867: -- UTL_FILE.PUT_LINE(filehandle,' ');
2868: -- UTL_FILE.PUT_LINE(filehandle,'WHERE');
2869: whereFlag:= FALSE;
2870: --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';

Line 2867: -- UTL_FILE.PUT_LINE(filehandle,' ');

2863: -- UTL_FILE.PUT_LINE(filehandle,' ');
2864: END LOOP;
2865: mvtableList.DELETE;
2866: -- UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
2867: -- UTL_FILE.PUT_LINE(filehandle,' ');
2868: -- UTL_FILE.PUT_LINE(filehandle,'WHERE');
2869: whereFlag:= FALSE;
2870: --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';
2871:

Line 2868: -- UTL_FILE.PUT_LINE(filehandle,'WHERE');

2864: END LOOP;
2865: mvtableList.DELETE;
2866: -- UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
2867: -- UTL_FILE.PUT_LINE(filehandle,' ');
2868: -- UTL_FILE.PUT_LINE(filehandle,'WHERE');
2869: whereFlag:= FALSE;
2870: --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';
2871:
2872: bufString:= bufString

Line 2925: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

2921: FETCH curLeavName into eLeavColName ;
2922: EXIT WHEN curLeavName%FOUND;
2923: END LOOP;
2924: CLOSE curLeavName;
2925: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2926: --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || ' = ' || eFactName || '.' || eLeavColName || ') ';
2927:
2928: bufString:= bufString
2929: || '('

Line 2940: -- UTL_FILE.PUT_LINE(filehandle,' AND');

2936: || dbms_assert.enquote_name(eLeavColName)
2937: || ') ';
2938:
2939: IF ePosition < poscount THEN
2940: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2941: bufString:= bufString || ' AND ';
2942: END IF;
2943: END LOOP;
2944: CLOSE curColName;

Line 2981: -- UTL_FILE.PUT_LINE(filehandle,' AND');

2977: FETCH curLeavName into eLeavColName ;
2978: EXIT WHEN curLeavName%FOUND;
2979: END LOOP;
2980: CLOSE curLeavName;
2981: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2982: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2983: --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || ' = ' || eFactName || '.' || eLeavColName || ') ';
2984:
2985: bufString:= bufString

Line 2982: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);

2978: EXIT WHEN curLeavName%FOUND;
2979: END LOOP;
2980: CLOSE curLeavName;
2981: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2982: -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2983: --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || ' = ' || eFactName || '.' || eLeavColName || ') ';
2984:
2985: bufString:= bufString
2986: || '('

Line 2997: -- UTL_FILE.PUT_LINE(filehandle,' AND');

2993: || dbms_assert.enquote_name(eLeavColName)
2994: || ') ';
2995:
2996: IF ePosition < poscount THEN
2997: -- UTL_FILE.PUT_LINE(filehandle,' AND');
2998: bufString:= bufString || ' AND ';
2999: END IF;
3000: END LOOP;
3001: CLOSE curColDimName;

Line 3005: -- UTL_FILE.PUT_LINE(filehandle,' AND');

3001: CLOSE curColDimName;
3002: END IF;
3003: END LOOP;
3004: IF mvnum < dimList.Count THEN
3005: -- UTL_FILE.PUT_LINE(filehandle,' AND');
3006: bufString:= bufString || ' AND ';
3007: END IF;
3008: END IF;
3009: END LOOP;

Line 3046: -- UTL_FILE.PUT_LINE(filehandle,' AND');

3042: CLOSE curParentName;
3043: childString:= eparLevelName;
3044: IF childString <> parentString THEN
3045: bufString:= bufString || ' AND ';
3046: -- UTL_FILE.PUT_LINE(filehandle,' AND');
3047: -- UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
3048: --Bug 8915150 bufString:= bufString || '(' || childString || ' = ' || parentString || ') ';
3049:
3050: bufString:= bufString

Line 3047: -- UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);

3043: childString:= eparLevelName;
3044: IF childString <> parentString THEN
3045: bufString:= bufString || ' AND ';
3046: -- UTL_FILE.PUT_LINE(filehandle,' AND');
3047: -- UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
3048: --Bug 8915150 bufString:= bufString || '(' || childString || ' = ' || parentString || ') ';
3049:
3050: bufString:= bufString
3051: || '('

Line 3065: -- UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');

3061: END LOOP;
3062: END IF;
3063: END LOOP;
3064:
3065: -- UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');
3066: bufString:= bufString || 'GROUP BY ';
3067: FOR j in 1..mvListinc.Count LOOP
3068: IF mvListinc(j).mvNum= i THEN
3069: mvnum:= 0;

Line 3104: -- UTL_FILE.PUT(filehandle,'(');

3100: CLOSE curTabName;
3101:
3102: IF ePosition < poscount AND ePosition = 1 THEN
3103: bufString:= bufString || '(';
3104: -- UTL_FILE.PUT(filehandle,'(');
3105: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
3106: bufString:= bufString || ', ';
3107: -- UTL_FILE.PUT(filehandle,', ');
3108: END IF;

Line 3107: -- UTL_FILE.PUT(filehandle,', ');

3103: bufString:= bufString || '(';
3104: -- UTL_FILE.PUT(filehandle,'(');
3105: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
3106: bufString:= bufString || ', ';
3107: -- UTL_FILE.PUT(filehandle,', ');
3108: END IF;
3109: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
3110: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ;
3111:

Line 3109: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);

3105: ELSIF ePosition <= poscount AND ePosition <> 1 THEN
3106: bufString:= bufString || ', ';
3107: -- UTL_FILE.PUT(filehandle,', ');
3108: END IF;
3109: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
3110: --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ;
3111:
3112: bufString:= bufString
3113: || dbms_assert.enquote_name(eTabName)

Line 3121: -- UTL_FILE.PUT(filehandle,')');

3117: END LOOP;
3118: CLOSE curColName;
3119: IF ePosition <> 1 THEN
3120: bufString:= bufString || ') ';
3121: -- UTL_FILE.PUT(filehandle,')');
3122: END IF;
3123: IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
3124: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
3125: bufString:= bufString || ', ROLLUP( ';

Line 3124: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');

3120: bufString:= bufString || ') ';
3121: -- UTL_FILE.PUT(filehandle,')');
3122: END IF;
3123: IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
3124: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
3125: bufString:= bufString || ', ROLLUP( ';
3126: ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
3127: -- UTL_FILE.PUT_LINE(filehandle,', ');
3128: bufString:= bufString || ', ';

Line 3127: -- UTL_FILE.PUT_LINE(filehandle,', ');

3123: IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
3124: -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
3125: bufString:= bufString || ', ROLLUP( ';
3126: ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
3127: -- UTL_FILE.PUT_LINE(filehandle,', ');
3128: bufString:= bufString || ', ';
3129: ELSIF mvListinc(j).lvlCount= mvnum THEN
3130: -- UTL_FILE.PUT(filehandle,') ');
3131: bufString:= bufString || ') ';

Line 3130: -- UTL_FILE.PUT(filehandle,') ');

3126: ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
3127: -- UTL_FILE.PUT_LINE(filehandle,', ');
3128: bufString:= bufString || ', ';
3129: ELSIF mvListinc(j).lvlCount= mvnum THEN
3130: -- UTL_FILE.PUT(filehandle,') ');
3131: bufString:= bufString || ') ';
3132: END IF;
3133: ELSIF hierLevelList(l).dimName = eDimName AND
3134: hierLevelList(l).hierName IS NULL THEN

Line 3170: -- UTL_FILE.PUT(filehandle,'(');

3166: || dbms_assert.enquote_name(eLevelColName) ;
3167:
3168: -- IF ePosition < poscount AND ePosition <> 1 THEN
3169: -- bufString:= bufString || '(';
3170: -- UTL_FILE.PUT(filehandle,'(');
3171: -- ELSIF ePosition <= poscount THEN
3172: -- bufString:= bufString || ', ';
3173: -- UTL_FILE.PUT(filehandle,',');
3174: -- END IF;

Line 3173: -- UTL_FILE.PUT(filehandle,',');

3169: -- bufString:= bufString || '(';
3170: -- UTL_FILE.PUT(filehandle,'(');
3171: -- ELSIF ePosition <= poscount THEN
3172: -- bufString:= bufString || ', ';
3173: -- UTL_FILE.PUT(filehandle,',');
3174: -- END IF;
3175: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
3176:
3177: END LOOP;

Line 3175: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);

3171: -- ELSIF ePosition <= poscount THEN
3172: -- bufString:= bufString || ', ';
3173: -- UTL_FILE.PUT(filehandle,',');
3174: -- END IF;
3175: -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
3176:
3177: END LOOP;
3178: CLOSE curColDimName;
3179: IF ePosition <> 1 THEN

Line 3180: -- UTL_FILE.PUT(filehandle,' )');

3176:
3177: END LOOP;
3178: CLOSE curColDimName;
3179: IF ePosition <> 1 THEN
3180: -- UTL_FILE.PUT(filehandle,' )');
3181: bufString:= bufString || ')';
3182: END IF;
3183: END IF;
3184: END LOOP;

Line 3186: -- UTL_FILE.PUT(filehandle,', ');

3182: END IF;
3183: END IF;
3184: END LOOP;
3185: IF mvListinc(j).highestLvl = FALSE THEN
3186: -- UTL_FILE.PUT(filehandle,', ');
3187: bufString:= bufString || ', ';
3188: -- ELSE
3189: -- UTL_FILE.PUT(filehandle,';');
3190: -- bufString:= bufString || ';';

Line 3189: -- UTL_FILE.PUT(filehandle,';');

3185: IF mvListinc(j).highestLvl = FALSE THEN
3186: -- UTL_FILE.PUT(filehandle,', ');
3187: bufString:= bufString || ', ';
3188: -- ELSE
3189: -- UTL_FILE.PUT(filehandle,';');
3190: -- bufString:= bufString || ';';
3191: END IF;
3192: END IF;
3193: END LOOP;

Line 3220: -- UTL_FILE.NEW_LINE(filehandle,2);

3216: EXIT WHEN checkName%NOTFOUND;
3217: END LOOP;
3218: CLOSE checkName;
3219:
3220: -- UTL_FILE.NEW_LINE(filehandle,2);
3221: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3222: -- eOwnerName);
3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');

Line 3221: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',

3217: END LOOP;
3218: CLOSE checkName;
3219:
3220: -- UTL_FILE.NEW_LINE(filehandle,2);
3221: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3222: -- eOwnerName);
3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
3225: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);

3219:
3220: -- UTL_FILE.NEW_LINE(filehandle,2);
3221: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3222: -- eOwnerName);
3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
3225: -- UTL_FILE.NEW_LINE(filehandle,1);
3226: -- UTL_FILE.NEW_LINE(filehandle,2);
3227: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);

Line 3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');

3220: -- UTL_FILE.NEW_LINE(filehandle,2);
3221: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3222: -- eOwnerName);
3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
3225: -- UTL_FILE.NEW_LINE(filehandle,1);
3226: -- UTL_FILE.NEW_LINE(filehandle,2);
3227: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
3228:

Line 3225: -- UTL_FILE.NEW_LINE(filehandle,1);

3221: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3222: -- eOwnerName);
3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
3225: -- UTL_FILE.NEW_LINE(filehandle,1);
3226: -- UTL_FILE.NEW_LINE(filehandle,2);
3227: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
3228:
3229: eTempMV:= '';

Line 3226: -- UTL_FILE.NEW_LINE(filehandle,2);

3222: -- eOwnerName);
3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
3225: -- UTL_FILE.NEW_LINE(filehandle,1);
3226: -- UTL_FILE.NEW_LINE(filehandle,2);
3227: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
3228:
3229: eTempMV:= '';
3230: bufString:= '';

Line 3227: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);

3223: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224: -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
3225: -- UTL_FILE.NEW_LINE(filehandle,1);
3226: -- UTL_FILE.NEW_LINE(filehandle,2);
3227: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
3228:
3229: eTempMV:= '';
3230: bufString:= '';
3231: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 3231: -- UTL_FILE.NEW_LINE(filehandle,1);

3227: -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
3228:
3229: eTempMV:= '';
3230: bufString:= '';
3231: -- UTL_FILE.NEW_LINE(filehandle,1);
3232: mvnum:= 0;
3233: tpnum:= 0;
3234: FOR j in 1..mvListinc.Count LOOP
3235: IF mvListinc(j).mvNum= i THEN

Line 3260: -- UTL_FILE.NEW_LINE(filehandle,2);

3256: END LOOP;
3257: CLOSE curTabName;
3258: tpnum:= j * l;
3259: IF hierLevelList(l).levelNum <> 1 THEN
3260: -- UTL_FILE.NEW_LINE(filehandle,2);
3261: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
3262: -- UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
3263:
3264: --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;

Line 3261: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);

3257: CLOSE curTabName;
3258: tpnum:= j * l;
3259: IF hierLevelList(l).levelNum <> 1 THEN
3260: -- UTL_FILE.NEW_LINE(filehandle,2);
3261: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
3262: -- UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
3263:
3264: --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;
3265:

Line 3262: -- UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);

3258: tpnum:= j * l;
3259: IF hierLevelList(l).levelNum <> 1 THEN
3260: -- UTL_FILE.NEW_LINE(filehandle,2);
3261: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
3262: -- UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
3263:
3264: --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;
3265:
3266: bufString:= bufString

Line 3276: -- UTL_FILE.NEW_LINE(filehandle,1);

3272: || '_'
3273: || tpnum;
3274:
3275: eLevelColName:= eLevelColName || '_' || l;
3276: -- UTL_FILE.NEW_LINE(filehandle,1);
3277: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3278: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3279: -- IF indspcName IS NOT NULL THEN
3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

Line 3277: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');

3273: || tpnum;
3274:
3275: eLevelColName:= eLevelColName || '_' || l;
3276: -- UTL_FILE.NEW_LINE(filehandle,1);
3277: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3278: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3279: -- IF indspcName IS NOT NULL THEN
3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3281: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 3278: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

3274:
3275: eLevelColName:= eLevelColName || '_' || l;
3276: -- UTL_FILE.NEW_LINE(filehandle,1);
3277: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3278: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3279: -- IF indspcName IS NOT NULL THEN
3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3281: -- UTL_FILE.NEW_LINE(filehandle,1);
3282: -- END IF;

Line 3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

3276: -- UTL_FILE.NEW_LINE(filehandle,1);
3277: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3278: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3279: -- IF indspcName IS NOT NULL THEN
3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3281: -- UTL_FILE.NEW_LINE(filehandle,1);
3282: -- END IF;
3283: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3284: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

Line 3281: -- UTL_FILE.NEW_LINE(filehandle,1);

3277: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3278: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3279: -- IF indspcName IS NOT NULL THEN
3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3281: -- UTL_FILE.NEW_LINE(filehandle,1);
3282: -- END IF;
3283: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3284: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3285: -- UTL_FILE.NEW_LINE(filehandle,2);

Line 3283: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');

3279: -- IF indspcName IS NOT NULL THEN
3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3281: -- UTL_FILE.NEW_LINE(filehandle,1);
3282: -- END IF;
3283: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3284: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3285: -- UTL_FILE.NEW_LINE(filehandle,2);
3286: INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3287: SUMMARY_OWNER) VALUES

Line 3284: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

3280: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3281: -- UTL_FILE.NEW_LINE(filehandle,1);
3282: -- END IF;
3283: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3284: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3285: -- UTL_FILE.NEW_LINE(filehandle,2);
3286: INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3287: SUMMARY_OWNER) VALUES
3288: (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);

Line 3285: -- UTL_FILE.NEW_LINE(filehandle,2);

3281: -- UTL_FILE.NEW_LINE(filehandle,1);
3282: -- END IF;
3283: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3284: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3285: -- UTL_FILE.NEW_LINE(filehandle,2);
3286: INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3287: SUMMARY_OWNER) VALUES
3288: (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
3289: END IF;

Line 3323: -- UTL_FILE.NEW_LINE(filehandle,2);

3319: END LOOP;
3320: CLOSE curTabName;
3321: -- bufString:= bufString || 'BMPIDX_' || eLevelColName;
3322: IF hierLevelList(l).levelNum <> 1 THEN
3323: -- UTL_FILE.NEW_LINE(filehandle,2);
3324: --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;
3325:
3326: bufString:= bufString
3327: || 'BI_'

Line 3336: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);

3332: || '_'
3333: || tpnum;
3334:
3335: eLevelColName:= eLevelColName || '_' || l;
3336: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
3337: -- UTL_FILE.NEW_LINE(filehandle,1);
3338: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3339: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3340: -- IF indspcName IS NOT NULL THEN

Line 3337: -- UTL_FILE.NEW_LINE(filehandle,1);

3333: || tpnum;
3334:
3335: eLevelColName:= eLevelColName || '_' || l;
3336: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
3337: -- UTL_FILE.NEW_LINE(filehandle,1);
3338: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3339: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3340: -- IF indspcName IS NOT NULL THEN
3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

Line 3338: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');

3334:
3335: eLevelColName:= eLevelColName || '_' || l;
3336: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
3337: -- UTL_FILE.NEW_LINE(filehandle,1);
3338: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3339: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3340: -- IF indspcName IS NOT NULL THEN
3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3342: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 3339: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

3335: eLevelColName:= eLevelColName || '_' || l;
3336: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
3337: -- UTL_FILE.NEW_LINE(filehandle,1);
3338: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3339: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3340: -- IF indspcName IS NOT NULL THEN
3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3342: -- UTL_FILE.NEW_LINE(filehandle,1);
3343: -- END IF;

Line 3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

3337: -- UTL_FILE.NEW_LINE(filehandle,1);
3338: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3339: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3340: -- IF indspcName IS NOT NULL THEN
3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3342: -- UTL_FILE.NEW_LINE(filehandle,1);
3343: -- END IF;
3344: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3345: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

Line 3342: -- UTL_FILE.NEW_LINE(filehandle,1);

3338: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3339: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3340: -- IF indspcName IS NOT NULL THEN
3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3342: -- UTL_FILE.NEW_LINE(filehandle,1);
3343: -- END IF;
3344: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3345: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3346: -- UTL_FILE.NEW_LINE(filehandle,2);

Line 3344: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');

3340: -- IF indspcName IS NOT NULL THEN
3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3342: -- UTL_FILE.NEW_LINE(filehandle,1);
3343: -- END IF;
3344: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3345: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3346: -- UTL_FILE.NEW_LINE(filehandle,2);
3347: INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3348: SUMMARY_OWNER) VALUES

Line 3345: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

3341: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3342: -- UTL_FILE.NEW_LINE(filehandle,1);
3343: -- END IF;
3344: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3345: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3346: -- UTL_FILE.NEW_LINE(filehandle,2);
3347: INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3348: SUMMARY_OWNER) VALUES
3349: (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);

Line 3346: -- UTL_FILE.NEW_LINE(filehandle,2);

3342: -- UTL_FILE.NEW_LINE(filehandle,1);
3343: -- END IF;
3344: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3345: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3346: -- UTL_FILE.NEW_LINE(filehandle,2);
3347: INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3348: SUMMARY_OWNER) VALUES
3349: (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
3350:

Line 3414: -- UTL_FILE.NEW_LINE(filehandle,2);

3410: ehierName:= partList(j).hierName;
3411: m:= 0;
3412:
3413: tpnum:= j * i;
3414: -- UTL_FILE.NEW_LINE(filehandle,2);
3415: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
3416: -- UTL_FILE.NEW_LINE(filehandle,1);
3417: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3418: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');

Line 3415: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);

3411: m:= 0;
3412:
3413: tpnum:= j * i;
3414: -- UTL_FILE.NEW_LINE(filehandle,2);
3415: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
3416: -- UTL_FILE.NEW_LINE(filehandle,1);
3417: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3418: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
3419: -- UTL_FILE.PUT_LINE(filehandle,' ( ');

Line 3416: -- UTL_FILE.NEW_LINE(filehandle,1);

3412:
3413: tpnum:= j * i;
3414: -- UTL_FILE.NEW_LINE(filehandle,2);
3415: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
3416: -- UTL_FILE.NEW_LINE(filehandle,1);
3417: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3418: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
3419: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3420: FOR l in 1..hierLevelList.Count LOOP

Line 3417: -- UTL_FILE.PUT_LINE(filehandle,' ( ');

3413: tpnum:= j * i;
3414: -- UTL_FILE.NEW_LINE(filehandle,2);
3415: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
3416: -- UTL_FILE.NEW_LINE(filehandle,1);
3417: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3418: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
3419: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3420: FOR l in 1..hierLevelList.Count LOOP
3421: IF hierLevelList(l).dimName= eDimName AND

Line 3418: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');

3414: -- UTL_FILE.NEW_LINE(filehandle,2);
3415: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
3416: -- UTL_FILE.NEW_LINE(filehandle,1);
3417: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3418: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
3419: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3420: FOR l in 1..hierLevelList.Count LOOP
3421: IF hierLevelList(l).dimName= eDimName AND
3422: hierLevelList(l).hierName= eHierName THEN

Line 3419: -- UTL_FILE.PUT_LINE(filehandle,' ( ');

3415: -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
3416: -- UTL_FILE.NEW_LINE(filehandle,1);
3417: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3418: -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
3419: -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3420: FOR l in 1..hierLevelList.Count LOOP
3421: IF hierLevelList(l).dimName= eDimName AND
3422: hierLevelList(l).hierName= eHierName THEN
3423: eLevelName:= hierLevelList(l).levelName;

Line 3442: -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);

3438: END LOOP;
3439: CLOSE curTabName;
3440: times:= times - 1;
3441: m:= m + 1;
3442: -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
3443: -- IF m < partList(j).levelCount THEN
3444:
3445: -- UTL_FILE.PUT(filehandle,',');
3446: -- END IF;

Line 3445: -- UTL_FILE.PUT(filehandle,',');

3441: m:= m + 1;
3442: -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
3443: -- IF m < partList(j).levelCount THEN
3444:
3445: -- UTL_FILE.PUT(filehandle,',');
3446: -- END IF;
3447: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
3448: -- UTL_FILE.NEW_LINE(filehandle,1);
3449: END IF;

Line 3447: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);

3443: -- IF m < partList(j).levelCount THEN
3444:
3445: -- UTL_FILE.PUT(filehandle,',');
3446: -- END IF;
3447: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
3448: -- UTL_FILE.NEW_LINE(filehandle,1);
3449: END IF;
3450: END LOOP;
3451: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

Line 3448: -- UTL_FILE.NEW_LINE(filehandle,1);

3444:
3445: -- UTL_FILE.PUT(filehandle,',');
3446: -- END IF;
3447: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
3448: -- UTL_FILE.NEW_LINE(filehandle,1);
3449: END IF;
3450: END LOOP;
3451: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3452: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

Line 3451: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

3447: -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
3448: -- UTL_FILE.NEW_LINE(filehandle,1);
3449: END IF;
3450: END LOOP;
3451: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3452: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3453: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3454: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3455: -- IF indspcName IS NOT NULL THEN

Line 3452: -- UTL_FILE.PUT_LINE(filehandle,' ) ');

3448: -- UTL_FILE.NEW_LINE(filehandle,1);
3449: END IF;
3450: END LOOP;
3451: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3452: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3453: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3454: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3455: -- IF indspcName IS NOT NULL THEN
3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

Line 3453: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');

3449: END IF;
3450: END LOOP;
3451: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3452: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3453: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3454: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3455: -- IF indspcName IS NOT NULL THEN
3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3457: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 3454: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');

3450: END LOOP;
3451: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3452: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3453: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3454: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3455: -- IF indspcName IS NOT NULL THEN
3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3457: -- UTL_FILE.NEW_LINE(filehandle,1);
3458: -- END IF;

Line 3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);

3452: -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3453: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3454: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3455: -- IF indspcName IS NOT NULL THEN
3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3457: -- UTL_FILE.NEW_LINE(filehandle,1);
3458: -- END IF;
3459: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3460: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

Line 3457: -- UTL_FILE.NEW_LINE(filehandle,1);

3453: -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3454: -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3455: -- IF indspcName IS NOT NULL THEN
3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3457: -- UTL_FILE.NEW_LINE(filehandle,1);
3458: -- END IF;
3459: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3460: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3461: -- UTL_FILE.NEW_LINE(filehandle,2);

Line 3459: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');

3455: -- IF indspcName IS NOT NULL THEN
3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3457: -- UTL_FILE.NEW_LINE(filehandle,1);
3458: -- END IF;
3459: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3460: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3461: -- UTL_FILE.NEW_LINE(filehandle,2);
3462: END LOOP;
3463: partList.DELETE;

Line 3460: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');

3456: -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3457: -- UTL_FILE.NEW_LINE(filehandle,1);
3458: -- END IF;
3459: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3460: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3461: -- UTL_FILE.NEW_LINE(filehandle,2);
3462: END LOOP;
3463: partList.DELETE;
3464: -- UTL_FILE.NEW_LINE(filehandle,2);

Line 3461: -- UTL_FILE.NEW_LINE(filehandle,2);

3457: -- UTL_FILE.NEW_LINE(filehandle,1);
3458: -- END IF;
3459: -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3460: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3461: -- UTL_FILE.NEW_LINE(filehandle,2);
3462: END LOOP;
3463: partList.DELETE;
3464: -- UTL_FILE.NEW_LINE(filehandle,2);
3465: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',

Line 3464: -- UTL_FILE.NEW_LINE(filehandle,2);

3460: -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3461: -- UTL_FILE.NEW_LINE(filehandle,2);
3462: END LOOP;
3463: partList.DELETE;
3464: -- UTL_FILE.NEW_LINE(filehandle,2);
3465: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3466: -- eOwnerName);
3467: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3468: -- 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'' , granularity=>''GLOBAL'') ;');

Line 3465: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',

3461: -- UTL_FILE.NEW_LINE(filehandle,2);
3462: END LOOP;
3463: partList.DELETE;
3464: -- UTL_FILE.NEW_LINE(filehandle,2);
3465: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3466: -- eOwnerName);
3467: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3468: -- 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'' , granularity=>''GLOBAL'') ;');
3469: -- UTL_FILE.NEW_LINE(filehandle,1);

Line 3467: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);

3463: partList.DELETE;
3464: -- UTL_FILE.NEW_LINE(filehandle,2);
3465: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3466: -- eOwnerName);
3467: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3468: -- 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'' , granularity=>''GLOBAL'') ;');
3469: -- UTL_FILE.NEW_LINE(filehandle,1);
3470:
3471: END LOOP;

Line 3468: -- 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'' , granularity=>''GLOBAL'') ;');

3464: -- UTL_FILE.NEW_LINE(filehandle,2);
3465: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3466: -- eOwnerName);
3467: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3468: -- 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'' , granularity=>''GLOBAL'') ;');
3469: -- UTL_FILE.NEW_LINE(filehandle,1);
3470:
3471: END LOOP;
3472:

Line 3469: -- UTL_FILE.NEW_LINE(filehandle,1);

3465: -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3466: -- eOwnerName);
3467: -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3468: -- 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'' , granularity=>''GLOBAL'') ;');
3469: -- UTL_FILE.NEW_LINE(filehandle,1);
3470:
3471: END LOOP;
3472:
3473:

Line 3475: -- UTL_FILE.FCLOSE(filehandle);

3471: END LOOP;
3472:
3473:
3474: -- commit;
3475: -- UTL_FILE.FCLOSE(filehandle);
3476: -- EXCEPTION
3477: -- WHEN others THEN
3478: -- UTL_FILE.PUT('others');
3479:

Line 3478: -- UTL_FILE.PUT('others');

3474: -- commit;
3475: -- UTL_FILE.FCLOSE(filehandle);
3476: -- EXCEPTION
3477: -- WHEN others THEN
3478: -- UTL_FILE.PUT('others');
3479:
3480: --END ;
3481:
3482: commit;

Line 3484: -- WHEN UTL_FILE.INVALID_PATH THEN

3480: --END ;
3481:
3482: commit;
3483: EXCEPTION
3484: -- WHEN UTL_FILE.INVALID_PATH THEN
3485: -- UTL_FILE.FCLOSE(filehandle);
3486: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3487: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3488: -- UTL_FILE.FCLOSE(filehandle);

Line 3485: -- UTL_FILE.FCLOSE(filehandle);

3481:
3482: commit;
3483: EXCEPTION
3484: -- WHEN UTL_FILE.INVALID_PATH THEN
3485: -- UTL_FILE.FCLOSE(filehandle);
3486: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3487: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3488: -- UTL_FILE.FCLOSE(filehandle);
3489: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');

Line 3487: -- WHEN UTL_FILE.INVALID_OPERATION THEN

3483: EXCEPTION
3484: -- WHEN UTL_FILE.INVALID_PATH THEN
3485: -- UTL_FILE.FCLOSE(filehandle);
3486: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3487: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3488: -- UTL_FILE.FCLOSE(filehandle);
3489: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3490: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3491: -- UTL_FILE.FCLOSE(filehandle);

Line 3488: -- UTL_FILE.FCLOSE(filehandle);

3484: -- WHEN UTL_FILE.INVALID_PATH THEN
3485: -- UTL_FILE.FCLOSE(filehandle);
3486: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3487: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3488: -- UTL_FILE.FCLOSE(filehandle);
3489: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3490: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3491: -- UTL_FILE.FCLOSE(filehandle);
3492: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');

Line 3490: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN

3486: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3487: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3488: -- UTL_FILE.FCLOSE(filehandle);
3489: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3490: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3491: -- UTL_FILE.FCLOSE(filehandle);
3492: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3493: -- WHEN UTL_FILE.WRITE_ERROR THEN
3494: -- UTL_FILE.FCLOSE(filehandle);

Line 3491: -- UTL_FILE.FCLOSE(filehandle);

3487: -- WHEN UTL_FILE.INVALID_OPERATION THEN
3488: -- UTL_FILE.FCLOSE(filehandle);
3489: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3490: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3491: -- UTL_FILE.FCLOSE(filehandle);
3492: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3493: -- WHEN UTL_FILE.WRITE_ERROR THEN
3494: -- UTL_FILE.FCLOSE(filehandle);
3495: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');

Line 3493: -- WHEN UTL_FILE.WRITE_ERROR THEN

3489: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3490: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3491: -- UTL_FILE.FCLOSE(filehandle);
3492: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3493: -- WHEN UTL_FILE.WRITE_ERROR THEN
3494: -- UTL_FILE.FCLOSE(filehandle);
3495: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3496: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3497: -- UTL_FILE.FCLOSE(filehandle);

Line 3494: -- UTL_FILE.FCLOSE(filehandle);

3490: -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3491: -- UTL_FILE.FCLOSE(filehandle);
3492: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3493: -- WHEN UTL_FILE.WRITE_ERROR THEN
3494: -- UTL_FILE.FCLOSE(filehandle);
3495: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3496: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3497: -- UTL_FILE.FCLOSE(filehandle);
3498: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');

Line 3496: -- WHEN UTL_FILE.INTERNAL_ERROR THEN

3492: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3493: -- WHEN UTL_FILE.WRITE_ERROR THEN
3494: -- UTL_FILE.FCLOSE(filehandle);
3495: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3496: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3497: -- UTL_FILE.FCLOSE(filehandle);
3498: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3499: WHEN others THEN
3500: -- UTL_FILE.FCLOSE(filehandle);

Line 3497: -- UTL_FILE.FCLOSE(filehandle);

3493: -- WHEN UTL_FILE.WRITE_ERROR THEN
3494: -- UTL_FILE.FCLOSE(filehandle);
3495: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3496: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3497: -- UTL_FILE.FCLOSE(filehandle);
3498: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3499: WHEN others THEN
3500: -- UTL_FILE.FCLOSE(filehandle);
3501: RAISE;

Line 3500: -- UTL_FILE.FCLOSE(filehandle);

3496: -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3497: -- UTL_FILE.FCLOSE(filehandle);
3498: -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3499: WHEN others THEN
3500: -- UTL_FILE.FCLOSE(filehandle);
3501: RAISE;
3502: END ;
3503:
3504: END;