Native Dynamic SQL

Dynamic SQL allows an application to run SQL statements whose contents are not known until runtime. The statement is built up as a string by the application and is then passed to the server, in a similar way to the ADO interface in VB.

Generally dynamic SQL is slower than static SQL so it should not be used unless absolutely necessary. Also, since syntax checking and object validation cannot be done until runtime, code containing large amounts of dynamic SQL may be littered with mistakes but still compile.

The main advantage of dynamic SQL is that it allows you to perform DDL commands that are not supported directly within PL/SQL, such as creating tables. It also allows you to access objects that will not exist until runtime.

Dynamic Cursors

Sometimes the definition of the cursor may not be known until runtime. The following example shows this along with access of an object that it not currently present. It should compile in any schema as there are no direct references to database objects until runtime.