Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to use pl/sql table in select queries?

Status
Not open for further replies.

kachara

Programmer
May 22, 2006
2
US
Hi, I have a pl/sql table as

TYPE ER_SUBID_RECORD_TYPE IS RECORD (
SUBSCRIPTION_ID SUBSCRIPTIONS.SUBSCRIPTION_ID%TYPE
);
TYPE ER_SUBID_TABLE_TYPE IS TABLE OF ER_SUBID_RECORD_TYPE;

I basically use this to store some subscription_ids for further processing. My goal is to use this table in select queries to join with other table. I tried the following but it gives "PL/SQL: ORA-00902: invalid datatype" error, for the line where I use TABLE( cast ...

<snip>

....
v_subid_table ER_SUBID_TABLE_TYPE := ER_SUBID_TABLE_TYPE();
BEGIN
SELECT DISTINCT subs.SUBSCRIPTION_ID
BULK COLLECT INTO v_subid_table
FROM SUBSCRIPTIONS subs
WHERE clause ...
;

-- Use the values from pl/sql table..
OPEN p_subscriptions FOR
SELECT DISTINCT
subs.subscription_id
, subs.account_id, ...
FROM SUBSCRIPTIONS SUBS,
TABLE(CAST(v_subid_table AS ER_SUBID_TABLE_TYPE)) SUBSID_T,
ACCOUNTS ACT ...
WHERE SUBSID_T.SUBSCRIPTION_ID = SUBS.SUBSCRIPTION_ID
and .....

</snip>

Can you please tell me what is wrong in here? Also, what is the best way of storing these IDs temporarialy and using them in another select queries?

Thanks,

RJ

 
Are you creating your types on the database or in the PL/SQL block ? They should be created as database types. Here's a very simple example I created which demonstrates the technique:

Code:
DROP TYPE TMP_TAB
/

DROP TYPE TMP_REC
/

create or replace TYPE TMP_REC IS OBJECT (DUMMY_ID INTEGER)
/

CREATE OR REPLACE TYPE TMP_TAB AS TABLE OF TMP_REC
/

DECLARE
lv_SQL	    VARCHAR2(30000);
v_id		number(22);
TYPE REF_CURSOR IS REF CURSOR;
l_Cursor	REF_CURSOR;
v_tmp_tab   tmp_tab:= 	tmp_tab();
cursor c_curs (p_tmp_tab tmp_tab) is 
SELECT  1 AS X from DUAL WHERE 1 IN (SELECT dummy_id FROM table(cast(v_tmp_tab as tmp_tab)));

BEGIN

v_tmp_tab.delete;
v_tmp_tab.extend;
v_tmp_tab(1) := TMP_rec(1);

 for rec_curs in c_curs(v_tmp_tab) loop
     dbms_output.put_line(rec_curs.x);
 end loop;
END;
/
 
Hi Dagon,

Thank you for the reply.

Would you suggest to use a global temporary table instead? Do you see any advantage/disadvantage over using pl/sql table?

Thanks,

RJ
 
The only advantage might be that you could put an index on the global temporary table. Apart from that, I've not noticed a lot of difference in performance. You always have potential performance pitfalls with these type of temporary structures because the database doesn't have stats on them, so you might have to use hints to get the performance you want.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top