Thread: an interesting topic

when to use dynamic sql rather than static sql

if answer is

in static sql one hve a fixed sql statement but where in dynamic sql u can build sql statements as charecter string at run time

den wat is the difference between ref cursor and dynamic sql

in normal PL/SQL block it will allow only DML statements to execute.if we are executing other than DML like DDL(CREATE ,ALTER ,DROP),DCL(GRANT,REVOKE) AND SESSION CONTROL LANGUAGE(CREATE SESSION) it will come out from the block and gives an error.
by using dynamic sql we can execute DDL ,DCL,SESSION CONTROL langueges.this is one advantage of dynamic sql.

Originally Posted by ashalalaxmi2002

in normal PL/SQL block it will allow only DML statements to execute.if we are executing other than DML like DDL(CREATE ,ALTER ,DROP),DCL(GRANT,REVOKE) AND SESSION CONTROL LANGUAGE(CREATE SESSION) it will come out from the block and gives an error.
by using dynamic sql we can execute DDL ,DCL,SESSION CONTROL langueges.this is one advantage of dynamic sql.

question is not advantage of dynamic sql but in such case i mentioned which is appropriate?

Originally Posted by bhaski

question is not advantage of dynamic sql but in such case i mentioned which is appropriate?

Use dynamic sql when the anme of the objects are not known in advance and passed only at runtime.

use the execute immediate statement to do ddl statement in oracle