By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,498 Members | 1,558 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,498 IT Pros & Developers. It's quick & easy.

Difference execute immediate within PL SQL

P: n/a
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;
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jul 19 '05 #2

P: n/a

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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.