Temporary table
The poster asked how, inside a stored procedure, to test whether a temp table (e.g. _t1) already exists in the current session, ideally via a catalog/system-table query. Replies first misread the question, then suggested simply attempting the CREATE with error handling (WHENEVER ERROR CONTINUE, or an ON EXCEPTION block) and treating failure as 'already exists'. The poster already knew that workaround and showed a sysmaster query listing all temp tables instance-wide, but no way to filter by session id was found; another responder pointed to an earlier thread that got no further. No definitive resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi! How can I define if defined temp table exists in the current session? -- Sauron
Sauron wrote: > Hi! > > How can I define if defined temp table exists in the current session? Err... "defined temp table"?! A "CREATE TEMP TABLE..." will create a temporary table. It will exist while the session exists or until you drop it. You can't share temporary tables between different sessions. Regards.
Hi! > Err... "defined temp table"?! > A "CREATE TEMP TABLE..." will create a temporary table. It will exist while the session exists or until you drop it. > You can't share temporary tables between different sessions. Probably, you didn't undestand me owing to my bad english...:( How must be to look select statement which will select created temp tables in a current session? -- Sauron
> How can I define if defined temp table exists in the current session? Try to create it (after having set "whenever error continue"). If it fails, the table already exists. If it works, the table didn't exist.
Sauron wrote:
> Hi!
>
> > Err... "defined temp table"?!
> > A "CREATE TEMP TABLE..." will create a temporary table. It will exist
> while the session exists or until you drop it.
> > You can't share temporary tables between different sessions.
>
> Probably, you didn't undestand me owing to my bad english...:(
> How must be to look select statement which will select created temp tables
> in a current session?
If I understand what you are asking, a temp table is referenced within a
SELECT the same way that you would reference a regular table.
For example:
CREATE TEMP TABLE test_table
(field_a char(1),
field_b smallint) WITH NO LOG;
SELECT count(*) FROM test_table;
Are you having a specific problem using a temp table, or is this a general
question?
--
June Hunt
Hi, All!
:((
I repeat my question by example:
------------------
create procedure _p1()
create temp table _t1 (a int);
insert into _t1 values(1);
end procedure;
.........
.........
.........
create procedure _p2()
if not exists (*) then
call _p1();
end if;
end procedure;
------------------
* - select temp_table_name from ... where sesionid = dbinfo('sessionid') and
temp_table_name = '_t1'
I hope, you will undestand me now...
I know one solution:
-------------
begin
on exception
end exception;
create temp table _t1 (a int);end;
------------
But must be solution by system tables using.
For example, the following script returns all TEMP tables from instance:
select tn.tabname[1,18] temp_table, tn.dbsname[1,18] db_name, s.name[1,14]
dbspace, tn.owner[1,18]
from sysmaster:systabnames tn, sysmaster:systabinfo ti,
sysmaster:sysdbspaces s
where tn.partnum = ti.ti_partnum
and s.dbsnum = sysmaster:partdbsnum(ti_partnum)
and (sysmaster:bitval(ti_flags,32) = 1 or
sysmaster:bitval(ti_flags,64) = 1)
--
Sauron
Sauron wrote:
> Hi, All!
>
> :((
> I repeat my question by example:
> ------------------
> create procedure _p1()>
> create temp table _t1 (a int);>
> insert into _t1 values(1);>
> end procedure;
> .........
> .........
> .........
> create procedure _p2()>
> if not exists (*) then
> call _p1();
> end if;
>
> end procedure;
> ------------------
>
> * - select temp_table_name from ... where sesionid = dbinfo('sessionid')
and
> temp_table_name = '_t1'
>
> I hope, you will undestand me now...
>
> I know one solution:
> -------------
> begin
> on exception
> end exception;
>
> create temp table _t1 (a int);> end;
> ------------
>
> But must be solution by system tables using.
> For example, the following script returns all TEMP tables from instance:
>
> select tn.tabname[1,18] temp_table, tn.dbsname[1,18] db_name, s.name[1,14]
> dbspace, tn.owner[1,18]
> from sysmaster:systabnames tn, sysmaster:systabinfo ti,
> sysmaster:sysdbspaces s
> where tn.partnum = ti.ti_partnum
> and s.dbsnum = sysmaster:partdbsnum(ti_partnum)
> and (sysmaster:bitval(ti_flags,32) = 1 or
> sysmaster:bitval(ti_flags,64) = 1)
Your example helped clarify the issue. Thank you. Unfortunately, I don't
have an answer for you. There was a similar question asked very recently
(see the thread at http://tinyurl.com/yfmh ). The answers posted got about
as close as you've already managed. Good luck.
--
June Hunt