Apr 7, 2016

USING clause in EXECUTE IMMEDIATE


BEGIN
   INSERT INTO MY_DATA  VALUES (1, 'STEVEN');
   INSERT INTO MY_DATA  VALUES (2, 'VEVA');
   INSERT INTO MY_DATA VALUES (3, 'ELI');
   COMMIT;
END;


(a)  
 DECLARE
   TYPE MY_DATA_T IS TABLE OF MY_DATA%ROWTYPE INDEX BY PLS_INTEGER;
   L_MY_DATA   MY_DATA_T;
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM my_data
         WHERE nm <> :nmvalue
           AND pky <> :keyvalue
           AND SUBSTR (nm, 1, 3) = :nmvalue'
     USING 'abc' ,5  ,'def';
END;
 

(b)
DECLARE
   TYPE MY_DATA_T IS TABLE OF MY_DATA%ROWTYPE INDEX BY PLS_INTEGER;
   L_MY_DATA   MY_DATA_T;
BEGIN
   EXECUTE IMMEDIATE
      'begin
           DELETE FROM my_data
            WHERE nm <> :nmvalue
              AND pky <> :keyvalue
              AND SUBSTR (nm, 1, 3) = :nmvalue;
        end;'
      USING 'abc', 5 ;    
END;



What is the difference between the PLSQL (a) & (b) 




When executing dynamic SQL, you must provide an expression or variable for each placeholder.


When executing dynamic PL/SQL, you must provide an expression or variable for each uniquely-named placeholder.

No comments: