Dynamic SQL Statement is Anonymous Block or CALL Statement

 
--==============================================
Repeated Placeholder Names in Dynamic SQL Statements
--==============================================
If you repeat placeholder names in dynamic SQL statements, be aware that the way
placeholders are associated with bind variables depends on the kind of dynamic SQL
statement.
 
Topics
1.  Dynamic SQL Statement is Not Anonymous Block or CALL Statement
 
2.  Dynamic SQL Statement is Anonymous Block or CALL Statement
 
--==============================================
Dynamic SQL Statement is Anonymous Block or CALL Statement
--==============================================
 
If the dynamic SQL statement represents an anonymous PL/SQL block or a CALL
Each unique placeholder name must have a corresponding bind variable in the USING
clause.
 
If you repeat a placeholder name, you need not repeat its corresponding bind
variable. All references to that placeholder name correspond to one bind variable in
the USING clause.
 
For Example : Repeated Placeholder Names in Dynamic PL/SQL Block
 
In this example,
all references to the first unique placeholder name, :x, are associated with the first bind variable in the USING clause, a,
and the second unique placeholder name, :y, is associated with the second bind variable in the USING clause, b.
 
 
 
 
CREATE OR REPLACE PROCEDURE XX_calc_stats ( w NUMBER, x NUMBER, y NUMBER, z NUMBER ) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
 
 
 
DECLARE
a NUMBER := 1;
b NUMBER := 2;
c NUMBER := 3;
d NUMBER := 4;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN XX_calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b, c, d; -- Not Required because this is not a right way to do this thing----
END;
 
 
DECLARE
a NUMBER := 1;
b NUMBER := 2;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN XX_calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b; -- calc_stats(a, a, b, a)
END;
   
 
x