Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Temp tables in stored procedure

Status
Not open for further replies.

CrystalProgDev

Programmer
Oct 1, 2010
69
US
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);
 
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
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top