CrystalProgDev
Programmer
I have stmtvar to have the selct statement in my procedure.
stmtvar= 'select * from A,B,C ....lots of logoc in there.....'
I wanted to execute and insert the data to a temp table called DATA.
and again I need two more temp tables from Above temp table DATA. One is to contain A specific data called ADATA. another is BDATA, which will contain B specific data.
Join ADATA And BDATA on ID to get the final result.
Can any one please provide me the sample code for the above scenario.
I tried the below...But getting error: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML.
SELECTSTMT := 'SELECT * FROM A,B,C ......;
CREATETABLE :='create global temporary table DATA(Col1 CHAR (10),
Col2 CHAR (25) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE CREATETABLE;
DATATABLE := 'insert into DATA(Col1,Col2) values ('|| SELECTSTMT ||')';
EXECUTE IMMEDIATE DATATABLE;
SELECTSTMT := 'SELECT * FROM DATA';
DBMS_OUTPUT.PUT_LINE (SELECTSTMT);
stmtvar= 'select * from A,B,C ....lots of logoc in there.....'
I wanted to execute and insert the data to a temp table called DATA.
and again I need two more temp tables from Above temp table DATA. One is to contain A specific data called ADATA. another is BDATA, which will contain B specific data.
Join ADATA And BDATA on ID to get the final result.
Can any one please provide me the sample code for the above scenario.
I tried the below...But getting error: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML.
SELECTSTMT := 'SELECT * FROM A,B,C ......;
CREATETABLE :='create global temporary table DATA(Col1 CHAR (10),
Col2 CHAR (25) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE CREATETABLE;
DATATABLE := 'insert into DATA(Col1,Col2) values ('|| SELECTSTMT ||')';
EXECUTE IMMEDIATE DATATABLE;
SELECTSTMT := 'SELECT * FROM DATA';
DBMS_OUTPUT.PUT_LINE (SELECTSTMT);