Printable View
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.
[QUOTE=ashalalaxmi2002;24739]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.[/QUOTE]
question is not advantage of dynamic sql but in such case i mentioned which is appropriate?
[QUOTE=bhaski;24741]question is not advantage of dynamic sql but in such case i mentioned which is appropriate?[/QUOTE]
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