DBMS_SQL system package

The DBMS_SQL system package defines an entity called an SQL cursor number. Because SQL cursor numbers are integers, they can be passed across call boundaries and stored.

You must use the DBMS_SQL package to execute dynamic SQL statements if any of the following conditions are met:

• You do not know the content of the SELECT list at runtime.

• You do not know which placeholders in the SELECT or DML statement must be bound at runtime.

• You want the subprogram to implicitly return query results by using the DBMS_SQL.RETURN_RESULT stored procedure (instead of by using the OUT REF CURSOR parameter).

You must use dynamic SQL instead of the DBMS_SQL package in the following cases:

• Dynamic SQL statements retrieve rows into records.

• You want to use the SQL cursor attributes ％FOUND, ％ISOPEN, ％NOTFOUND, or ％ROWCOUNT after declaring dynamic SQL in INSERT, UPDATE, DELETE, MERGE, or single-row SELECT statements.

For more information about the DBMS_SQL system package, see DBMS_SQL.