Thanks for the question, Chirag.

Asked: May 08, 2001 - 7:45 am UTC

Last updated: July 26, 2009 - 7:15 am UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Hi Tom,
In a stored procedure I am trying to create a temporary table, select values from an existing table and insert those values into the temporary table i just created.

My stored procedure is:

create or replace procedure temp_table
as
cid INTEGER;
create_table varchar2(255);
temp_slot varchar2(255);
pl_sql varchar2(255);
iid RAW(16);
nname varchar2(255);

cursor_name pls_integer default dbms_sql.open_cursor;
ignore pls_integer;
BEGIN
CURSOR_NAME:= DBMS_SQL.OPEN_CURSOR;

--creating a temporary table
create_table :='CREATE GLOBAL TEMPORARY TABLE tempslot(iddd raw(16),nnname varchar2(255))';

--my existing table
SELECT ID, name INTO IID, nname FROM Temptest;

PL_SQL:='INSERT INTO TEMPslot values(IID, nname)';

dbms_sql.parse(cursor_name, CREATE_TABLE, dbms_sql.native);
dbms_sql.parse(cursor_name, pL_SQL, dbms_sql.native);
ignore := dbms_sql.execute(cursor_name);
--dbms_sql.parse(cid,create_table, dbms_sql.v7);
dbms_sql.close_cursor(cursor_name);

END;

-- drop table tempslot;

The above procedure compiles fine, but when I execute it, it gives me an error which is:
SQL> exec temp_table;
BEGIN temp_table; END;

*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "EBYZ.TEMP_TABLE", line 24
ORA-06512: at line 1

I have tried creating 2 cursors as well in the same API, the oracle user who is executing has the necessary previledges for DBMS_SYS_SQL and DBMS_SQL as well.
Am I missing out somewhere.
Thanks in advance

Chirag

and Tom said...

That is the incorrect way to use temporary tables in Oracle -- it might be the right way in Sybase or Informix -- but not Oracle.

In Oracle, you should create the global temporary table ONCE. DDL is hugely expensive, it commits any outstanding work, you have to drop the table yourself (which again commits), and unless you name them uniquely -- it will cause you to be able to run this stored procedure serially (only ONE person at a time).

The correct way to code the above will be:

create global temporary table tempslot ( iddd raw(16), nnname varchar2(255) );

create or replace procedure temp_table
as
begin
insert into tempslot select id, name from temptest;
end;
/

and that is it.

If you persist down this incorrect path of creating a temporary table inside of the procedure, you would have to code like this:

create or replace procedure temp_table
as
iid RAW(16);
nname varchar2(255);
begin
execute immediate 'create global temporary table tempslot (.....';

SELECT ID, name INTO IID, nname FROM Temptest;

execute immediate 'insert into tempslot values ( :id, :name )'
using iid, nname;

insert into tempslot select id, name from temptest;
end;
/

You have to use BIND variables - you tried to execute the insert statement:

INSERT INTO TEMPslot values(IID, nname)

here, IID and NNAME were assume to be column names -- you weren't in "plsql" anymore. you should have executed:

INSERT INTO TEMPslot values(:IID, :nname)

and made calls to dbms_sql.bind_variable to bind those values in.

I urge you to rethink your approach!!!! temporary tables are not to be created "on the fly" in Oracle.

followup to first comment below.

I totally disagree. If you use temporary tables properly -- that is you create them ONCE and never do it "on the fly" (as urged above) -- you'll not be dropping temporary tables. If your application needs to drop temporary tables it creates on the fly -- your application is not coded using best practices. DDL is HUGELY expensive, it should not be done in a production system.

That bug you reference *might* affect a development system where you have a run away session due to a bug, but there you would just bounce the instance if you really need to drop that table right away to change its definition.

follow up to comment #2 below...

I suppose, if you read the answer, you would have seen thats included in the answer as well.

Look for the paragraph that says
"if you persist down this incorrect path"...
you'll find that I told him everything you mention below (and even gave him the code with native dynamic sql) -- told him of bind variables.

One thing i did not do however was to even suggest the:
'insert into table(col1,col2) values('||to_char(IID)||','''||namevar||''''||')'

approach as that is the single worst thing you could do -- to skip using bind variables.

I always take the opportunity to not only ANSWER the question -- but to point out obviously flawed approaches. Why tell a person how to play russian roulette but not tell them they will probably shoot themselves, causing great pain or death??

So, the problem is in fact -- that this is NOT the right way to do it, the right way is explained but if they wish to shoot themselves -- that is included as well.

Rating

(41 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kenneth Whitaker, May 08, 2001 - 9:13 am UTC

Don't recommand using GLOBAL TEMPORARY TABLE until this bug is fixed.
BUG:1055420, CANNOT DROP A GLOBAL TEMPORARY TABLE AFTER INSERTING SESSION HAS BEEN KILLED

It appears that PMON even after the session is killed does not release all resources held by the killed session, which results in the ORA-14452 even though you think the session has already been removed.

dynamic sql

andrew lapides, May 08, 2001 - 1:23 pm UTC

the problem is not whether or not to use the temp tables, but rather why the code doesn't run.

When sql string is created in this example it is created as is. One can output it (using dbms_outout for example) and see that the value clause will look like this: "values(IID,..." Of course this can't be executed.

The correct approach will be either to bind variables or convert them properly:

plsql:='insert into table(col1,col2) values('||to_char(IID)||','''||namevar||''''||')';

one can output this string and will see the correct values clause with real values.

another way on 8 is to use NDS (native dynamic sql)

Chi, May 09, 2001 - 9:54 am UTC

Kenneth Whitaker, May 09, 2001 - 10:34 am UTC

I totally agree with you about dropping the temp table.
We starting using temp tables worked great until we needed a change to the table - had to bounce the database - thus the fix could not be made until that weekend - stopping their work - well they cheated and added a "x" to end of the new table until the bounced. I think the bug is serious enough to limit use of this type of tables in a production database, assuming a table change may be needed.

Tom

A reader, September 13, 2001 - 10:10 am UTC

".
BUG:1055420, CANNOT DROP A GLOBAL TEMPORARY TABLE AFTER INSERTING SESSION HAS
BEEN KILLED
"

Has this been fixed. Which version has got this fixed. What was this bug about.

September 13, 2001 - 11:08 am UTC

It'll be fixed in Oracle9i release 2 and patches for it may be available as needed for 8i.

It basically meant that if a session was accessing a temp table and that session was killed, you could not drop the temp table until the database was restarted. This would ONLY affect a development system -- not a production system -- as you would not be dropping and recreating temporary tables in a production system, they would be statically defined.

Use or Cursor

Srimal, September 13, 2001 - 1:55 pm UTC

Hello Mr Tom,

I come in from Sybase background and I have transitioned out of Temp tables by effectively using Cursors and the results have been phenomenal...

Thank you for showing how to use temp tables though...

Srimal

Behaviour of global temporary tables MIS database (Read only mode)

Ramesh Golkonda, March 14, 2002 - 8:39 am UTC

I have created a package which generates a report and stores it in globla temporary table. The MIS database was created in read only mode would it be possible to generate and store the report in global temporary table?

March 14, 2002 - 10:09 am UTC

well, tablespaces are read only and a database can be opened in read only mode -- not sure which you mean here.

If you have read only tablespaces -- then you are OK. If you have a database opened in read only mode, you cannot do DML, even on temp tables.

Ravi, March 14, 2002 - 11:03 am UTC

Hi Tom,
Can we not use this instead of your response (below):
create or replace procedure temp_test as iid RAW(16); nname varchar2(255);
BEGIN
execute immediate 'create global temporary table tempslot (.....';
insert into tempslot select id, name from temptest;
end;
/
Do we have to use Bind Variables and EXECUTE immediate with 'INSERT ...USING'?? Please clarify. Thanks. Somewhat confused.
---- Your answer:

create or replace procedure temp_table
as
iid RAW(16);
nname varchar2(255);
begin
execute immediate 'create global temporary table tempslot (.....';

SELECT ID, name INTO IID, nname FROM Temptest;

execute immediate 'insert into tempslot values :id, :name )' using iid, nname;

insert into tempslot select id, name from temptest;
end;
/

March 14, 2002 - 11:12 am UTC

sure, it would not have the check that there is AT LEAST one and AT MOST one row in the table temptest -- you should add:

if ( sql%rowcount != 1 ) then
raise_application_error( -20001, 'expected 1 row, got ' || .... );
end if;

after the insert into.

what if i have to alter the temporary table

john, August 05, 2002 - 3:11 pm UTC

tom,

in oracle 8i, if i have to drop a created global temporary table i understand i have restart the system.
what if i have to alter the temporary table say for example adding a new column. even then i have to restart the system.

can i put a temporary table in a normal tablespace.

please guide.

thanks
john

August 06, 2002 - 8:14 am UTC

No you do not.

You'll really need to "clarify" what you mean.


ops$tkyte@ORA817DEV.US.ORACLE.COM> create global temporary table t ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select rownum from all_objects;

22939 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
ops$tkyte@ORA817DEV.US.ORACLE.COM> connect 
Enter user-name: /
Connected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create global temporary table t ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select rownum from all_users;

43 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;   

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add y number;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> desc t;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------------------------------------
 X                                                                                NUMBER(38)
 Y                                                                                NUMBER

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM>


That shows you need not stop anything.  True -- if i was ACTUALLY USING the table, i would not be able to drop or modify it but that is true of "real" tables as well.

placing the temporary table in tablespace

john, August 06, 2002 - 1:35 pm UTC

1.can i place a global temporary table in a specified tablespace?
2.if yes it should be only in a temporary table space or any other tablespace

August 07, 2002 - 10:08 am UTC

1) no, it goes into the temp tablespace defined by the current schema id (eg: if you access a temp table in a stored procedure, the temp space will be allocated from the temporary tablespace of the OWNER of the procedure. If you access a temp table directly from SQL, it'll allocate space in the temp tablespace of the currently logged in user).

A temp table is an interesting thing -- it is one non-partitioned table that can exist in many tablespaces simultaneously - this is because it is really like a "partitioned table" where the partition key is a session or transaction id. Each "partition" is in its own (potentially) tablespace

2) only in temp tablespaces.

ORA-14452 - But ME, NOBODY is using this GTT

Robert, February 28, 2003 - 10:38 am UTC

Tom, in your reply to a 9/13/01 message, you saild :
>>This would ONLY affect a development system -- not a >>production system -- as you would not be dropping and
>> recreating temporary tables in a production system,
>> they would be statically defined.

I am puzzled...
What do you mean "not dropping GTT in prod" ?
Things change and I'd like to drop'em and re-define them in the way I like the columns arranged. What's wrong with that ?

And I searched out this page cause I got the error below:

LMS@LMS1 > drop table GLOBALTEMP_CBSI_DAILY_PROD ;
drop table GLOBALTEMP_CBSI_DAILY_PROD
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

I am not using this GTT, everyone has gone home...I kept re-connecting but Oracle won't let it happen, any idea ?

Thanks

February 28, 2003 - 11:28 am UTC

column arrangement is IRRELEVANT, you must use insert into gtt(c1,c2,....)

Also -- in production -- you cannot tell me that you just willy nilly drop and recreate objects just to get "columns in the right order" -- this is what VIEWS are all about.

But -- I do recall in a very early release of temp tables -- they could get into a state whereby you needed to "bounce" in order to drop them. It would happen if you KILLED a session that was inserting into them.

that was fixed in all current releases of 8iR3, 9iR1 and 9iR2

but if you are dropping them to put columns in order -- well.....

Correct Use of Temporary Table

Su Baba, August 10, 2003 - 6:27 pm UTC

Hi Tom,

I have a situation where I have a big query (comlex joins and
may return anywhere between 0 to 1 million rows). Let's call
it <Big Query>.

Then I have 10 other dynamic SQL's that use <Big Query> as a
subquery.

SELECT ...
FROM <some table>
WHERE customer_id IN (<Big Query>);

SELECT ...
FROM <some other table>
WHERE customer_id IN (<Big Query>);

and repeat for 8 other tables.

Does it make sense to insert the result of the <Big Query>
into a temporary table to avoid running the <Big Query> for
more than once.

Also I'm thinking about indexing the temporary table and
use dbms_stat to creat the statistics for the table:

SELECT COUNT(*)
INTO l_num_of_records
FROM <temp table name>;

dbms_stats.set_table_stats(ownname => USER,
tabname => <temp table name>,
numrows => l_num_of_records,
numblks => ?,
avgrlen => ?);

So for the 10 dynamic SQL's mentioned above, I'll rewrite them to

SELECT ...
FROM <some table>
WHERE customer_id IN (<temp table name>);

Is this the right approach?

thanks

August 10, 2003 - 7:54 pm UTC

version? (makes a big difference in this case)

Su Baba, August 10, 2003 - 8:10 pm UTC

Version: 8.1.7

August 10, 2003 - 8:26 pm UTC

in 8i -- that would be ok - but DO NOT select count(*) from it!!! you already KNOW how many rows you put in there (sql%rowcount, or sqlca.sqlerrd[2], or whatever attribute your language provides)

Also, if this is used by many people at the same time -- watch out -- there can only be ONE set of stats of course.

Su Baba, August 10, 2003 - 8:32 pm UTC

This is a batch process that would only be run by one person at any one time.

Is there a much better way to accomplish this in 9i?

August 10, 2003 - 8:47 pm UTC

we could skip the dbms_stats by using optimizer dynamic sampling in 9ir2, but that is the only change really.

Use dbms_stats to set statistics for a temporary table

A reader, August 11, 2003 - 1:45 pm UTC

Hi Tom,

Taken from the above example,

dbms_stats.set_table_stats(ownname => USER,
tabname => <temp table name>,
numrows => l_num_of_records,
numblks => ?,
avgrlen => ?);

How do we figure out numblks and avgrlen programmatically?

thax

August 11, 2003 - 2:19 pm UTC

well, you can just dbms_stats.gather_table_stats -- even on a temp table in 9i.

but you would "have to know" -- to programatically get them would be as compute instense as asking dbms_stats to get them (so the answer would be to use dbms_stats)

A reader, August 11, 2003 - 2:43 pm UTC

Sorry, Tom. The database version is 8.1.7. Can I still use dbms_stats to do that? thanks

August 11, 2003 - 3:15 pm UTC

no, the gather_temp parameter was not available to dbms-stats in that release.

A reader, August 11, 2003 - 3:41 pm UTC

So back to the question. How would I compute numblks and avgrlen? thanks

dbms_stats.set_table_stats(ownname => USER,
tabname => <temp table name>,
numrows => l_num_of_records,
numblks => ?,
avgrlen => ?);

August 11, 2003 - 5:05 pm UTC

you would use GATHER, not SET

A reader, August 11, 2003 - 5:39 pm UTC

Sorry for not being clear. I have Oracle 8.1.7 and would like to set the statistics for a temporary table. Since "gather stats" won't work for a temporary table in 8i, I assume I would have to compute numblks and avrglen myself before I can set the statistics. What algorithm should I use to compute these two parameters programmatically?

August 11, 2003 - 6:56 pm UTC

well, I would just "know" (guesstimate) what avgrlen is (just take a nice average given the type of data you put in there)

Then, you know how many rows you put in there (sql%rowcount)

rows * avgrlen = size of data

(blocksize - some_swag_at_overhead)/ size_of_data = nblocks estimate.

if I had an 8k block, i might use (7000) / size_of_data for example to "guestimate" the amount of data.

report print from global temporary table

Dulal, October 19, 2003 - 1:43 am UTC

Hi Tom,

I have successfully created a global temporary table and inserted data into this.
When I build a report using this temporary table there is no output but data in temporary table already exits. What is the problem?

I am using client/server with Oracle8i (8.1.6), Report Builder 6i(6.0.8.8.3), Win2000.

Kindly advice.

October 19, 2003 - 6:16 pm UTC

sounds just like what this other guy asked:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1086033238621#13120451359967 <code>

Global Temporary Table with Autonomous Transaction

Sami, December 24, 2003 - 10:56 am UTC

Dear Tom,

Happy Holidays.

Thanks for all your support and help.

We are using GLOBAL TEMPORARY TABLE(transaction scope) in stored procedure(part of the package). The functionality of the
stored procedure is to return CLOB in customized XML format(8i does not have great XML features like 9i. Till 9i upgrade we have to live with this).
We use TEMPORARY table to store list of records based on the search condition provided by Web-based application
and construct XML CLOB based on the records stored in the TEMPORARY table.Once the XML clob is constricted
we can destroy the record by issuing COMMIT statement.
(Many web-application API is calling the stored procedure to get the result in XML format.)
But the problem here is that the application which is invoking this stored procedure may come up with some pending transactions, so we don't want to issue COMMIT.

(Question-1)
What would be your recommendation here?
A) Use of autonomous transaction for the search stored procedure
B) Use DELETE statement before we store anything into TEMPORARY table

(Question-2)
Is there any known issues with connection pool application using TRANSACTION based GLOBAL TEMPORARY TABLE.

I know we can not use "ON COMMIT PRESERVE ROWS" because the same database session will be used by different web-application users.

Thanks in advance,
Sami

December 24, 2003 - 11:10 am UTC

i don't know why you need a gtt for this at all?

you can use an automous transaction, sure. (but I don't know why you would need to put the rows into a gtt, just to read them out to format a clob? why not just read from the source in the first place)

there are no issues I'm aware of with connection pools and gtt's as long as they are cleaned out between connection pool "grabs"

How to use ARRAY values in "IN clause"

Sami, December 24, 2003 - 11:41 am UTC

Dear Tom,

Thank you so much for your very quick response.
[Sorry,the TEMP table values are used in predicator not to construct XML clob]

PROCEDURE get_query_results (
uid_array IN string_varray, ----> Array of UserIDs
name_array IN string_varray, ----> this is to ensure correct order of the SQL output
xml_output OUT CLOB ----> XML output.
) is
..
..

The stored procedure is getting String Array(user defined string_varray) from Java(Sorry not from DB, I was wrong) and that array values should be used in "IN clause" of select statement as a predicator. The select statements output will be used to construct XML clob.

I use ARRAY_TO_COMMA_SEPARATED_VALUE will take String Array and return VARCHAR2(all array values with comma separated).

A) Either I have to use dynamic SQL to prepare statement like

'select * from Tab1 where userid in ( '||ARRAY_TO_COMMA_SEPARATED_VALUE(uid_array)||');';

OR

(B)
ARRAY_TO_COMMA_SEPARATED_VALUE can insert values into TEMP table(from uid_array) and my query can be modified as

select * from Tab1 where userid in (select userid from TEMP_TABLE)

Which one would be better in terms of performance? Do you suggest any other better way of doing it?

Thanks Again
Sami

December 24, 2003 - 1:26 pm UTC

i use method A myself -- but it does NOT need dynamic sql.

search for

varying in list

on this site for the technique.

Insert performance of gtt vs normal heap table

Peter Tran, March 24, 2004 - 12:01 am UTC

Hi Tom,

A coworker recently showed this to me.  Do you do have any light you can shed on this?

Thanks for your help.

-Peter

------------------------

This is interesting...  It appears that CREATE-AS-SELECT produces equivalent timing, but if the tables are created first and then populated, the timing diverges significantly.  And if a PK is added it takes even more time to populate the temp table.  These timings are consistently repeatable.

This testing was done on cmsr2@soldb2.

SQL> CREATE TABLE objects
  2  AS
  3  SELECT object_id, object_name, object_type, owner
  4    FROM dba_objects
  5   WHERE object_id IS NOT NULL
  6     AND ROWNUM < 100000;

Table created.

Elapsed: 00:00:01.04

SQL> CREATE global temporary TABLE temp_objects
  2  ON COMMIT DELETE ROWS
  3  AS
  4  SELECT object_id, object_name, object_type, owner
  5    FROM dba_objects
  6   WHERE object_id IS NOT NULL
  7     AND ROWNUM < 100000;

Table created.

Elapsed: 00:00:01.04


SQL> rollback;

Rollback complete.


SQL> drop table objects;

Table dropped.


SQL> drop table temp_objects;

Table dropped.


SQL> CREATE TABLE objects (
  2   OBJECT_ID     NUMBER,
  3   OBJECT_NAME   VARCHAR2(128),
  4   OBJECT_TYPE   VARCHAR2(18),
  5   OWNER         VARCHAR2(30)
  6  );

Table created.


SQL> create global temporary table temp_objects
  2  on commit delete rows
  3  as
  4  select * from objects where 1=0;

Table created.


SQL> INSERT INTO objects (object_id, object_name, object_type, owner)
  2  SELECT object_id, object_name, object_type, owner
  3    FROM dba_objects
  4   WHERE object_id IS NOT NULL
  5     AND ROWNUM < 100000;

38468 rows created.

Elapsed: 00:00:01.06


SQL> INSERT INTO temp_objects (object_id, object_name, object_type, owner)
  2  SELECT object_id, object_name, object_type, owner
  3    FROM dba_objects
  4   WHERE object_id IS NOT NULL
  5     AND ROWNUM < 100000;

38468 rows created.

Elapsed: 00:00:04.05

SQL> rollback;

Rollback complete.

Elapsed: 00:00:02.03


SQL> drop table objects;

Table dropped.


SQL> drop table temp_objects;

Table dropped.


SQL> CREATE TABLE objects (
  2   OBJECT_ID     NUMBER,
  3   OBJECT_NAME   VARCHAR2(128),
  4   OBJECT_TYPE   VARCHAR2(18),
  5   OWNER         VARCHAR2(30)
  6  );

Table created.

 
SQL> ALTER TABLE objects
  2    ADD  ( CONSTRAINT objects_PK
  3           PRIMARY KEY (OBJECT_ID)
  4  );

Table altered.

 
SQL> create global temporary table temp_objects
  2  on commit delete rows
  3  as
  4  select * from objects where 1=0;

Table created.

 
SQL> ALTER TABLE temp_objects
  2    ADD  ( CONSTRAINT temp_objects_PK
  3           PRIMARY KEY (OBJECT_ID)
  4  );

Table altered.


SQL> INSERT INTO objects (object_id, object_name, object_type, owner)
  2  SELECT object_id, object_name, object_type, owner
  3    FROM dba_objects
  4   WHERE object_id IS NOT NULL
  5     AND ROWNUM < 100000;

38470 rows created.

Elapsed: 00:00:02.02


SQL> INSERT INTO temp_objects (object_id, object_name, object_type, owner)
  2  SELECT object_id, object_name, object_type, owner
  3    FROM dba_objects
  4   WHERE object_id IS NOT NULL
  5     AND ROWNUM < 100000;

38470 rows created.

Elapsed: 00:00:07.09

March 24, 2004 - 8:39 am UTC

if you are in noarchivelog mode, it looks like this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3329273788164#15940387620208 <code>

(the CTAS = no redo, insert = redo)

9.2.0.5 fixes the bug

Peter Tran, March 24, 2004 - 6:16 pm UTC

Hi Tom,

The database I'm testing against was 9.2.0.4 in noarchivedlog mode.

This bug is fix in 9.2.0.5 and 10g. I just reinstalled the 9.2.0.5 patch, and reran my test. The numbers are much better.

Thanks,
-Peter

-----------------------------------------------------

sqlplus> CREATE TABLE objects
2 AS
3 SELECT object_id, object_name, object_type, owner
4 FROM dba_objects
5 WHERE object_id IS NOT NULL
6 AND ROWNUM < 100000;

Table created.

Elapsed: 00:00:01.09

sqlplus> CREATE global temporary TABLE temp_objects
2 ON COMMIT DELETE ROWS
3 AS
4 SELECT object_id, object_name, object_type, owner
5 FROM dba_objects
6 WHERE object_id IS NOT NULL
7 AND ROWNUM < 100000;

Table created.

Elapsed: 00:00:01.07

sqlplus> drop table objects;

Table dropped.

Elapsed: 00:00:00.01

sqlplus> drop table temp_objects;

Table dropped.

Elapsed: 00:00:00.00

sqlplus> CREATE TABLE objects (
2 OBJECT_ID NUMBER,
3 OBJECT_NAME VARCHAR2(128),
4 OBJECT_TYPE VARCHAR2(18),
5 OWNER VARCHAR2(30)
6 );

Table created.

Elapsed: 00:00:01.00

sqlplus> create global temporary table temp_objects
2 on commit delete rows
3 as
4 select * from objects where 1=0;

Table created.

Elapsed: 00:00:01.00

sqlplus> INSERT INTO objects (object_id, object_name, object_type, owner)
2 SELECT object_id, object_name, object_type, owner
3 FROM dba_objects
4 WHERE object_id IS NOT NULL
5 AND ROWNUM < 100000;

39682 rows created.

Elapsed: 00:00:03.01

Statistics
----------------------------------------------------------
891 recursive calls
3028 db block gets
33818 consistent gets
0 physical reads
2247796 redo size
625 bytes sent via SQL*Net to client
946 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
39682 rows processed

sqlplus> INSERT INTO temp_objects (object_id, object_name, object_type, owner)
2 SELECT object_id, object_name, object_type, owner
3 FROM dba_objects
4 WHERE object_id IS NOT NULL
5 AND ROWNUM < 100000;

39682 rows created.

Elapsed: 00:00:03.01

Statistics
----------------------------------------------------------
9 recursive calls
1464 db block gets
33315 consistent gets
0 physical reads
141152 redo size
628 bytes sent via SQL*Net to client
951 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
39682 rows processed

sqlplus> CREATE TABLE objects (
2 OBJECT_ID NUMBER,
3 OBJECT_NAME VARCHAR2(128),
4 OBJECT_TYPE VARCHAR2(18),
5 OWNER VARCHAR2(30)
6 );

Table created.

Elapsed: 00:00:00.00

sqlplus> ALTER TABLE objects
2 ADD ( CONSTRAINT objects_PK
3 PRIMARY KEY (OBJECT_ID)
4 );

Table altered.

Elapsed: 00:00:00.02

sqlplus> create global temporary table temp_objects
2 on commit delete rows
3 as
4 select * from objects where 1=0;

Table created.

Elapsed: 00:00:00.00

sqlplus> ALTER TABLE temp_objects
2 ADD ( CONSTRAINT temp_objects_PK
3 PRIMARY KEY (OBJECT_ID)
4 );

Table altered.

Elapsed: 00:00:00.00
sqlplus> INSERT INTO objects (object_id, object_name, object_type, owner)
2 SELECT object_id, object_name, object_type, owner
3 FROM dba_objects
4 WHERE object_id IS NOT NULL
5 AND ROWNUM < 100000;

39684 rows created.

Elapsed: 00:00:04.06

Statistics
----------------------------------------------------------
1361 recursive calls
36830 db block gets
34505 consistent gets
2 physical reads
8143996 redo size
629 bytes sent via SQL*Net to client
946 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
39684 rows processed

sqlplus> INSERT INTO temp_objects (object_id, object_name, object_type, owner)
2 SELECT object_id, object_name, object_type, owner
3 FROM dba_objects
4 WHERE object_id IS NOT NULL
5 AND ROWNUM < 100000;

39684 rows created.

Elapsed: 00:00:04.04

Statistics
----------------------------------------------------------
209 recursive calls
34676 db block gets
34069 consistent gets
0 physical reads
3496400 redo size
630 bytes sent via SQL*Net to client
951 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
39684 rows processed

Is this possible in ORACLE

Rick, March 25, 2004 - 3:34 pm UTC

I will put the pseudo code here

procedure1 ( xxxxx, variable_A)

and then in another procedure

procedure_2(xxxx,xxxx)
variable_A = "happy"

in order words I want to be able to reference
a variable that has been defined in
procedure2 in procedure1 IN THE
INPUT AREA. I Hope this make sense.

March 25, 2004 - 9:26 pm UTC

nope, no sense.... not sure what you are asking.

what is an "input area"?

but basically variable_a's scope is procedure1 -- if you code:

procedure p1( x, y )
as
...
procedure p2( ... )
as
begin
y := .....;
end;
begin
......

that'll work, but otherwise, Y (variable_a) is private to procedure1.

I have a similar question

sonali, April 29, 2004 - 3:10 pm UTC

I created a global temporary table
Create global temporary TABLE mwebWorkCalcs_Temp
( WorkCalcs_Work_ID number(10,0) PRIMARY KEY,
WorkCalcs_Work_Level number(10,0));

Then I have a procedure that does some math on this table..

create or replace procedure pr_work_calculations

insert into mwebWorkCalcs_Temp (blah, blah blah)...
...some other updates on this temp table...
execute immediate 'Create TABLE mwebworkcalcs as select * from mwebWorkCalcs_Temp';
Commit;
END pr_work_calculations;
/

I want to write that data into a permanant table at the end of the procedure so other sessions can see it.

I get the following error
SQLWKS> execute pr_work_calculations
ORA-01031: insufficient privileges
ORA-06512: at "V51TST.PR_WORK_CALCULATIONS", line 410
ORA-06512: at line 2

Whats wrong with my dynalic sql..

If I just copy paste.. this
SQLWKS> Create TABLE mwebworkcalcs as select * from mwebWorkCalcs_Temp
2>
Statement processed.

It works , means I have correct privileges to create the table, actually I gave dba privilege to this user for this test.. but still get same error, why ?

Thanks

April 29, 2004 - 3:43 pm UTC

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

performance of global temprary table created from object types

Thiru, July 22, 2004 - 10:22 am UTC

I have created a global temp table of an object type.

create global temporary table my_gtemp_tbl of my_obj on commit preserve rows;

insert into my_gtemp_tbl select * from other_tbl;

This is an insert of around 20000 rows only. But the insert takes 5 secs. Can this in anyway related to global table created from object types. Can i increase the performance of an insert. There are no indexes on the global table.

Thank's

July 23, 2004 - 7:50 am UTC

need entire example -- i don't know how I can state that more clearly on the page you used to post this.  I need a simple concise, yet 100% complete example -- in the fashion I give to you in answer to questions.


ops$tkyte@ORA9IR2> create or replace type myType as object ( x number, y date, z varchar2(30) );
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create global temporary table gtt of myType on commit preserve rows;
 
Table created.
 
ops$tkyte@ORA9IR2> create global temporary table gtt2 (x number, y date, z varchar2(30) ) on commit preserve rows;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> insert into gtt select myType(object_id,created,object_name) from all_objects where rownum <= 20000;
 
20000 rows created.
 
Elapsed: 00:00:01.13
ops$tkyte@ORA9IR2> insert into gtt2 select object_id,created,object_name from all_objects where rownum <= 20000;
 
20000 rows created.
 
Elapsed: 00:00:00.96
ops$tkyte@ORA9IR2> set timing off

Planning to use GTT

Naivedya, August 29, 2005 - 8:47 am UTC

Hi Tom,

I have a requirement where I think I want to use a GTT. I select some records based on a certain query, this record set is passed on using a REF cursor, from a procedure.
A Pro*C program picks up the first 5 records (record set has 10 records) from this REF cursor & makes a packet, it passes the packet to a Java client, which the requesting user receives, through a Router.

Once the records are handed over to the user the Pro*c thread is disconnected over idle period of 10 mins..

As per the new requirement, I need to hold the remaining records from this record set (6-10), so that if the client wants, he can have the remaining list.
(for this changes are to be done in Pro*C, Java client & Procedure which passes the REF cursor).

We have around 3000 users acorss globe, who would be requesting this at any given time.

Can I use a GTT ?, to hold the entire record set, & create a new procedure which can send the data to Pro*C ?
we want to avoid creating new permanent tables in the Production DB for this.

Will this data be available between sessions?, since the Pro*c will disconnect the session ....
or can I retain the data till my transaction gets over ....

August 29, 2005 - 1:39 pm UTC

I don't see how the global temporary table in this case would be superior to the ref cursor you already have.

If the session is disconnected, both "disappear", that is, a ref cursor has the same lifespan as a global temporary table with "on commit preserve rows" - and vice versa.

Inserting into a temporary table

Anoop, October 21, 2005 - 11:40 am UTC

I am doing the following:

1. declare a global temporary table
2. Then in the database

CREATE OR REPLACE FUNCTION pass_f (sortorder IN VARCHAR2)
RETURN STRARRAY
AS
LANGUAGE JAVA NAME 'com.mountain.molehill.crm.client.CRMTransferClient.transactionDetails(java.lang.String)return oracle.sql.ARRAY[]';
/

CREATE OR REPLACE PROCEDURE lmuk_transaction_p
AS
var_tab strarray;(strarray is a collections type)

TYPE rec IS RECORD (
lv_var1 VARCHAR2 (50),
lv_var2 VARCHAR2 (50),
lv_var3 VARCHAR2 (50),
lv_var4 VARCHAR2 (200),
lv_var5 VARCHAR2 (50),
lv_var6 VARCHAR2 (50),
lv_var7 VARCHAR2 (50),
lv_var8 VARCHAR2 (50)
);

TYPE tbl IS TABLE OF rec
INDEX BY PLS_INTEGER;

ltbl tbl;
cnt NUMBER;
j NUMBER;
BEGIN
SELECT pass_f ('85768846013','CSR_L4','TAAAAA','85768846013','ASSA','N',
NULL,'','','Anand','Raj',NULL )INTO var_tab FROM DUAL;
cnt := 1;

FOR i IN 1 .. var_tab.COUNT
LOOP
IF i = cnt
THEN
ltbl(j).lv_var1 := var_tab (i);
ltbl(j).lv_var2 := var_tab (i + 1);
ltbl(j).lv_var3 := var_tab (i + 2);
ltbl(j).lv_var4 := var_tab (i + 3);
ltbl(j).lv_var5 := var_tab (i + 4);
ltbl(j).lv_var6 := var_tab (i + 5);
ltbl(j).lv_var7 := var_tab (i + 6);
ltbl(j).lv_var8 := var_tab (i + 7);

cnt := cnt + 8;

FORALL j IN 1 .. ltbl.COUNT
-- insert into a global temporary table lmuk_temp_transaction_t
INSERT INTO lmuk_temp_transaction_t
VALUES ltbl (j);
END IF;
END LOOP;
END;

But this is giving me the following error:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "APPS.LMUK_TRANSACTION_P", line 46
ORA-06512: at line 4

Even if I succeed in getting the data into the global temp table How can I read that into my Oracle Forms 6i.

October 21, 2005 - 11:44 am UTC

look at line 46. what is line 46.

Line 46

Anoop, October 22, 2005 - 3:09 am UTC

Hi Tom,
Line 46 is where I am trying to insert the returns into the record thru the table type.
46 ltbl (j).lv_var1 := var_tab (i);

What is the error here?

October 22, 2005 - 10:39 am UTC

look at j, you never set it, logic looks "botched"

Thanks a Lot.

Anoop, October 22, 2005 - 5:28 am UTC

I am able to get the records into a Global Temporary table.
Can I base my Forms on this table? What are the alternatives.

When I am trying to base my form on a REF Cursor I am getting
"FRM-10771 :Arguments of Type Ref Cursor not permitted".

Table Temporary - Forms 6.0.5.0.2 - DB 8.0

Valéria, January 20, 2006 - 4:04 pm UTC

Hi,

How create table temporary into forms?

Tks

January 20, 2006 - 4:31 pm UTC

does not compute. Don't know what you mean.

GTT inserts and stats

Reader, March 07, 2007 - 5:32 pm UTC

If we do not have representative stats on a temp table, will the insert into the temp table slow down ? . As far as my understanding goes "stats" are required by the CBO to arrive at an optimal execution plan. However, in one of our test systems - after setting representative stats on the temp table the insert performance improved. Can you pls help me understand what could be the reason for this ?

Thanks in advance

March 07, 2007 - 7:33 pm UTC

setting stats on the table to be inserted into would likely have.....

no effect whatsoever on the performance.

the stats are important on things being READ, not written to...

What are they good for?

Paulo, March 08, 2007 - 8:48 am UTC

Tom, I've never (fortunately) worked with SQL Server, Sybase and the like so the temporary table concept is not really clear to me. What do we need them for? Can you give any real life examples?

Oh, and before I forget: "Tks, ur site is great 4 dba's"
Hahahaha

March 08, 2007 - 11:01 am UTC

<quote src=Expert Oracle Database Architecture>

Temporary Tables

Temporary tables are used to hold intermediate resultsets, for the duration of either a transaction or a session. The data held in a temporary table is only ever visible to the current session¿no other session will see any other session¿s data, even if the current session COMMITs the data. Multiuser concurrency is not an issue with regard to temporary tables either, as one session can never block another session by using a temporary table. Even if we ¿lock¿ the temporary table, it will not prevent other sessions using their temporary table As we observed in Chapter 9, temporary tables generate significantly less redo than regular tables would. However, since they must generate undo information for the data they contain, they will generate some amount of redo. UPDATEs and DELETEs will generate the largest amount; INSERTs and SELECTs the least amount.

Temporary tables will allocate storage from the currently logged-in user¿s temporary tablespace, or if they are accessed from a definer rights procedure, the temporary tablespace of the owner of that procedure will be used. A global temporary table is really just a template for the table itself. The act of creating a temporary table involves no storage allocation; no INITIAL extent is allocated, as it would be for a regular table. Rather, at runtime when a session first puts data into the temporary table, a temporary segment for that session will be created. Since each session gets its own temporary segment (not just an extent of an existing segment), every user might be allocating space for her temporary table in different tablespaces. USER1 might have his temporary tablespace set to TEMP1, so his temporary tables will be allocated from this space. USER2 might have TEMP2 as her temporary tablespace, and her temporary tables will be allocated there.

Oracle¿s temporary tables are similar to temporary tables in other relational databases, with the main exception being that they are ¿statically¿ defined. You create them once per database, not once per stored procedure in the database. They always exist¿they will be in the data dictionary as objects, but they will always appear empty until your session puts data into them. The fact that they are statically defined allows you to create views that reference temporary tables, to create stored procedures that use static SQL to reference them, and so on.

....

....

I find many times people use temporary tables because they learned in other databases that joining too many tables in a single query is a ¿bad thing.¿ This is a practice that must be unlearned for Oracle development. Rather then trying to out-smart the optimizer and breaking what should be a single query into three or four queries that store their subresults into temporary tables, and then combining the temporary tables, you should just code a single query that answers the original question. Referencing many tables in a single query is OK; the temporary table crutch is not needed in Oracle for this purpose.

In other cases, however, the use of a temporary table in a process is the correct approach. For example, I once wrote a Palm sync application to synchronize the date book on a Palm Pilot with calendar information stored in Oracle. The Palm gives me a list of all records that have been modified since the last hot synchronization. I must take these records and compare them against the live data in the database, update the database records, and then generate a list of changes to be applied to the Palm. This is a perfect example of when a temporary table is very useful. I used a temporary table to store the changes from the Palm in the database. I then ran a stored procedure that bumps the Palm-generated changes against the live (and very large) permanent tables to discover what changes need to be made to the Oracle data, and then to find the changes that need to come from Oracle back down to the Palm. I have to make a couple of passes on this data. First, I find all records that were modified only on the Palm and make the corresponding changes in Oracle. I next find all records that were modified on both the Palm and my database since the last synchronization and rectify them. Then I find all records that were modified only on the database and place their changes into the temporary table. Lastly, the Palm sync application pulls the changes from the temporary table and applies them to the Palm device itself. Upon disconnection, the temporary data goes away.
</quote>

A reader, August 28, 2008 - 12:33 pm UTC

Hi Tom,
Could you please tell me why it take so long to do the insert data into a Temporary Table inside Store Procedure compare to the straight Insert from SQLPLUS.
User the same statement INSERT as:
Insert into TMP_GTT
Select t1.a, t2.b, t3.c, t4.d, ....t12.l
from t1, t2, t3, t4,...t12
where ....
....

When execute the store procedure (has only the insert statement), it took 30 minutes. But if I execute the Insert statement from SQLPLUS, it took 1 minute & 30 sec.

Thanks.

August 29, 2008 - 10:52 pm UTC

tkprof it and post the results of each.

I'll bet - almost 100% sure - in plsql you are running a different query....

A reader, September 03, 2008 - 12:08 am UTC

Hi Tom, 
Could you please tell me why it take so long to do the insert data into a Temporary Table inside Store Procedure compare to the straight Insert from SQLPLUS. 
User the same statement INSERT as: 
Insert into TMP_GTT 
Select t1.a, t2.b, t3.c, t4.d, ....t12.l 
from t1, t2, t3, t4,...t12 
where .... 
.... 

When execute the store procedure (has only the insert statement), it took 30 minutes. But if I execute the Insert statement from SQLPLUS, it took 1 minute & 30 sec. 

Thanks. 


Followup   August 29, 2008 - 10pm US/Eastern:

tkprof it and post the results of each. 

I'll bet - almost 100% sure - in plsql you are running a different query....

I use the same Insert statement to insert data into a Temporary table. Since we're off Monday & I've to wait DBA extract the trace file.

Here is the tkprof when calling the store procedure

TKPROF: Release 9.2.0.6.0 - Production on Tue Sep 2 16:48:13 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: xxxxx_ora_xxxxx.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

INSERT INTO TEMP_RPI_TBL 
SELECT E.CHANNEL_ID, E.ON_DATE, 
  NEXT_DAY(E.ON_DATE-7,:B10 ), E.START_TIME, E.START_TIME + E.DURATION, 
  E.DURATION, ETD.CC_SOURCE_CODE, DECODE(CN.NETWORK_TYPE,500000681,
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,ES.CATEGORY,PS.CATEGORY), 
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,EB.CATEGORY,PB.CATEGORY)), 
  DECODE(CN.NETWORK_TYPE,500000681,DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  ES.COUNTRY_OF_ORIGIN,PS.COUNTRY_OF_ORIGIN), DECODE(PROG.PROGRAM_KIND_CODE,
  4270001,EB.COUNTRY_OF_ORIGIN,PB.COUNTRY_OF_ORIGIN)), DECODE(CN.NETWORK_TYPE,
  500000682,DECODE(PROG.PROGRAM_KIND_CODE,4270001,EB.BROADCAST_ORIGIN_POINT,
  PB.BROADCAST_ORIGIN_POINT), NULL), DECODE(CN.NETWORK_TYPE,500000681,
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,ES.EXHIBITION_CODE,PS.EXHIBITION_CODE)
  , DECODE(PROG.PROGRAM_KIND_CODE,4270001,EB.COMPOSITION_CODE ,
  PB.COMPOSITION_CODE)), DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  CEC.CONTENT_SOURCE,CPC.CONTENT_SOURCE), DECODE(CN.NETWORK_TYPE,500000681,
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,ES.PRODUCER_1,PS.PRODUCER_1), 
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,EB.PRODUCER_1 ,PB.PRODUCER_1)), 
  DECODE(CN.NETWORK_TYPE,500000681,DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  ES.PRODUCER_2,PS.PRODUCER_2), DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  EB.PRODUCER_2 ,PB.PRODUCER_2)), DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  CEC.PRODUCTION_LOCATION_1,CPC.PRODUCTION_LOCATION_1), 
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,CEC.PRODUCTION_LOCATION_1_PERCENT,
  CPC.PRODUCTION_LOCATION_1_PERCENT), DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  CEC.PRODUCTION_LOCATION_2,CPC.PRODUCTION_LOCATION_2), 
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,CEC.PRODUCTION_LOCATION_2_PERCENT,
  CPC.PRODUCTION_LOCATION_2_PERCENT), DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  CEC.PRODUCTION_LOCATION_3,CPC.PRODUCTION_LOCATION_3), 
  DECODE(PROG.PROGRAM_KIND_CODE,4270001,CEC.PRODUCTION_LOCATION_3_PERCENT,
  CPC.PRODUCTION_LOCATION_3_PERCENT), SYSDATE FROM EVENT E, EVENT_RUN ER, 
  EVENT_COMPOSITION EC, PROGRAM PROG, EPISODE EPIS, CHANNEL_NETWORK CN, 
  CHANNEL CH, PROGRAM_BROADCAST PB, PROGRAM_SPECIALITY PS, EPISODE_BROADCAST 
  EB, EPISODE_SPECIALITY ES, C_PROGRAM_CLASSIFICATION CPC, 
  C_EPISODE_CLASSIFICATION CEC, EVENT_TECHNICAL_DATA ETD WHERE EC.ON_DATE 
  BETWEEN :B9 AND :B8 AND BITAND(:B7 , POWER( 2, (TO_NUMBER(TO_CHAR( 
  E.ON_DATE, 'D') ) -1 ) )) <> 0 AND E.CHANNEL_ID = CH.CHANNEL_ID AND 
  E.CHANNEL_ID = CN.CHANNEL_ID AND ER.ON_DATE = E.ON_DATE AND ER.EVENT_ID = 
  E.EVENT_ID AND ER.DETAIL_ID = E.DETAIL_ID AND E.START_TIME >= :B6 AND :B5 >=
   DECODE(:B5 ,0,0,(E.START_TIME + E.DURATION)) AND E.DAY_TYPE_ID = 
  DECODE(:B4 ,0,E.DAY_TYPE_ID,:B4 ) AND EC.ON_DATE = ER.ON_DATE AND 
  EC.DETAIL_ID = ER.DETAIL_ID AND EC.EVENT_COMPOSITION_ID = 
  ER.EVENT_COMPOSITION_ID AND EC.PROGRAM_ID = EPIS.PROGRAM_ID (+) AND 
  EC.EPISODE_ID = EPIS.EPISODE_ID (+) AND PROG.PROGRAM_ID = EC.PROGRAM_ID AND 
  PROG.COMPANY_POLICY = :B3 AND ETD.ON_DATE = E.ON_DATE AND 
  ETD.EVENT_TECHNICAL_DATA_ID = E.EVENT_TECHNICAL_DATA_ID AND EC.PROGRAM_ID = 
  PB.PROGRAM_ID (+) AND EC.PROGRAM_ID = EB.PROGRAM_ID (+) AND EC.EPISODE_ID = 
  EB.EPISODE_ID (+) AND EC.PROGRAM_ID = PS.PROGRAM_ID (+) AND EC.PROGRAM_ID = 
  ES.PROGRAM_ID (+) AND EC.EPISODE_ID = ES.EPISODE_ID (+) AND EC.PROGRAM_ID = 
  CPC.PROGRAM_ID (+) AND EC.PROGRAM_ID = CEC.PROGRAM_ID (+) AND EC.EPISODE_ID 
  = CEC.EPISODE_ID (+) AND 
  CBC_PKG_BROADCAST_RPT.CBC_FCT_ORIGINAL_HOURS(ER.TELECAST_NUMBER,
  ER.DETAIL_ID) >= :B2 AND DECODE(PROG.PROGRAM_KIND_CODE,4270001,
  EB.BROADCAST_ORIGIN_POINT,PB.BROADCAST_ORIGIN_POINT) = DECODE(:B1 ,0,
  500000436, DECODE(PROG.PROGRAM_KIND_CODE,4270001,EB.BROADCAST_ORIGIN_POINT,
  PB.BROADCAST_ORIGIN_POINT)) ORDER BY E.CHANNEL_ID, E.ON_DATE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1   1579.42    2148.30      76953   24929744      82103       53981
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2   1579.43    2148.32      76953   24929744      82103       53981

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1743     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  53981  SORT ORDER BY (cr=27661707 r=77017 w=0 time=2168482799 us)
  53981   CONCATENATION  (cr=27661707 r=77017 w=0 time=2167234416 us)
  53981    FILTER  (cr=27661707 r=77017 w=0 time=2167198474 us)
  53981     NESTED LOOPS  (cr=27661707 r=77017 w=0 time=2167153836 us)
 341926      NESTED LOOPS OUTER (cr=26977853 r=77017 w=0 time=2162352480 us)
 341926       NESTED LOOPS OUTER (cr=26267234 r=77006 w=0 time=2151391350 us)
 341926        NESTED LOOPS OUTER (cr=25583840 r=77003 w=0 time=2147546126 us)
 341926         NESTED LOOPS OUTER (cr=24900430 r=76997 w=0 time=2143739958 us)
 341926          NESTED LOOPS OUTER (cr=24279981 r=76922 w=0 time=2138659725 us)
 341926           NESTED LOOPS OUTER (cr=23660978 r=76817 w=0 time=2133202134 us)
 341926            NESTED LOOPS OUTER (cr=23126242 r=76809 w=0 time=2130044803 us)
 341926             NESTED LOOPS  (cr=23122804 r=76807 w=0 time=2128158776 us)
 341926              NESTED LOOPS  (cr=22086391 r=76706 w=0 time=2121390834 us)
1677211               NESTED LOOPS  (cr=15575367 r=75858 w=0 time=2004925844 us)
1677211                NESTED LOOPS  (cr=13898154 r=75858 w=0 time=1992246113 us)
5543578                 NESTED LOOPS  (cr=7189310 r=68405 w=0 time=1869523653 us)
     76                  TABLE ACCESS FULL CHANNEL (cr=15 r=0 w=0 time=3612 us)
5543578                  PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=7189295 r=68405 w=0 time=1866723705 us)
5543578                   TABLE ACCESS BY LOCAL INDEX ROWID EVENT PARTITION: KEY KEY (cr=7189295 r=68405 w=0 time=1863823515 us)
5593708                    INDEX RANGE SCAN EVENT_ONDATE_CHANNEL_IDX PARTITION: KEY KEY (cr=3765948 r=16599 w=0 time=1460964886 us)(object id 54519)
1677211                 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=6708844 r=7453 w=0 time=99338566 us)
1677211                  TABLE ACCESS BY LOCAL INDEX ROWID EVENT_TECHNICAL_DATA PARTITION: KEY KEY (cr=6708844 r=7453 w=0 time=92302405 us)
1677211                   INDEX UNIQUE SCAN EVENT_TECHNCL_DATA_ID_DATE_PK PARTITION: KEY KEY (cr=5031633 r=2600 w=0 time=44525085 us)(object id 55068)
1677211                TABLE ACCESS BY INDEX ROWID CHANNEL_NETWORK (cr=1677213 r=0 w=0 time=8508732 us)
1677211                 INDEX UNIQUE SCAN CHANNEL_NETWORK_PK_IDX (cr=2 r=0 w=0 time=2802378 us)(object id 44857)
 341926               TABLE ACCESS BY INDEX ROWID EVENT_RUN (cr=6511024 r=848 w=0 time=111382420 us)
 341926                INDEX RANGE SCAN EVENT_RUN_PK_IDX (cr=3419906 r=560 w=0 time=23676300 us)(object id 51151)
 341926              TABLE ACCESS BY INDEX ROWID EVENT_COMPOSITION (cr=1036413 r=101 w=0 time=5444400 us)
 341926               INDEX UNIQUE SCAN EVENT_COMP_ON_DATE_IDX (cr=683854 r=101 w=0 time=3621422 us)(object id 41970)
   3436             TABLE ACCESS BY INDEX ROWID C_EPISODE_CLASSIFICATION (cr=3438 r=2 w=0 time=776854 us)
   3436              INDEX UNIQUE SCAN C_EPISODE_CLASSIFICATION_PK (cr=2 r=0 w=0 time=505506 us)(object id 44889)
 192808            TABLE ACCESS BY INDEX ROWID C_PROGRAM_CLASSIFICATION (cr=534736 r=8 w=0 time=2183909 us)
 192808             INDEX UNIQUE SCAN C_PROGRAM_CLASSIFICATION_PK (cr=341928 r=1 w=0 time=1143260 us)(object id 86827)
 276987           TABLE ACCESS BY INDEX ROWID EPISODE_SPECIALITY (cr=619003 r=105 w=0 time=4305215 us)
 276987            INDEX UNIQUE SCAN EPISODE_SPECIALITY_PK_IDX (cr=341928 r=22 w=0 time=2011892 us)(object id 44953)
 278521          TABLE ACCESS BY INDEX ROWID EPISODE_BROADCAST (cr=620449 r=75 w=0 time=3947050 us)
 278521           INDEX UNIQUE SCAN EPISODE_BROADCAST_PK_IDX (cr=341928 r=28 w=0 time=2030345 us)(object id 44941)
 341482         TABLE ACCESS BY INDEX ROWID PROGRAM_SPECIALITY (cr=683410 r=6 w=0 time=2781355 us)
 341482          INDEX UNIQUE SCAN PROGRAM_SPECIALITY_PK_IDX (cr=341928 r=2 w=0 time=1308375 us)(object id 45013)
 341466        TABLE ACCESS BY INDEX ROWID PROGRAM_BROADCAST (cr=683394 r=3 w=0 time=2814329 us)
 341466         INDEX UNIQUE SCAN PROGRAM_BROADCAST_PK_IDX (cr=341928 r=2 w=0 time=1257436 us)(object id 44999)
 330872       TABLE ACCESS BY INDEX ROWID EPISODE (cr=710619 r=11 w=0 time=9919952 us)
 341926        INDEX UNIQUE SCAN EPISODE_UK_IDX (cr=341928 r=11 w=0 time=1680328 us)(object id 51606)
  53981      TABLE ACCESS BY INDEX ROWID PROGRAM (cr=683854 r=0 w=0 time=4013994 us)
 341926       INDEX UNIQUE SCAN PROGRAM_PK_IDX (cr=341928 r=0 w=0 time=1396292 us)(object id 42891)
      0    FILTER  (cr=0 r=0 w=0 time=1 us)
      0     FILTER  
      0      NESTED LOOPS OUTER 
      0       NESTED LOOPS OUTER 
      0        NESTED LOOPS OUTER 
      0         NESTED LOOPS  
      0          NESTED LOOPS OUTER 
      0           NESTED LOOPS OUTER 
      0            NESTED LOOPS  
      0             NESTED LOOPS  
      0              NESTED LOOPS  
      0               NESTED LOOPS  
      0                NESTED LOOPS OUTER 
      0                 NESTED LOOPS OUTER 
      0                  NESTED LOOPS  
      0                   TABLE ACCESS FULL PROGRAM 
      0                   TABLE ACCESS BY INDEX ROWID EVENT_COMPOSITION 
      0                    INDEX RANGE SCAN EVENT_COMP_PROGRAM_FK_IDX (object id 41969)
      0                  TABLE ACCESS BY INDEX ROWID EPISODE_SPECIALITY 
      0                   INDEX UNIQUE SCAN EPISODE_SPECIALITY_PK_IDX (object id 44953)
      0                 TABLE ACCESS BY INDEX ROWID EPISODE_BROADCAST 
      0                  INDEX UNIQUE SCAN EPISODE_BROADCAST_PK_IDX (object id 44941)
      0                TABLE ACCESS BY INDEX ROWID EVENT_RUN 
      0                 INDEX RANGE SCAN EVENT_RUN_PK_IDX (object id 51151)
      0               TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION 
      0                INDEX UNIQUE SCAN EVENT_PK (object id 41917)
      0              TABLE ACCESS BY INDEX ROWID CHANNEL 
      0               INDEX UNIQUE SCAN CHANNEL_PK_IDX (object id 41245)
      0             PARTITION RANGE ITERATOR PARTITION: KEY KEY 
      0              TABLE ACCESS BY LOCAL INDEX ROWID EVENT_TECHNICAL_DATA PARTITION: KEY KEY 
      0               INDEX UNIQUE SCAN EVENT_TECHNCL_DATA_ID_DATE_PK PARTITION: KEY KEY (object id 55068)
      0            TABLE ACCESS BY INDEX ROWID C_EPISODE_CLASSIFICATION 
      0             INDEX UNIQUE SCAN C_EPISODE_CLASSIFICATION_PK (object id 44889)
      0           TABLE ACCESS BY INDEX ROWID EPISODE 
      0            INDEX UNIQUE SCAN EPISODE_UK_IDX (object id 51606)
      0          TABLE ACCESS BY INDEX ROWID CHANNEL_NETWORK 
      0           INDEX UNIQUE SCAN CHANNEL_NETWORK_PK_IDX (object id 44857)
      0         TABLE ACCESS BY INDEX ROWID C_PROGRAM_CLASSIFICATION 
      0          INDEX UNIQUE SCAN C_PROGRAM_CLASSIFICATION_PK (object id 86827)
      0        TABLE ACCESS BY INDEX ROWID PROGRAM_SPECIALITY 
      0         INDEX UNIQUE SCAN PROGRAM_SPECIALITY_PK_IDX (object id 45013)
      0       TABLE ACCESS BY INDEX ROWID PROGRAM_BROADCAST 
      0        INDEX UNIQUE SCAN PROGRAM_BROADCAST_PK_IDX (object id 44999)

and here is the tkprof when using a straigh SQL insertion

TKPROF: Release 9.2.0.6.0 - Production on Tue Sep 2 17:58:13 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: xxxxx_ora_xxxxx.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

        Insert into temp_rpi_tbl
        Select e.channel_id, e.on_date, next_day(e.on_date-7,'Monday'), e.start_time,
              e.start_time + e.duration, e.duration, etd.cc_source_code,
          decode(cn.network_type,500000681,decode(prog.program_kind_code,4270001,es.category,ps.category),
                                   decode(prog.program_kind_code,4270001,eb.category,pb.category)),
        decode(cn.network_type,500000681,decode(prog.program_kind_code,4270001,es.country_of_origin,ps.country_of_origin),
                                   decode(prog.program_kind_code,4270001,eb.country_of_origin,pb.country_of_origin)),     decode(cn.network_type,500000682,decode(prog.program_kind_code,4270001,eb.broadcast_origin_point,pb.broadcast_origin_point),null),
          decode(cn.network_type,500000681,decode(prog.program_kind_code,4270001,es.exhibition_code,ps.exhibition_code),
                                   decode(prog.program_kind_code,4270001,eb.composition_code ,pb.composition_code)),
          decode(prog.program_kind_code,4270001,cec.content_source,cpc.content_source),
          decode(cn.network_type,500000681,decode(prog.program_kind_code,4270001,es.producer_1,ps.producer_1),
                                   decode(prog.program_kind_code,4270001,eb.producer_1 ,pb.producer_1)),
          decode(cn.network_type,500000681,decode(prog.program_kind_code,4270001,es.producer_2,ps.producer_2),
                                   decode(prog.program_kind_code,4270001,eb.producer_2 ,pb.producer_2)),
          decode(prog.program_kind_code,4270001,cec.production_location_1,cpc.production_location_1),
          decode(prog.program_kind_code,4270001,cec.production_location_1_percent,cpc.production_location_1_percent),
          decode(prog.program_kind_code,4270001,cec.production_location_2,cpc.production_location_2),
          decode(prog.program_kind_code,4270001,cec.production_location_2_percent,cpc.production_location_2_percent),
          decode(prog.program_kind_code,4270001,cec.production_location_3,cpc.production_location_3),
          decode(prog.program_kind_code,4270001,cec.production_location_3_percent,cpc.production_location_3_percent),
          sysdate
         From event e, event_run er, event_composition ec, program prog, episode epis,
              channel_network cn, channel ch, program_broadcast pb, program_speciality ps,
              episode_broadcast eb, episode_speciality es, c_program_classification cpc,
              c_episode_classification cec, event_technical_data etd
         Where ec.on_date between '07-01-01' and '07-06-30'
           and bitand(127, power(  2, (to_number(to_char( e.on_date, 'D') ) -1  ) )) <> 0
           and e.channel_id = ch.channel_id
           and e.channel_id = cn.channel_id
           and er.on_date = e.on_date
           and er.event_id = e.event_id
           and er.detail_id = e.detail_id
           and e.start_time >= 0
           and 5183999 >= decode(5183999,0,0,(e.start_time + e.duration))
           and e.day_type_id = decode(0,0,e.day_type_id,0)
           and ec.on_date = er.on_date
           and ec.detail_id = er.detail_id
           and ec.event_composition_id = er.event_composition_id
           and ec.program_id = epis.program_id (+)
           and ec.episode_id = epis.episode_id (+)
           and prog.program_id = ec.program_id
           and prog.company_policy = '110'
           and etd.on_date = e.on_date
           and etd.event_technical_data_id = e.event_technical_data_id
               and ec.program_id = pb.program_id (+)
           and ec.program_id = eb.program_id (+)
           and ec.episode_id = eb.episode_id (+)
           and ec.program_id = ps.program_id (+)
           and ec.program_id = es.program_id (+)
           and ec.episode_id = es.episode_id (+)
           and ec.program_id = cpc.program_id (+)
           and ec.program_id = cec.program_id (+)
           and ec.episode_id = cec.episode_id (+)
           and cbc_pkg_broadcast_rpt.cbc_fct_original_hours(er.telecast_number,er.detail_id) >= 0
           and decode(prog.program_kind_code,4270001,eb.broadcast_origin_point,pb.broadcast_origin_point) =
                     decode(0,0,500000436,
                             decode(prog.program_kind_code,4270001,eb.broadcast_origin_point,pb.broadcast_origin_point))
        order by e.channel_id, e.on_date

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.18       0.17          0         22          0           0
Execute      1     27.10      26.58          0    1802450     105199       53981
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     27.28      26.76          0    1802472     105199       53981

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1743

both of them using the same parameter values as:

P_STARTDATE DATE = '2007-01-01';
P_ENDDATE DATE = '2007-06-30';
P_STARTTIME INTEGER = 0;
P_ENDTIME INTEGER = 5183999;
P_OTHER INTEGER = 0;
P_DOW INTEGER = 127;
P_WEEKDAY INTEGER = 1082002;
P_SCHEDULE INTEGER = 0;
P_PROG_STIME INTEGER = 2052000;
P_PROG_ETIME INTEGER = 2484000;
P_HOUR INTEGER = 0;
LANGUAGE_ID_ INTEGER = 1;
COMPANY_TAG_ VARCHAR2 = '110';
P_WEEKDAY VARCHAR2 = 'Monday';

Thanks Tom.

P.S.: both tkprof has been trim out of the unrelate to my question (all SQL of other users), otherwise it'll be a long message.

September 03, 2008 - 10:39 am UTC

entirely different queries. do you not see that? Do you see a single bind variable in the second one? It is full of literal sql.

...
ec.on_date between '07-01-01' and '07-06-30'
.....
that is the wrong way to do it, always - always use to_date and an explicit date format to convert strings into dates.

A reader, September 03, 2008 - 12:11 am UTC

Sorry........I'm missing a last part... as

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.18       0.17          0         22          0           0
Execute      1     27.10      26.58          0    1802450     105199       53981
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     27.28      26.76          0    1802472     105199       53981

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1743

A reader, September 03, 2008 - 1:39 pm UTC

Followup   September 3, 2008 - 10am US/Eastern:

entirely different queries. do you not see that? Do you see a single bind variable in the second one? It is full of literal sql. 

... 
ec.on_date between '07-01-01' and '07-06-30' 
..... 
that is the wrong way to do it, always - always use to_date and an explicit date format to convert strings into dates.

Hi Tom,
Sorry not to mention this:
Due to the date format default in NLS_SESSION_PARAMETERS set as "RR-MM-DD", that's why I let the date as "RR-MM-DD" in my Insert statement or event I set as "ec.on_date between to_date('2007-01-01','yyyy-mm-dd') and to_date('2007-06-30','yyyy-mm-dd')" and I still get approx. 54000 records.
My question is why it take so long (1 hour) if I got the insertion from Store Procedure compare to a straight Insert statement (using the same parameters' value).

Thanks again.

September 03, 2008 - 4:28 pm UTC

... Due to the date format default in NLS_SESSION_PARAMETERS ...

never rely on that, what happens when someone decides to change it. Always use a date format to convert a string into a date. Never rely on the default format, anyone can change that on you and then what.

You ran two entirely different queries.

select * from t where column = :x;

is not even remotely close to

select * from t where column = 5;

http://www.google.com/search?q=site%3Atkyte.blogspot.com+bind+peeking

it has nothing to do with a stored procedure versus 'straight insert', they are both 'straight inserts'.

A reader, September 03, 2008 - 10:59 pm UTC

Thanks for the advise ... I'm always use the date format as "yyyy-mm-dd" in my coding. Except in this case I use the default format setup (the short way) in order to compare with the one in store procedure ... :)

and thank for the link ... it's enlight me a lot.

Rgds,
T.

Use of GTT in Procedures Supplying Data to a Web Application

Ramesh, September 12, 2008 - 3:12 pm UTC

Hi Tom,

Is the use of GTT appropriate in Procedures Supplying Data to a Web Application ?

Since GTT's partition data by session and a Web application may reuse a single session to handle multiple transactions by way of connection pooling - i suspect the answer is No. Do you agree ?

If you agree - 2 followup q's

a) Is there a way to partition data in a GTT by 'single invocation of a procedure' ?

b) what other techniques would you suggest in such a scenario

Thanks in Advance

September 16, 2008 - 3:23 pm UTC

normally a web application will

a) grab a connection
b) use it to do a transaction
c) "ungrab" it

so, gtt's are perfectly safe - during (b) you own the connection - you do not

a) grab
b) do something
c) ungrab
d) grab
e) do something more, in conjunction with b)
f) ungrab

if you do - you are doing it WRONG.

so, grab the connection, load the gtt, call the procedure, upon success commit, ungrab connection.

ORA-14452

Reene, October 29, 2008 - 10:35 am UTC

Hi

there is a metalink note explain the resolution of ora-14552. this has helped me in resolving this error.

Note:252091.1

Thanks

Insert into Temp Table.

JP, July 24, 2009 - 4:56 am UTC

Tom,

I have a query, which gets executed returns around 200K records under 3 mins which is acceptable. But when i try to insert this data into a Global Temp table its taking more than an hour. I have to insert this data into Temp table as i have to use this data for various rollups and sub sections in a report.

My question - are there any limitations/bottle necks when we are inserting into temporary tables?

DB Version :
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Temp Tablespace:
Tablespace Name Block Size Initial Extent Next Extent
TEMP 8192 1048576 1048576

Thanks
-JP

July 26, 2009 - 7:15 am UTC

No, there is not.

ops$tkyte%ORA10GR2> drop table gtt;

Table dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create global temporary table gtt
  2  as
  3  select 0 l, all_objects.* from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set timing on
ops$tkyte%ORA10GR2> insert into gtt
  2  select level l, d.*
  3    from (select * from all_objects where rownum = 1 ) d
  4  connect by level <= 200000
  5  /

200000 rows created.

Elapsed: 00:00:01.03
ops$tkyte%ORA10GR2> set timing off

that is a virtual machine running on a laptop.

check

a) plans are the same (tkprof)
b) that you don't have 1,000 indexes (they have to be maintained)

tkprof will be the first thing to do here - to see where the time is spent

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

dynamic sql

Tom

Use or Cursor

Behaviour of global temporary tables MIS database (Read only mode)

what if i have to alter the temporary table

placing the temporary table in tablespace

ORA-14452 - But ME, NOBODY is using this GTT

Correct Use of Temporary Table

Use dbms_stats to set statistics for a temporary table

report print from global temporary table

Global Temporary Table with Autonomous Transaction

How to use ARRAY values in "IN clause"

Insert performance of gtt vs normal heap table

9.2.0.5 fixes the bug

Is this possible in ORACLE

I have a similar question

performance of global temprary table created from object types

Planning to use GTT

Inserting into a temporary table

Line 46

Thanks a Lot.

Table Temporary - Forms 6.0.5.0.2 - DB 8.0

GTT inserts and stats

What are they good for?

Use of GTT in Procedures Supplying Data to a Web Application

ORA-14452

Insert into Temp Table.

More to Explore

PL/SQL demos

PL/SQL docs

insert into Temporary tables

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

dynamic sql

Tom

Use or Cursor

Behaviour of global temporary tables MIS database (Read only mode)

what if i have to alter the temporary table

placing the temporary table in tablespace

ORA-14452 - But ME, NOBODY is using this GTT

Correct Use of Temporary Table

Use dbms_stats to set statistics for a temporary table

report print from global temporary table

Global Temporary Table with Autonomous Transaction

How to use ARRAY values in "IN clause"

Insert performance of gtt vs normal heap table

9.2.0.5 fixes the bug

Is this possible in ORACLE

I have a similar question

performance of global temprary table created from object types

Planning to use GTT

Inserting into a temporary table

Line 46

Thanks a Lot.

Table Temporary - Forms 6.0.5.0.2 - DB 8.0

GTT inserts and stats

What are they good for?

Use of GTT in Procedures Supplying Data to a Web Application

ORA-14452

Insert into Temp Table.

More to Explore

PL/SQL demos

PL/SQL docs