Temporary table insert before creation problem! 3

[hazelsisson]

Hello,

I have a PL/SQL procedure that creates a temporary global table (after checking that it doesn't already exist). Later on in the procedure, values are inserted into the table (after checking again to see if it exists).

The problem is, when I compile the code I get the error "PL/SQL: ORA-00942: table or view does not exist", which points to the insert statement. This is because the table has not yet been created, but if the code before that particular line had been executed the table would exist!

It all works fine if I create the table first in SQL*Plus but obviously this is not practical.

Is there any way I can get round this problem? I'm using Oracle 9i JDeveloper.

Thanks,
Hazel

 

[carp]

I think the only way around this is to do your inserts via "EXECUTE IMMEDIATE" commands. This way, your insert command is viewed as a text string at compile time, so the compiler is unaware of the table dependency.

You could create the table first, and then get it to compile but then, of course, if you drop the table, it invalidates the code, so it needs to be compiled but can't be because the table is missing, so you create the table ....

Or - just create the table and don't drop it!

 

[sem]

I think you should create that table as a part of deployment. In fact DDL operations are not encouraged within normal work, because they're quite heavy for database. As an option you may use another "temporary" storage (e.g. package variable of some collection type)

Regards, Dima

http://seminihin.narod.ru

 

[THoey]

Sounds like you create it permanently and then just truncate it before or after your procedure uses it to make sure it is empty. The simple size of an empty structure can't cause too much overhead...

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[kevineva]

In general, this type of question indicates that you're probably not using temporary tables in a way that makes sense in Oracle.
If your plan is to create a table, fill it with values, do something to the rows you've generated, then drop the table, this is *not* what Oracle intends to support with temp tables. Use a ref cursor instead. (This is the single biggest mindset-change from SQLServer/Sybase that I've seen).
If this table is supposed to be a staging table -- load it up, then put the resulting rows somewhere else, then lose the rows -- a temp table is the perfect solution. But the way to handle it in Oracle is to create it once, then let the database handle purging when you commit, or when you drop the session (the ON COMMIT PRESERVE ROWS parameter).
Temp tables generate almost no redo and rollback. They
clean themselves out upon commit or session end. Those are their advantages. Trying to create them in PL/SQL doesn't make sense...

 

[hazelsisson]

Thanks very much for all your replies, you've given me lots of helpful suggestions.

The purpose of my temporary table is to store data resulting from a cursor, which can then be sorted by the column name I specify and output to the screen. Then when different data is extracted from the cursor I would replace all the rows in the temp table with this new data.

It looks like if I carry on with using temp tables I will have to create them first, or try the execute immediate command. I will also look into the use of Ref Cursors or other collection objects as an alternative.

Thanks again for your help everyone.

Hazel

 

[carp]

It seems like it would be easier to sort the data when the cursor is populated. REF cursors will allow you to specify the ORDER BY clause so that when you open the cursor and the data will already be sorted.

 

[kevineva]

How are you 'output to screen'ing? Most languages can deal with ref cursors in some way shape or form. That sounds like the best way to meet your requirements.

 

[sem]

I suppose you may use server-side dynamic sql. In general this may be even more efficient, because indexes on original tables may be utilized.

Regards, Dima

http://seminihin.narod.ru

 

[hazelsisson]

I successfully solved the problem by implementing ref cursors, so thanks very much for all your advice!

Hazel