Need to create and reference a temp table in procedure

[elaine3839]

Hi gurus,

I need to create a global temp table in a procedure to save intermediate result, and then create a cursor based on the temp table for record update. However, I was unable to declare the cursor in the declaration section of the procedure as that temp table hasn't been created yet.

So I tried to write another procedure (let's call it 2nd proc) doing nothing but create that temp table, so I can call it in my original proc. But the problem still exists 'cause during the declaration time, the 2nd proc has been called yet.

How can I resolve this problem?

Many thanks!

[slimdave]

Create a permanent table of the "temporary" type and reference that. Have a read of the Concepts Guide -- there ought to be no reason for creating tables on the fly like this.

http://download-west.oracle.com/docs...chem.htm#16097

[elaine3839]

Thanks for your reply!

I am not quite clear on what is 'permanent table of the temporary type'? Do you mean I just create a permanent table but treat it as a temporary one?

I read the temp table (same page as the link you gave belo) this afternoon and looks like there are quite a few benefit of create temp table instead of perm table. e.g. no redo log, will be truncate automatically when sesson or transaction is done. And since this table can potentially be big, I really don't want to create it permanently unless I have to.

Can you elaborate your point a little bit more? Thanks!

Originally posted by slimdave
Create a permanent table of the "temporary" type and reference that. Have a read of the Concepts Guide -- there ought to be no reason for creating tables on the fly like this.

http://download-west.oracle.com/docs...chem.htm#16097

[slimdave]

You use "Create Global Temporary Table ... On Commit Delete/Presrve Rows;" to create one of these tables. The space usage will be minimal while the table is empty, and the overhead will be much less than using a regular table, especially compared to creating and dropping the permanent table to make it "temporary".

It'sa much lighter weight mechanism, and is intended for precisely the scenario you have.

[elaine3839]

I may not have made it clear in my original post --

Create global temporary table was exactly what I did. The problem is, I tried to create the temp table inside a sproc, and in that same sporc, I need to create a cursor to reference that temp table. Since the declaration section is prior to the bgin section (in a sproc), PL/SQL always complain I was trying to reference a table (that temp table which I referenced in the cursor) that was not exist.

How can I get around of that?

Thanks!

Elaine

Originally posted by slimdave
You use "Create Global Temporary Table ... On Commit Delete/Presrve Rows;" to create one of these tables. The space usage will be minimal while the table is empty, and the overhead will be much less than using a regular table, especially compared to creating and dropping the permanent table to make it "temporary".

It'sa much lighter weight mechanism, and is intended for precisely the scenario you have.

[marist89]

Originally posted by elaine3839
I may not have made it clear in my original post --

Create global temporary table was exactly what I did. The problem is, I tried to create the temp table inside a sproc, and in that same sporc, I need to create a cursor to reference that temp table.

That's kinda the point of a GTT. You create it outside the procedure. When you use it inside a procedure, it is guaranteed to be empty. This way you can build procedures/packages with the GTT and you don't have to write a bunch of messy dynamic pl/sql.

[elaine3839]

Thanks -- that's very clear!

Just wonder, will oracle also limit the transaction to the procedure scope (i.e. it will auto commit when the sproc end), or it won't commit until I issue a commit explicitly? Trying to figure out when I create the temp table, shall I should use On Commit Delete or Presrve Rows?

Originally posted by marist89
That's kinda the point of a GTT. You create it outside the procedure. When you use it inside a procedure, it is guaranteed to be empty. This way you can build procedures/packages with the GTT and you don't have to write a bunch of messy dynamic pl/sql.

[marist89]

Originally posted by elaine3839
Just wonder, will oracle also limit the transaction to the procedure scope (i.e. it will auto commit when the sproc end), or it won't commit until I issue a commit explicitly? Trying to figure out when I create the temp table, shall I should use On Commit Delete or Presrve Rows?

Does a procedure normally commit when it completes?

[elaine3839]

Yeah, 99% of the time it will. But for that rare 1% occasion, will Oracle commit it for you anyway? :-)

Originally posted by marist89
Does a procedure normally commit when it completes?

[marist89]

Originally posted by elaine3839
Yeah, 99% of the time it will.

Ah, I don't think so.