0

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.

2
  • I cannot see why you would not have CREATE TABLE privilege in your own schema. It is not a DBA type privilege. Have you asked for it? Dec 14 '20 at 23:52
  • @BobbyDurrett Even our own schemas for our IDs has no CREATE. It's the decades-old policy of both my employer and the contractor managing the systems. I would have to be approved as an exception for roles that are unusual in my organization. I'll pass on that; I already create enough trouble. But it also appears I have workarounds now.
    – wistlo
    Dec 17 '20 at 7:49
0

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
...
3
  • Not sure that the user has rights to create a type in their own schema. Dec 15 '20 at 18:28
  • True that. I actually started by using the SDO_STRING_ARRAY type, that is very much like the one I proposed creating. This comes with the Spatial feature and unless a DBA has explicitly removed it from the database, it should be present. So replace string_array('1-ABCD','1-BCDE',...) with sdo_string_array('1-ABCD','1-BCDE',...) and Wistlo should be all set. Dec 17 '20 at 8:48
  • And a describe sdo_string_array will confirm whether it is available or not. Dec 17 '20 at 8:48
0

As you can't create anything, your only option is to explicitly write the list of IDs. To avoid repeating it, you could put it into a common table expression:

with params (id) as (
  select '1-ABCD' from dual union all 
  select '1-BCDE' from dual union all 
  ....
  select '999-XYZ' from dual
)  
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in (select id from params)
UNION ALL
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in (select id from params)

As Oracle still doesn't support a VALUES clause, you need to write many lines of select ... from dual to union all values together - but at least you only need to write it once.

Creating a spreadsheet that generates the select .. from dual union all ... select for your (based on the "plain values", might make maintaining this list a bit easier. Then copy & paste the generated values from the spreadsheet into the WITH clause.

0

Maybe you could write a program, say in Python, that generates the SQL for you. It could read a file with your list of ids and generate the SQL statement with the ids repeated however many times they are needed.

0

Similar to Albert Godfrind's answer, but there's a built-in type, SYS.DBMS_DEBUG_VC2COLL, that has a PUBLIC privilege that can be used. The following code sample should give you an idea on usage

declare
   v_tab1 SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL('ONE','TWO','THREE');
   v_tab2 SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL('TWO','THREE');
   v_cnt  number;
begin
   select count(*)
   into v_cnt
   from table(v_tab1)
   where column_value not in (select column_value from table(v_tab2));
end;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.