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
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