Temporary tables

Hi all,

I have a question on temporary tables .
Can I use a temporary table as an ordinary table within its scope, i.e., can I run DML statements on it ?? If yes, is the syntax same as that for ordinary tables?

Any guidance in the right direction would be of much help.

Shiva.

Yes

Thanks for your response.
If YES, then here is my issue.
I need to create a temporary table, insert rows into it and then sort the records based on a particular field.
I have simplified my procedure :
Here is it:

Declare
cursor s_shiva is
select distinct contact_type
from shiva order by contact_type;

contacttype varchar2(20);
a varchar2(500);
begin
a := 'create global temporary table shiva(customer_cd varchar2(20) NULL,contact_type varchar2(20) NULL,seq_no integer) NULL';
execute immediate a;
insert into shiva(customer_cd,contact_type,seq_no) values ('1','cust',1);
insert into shiva(customer_cd,contact_type,seq_no) values ('1','billing',1);
insert into shiva(customer_cd,contact_type,seq_no) values ('1','noc',1);
commit;

open s_shiva;
loop
fetch s_shiva into contacttype;
exit when s_shiva%notfound;
dbms_output.put_line(contacttype);
end loop;
close s_shiva;

end;

But it gives me an error saying identifier 'Shiva' must be declared wherever I have used it. Is this not the right way of creation or is it that I should affix a qualifier or some kind to the temporary table name before using it.

Any help or atleast a direction in which I can find some sample is appreciated.

Shiva.

Hi,

First, check what version of Oracle u are using. If it is not O8 then probably you should user DBMS_SQL package to create the table on the fly.

Second, If you want to create a table using Dynamic SQL, you must have CREATE TABLE or CREATE ANY TABLE privileges granted directly to your schema. Remember that roles are disabled during PL/SQL compilation and execution.

My guess would be that u must not have the necessary privileges to exeucte that DDL statement from this PL/SQL block.

Let me know if it helps

AD

You shoud write query like this....
a := 'create global temporary table shiva(
customer_cd varchar2(20) NULL,
contact_type varchar2(20) NULL,
seq_no integer NULL)';

Thanks for pointing out that syntax mistake. I have changed it and still get the same error.
I am running Oracle8i on Solaris and I do have all the required privileges. Can I use DBMS_SQL package to create temporary tables too??


Shiva.

Okay. I spent sometime on this and finally got it. Since you are creating the table SHIVA on the fly your cursor cannot refer this table. Here is the solution :

1. Create a table shiva1 and refer this in the cursor select.
2. Now create the table shiva on the fly and insert the rows using dbms_sql (Please note that the insert also should be using dbms_sql )
3. Now copy the data from shiva to shiva1. ( use dbms_sql and insert into the shiva1 table.
4. open the cursor and display the value.

Here is the final code which does the above mentioned steps (btw, it works. I have tested it )
:
Declare

cid integer;

cursor s_shiva is
select distinct contact_type
from shiva1
order by contact_type;

contacttype varchar2(20);
a varchar2(500);
fdbk binary_integer;

begin

cid := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cid, 'create table shiva(customer_cd varchar2(20) NULL,contact_type varchar2(20), seq_no number)', dbms_sql.native);
fdbk := DBMS_SQL.EXECUTE(cid);

dbms_sql.parse(cid, 'insert into shiva(
customer_cd,
contact_type,
seq_no )
values ('||''''||'1'||''''||','||''''||'cust'||''''||',1)', dbms_sql.native);

fdbk := dbms_sql.execute(cid);

dbms_sql.parse(cid, 'insert into shiva(
customer_cd,
contact_type,
seq_no )
values ('||''''||'2'||''''||','||''''||'two'||''''||',2)', dbms_sql.native);
fdbk := dbms_sql.execute(cid);

dbms_sql.parse(cid, 'insert into shiva(
customer_cd,
contact_type,
seq_no )
values ('||''''||'3'||''''||','||''''||'three'||''''||', 3)', dbms_sql.native);
fdbk := dbms_sql.execute(cid);

dbms_sql.parse(cid, 'insert into shiva1 select contact_type from shiva', dbms_sql.native);
fdbk := dbms_sql.execute(cid);

DBMS_SQL.CLOSE_CURSOR(cid);

commit;

open s_shiva;
loop
fetch s_shiva into contacttype;
exit when s_shiva%notfound;
dbms_output.put_line(contacttype);
end loop;
close s_shiva;

EXCEPTION
/* If an exception is raised, close cursor before exiting. */
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE; -- reraise the exception
end;
/


Please let me know if it helps.

Thanks,
AD

Ad,

Thanks for the time you have taken for the research.

But my question is - I want to use temporary table and not an ordinary permanent table - the reason - I do not want to waste space, as it is not going to be used anywhere else, but in this one precedure to hold values.

It would be great if you can guide me through creating and accessing a temporary table...

Shiva.

Hi,

I am aware of temporary table in Oracle using PL/SQL table using CREATE TYPE only. I am new to CREATE GLOBAL TEMPORARY table statement. Is it in O8i ? Unfortunately i am still working with O8. Sorry i could not help.

thanks.
AD

I picked this 'Create global temporary table' statement up from one of the posts in this forum and it says it works in 8.1.6.
I do not want to use PL/SQL tables as I would not be able to run a select query to it.

I am not able to find information on this particular command in the web....Do you know of any link or site which would give me a guideline??

Shiva.

hi shiva,

If u want to create global temp table at runtime then while inserting also u have to use dynamic sql. e. g. if u r creating global temp table as
a := 'create global temporary table shiva(customer_cd varchar2(20) NULL,
contact_type VARCHAR2(20) NULL,
seq_no INTEGER NULL)';
execute IMMEDIATE a;

then while inserting u should write insert statement as
a:= 'INSERT INTO shiva(customer_cd,contact_type,seq_no) VALUES ('||'1,'||'cust,'||1||')';
execute IMMEDIATE a;

The better solution on this is u create global temp table at sql prompt and use it where ever u want just like normal fisical table. Yes, it is session specific. Only thing is it would exist in ur schema with no records.

Kancha



[Edited by Kancha on 03-15-2001 at 06:18 PM]

declare
a varchar2(200);
begin
a := 'create global temporary table jegan(a number,c number) on commit preserve rows';
execute immediate a;
end;
/
jegan