468,456 Members | 1,757 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,456 developers. It's quick & easy.

Difference execute immediate within PL SQL

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
2 22029
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

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.

Similar topics

4 posts views Thread by finlma | last post: by
3 posts views Thread by Agoston Bejo | last post: by
1 post views Thread by lakon15 | last post: by
3 posts views Thread by nghivo | last post: by
3 posts views Thread by Rahul Babbar | last post: by
6 posts views Thread by Oliver | last post: by
1 post views Thread by subhajit12345 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.