Oracle PL/SQL: see the execute immediate sql with bind variable replaced

Question

This is the PL/SQL code. I want to DBMS output the complete query as it is executed since it fails.

vSql := 'SELECT :p_nomeCampo FROM :vTable WHERE :vPkColumn = :p_id';
EXECUTE IMMEDIATE vSql INTO vOutput USING p_nomeCampo, vTable, vPkColumn, p_id;

Alex Poole · Accepted Answer · 2012-02-27 11:21:19Z

4

Not directly answering the question, but it's probably failing because you can only bind variable values, not object names; so only :p_id is valid here. The best you can probably do is:

vSql := 'SELECT ' || p_nomeCampo || ' FROM ' || vTable
    || ' WHERE ' || vPkColumn || ' = :p_id';

Of course you need to be sure the values you have for p_nomeCampo, vTable and vPkColumn aren't susceptible to SQL injection.

It helps to show the error you're getting, though again in this case that's not directly the point of the question...

answered Feb 27, 2012 at 11:21

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Codo Over a year ago

p_nomeCampo, vTable and vPkColumn aren't susceptible to SQL injection only if all values are coming from the software itself and not from the user interface.

Revious Over a year ago

yes they are coming from the previous query. There is no user interface.

Revious Over a year ago

I had to read the answer many times before understandig that you mean object names in the query!

Collectives™ on Stack Overflow

Oracle PL/SQL: see the execute immediate sql with bind variable replaced

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related