OLAPSYS.OLAPDIMVIEW SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 148

    CURSOR curDispName IS SELECT DISPLAY_NAME
   	FROM ALL_OLAP_DIMENSIONS WHERE DIMENSION_NAME = eDimName AND
   	OWNER= eOwnerName;

Line: 152

		CURSOR curDimName IS SELECT DIMENSION_NAME
   	FROM ALL_OLAP_FACT_LEVEL_USES WHERE FACT_TABLE_NAME= eFactName AND
   	OWNER= eOwnerName;

Line: 156

		CURSOR curLevelName IS SELECT V.L
	   FROM ( SELECT CHILD_LEVEL_NAME L, POSITION P
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
	   UNION
	         SELECT PARENT_LEVEL_NAME L, POSITION+1
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName) V
	   ORDER BY V.P DESC;

Line: 168

		CURSOR curHierarchyName IS SELECT HIERARCHY_NAME
   		FROM ALL_OLAP_DIM_HIERARCHIES WHERE
				 OWNER= eOwnerName AND
				 DIMENSION_NAME= eDimName;

Line: 173

		CURSOR curColName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
	         AND DIMENSION_NAME= eDimName;

Line: 178

  	CURSOR curTabName IS SELECT LEVEL_TABLE_NAME
	   FROM ALL_OLAP_DIM_LEVELS
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
           AND DIMENSION_NAME= eDimName;

Line: 183

		CURSOR curParentName IS SELECT TABLE_NAME || '.' || COLUMN_NAME, TABLE_NAME
	   FROM ALL_OLAP_JOIN_KEY_COLUMN_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
		 AND CHILD_LEVEL_NAME= eLevelName
     AND POSITION = ePosition;

Line: 190

    CURSOR curLeavName IS SELECT distinct a.column_name
		FROM ALL_OLAP_KEY_COLUMN_USES a, ALL_OLAP_FOREIGN_KEYS b,
     ALL_OLAP_FACT_LEVEL_USES c
		where a.table_name = b.table_name
      and a.position = ePosition
		  and   a.key_name = b.foreign_key_name
		  and   b.table_name = c.fact_table_name
		  and   b.foreign_key_name = c.foreign_key_name
		  and   c.dimension_name = eDimName
		  and   c.level_name = eLevelName
		  and   c.dimension_owner = eOwnerName
		  and   c.fact_table_owner = eOwnerName
		  and   c.fact_table_name = eFactName;

Line: 204

	--	CURSOR curFactName IS SELECT distinct c.fact_table_name
	--	from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
	--	where a.owner = b.owner
	--	and a.cube_name = b.cube_name
	--	and b.owner = c.owner
	--	and b.cube_name = c.cube_name
	--	and b.measure_name = c.measure_name
	--	and a.owner = eOwnerName
	--	and a.cube_name = eCubeName;

Line: 224

		CURSOR checkName IS SELECT QUERY from ALL_MVIEWS
		where owner= eOwnerName
		and QUERY_LEN= query_length;

Line: 229

		CURSOR curLevDimName IS SELECT COLUMN_NAME
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

Line: 234

	  CURSOR curAttrName IS SELECT COLUMN_NAME, ATTRIBUTE_NAME, LEVEL_NAME
	   FROM ALL_OLAP_DIM_LEVEL_ATTR_MAPS
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName
     	   AND LEVEL_NAME= eeLevelName;

Line: 240

		CURSOR curAttrTabName IS SELECT LEVEL_TABLE_NAME
	   FROM ALL_OLAP_DIM_LEVELS
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName
	   AND LEVEL_NAME= eLevelName;

Line: 245

    CURSOR curColDimName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

Line: 498

		  UTL_FILE.PUT_LINE(filehandle,'SELECT');

Line: 503

	    bufString:= bufString || 'SELECT ';

Line: 979

			mvtableList.DELETE;

Line: 1235

	 -- 			INSERT INTO SYSTEM.MVIEW$_ADV_OUTPUT(RUNID#,OUTPUT_TYPE,RANK#,SUMMARY_OWNER,
  --									QUERY_TEXT,STORAGE_IN_BYTES,BENEFIT_TO_COST_RATIO) VALUES
  --														(runid,0,i+ 100,eOwnerName,bufString,23333,0);

Line: 1251

	--  	INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
  --                             (runId,i+100,eOwnerName,indexString);

Line: 1328

			--						INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  	--										SUMMARY_OWNER) VALUES
	  	--									(runid,i + 100,mvnum,0,bufString,eLevelColName,eOwnerName);

Line: 1385

			--						INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  	--										SUMMARY_OWNER) VALUES
	  	--									(runid,i + 100,mvnum,0,bufString,eLevelColName,eOwnerName);

Line: 1659

    partList.DELETE;

Line: 1660

    parnum.DELETE;

Line: 1661

    tabCol.DELETE;

Line: 1673

	 -- 	INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
	 -- 									INDEX_TEXT) VALUES
	 -- 										(runid,i+ 100,eOwnerName,bufString);

Line: 1845

    CURSOR curDispName IS SELECT DISPLAY_NAME
   	FROM ALL_OLAP_DIMENSIONS WHERE DIMENSION_NAME = eDimName AND
   	OWNER= eOwnerName;

Line: 1849

		CURSOR curDimName IS SELECT distinct DIMENSION_NAME
   	FROM ALL_OLAP_FACT_LEVEL_USES WHERE FACT_TABLE_NAME= eFactName AND
   	OWNER= eOwnerName;

Line: 1853

		CURSOR curLevelName IS SELECT V.L
	   FROM ( SELECT CHILD_LEVEL_NAME L, POSITION P
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
	   UNION
	         SELECT PARENT_LEVEL_NAME L, POSITION+1
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName) V
	   ORDER BY V.P DESC;

Line: 1865

		CURSOR curHierarchyName IS SELECT HIERARCHY_NAME
   		FROM ALL_OLAP_DIM_HIERARCHIES WHERE
				 OWNER= eOwnerName AND
				 DIMENSION_NAME= eDimName;

Line: 1870

		CURSOR curColName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
	         AND DIMENSION_NAME= eDimName;

Line: 1875

  	CURSOR curTabName IS SELECT LEVEL_TABLE_NAME
	   FROM ALL_OLAP_DIM_LEVELS
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
           AND DIMENSION_NAME= eDimName;

Line: 1880

		CURSOR curParentName IS SELECT TABLE_NAME || '.' || COLUMN_NAME, TABLE_NAME
	   FROM ALL_OLAP_JOIN_KEY_COLUMN_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
		 AND CHILD_LEVEL_NAME= eLevelName
     AND POSITION = ePosition;

Line: 1887

    CURSOR curLeavName IS SELECT distinct a.column_name
		FROM ALL_OLAP_KEY_COLUMN_USES a, ALL_OLAP_FOREIGN_KEYS b,
     ALL_OLAP_FACT_LEVEL_USES c
		where a.table_name = b.table_name
      and a.position = ePosition
		  and   a.key_name = b.foreign_key_name
		  and   b.table_name = c.fact_table_name
		  and   b.foreign_key_name = c.foreign_key_name
		  and   c.dimension_name = eDimName
		  and   c.level_name = eLevelName
		  and   c.dimension_owner = eOwnerName
		  and   c.fact_table_owner = eOwnerName
		  and   c.fact_table_name = eFactName;

Line: 1901

	--	CURSOR curFactName IS SELECT distinct c.fact_table_name
	--	from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
	--	where a.owner = b.owner
	--	and a.cube_name = b.cube_name
	--	and b.owner = c.owner
	--	and b.cube_name = c.cube_name
	--	and b.measure_name = c.measure_name
	--	and a.owner = eOwnerName
	--	and a.cube_name = eCubeName;

Line: 1921

		CURSOR checkName IS SELECT QUERY from ALL_MVIEWS
		where owner= eOwnerName
		and QUERY_LEN= query_length;

Line: 1926

		CURSOR curLevDimName IS SELECT LEVEL_NAME
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

Line: 1931

	  CURSOR curAttrName IS SELECT COLUMN_NAME,ATTRIBUTE_NAME, LEVEL_NAME
	   FROM ALL_OLAP_DIM_LEVEL_ATTR_MAPS
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName
     AND LEVEL_NAME= eeLevelName;

Line: 1937

		CURSOR curAttrTabName IS SELECT LEVEL_TABLE_NAME
	   FROM ALL_OLAP_DIM_LEVELS
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName
	   AND LEVEL_NAME= eLevelName;

Line: 1942

    CURSOR curColDimName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

Line: 2085

		SELECT nvl(max(rank#), 100) INTO rankNum FROM SYSTEM.MVIEW$_ADV_OUTPUT
     		WHERE RUNID# = runid;

Line: 2198

	--	  UTL_FILE.PUT_LINE(filehandle,'SELECT');

Line: 2203

	    bufString:= bufString || 'SELECT ';

Line: 2712

			mvtableList.DELETE;

Line: 2963

	  			  INSERT INTO SYSTEM.MVIEW$_ADV_OUTPUT(RUNID#,OUTPUT_TYPE,RANK#,SUMMARY_OWNER,
  									QUERY_TEXT,STORAGE_IN_BYTES,BENEFIT_TO_COST_RATIO) VALUES
  														(runid,1,rankNum,eOwnerName,bufString,num_bytes,0);

Line: 2966

  				  INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
                             (runId,rankNum,eOwnerName,indexString);

Line: 2980

	--  	INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
  --                             (runId,i+100,eOwnerName,indexString);

Line: 3060

 								  INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  											SUMMARY_OWNER) VALUES
	  										(runid,rankNum,mvnum,0,bufString,eLevelColName,eOwnerName);

Line: 3116

 								    INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  			  								SUMMARY_OWNER) VALUES
	  										(runid,rankNum,mvnum,0,bufString,eLevelColName,eOwnerName);

Line: 3129

		--							INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  --											SUMMARY_OWNER) VALUES
	  --										(runid,i + 100,mvnum,0,bufString,eLevelColName,eOwnerName);

Line: 3170

              INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
              SUMMARY_OWNER) VALUES
              (runid, rankNum,mvnum,0,bufString,'gid',eOwnerName);

Line: 3327

      INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
        SUMMARY_OWNER,INDEX_CONTENT) VALUES
        (runid,rankNum,mvnum,1,bufString,eLevelColName,eOwnerName,indexString);

Line: 3391

        INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
          SUMMARY_OWNER,INDEX_CONTENT) VALUES
          (runid,rankNum,mvnum,1,bufString,eLevelColName,eOwnerName,indexString);

Line: 3397

    partList.DELETE;

Line: 3398

    parnum.DELETE;

Line: 3399

    tabCol.DELETE;

Line: 3408

	 -- 	INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
	 -- 									INDEX_TEXT) VALUES
	 -- 										(runid,i+ 100,eOwnerName,bufString);