Home » SQL & PL/SQL » SQL & PL/SQL » Global temporary tables Session issue (Oracle 9i)
Global temporary tables Session issue [message #400340] Mon, 27 April 2009 12:58 Go to next message
krylor
Messages: 2
Registered: April 2009
Location: Mumbai
Junior Member
Hi ,

Am a developer with more Sql Server experience than Oracle.
I Am working on Web application developed in C#,for which I need to use Global temporary tables in pl/sql procedure.
(
I use the temp tables to store the unique ids of the transaction table I am trying to fetch data from.
I need to use a temp table as there are various conditions to be filtered.
)

The only issue here is that Oracle says GTT can be either session specific or transaction specific.
I want want the data in the temp table to exist only for the scope of the PL/SQL procedure and not persist beyond that.

What I found was that each time I refreshed the page ( for every refresh the PL/SQL procedure is invoked once ) the no of records being displayed kept increasing as the Oracle session does not get terminated due to connection pooling
and it keeps adding records to the temp table.
.
Under such circumstances what can be done to make the data persist only for the current PL/SQL scope?.

Currently am using a rough hack,inserting a sequence value ( which is unique everytime the PL/SQL procedure is invoked ) into the temporary table.
It does do the job, but am not very happy with this.

Incidentally this is effortlessly done in sql server using inline tables.

Can somebody suggest a better approach.

ps:
PL/SQL tables wont work for me as I need to delete data from the temp table using sql syntax

regards
Krylor





Re: Global temporary tables Session issue [message #400344 is a reply to message #400340] Mon, 27 April 2009 13:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could truncate the table at the beginning of the PL/SQL procedure.

Or whenever you want it to be empty again.

Re: Global temporary tables Session issue [message #400353 is a reply to message #400344] Mon, 27 April 2009 14:11 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You should try to avoid using any type of temp table unless you have a good reason why other approaches won't work. You mention "inline tables". Oracle supports that is several ways.

-- this isn't a great example, but it illustracted that the inline view occurs twice in the same SQL.
-- and gets evaluated twice.
SQL> select A.ename, B.dname from emp A, (select * from dept where 1>0) B
  2  where A.deptno = B.deptno
  3  and A.ename='ADAMS';

ENAME      DNAME
---------- --------------
ADAMS      RESEARCH

SQL>


If you use the WITH clause, the "inline view" only gets evaluated once, so can be more efficient. Like I say - this isn't a good example...
with Z as (SELECT * FROM DEPT WHERE 1>0)
select A.ename, B.dname from emp A,  
   (Z) B   
where A.deptno = B.deptno
and B.deptno in (select X.deptno from (Z) X)
and A.ename='ADAMS'
.

In a connection pooling case, yes the session would persist and a session level GTT would give the behavior you describe. COMMIT level would be make more sense as your web based connection would almost always commit/rollback. Remeber that in Oracle, any DDL (including truncating a table) issues an implicit commit, so if you do those things, you could find your GTT being flushed.
Re: Global temporary tables Session issue [message #400805 is a reply to message #400353] Wed, 29 April 2009 12:59 Go to previous message
krylor
Messages: 2
Registered: April 2009
Location: Mumbai
Junior Member
Hi People,

Perhaps I should have posted my sample code.
That would have made things clearer.

Below is a sample stored proc.

CREATE OR REPLACE PROCEDURE PTSDBUSER.sp_SearchFieldTest ( PSearchField IN VARCHAR2 , Pageno IN INTEGER , PageSize IN INTEGER, oResult1 OUT Types.o_Cursor , oResult2 OUT Types.o_Cursor , oResult3 OUT Types.o_Cursor , oResult4 OUT Types.o_Cursor )
IS
TMPSEQ NUMBER;
StartRec INTEGER;
EndRec INTEGER;
TotalRecords INTEGER;
BEGIN

StartRec := ((Pageno - 1 ) * PageSize) + 1;
EndRec := StartRec + PageSize - 1;
TotalRecords := 0 ;

SELECT USERROW_SEQ.NEXTVAL INTO TMPSEQ FROM DUAL;

-- USE A LOCK ID IN ORDER TO PREVENT 2 DIFFERENT PL/SQL CALLS FROM CLASHING.


IF LENGTH(PSearchField) > 0 THEN

INSERT INTO USERROW
( LOCK_ID , ROW_NO , USER_ID )
SELECT TMPSEQ , ROWNUM as ROW_NO , user_id FROM login_master
WHERE
( user_id LIKE PSearchField || '%' ) OR ( UPPER(NVL(user_first_name,'') || ' ' || NVL(user_last_name,'')) LIKE UPPER(PSearchField) || '%' ) OR ( UPPER(NVL(user_email,'')) LIKE UPPER(PSearchField) || '%' ) OR ( UPPER(NVL(user_last_name,'')) LIKE UPPER(PSearchField) || '%' ) OR ( UPPER(NVL(user_first_name,'')) LIKE UPPER(PSearchField) || '%' )
ORDER BY user_id DESC;


ELSE
INSERT INTO USERROW
( LOCK_ID , ROW_NO , USER_ID )
SELECT TMPSEQ , ROWNUM , user_id
FROM login_master;

END IF;


OPEN oResult1 FOR
SELECT a.id, a.user_id, NVL(a.user_first_name,'') AS user_first_name , NVL(a.user_last_name,'') AS user_last_name , NVL(a.user_email,'') AS user_email , a.user_expiry_date , NVL(a.location_type,'') AS location_type , NVL(a.USER_STATUS,'0') AS user_status , NVL(a.USER_PASSWORD,'') AS user_password , a.user_expiry_date
FROM login_master a
INNER JOIN
USERROW b
ON
a.user_id = b.USER_ID
WHERE
b.LOCK_ID = TMPSEQ
AND
b.ROW_NO >= StartRec AND b.ROW_NO <= EndRec
ORDER BY
b.ROW_NO;

OPEN oResult2 FOR
SELECT a.location_code , a.location_type ,a.user_id , b.location_name from
user_location a
INNER JOIN
location_master_vw b
ON
a.location_code = b.location_code
INNER JOIN
USERROW b
ON
a.user_id = b.USER_ID
WHERE
b.LOCK_ID = TMPSEQ
AND
b.ROW_NO >= StartRec AND b.ROW_NO <= EndRec ;


OPEN oResult3 FOR
SELECT a.user_id , a.role_code , b.role_name
FROM user_roles a
INNER JOIN
role_master b
ON
a.role_code = b.role_code
INNER JOIN
USERROW b
ON
a.user_id = b.USER_ID
WHERE
b.LOCK_ID = TMPSEQ
AND
b.ROW_NO >= StartRec AND b.ROW_NO <= EndRec
;

SELECT COUNT(*) INTO TotalRecords FROM USERROW b WHERE b.LOCK_ID = TMPSEQ;

OPEN oResult4 FOR
SELECT 1 AS ReturnCode, NVL(TotalRecords,0) as ReturnMessage FROM DUAL;

-- Clean up
DELETE FROM USERROW WHERE LOCK_ID = TMPSEQ;

END sp_SearchFieldTest;



The reason I need to use a temp table is because.

I need to build a lot more functionality into this like
more filter conditions, conditional sorting etc.
Edit/Delete Message
Previous Topic: Accessing directory entries in PL/SQL with clustering
Next Topic: Sequence reduction
Goto Forum:
  


Current Time: Sat Dec 10 04:58:42 CST 2016

Total time taken to generate the page: 0.12106 seconds