Need assistance in creating a procedure

Question

I have a three tables as below.

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,
);

T2 table data:

table t2 details

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.

I have tried the following procedure however I'm not able to insert the data into fact table from t3.

Procedure :

enter code here

**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.Table_Where_Clause%TYPE; 
sourceColumns t2.t3 columns%TYPE;
sourceMeasures t2.t3 measures%TYPE;
q1 VARCHAR2(3000 BYTE);
CURSOR C1 is 
    SELECT Table_Name, Table_Columns, Table_Measure, Table_Where_Clause 
--into reportName,procedureName,destinationTableName
 from t2 

BEGIN
 open c1;
 loop
   fetch c1 into tablename,destcolumns,destMeasures,whereClause;
   exit when c1%notfound;
    q1 := 'INSERT INTO ||tablename||"("||destColumns||","||destMeasures||","||Table_Name)"||
        " (select "||sourceColumns||","||sourceMeasures||","||sourceTableName||" FROM "||sourceTableName||" "||whereClause||
                              " GROUP BY "||sourceColumns||")';
   EXECUTE IMMEDIATE q1;


 end loop;
 close c1;

 End;

When I compile the above procedure getting following error.

Error starting at line : 3 in command -
BEGIN CommonProcedure('MIS_CDR_01_01',1); END;
Error report -
ORA-00903: invalid table name
ORA-06512: at "AMTEL_MIS.PROCESSCDRCOMMONPROCEDURE", line 35
ORA-06512: at line 1
00903. 00000 -  "invalid table name"
*Cause:    
*Action:

I have checked the table names and all the parameters getting the error.

Note: The above procedure is example one of the kind procedure that I'm creating right now.

Please assist me further.

Thanks in advance.

Answer 1

I think you want to have the following :

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 );


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);

  Cursor c1 Is
  SELECT Table_Name, Table_Columns, Table_Measure, t3_Where_Clause
    FROM t2;

Begin
 Open c1;
 Loop
   Fetch c1 Into tablename,destcolumns,destMeasures,whereClause;
   Exit When c1%notfound;
    q1 := 'INSERT INTO '||tablename||'('||destColumns||','||destMeasures||','||tablename||')'||
        ' ( SELECT '||sourceColumns||','||sourceMeasures||','||sourceTableName
          ||' FROM '||sourceTableName||' '||whereClause||
           ' GROUP BY '||sourceColumns||')';

   Execute Immediate q1;
 End Loop;

 Close c1;

End;