Need assistance in declaring the variable as rowtype without knowing the table name

Question

Create table t1_Fact ( Cur_date Date, Name varchar2(10), Event varchar2(50), Price Number(10,0), TAX Number(10,0), Flag Number );
Create table App_Fact ( Application_ID Number, Application_Name varchar2(100), Application_Price Number, Appliation_Tax Number, Flag Number );
Create table t2 ( Table_Name Varchar2(100), Table_Columns Varchar(100), Table_Measure varchar2(100), t3_columns varchar2(100), t3_measures varchar2(100), t3_Where_Clause varchar2(100) );
Create table t3 ( Cur_date Date, Name varchar2(10), Event varchar2(50), Application_ID Number, Application_Name varchar2(100), Application_Price Number, Appliation_Tax Number, Price Number(10,0), TAX Number(10,0), Flag Number );

table t2 contains all the table names,column names of each source and destination tables and where clause conditions.

Here I need to insert the data from t3 to particular fact table by using group by the column names of fact table, measures and where clause by passing the fact table name as parameter.

Like if we pass t1_Fact table in procedure, we must get all the details from t2 and get the details from t3 and insert into t1_Fact and also save them into CSV file

I have tried the following procedure however I'm not able to insert the data into csv file.

Note : In the following procedure I Need to fetch the table(tablename) data into q2, I have declared variable as recordsFetched VARCHAR2(3000 BYTE);. Need to know how an we declare the recordsFetched as %rowtype with out knowing the table name as we get table name from cursor. Also can I know how to use execute immediate in UTL_FILE.PUT_Line()

Create or Replace Procedure CommonProcedure(sourceTableName IN VARCHAR2) Is
  tablename t2.Table_Name%TYPE;
  destcolumns t2.Table_Columns%TYPE;
  destMeasures t2.Table_Measure%TYPE;
  whereClause t2.t3_Where_Clause%TYPE;
  sourceColumns t2.t3_columns%TYPE;
  sourceMeasures t2.t3_measures%TYPE;
  q1 VARCHAR2(3000 BYTE);
q2 VARCHAR2(3000 BYTE);
 recordsFetched VARCHAR2(3000 BYTE);
  pathInfo VARCHAR2(3000 BYTE);

   v_file  UTL_FILE.FILE_TYPE;

 Cursor TableName Is  SELECT Table_Name FROM t2;

Begin


 Open c1;
 Loop
   Fetch TableName Into tablename;
   Exit When TableName%notfound;

    SELECT Table_Columns, Table_Measure, t3_columns, t3_measures INTO destcolumns,destMeasures,sourceColumns,sourceMeasures FROM t2 where Table_Name = tablename;

    q1 := 'INSERT INTO '||tablename||'('||destColumns||','||destMeasures||')'||
        ' ( SELECT '||sourceColumns||','||sourceMeasures||','||sourceTableName
          ||' FROM '||sourceTableName||' GROUP BY '||sourceColumns||')';

    Execute Immediate q1;

q2 := 'SELECT ' || destColumns || ',' || destMeasures ||' FROM ' || tablename || '';
           dbms_output.put_line(q2);
           EXECUTE IMMEDIATE  q2 into recordsfetched; --Getting an error here


         v_file := UTL_FILE.FOPEN('USER_DIR', destinationTableName ||'.csv', 'W');  
           UTL_FILE.PUT_Line(v_file, recordsfetched);
           UTL_FILE.FCLOSE(v_file);

 End Loop;

 Close TableName;

End;

When I execute the above procedure getting following error

Error report -
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "GENERATECSV", line 47
ORA-06512: at line 1
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Please assist me further.

Thanks in advance.

Answer 1

You are selecting two values (destColumns and destMeasures) into one variable (recordsfetched).

  q2 := 'SELECT ' || destColumns || ',' || destMeasures ||' FROM ' || tablename || '';
  EXECUTE IMMEDIATE  q2 into recordsfetched;

Should be something like:

q2 := 'SELECT ' || destColumns || ',' || destMeasures ||' FROM ' || tablename || '';
EXECUTE IMMEDIATE  q2 into Columnsfetched,MeasuresFetched;

Answer 2

This is a convoluted procedure. but alas not convoluted enough. To make the second dynamic query work you need to select into a record set which matches the projection of the assembled query. So you need to generate some dynamic code to do that.

Or you could try something like this:

....
Execute Immediate q1;

destColumns := replace( destColumns, ',', q'[||','||]');
destMeasures := replace( destMeasures, ',', q'[||','||]');

q2 := 'SELECT ' || destColumns || ',' || destMeasures ||' FROM ' || tablename || '';
dbms_output.put_line(q2);
EXECUTE IMMEDIATE  q2 into recordsfetched;
....

This turns the projection into a single wide column, which can be written to a file.