Your dynamic SQL (EXECUTE IMMEDIATE argument) will in fact create its own cursor in the shared pool and can be queried in v$sql (your PL/SQL block will also be its own cursor and also be visible there). However, it will have the bind variable :1 rather than the literal, as the literal isn't part of the SQL statement. That's the whole point of bind variables.

So, if you are querying v$sql looking for LIKE '%3360%' you won't find the SELECT statement (which is exactly 'SELECT employee_name FROM employees WHERE employee_id = :1'), though you will find the PL/SQL block because there it is embedded as a literal. If you needed for investigative performance reasons to get some bind values to plug into a SQL obtained from the shared pool, you can find them in v$sql_bind_capture, but that is more of a representative sample per cursor and doesn't capture every execution. It's meant for DBAs, not programmers, to help diagnose things like cursor sharing problems, predicate datatype mismatches that throw out index use, and manually test-run SQL obtained from the shared pool that uses binds which the DBA may not know anything about.

Rather than trying to get what you need from the shared pool, typically programmers will simply log the contents (using a variable to hold it, of course) of their EXECUTE IMMEDIATE to a log table, if debugging might output it with dbms_output.put_line, and examine it that way. If they further need bind values, it's coming from their code and they're fully in control - they can log those bind values too.

Your dynamic SQL (EXECUTE IMMEDIATE argument) will in fact create its own cursor in the shared pool and can be queried in v$sql (your PL/SQL block will also be its own cursor and also be visible there). However, it will have the bind variable :1 rather than the literal, as the literal isn't part of the SQL statement. That's the whole point of bind variables.

So, if you are querying v$sql looking for LIKE '%3360%' you won't find the SELECT statement (which is exactly 'SELECT employee_name FROM employees WHERE employee_id = :1'), though you will find the PL/SQL block because there it is embedded as a literal. If you needed for investigative performance reasons to get some bind values to plug into a SQL obtained from the shared pool, you can find them in v$sql_bind_capture, but that is more of a representative sample per cursor and doesn't capture every execution. It's meant for DBAs, not programmers, to help diagnose things like cursor sharing problems, predicate datatype mismatches that throw out index use, and manually test-run SQL obtained from the shared pool that uses binds which the DBA may not know anything about. It isn't a logging mechanism.

Rather than trying to get what you need from the shared pool, typically programmers will simply log the contents (using a variable to hold it, of course) of their EXECUTE IMMEDIATE to a log table, or if debugging might output it with dbms_output.put_line, and examine it that way. If they further need bind values, it's coming from their code and they're fully in control - they can log those bind values too.

Your dynamic SQL (EXECUTE IMMEDIATE argument) will in fact create its own cursor in the shared pool and can be queried in v$sql (your PL/SQL block will also be its own cursor and also be visible there). However, it will have the bind variable :1 rather than the literal, as the literal isn't part of the SQL statement. That's the whole point of bind variables.

So, if you are querying v$sql looking for LIKE '%3360%' you won't find the SELECT statement, though you will find the PL/SQL block because there it is embedded as a literal. If you needed for investigative performance reasons to get some bind values to plug into a SQL obtained from the shared pool, you can find them in v$sql_bind_capture, but that is more of a representative sample per cursor and doesn't capture every execution.

Rather than trying to get what you need from the shared pool, typically programmers will simply log the contents (using a variable to hold it, of course) of their EXECUTE IMMEDIATE to a log table, if debugging might output it with dbms_output.put_line, and examine it that way. If they further need bind values, it's coming from their code and they're fully in control - they can log those bind values too.

Your dynamic SQL (EXECUTE IMMEDIATE argument) will in fact create its own cursor in the shared pool and can be queried in v$sql (your PL/SQL block will also be its own cursor and also be visible there). However, it will have the bind variable :1 rather than the literal, as the literal isn't part of the SQL statement. That's the whole point of bind variables.

So, if you are querying v$sql looking for LIKE '%3360%' you won't find the SELECT statement (which is exactly 'SELECT employee_name FROM employees WHERE employee_id = :1'), though you will find the PL/SQL block because there it is embedded as a literal. If you needed for investigative performance reasons to get some bind values to plug into a SQL obtained from the shared pool, you can find them in v$sql_bind_capture, but that is more of a representative sample per cursor and doesn't capture every execution. It's meant for DBAs, not programmers, to help diagnose things like cursor sharing problems, predicate datatype mismatches that throw out index use, and manually test-run SQL obtained from the shared pool that uses binds which the DBA may not know anything about.

Rather than trying to get what you need from the shared pool, typically programmers will simply log the contents (using a variable to hold it, of course) of their EXECUTE IMMEDIATE to a log table, if debugging might output it with dbms_output.put_line, and examine it that way. If they further need bind values, it's coming from their code and they're fully in control - they can log those bind values too.