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.
