CODEWITHSUNDEEP
sqlstringoracle-databaseplsql
Ganesan VC
Ganesan VC

Reputation: 53

How to pass multiple values as single parameter in Oracle SQL

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;

Upvotes: 1

Views: 3801

Answers (2)

MT0
MT0

Reputation: 167991

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

Upvotes: 1

alvalongo
alvalongo

Reputation: 571

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.

Upvotes: 0

Related Questions