Difference execute immediate within PL SQL

[michi]

Hello there...

Can anybody tell me what is the difference when I excecute a sql
statement within pl sql with/without "execute immediate" statement
Thanks

Michi :)
Example:

CREATE OR REPLACE PROCEDURE test
BEGIN

DELETE FROM test_table;

execute immediate 'truncate table test_table';

/*SOME HOW THIS STATEMENT I CANT COMPILE*/
truncate table test_table;

/*BUT I CAN COMPILE THIS STATEMENT*/
execute immediate 'truncate table test_table';


END;

[hrishy]

Hi Michi

/*SOME HOW THIS STATEMENT I CANT COMPILE*/
truncate table test_table;

This is static sql and hence it will not compile.Morever your not
allowed to do DDL within pl/sql like that using static sql.The reason
being oracle's philosophy of considering DDL as bad :-).even create
table will not work ;-)

/*BUT I CAN COMPILE THIS STATEMENT*/
execute immediate 'truncate table test_table';

well this is dynamic sql and the compiler will not complain as it
would consider this as a STRING.This would be evaluated at runtime and
hence compiles without error.

You might wnat to check out begining sql programming by Tom Kyte Joel
kallman and Sean Dillion co-authored by Howard Rogers <--my favourite
teacher ;-)

regards
Hrishy

[ovkrishna]

Truncate is a DDL statement and to use DDL statements within your Stored
Porcedure, you either need to use DBMS_DDL (or is it DBMS_SQL) package.

You cannot execute them directly. That is the reason you are getting the
compilation error.
--
Posted via http://dbforums.com