How to pass multiple values as single parameter in Oracle SQL

Question

I am trying to update values in multiple schemas which have the same table name.

So i tried to create query to pass multiple values as single parameter but its not working. Kindly suggest me.

DECLARE
    v_sql varchar2(500);
    v_schema varchar(30);
    v_prj_id varchar2(4000);
    in_PRJ_ID varchar2(4000);
    v_prj_id := 'B00781728,B00781628,B00781611,A43670001';

    CURSOR c1 is
      SELECT v_prj_id from DUAL;

BEGIN

    OPEN c1;

    FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l FROM dual CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1 ) LOOP

        FETCH c1 INTO in_PRJ_ID;

        EXIT WHEN c1%NOTFOUND;

        v_sql := 'UPDATE ' || in_PRJ_ID || '.SI_Recipient set email = email ||'';[email protected]'''  ;

        EXECUTE IMMEDIATE v_sql;

    END LOOP;

    CLOSE c1;

END;

When you say "its not working" what does that mean? Does it not compile? Does it hurl a runtime exception? Does it run but do nothing? Please remember we can't see your screen, we don't have your environments, we can't run your code. We only know what you tell us. — APC
– APC, Commented Nov 8, 2019 at 13:22
Please see help section How to Ask. Following that as a template greatly enhances you chance of getting a satisfactory answer. At a minimum post your table DDL, and describe what you are attempting to accomplish in business terms NOT in SQL terms. The way all answers use your values they represent table names; of course this steams from your initial post. To reiterate APC on its not working, well of course, if it were you wouldn't be asking help. But why is it not working. If I just said: "it's not right" gives you the same amount of information, but is pretty useless — Belayer
– Belayer, Commented Nov 9, 2019 at 17:21

MT0 · Accepted Answer · 2019-11-08 18:43:21Z

Use a collection or a VARRAY for the values so you don't have to split a delimited string and you can use a bind parameter for the value you wish to append to your email string:

DECLARE
  v_sql     varchar2(500);
  v_schema  varchar(30);
  v_prj_id  varchar2(4000);
  in_PRJ_ID SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST( 'B00781728','B00781628','B00781611','A43670001' );
BEGIN
  FOR i IN 1 .. in_PRJ_ID.COUNT LOOP
    v_sql := 'UPDATE ' || in_PRJ_ID(i) || '.SI_Recipient'
             || ' set email = email || '';'' || :addr';
    BEGIN
      EXECUTE IMMEDIATE v_sql USING '[email protected]';
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( SQLERRM );
    END;
  END LOOP;
END;
/

outputs:

UPDATE B00781728.SI_Recipient set email = email || ';' || :addr
ORA-00942: table or view does not exist
UPDATE B00781628.SI_Recipient set email = email || ';' || :addr
ORA-00942: table or view does not exist
UPDATE B00781611.SI_Recipient set email = email || ';' || :addr
ORA-00942: table or view does not exist
UPDATE A43670001.SI_Recipient set email = email || ';' || :addr
ORA-00942: table or view does not exist

db<>fiddle here

alvalongo · Accepted Answer · 2019-11-08 13:53:38Z

0

This works:

Declare
  v_sql varchar2(500);
  v_schema varchar(30);
  v_prj_id varchar2(4000):='B00781728,B00781628,B00781611,A43670001';
BEGIN
  Dbms_Output.Put_Line('v_prj_id='||v_prj_id);
  --
  FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
            FROM dual 
            CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1 
           ) LOOP
      --
      Dbms_Output.Put_Line('---------------------');
      --
      --
      v_sql := 'UPDATE ' || i.l|| '.SI_Recipient set email = email ||'';[email protected]'''  ;
      --
      Dbms_Output.Put_Line('v_sql='||v_sql);
      --
      begin
        EXECUTE IMMEDIATE v_sql;
      Exception
        when others then
             Dbms_Output.Put_Line('sqlerrm='||sqlerrm);
      End;
      --
  END LOOP;
END;
/

Output is:

v_prj_id=B00781728,B00781628,B00781611,A43670001
---------------------
v_sql=UPDATE B00781728.SI_Recipient set email = email ||';[email protected]'
sqlerrm=ORA-00942: la tabla o vista no existe
---------------------
v_sql=UPDATE B00781628.SI_Recipient set email = email ||';[email protected]'
sqlerrm=ORA-00942: la tabla o vista no existe
---------------------
v_sql=UPDATE B00781611.SI_Recipient set email = email ||';[email protected]'
sqlerrm=ORA-00942: la tabla o vista no existe
---------------------
v_sql=UPDATE A43670001.SI_Recipient set email = email ||';[email protected]'
sqlerrm=ORA-00942: la tabla o vista no existe
Total execution time 344 ms

Because in my oracle instance I don't have the schemas B00781728, B00781628, B00781611, A43670001 I get error ORA-00942 Table or view not exists.

answered Nov 8, 2019 at 13:53

alvalongo

6164 silver badges12 bronze badges

1 Comment

Ganesan VC Over a year ago

Hi @alvalongo Many thanks for the query.. it worked. I have one more question. i need to add where condition in that update statement like below v_prj_id= B00781728,B00781628,B00781611,A43670001 v_event = A,B,C,D; i.e for B00781728 = A, B00781628 = B, B00781611 = C and A43670001 = D --------------------- v_sql=UPDATE B00781728.SI_Recipient set email = email ||';[email protected]' Where EVENT = V_event in adition to this, i need to print the output of these result.

Collectives™ on Stack Overflow

How to pass multiple values as single parameter in Oracle SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related