Creating temp table within a procedure and returning a cursor ref

[mohit00m]

Hi Guys,

I have been struggling to with creating temp table within a procedure and returning a cursor ref.

Basically, I am trying to convert my in-line sql into procedures which returns cursor ref.

Using inline SQL, I had created series of temporary tables - output of temp tables used as input to other temp table within sql. However, due to audit issues, I had to convert all inline sql to procedures.

I have been trying to achieve whatever I had achieved using inline sql. Howver, not been able to create temp tables within a procedure and return a cursor ref.

Any expert advise on how do I achive this?

I am able to create temp tables using below.

declare
l_tname varchar2(30) default 'temp_table_' || userenv('sessionid');
begin
execute immediate 'create global temporary table ' ||
l_tname || ' on commit delete rows as
select * from all_tables where 1=0 ';
end;


I am trying to bundle above into procedure and return a cursor ref with, but not sure how do I get this?


PROCEDURE test(v_cur OUT sys_refcursor) IS
BEGIN
OPEN v_cur FOR

(execute immediate 'create global temporary table ' ||
l_tname || ' on commit preserve rows as
select * from imp_desk '

SELECT * FROM l_tname);
END;



Regards,
Mohit

 

[taupirho]

No need for temp tables at all, you just need to

open v_cur for select * from imp_desk '


Check out my answer to a similar query a while back

http://www.tek-tips.com/viewthread.cfm?qid=1298819

In order to understand recursion, you must first understand recursion.

 

[mohit00m]

Hi taupirho ,

Thanks for your reply - actually I have got around 10-15 temporary tables needs to be created - not one. I first started with below - but not sure my I am getting error. So though of creating temp tables.

Have been getting in below error if I bulk execute my procedure. Not been able to figure out problem.

ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 157 Column: 4


Using below query

--Creating 1st temp table here
WITH tmpSwap AS

(
SELECT DISTINCT
it.id_typ_imnt as "Imnt Type",
s.OID_PORTFOLIO_SWAP,

FROM
instrument_type it,
swaptrade st,

WHERE
-- Condition
-),

-- Creting 2nd temp table.
temp_ResetDates

AS

(

SELECT DISTINCT
fnd.ID_PARTY, fnd.NM_PARTY,
FROM SWAP psw
INNER JOIN SCHEDULE sch
ON psw.id_portfolio_swap = sch.id_portfolio_swap
INNER JOIN FUND fnd
ON psw.oid_fund = fnd.oid_fund
INNER JOIN instrument imnt
ON imnt.id_instrument = sch.id_instrument
WHERE
fnd.ID_PARTY IN ( (SELECT DISTINCT "Id Party" FROM tmpSwap) ) -- Using output of 1st temp table here

)
-- select ID_PARTY from temp_ResetDates -- output until here is being displayed fine here.
,
temp_Dates_Finance – creating 3rd temp table here
AS
(
SELECT DISTINCT
trd.ID_IMNT,
-- trd.ROLL_RULE_TYPE ,
FROM
temp_ResetDates trd,
(
SELECT DISTINCT
id_imnt,
DG_START_DATE
FROM
trd
WHERE
LEG_TYPE = 'FINANCE'
AND schedule_type = 'RESET'
) rd -- Creatinh 4th temp table here –output of these tables are used in next queries -
WHERE
rd.id_imnt = trd.id_imnt
-- rd1.id_imnt = trd.id_imnt

)

SELECT "Finance_Payment_Start" FROM temp_Dates_Finance

-- for testing trying to display output of 3rd temp table. – but been getting Error ORA-00942

Regards,

 

[LKBrwnDBA]

You only need to create temp tables ONCE.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mohit00m]

Hi LKBrwnDBA ,

Sorry I dind get you?

Regarsd,

 

[LKBrwnDBA]

Unlike Windoze SQL, an Oracle temporary table is a "special" permanent table that preserves the rows only during each particular session using it. Permanent because the definition of the table is "permanent", not the data.

Once created, you DO NOT need to drop/create each time, just use it.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mohit00m]

Hi LKBrwnDBA,

Thnaks for reply - not creating temp table dosent solve my purpose. I need to create temporary tables with different data at different time in order to get my output.

In below example - trying to creat 4 different temp tables , but not sure how oracle works - can get test output until 3 temp tables - been getting but been getting Error ORA-00942
from 4th temp onwards. Any idea? or sampel qry?

-Creating 1st temp table here
WITH tmpSwap AS

(
SELECT DISTINCT
it.id_typ_imnt as "Imnt Type",
s.OID_PORTFOLIO_SWAP,

FROM
instrument_type it,
swaptrade st,

WHERE
-- Condition
winky smile,

-- Creting 2nd temp table.
temp_ResetDates

AS

(

SELECT DISTINCT
fnd.ID_PARTY, fnd.NM_PARTY,
FROM SWAP psw
INNER JOIN SCHEDULE sch
ON psw.id_portfolio_swap = sch.id_portfolio_swap
INNER JOIN FUND fnd
ON psw.oid_fund = fnd.oid_fund
INNER JOIN instrument imnt
ON imnt.id_instrument = sch.id_instrument
WHERE
fnd.ID_PARTY IN ( (SELECT DISTINCT "Id Party" FROM tmpSwap) ) -- Using output of 1st temp table here

)
-- select ID_PARTY from temp_ResetDates -- output until here is being displayed fine here.
,
temp_Dates_Finance – creating 3rd temp table here
AS
(
SELECT DISTINCT
trd.ID_IMNT,
-- trd.ROLL_RULE_TYPE ,
FROM
temp_ResetDates trd,
(
SELECT DISTINCT
id_imnt,
DG_START_DATE
FROM
trd
WHERE
LEG_TYPE = 'FINANCE'
AND schedule_type = 'RESET'
) rd -- Creatinh 4th temp table here –output of these tables are used in next queries -
WHERE
rd.id_imnt = trd.id_imnt
-- rd1.id_imnt = trd.id_imnt

)

SELECT "Finance_Payment_Start" FROM temp_Dates_Finance

 

[Thargy]

mohit,

in Oracle, there's usually no need for temporary tables.
Can you tell us what it is you're trying to achieve?

Can you provide an English language narrative of the business function you're trying to implement. At the moment we can only surmise what your need is.

Regards

T

 

[mohit00m]

ok - in plain simple english - my requirement is to return a reference of cursor in procedures - I had series of inline sql (7-15 dependent on each other sqls to produce reports) . In these sqls I am creating temp tables and oputut of temp tables is used as input to other tables. I was getting expected results , however due to audit issues , I was asked to covert all inline sql to procedures which returns cursor.


I used below technique to achive my target results.



PROCEDURE trade_report(v_cur OUT sys_refcursor, date_from IN VARCHAR2,date_to IN VARCHAR2,p_id_desk IN imp_desk.id_desk%TYPE,Cpty_Name IN VARCHAR2)
IS BEGIN OPEN v_cur FOR

WITH temp_tab1 AS
(Select Fld1, Fld2, Fld3 FROM Table1 WHERE ....),
temp_Tab2 AS

(Select Fld1, Fld2, Fld3 FROM Table2 WHERE Fld1 in ( Select * FROM temp_tab1) ),
temp_tab3 as
(Select Fld1, Fld2, Fld3 FROM temp_tab3,temp_tab1 WHERE temp_tab3.Fld1 = temp_tab1..Fld1),
temp_tab4 as
(Select Fld1, Fld2, Fld3 FROM temp_tab4,temp_tab1 WHERE temp_tab4.Fld1 = temp_tab1..Fld1)

SELECT * FROM temp_tab1;

END

Please note - I am able split and execute above code succesfully until temp_tab3, however getting invalid table name error as soon as I include temp_tab4 (Error ORA-00942
), not sure if Oracle has any limit for temp tables ?

Is there any other way of creating temp table within a procedures and returning a cursor ref?

Many thanks for your time and help.

 

[Dagon]

Thnaks for reply - not creating temp table dosent solve my purpose. I need to create temporary tables with different data at different time in order to get my output.

A global temporary table (as opposed to an ordinary table) allows you to do exactly that. Data is transient - it only persists for a session or transaction. Therefore, you could have two users both inserting data into the temporary table at the same time and they would not interfere with each other.

For Oracle-related work, contact me through Linked-In.

 

[mohit00m]

Hi Dagon,

Still dont understand how this is related in resolving my issues? I am not creating temp tables now , using below technique to for multiple dymanic tables.

I used below technique to achive my target results.




PROCEDURE trade_report(v_cur OUT sys_refcursor, date_from IN VARCHAR2,date_to IN VARCHAR2,p_id_desk IN imp_desk.id_desk%TYPE,Cpty_Name IN VARCHAR2)
IS BEGIN OPEN v_cur FOR

WITH temp_tab1 AS
(Select Fld1, Fld2, Fld3 FROM Table1 WHERE ....),
temp_Tab2 AS

(Select Fld1, Fld2, Fld3 FROM Table2 WHERE Fld1 in ( Select * FROM temp_tab1) ),
temp_tab3 as
(Select Fld1, Fld2, Fld3 FROM temp_tab3,temp_tab1 WHERE temp_tab3.Fld1 = temp_tab1..Fld1),
temp_tab4 as
(Select Fld1, Fld2, Fld3 FROM temp_tab4,temp_tab1 WHERE temp_tab4.Fld1 = temp_tab1..Fld1)

SELECT * FROM temp_tab1;

END

Please note - I am able split and execute above code succesfully until temp_tab3, not sure if Oracle has any limit for temp tables ?


Now I am getting below error.

ORA-01762: vopdrv: view query block not in FROM
01762. 00000 - "vopdrv: view query block not in FROM"
*Cause:
*Action:
Error at Line: 307 Column: 48

Is there any other way of creating temp table within a procedures and returning a cursor ref?

Many thanks for your time and help.

 

[Dagon]

The fragment of code you have is nonsensical:

a) You don't use most of the tables from the with clause. Temp_tab2, temp_tab3 etc are not used in the main query.

b) The alias in the with query (temp_tab1) is the same name as an actual table.

c) Why you need to join TEMP_TAB1 so many times to different tables? Why not just have it once in the main query and then join it to the other tables.

I suggest you start to look at your problem by abandoning the use of WITH (which you don't seem to understand) and try to write an ordinary query instead.

For Oracle-related work, contact me through Linked-In.

 

[mohit00m]

thanks a lot for looking into this!! Will check and get back to you.