Hi Folks,
I am working on Oracle 8i. I need to create a stored procedure that creates a temp table on the fly. I will then insert some records in the temp table and then I need to query the temp table.
Can you pls tell me how to do this in Oracle8i. I am used to work on Sybase and it was pretty simple there.
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
... creating and dropping of the table on every execution of the procedure is not a good idea, because it will consume a lot of resources. Much better is to use the temporary table already created, so no parsing will be needed. Also, the temporary table's data are visible only in this session (even after commit).
You need to use dynamic sql in your pl sql program. Here is the syntax
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
It would create a regular table. You will have to drop it when you finished or instead you can create "real" temp table.Look into the syntax and option of the
CREATE GLOBAL TEMPORARY TABLE command
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.
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;
-- Inserted rows are missing after commit
select * from x;
So finaly a proposal looks like:
CREATE GLOBAL TEMPORARY TABLE x
ON COMMIT PRESERVE ROWS
AS SELECT * FROM your_table;
Dear: schwertner
I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.
See: http://www.experts-exchang
Paragraph: Site Update for Wednesday, November 06, 2002
By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.
Thanks !
modulo
Community Support Moderator
Experts Exchange
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Answered by : Split between hayrabedian and schwertner
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
SDutta
EE Cleanup Volunteer
Business Accounts
Answer for Membership
by: hayrabedianPosted on 2002-10-29 at 10:51:16ID: 7384356
you can create temporary table like:
CREATE GLOBAL TEMPORARY TABLE t (c1 number);
And use it in your procedure.