Parsing placeholders in PL/SQL statement for EXECUTE IMMEDIATE

Question

I'm studying this language from just some days. I was trying to use a string containing a PL/SQL block with a placeholdered string which have two fields that I want to replace with some data retrieved from a SELECT statement.

I've correctly created and populated the table employees.

The problem is that I need to "replace" those placeholders (:name and :salary in variable cmd2) but when I EXECUTE IMMEDIATE using the values retrieved I get this error: ORA-01006: bind variable does not exist.

This is the code snippet:

DECLARE
    cmd1 VARCHAR2(200) := 'SELECT * FROM employees';
    cmd2 VARCHAR2(200) := 'BEGIN DBMS_OUTPUT.PUT_LINE('':name has a salary of :salary;''); END;';
    str VARCHAR2(200);

    c1 SYS_REFCURSOR;

    emp employees%ROWTYPE;
BEGIN
    OPEN c1 FOR cmd1;
    LOOP
        FETCH c1 INTO emp;
        EXIT WHEN c1%NOTFOUND;

        -- It doesn't work
        EXECUTE IMMEDIATE cmd2 USING emp.name, emp.salary;

        -- It works, but just prints ':name has a salary of :salary;'
        EXECUTE IMMEDIATE cmd2;
    END LOOP;
END;

The expected result should be:

Name1 has a salary of 300;
Name2 has a salary of 700;
-- ...and so on

Answer 1

The bind variables are in a string, so they are not being seen as binds.

try

cmd2 VARCHAR2(200) := q'[BEGIN DBMS_OUTPUT.PUT_LINE(:name || ' has a salary of ' || :salary); END;]';