OLAPSYS.OLAPFACTVIEW SQL Statements

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

Line: 145

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

Line: 149

		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: 161

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

Line: 166

		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: 171

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

Line: 176

		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: 184

    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: 198

		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: 208

		CURSOR curUnitName IS SELECT distinct c.column_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: 218

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

Line: 223

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

Line: 227

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

Line: 702

	      partList.DELETE;

Line: 703

	      parnum.DELETE;

Line: 715

		  UTL_FILE.PUT_LINE(filehandle,'SELECT');

Line: 716

		 	bufString:= bufString || 'SELECT ';

Line: 1105

			mvtableList.DELETE;

Line: 1483

	--				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,eOwnerName,bufString,num_bytes,0);

Line: 1487

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

Line: 1563

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

Line: 1612

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

Line: 1622

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

Line: 1715

			partList.DELETE;

Line: 1903

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

Line: 1907

		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: 1919

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

Line: 1924

		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: 1929

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

Line: 1934

		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: 1942

    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: 1956

		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: 1966

		CURSOR curUnitName IS SELECT distinct c.column_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: 1976

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

Line: 1981

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

Line: 1985

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

Line: 2460

	      partList.DELETE;

Line: 2461

	      parnum.DELETE;

Line: 2474

		 	bufString:= bufString || 'SELECT ';

Line: 2865

			mvtableList.DELETE;

Line: 3206

					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,eOwnerName,bufString,num_bytes,0);

Line: 3210

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

Line: 3286

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

Line: 3296

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

Line: 3347

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

Line: 3357

  		--				  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: 3360

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

Line: 3370

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

Line: 3463

			partList.DELETE;