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