Posts Tagged ‘procedure’

Dynamic SQL statement or an anonymous PL/SQL block

Tuesday, 20 January, 2009 Leave a comment

Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do not matter. For example, PL/SQL makes no distinction between the following strings:
'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

The following PL/SQL block contains several examples of dynamic SQL:
Read more…