INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Temp tables in stored procedure

Temp tables in stored procedure

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

RE: Temp tables in stored procedure

Have you considered:

CREATE TABLE new_table
AS
SELECT *
  FROM old_table
 

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

Have fun.

---- Andy

RE: Temp tables in stored procedure

(OP)
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.

RE: Temp tables in stored procedure

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close