Temp tables in stored procedure

[CrystalProgDev]

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

 

[Andrzejek]

Have you considered:

[pre]
CREATE TABLE new_table
AS
SELECT *
FROM old_table
[/pre]

More info about it here
If you have right privileges to do so.

Have fun.

---- Andy

 

[CrystalProgDev]

I have the select stmt in a variable. I need to have it a variable for some conditional from and whre clauses. I need to come up with temp tables and get the output into temp tables for further joins.

 

[carp]

Why not just define the temp table outside of your procedure and then populate it from within the procedure? That way all you would need to do is an insert, which is easily done with a concatenated "WHERE" clause. It would also free you from the overhead of needless dynamic DDL, since the table would be there whenever your procedure needed it. A possible side benefit would be that the same table would be accessible from other procedures should you need it in the future.