Execute Immediate

[dr_gori]

Someone explains to me why using Immediate execute in this command?

Select all

EXECUTE IMMEDIATE 'DELETE tabela WHERE SUPPLIER_CCN = :CCN' USING P_CCN;

Why did not you direct the delete?

Select all

DELETE tabela WHERE SUPPLIER_CCN = P_CCN; 

[diegolenhardt]

I think to reuse the cached instruction,

making only BIND,

: D

should execute various times DELETE, just exchanging parameter não é??

In relation to performance this is great ...

[dr_gori]

It does not run several times ... Should rotate 1 time a day

Another thing:
The BIND is also used here:

Select all

DELETE tabela WHERE SUPPLIER_CCN = P_CCN;

This P_CCN is a procedure parameter. It is the most clean form of SQL command ...


I agree that this

Select all

EXECUTE IMMEDIATE 'DELETE tabela WHERE SUPPLIER_CCN = :CCN' USING P_CCN;

is much better than this:

Select all

EXECUTE IMMEDIATE 'DELETE tabela WHERE SUPPLIER_CCN='|| P_CCN;

But I think a direct delete is better yet ...

Select all

DELETE tabela WHERE SUPPLIER_CCN = P_CCN;

[diegolenhardt]

It's true, it seems that there's not much sense, if you say you use bind in the normal delete, it would be in the same same, even more being that it is once soo a day, no need for such ..
99]]
=]

[dr_gori]

Yes, in a SQL like this, using variables, Oracle transforms the variables to BIND automatically.

If you run a trace, you will see that you can not see the value of the variables, only SQL. Where you have variables, it alters to: 1,: 2, etc ... -> Binds.

Oracle Rocks.

: -O