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  |
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 #400353 is a reply to message #400344] |
Mon, 27 April 2009 14:11   |
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  |
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
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:30:22 CST 2025
|