Use a string array without CREATE privileges

cursorsoracleuser-defined-table-typeuser-defined-type

I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table.

select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in ('1-ABCD','1-BCDE')
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in ('1-ABCD','1-BCDE')

The above works well for two row_ids, but copy/pasting the IN clause for 1000 IDs is unwieldy when I need to update the list, especially as the list appears in each IN clause on ten UNION-ed queries.

I am looking for an array or a string variable to use in the IN clause and refer to that variable or array in the subsequent queries, in the form UNION SELECT ... WHERE id IN some kind of huge list of VARCHAR2 IDs .

Normally I would just create a table but in this system I'm a business analyst, not a dba. CREATE privileges won't be happening for me. If I do try create anything in the database, I get

[Error] Compilation (2: 1): ORA-01031: insufficient privileges

I've tried to use a DECLARE block with cursor, but with no success (no privilege violations, just faulty SQL code)

DECLARE 
    TYPE posrec_t IS RECORD (
    pos_row_id VARCHAR2(15) );
    TYPE poslist IS TABLE OF posrec; 
    todaypos poslist :=
      poslist (
      posrec ('1-103KO-18'),posrec ('1-10TOV-1'));
    posrec posrec_t;
    

CURSOR MYCURSOR IS select 'Opportunity' Data_Object, row_id Data_object_Id, pr_postn_id
from siebel.s_oppor
WHERE PR_POSTN_ID IN posrec
UNION 
[etc]
BEGIN
OPEN MYCURSOR;
END;

With the DECLARE BEGIN END block syntax, it seems like I could create a short-lived temporary table of 1000 IDs that appear once as a definition statement (and without needing CREATE rights), but I cannot figure out how to make it work.

Best Answer

I would use the following approach:

  1. Create a string array type:
create or replace type string_array as varray(1000) of varchar2(10);
/
  1. Use that in a with clause to construct the list of values as a virtual table, and use that table in your query, like this:
with t as (
  select column_value as code from table(string_array('1-ABCD','1-BCDE',...))
)
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in (select code from t)
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in (select code from t)
UNION
...
Related Question