Creating temporary tables within pl/sql

I need to wirte a procedure in which i create temp tables and insert values in it from a select statement.
What would be the best way to do it?

Appreciate your help
NShaikhAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
In addition to permanent tables, you can create temporary tables to hold session-private
data that exists only for the duration of a transaction or session.
The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table
that can be transaction-specific or session-specific. For transaction-specific temporary
tables, data exists for the duration of the transaction, while for session-specific
temporary tables, data exists for the duration of the session. Data in a session is private
to the session. Each session can only see and modify its own data. DML locks are not
acquired on the data of the temporary tables. The clauses that control the duration of
the rows are:
• ON COMMIT DELETE ROWS to specify that rows are only visible within the
transaction
• ON COMMIT PRESERVE ROWS to specify that rows are visible for the entire
session
You can create indexes, views, and triggers on temporary tables and you can also use
the Export and Import utilities to export and import the definition of a temporary table.
However, no data is exported, even if you use the ROWS option. The definition of a
temporary table is visible to all sessions.
0
 
TriskelionCommented:
Investigate
   CREATE CURSOR
0
 
schwertnerCommented:
rem -------------------------------------------------------
rem Purpose:    Demonstrate Oracle 8i temporary tables
rem -------------------------------------------------------

drop table x
/

create global temporary table x (a date)
        on commit delete rows     -- Delete rows after commit
        -- on commit preserve rows   -- Delete rows after exit session
/

select table_name, temporary, duration
from   user_tables
where  table_name = 'X'
/

insert into x values (sysdate);

select * from x;

commit;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
schwertnerCommented:
-- Inserted rows are missing after commit
select * from x;
0
 
schwertnerCommented:
So finaly a proposal looks like:

CREATE GLOBAL TEMPORARY TABLE  x
ON COMMIT PRESERVE ROWS
AS SELECT * FROM your_table;

0
 
NShaikhAuthor Commented:
This is the perfect answer.I will create a global temporary table and insert data in it  from a procedure and rows will be deleted on commit.

Thank you so much
Nisa

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.