DBMS_SQL package consists of many procedures like PARSE, BIND_ARRAY, BIND_VARIABLE, CLOSE_CURSOR, DEFINE_ARRAY etc and functions like EXCEUTE, FETCH_ROWS, IS_OPEN, and OPEN_CURSOR etc.

The following procedures and functions are executed to process an SQL statement..

• OPEN_CURSOR
• PARSE
• BIND_VARIABLE or BIND_ARRAY
• DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY
• EXECUTE
• FETCH_ROWS or EXECUTE_AND_FETCH
• VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG
• CLOSE_CURSOR

OPEN_CURSOR Function

To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR function, you receive a cursor id number for the data  structure representing a valid cursor maintained by Oracle.

PARSE Procedure

Every SQL statement must be parsed by calling the PARSE Procedure. Parsing the statement checks the statement's syntax and associates it with the cursor in your program.

BIND_VARIABLE  Procedure or BIND_ARRAY Procedure

Many DML statements require that data in your program be input to Oracle. When you define a SQL statement that contains input data to be supplied at runtime, you must use placeholders in the SQL statement to mark where data must be supplied.For each placeholder in the SQL statement, you must call one of the bind procedures, the BIND_VARIABLE Procedures or BIND_ARRAY Procedures, to supply the value of a variable in your program (or the values of an  array) to the placeholder.  DMBS_SQL can run a DML statement multiple times — each time with a different bind variable. The BIND_ARRAY procedure lets you bind a collection of scalars, each value of which is used as an input variable once for each EXECUTE.

DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY

The columns of the row being selected in a SELECT statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call one of the define procedures (DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY) to specify the variables that are to receive the SELECT values, much the way an INTO clause does for a static query.

Use the DEFINE_COLUMN_LONG procedure to define LONG columns, in the same way that DEFINE_COLUMN is used to define non-LONG columns. You must call DEFINE_COLUMN_LONG before using the COLUMN_VALUE_LONG procedure to fetch from the LONG column.

Use the DEFINE_ARRAY procedure to define a PL/SQL collection into which you want to fetch rows in a single SELECT statement. DEFINE_ARRAY provides an interface to fetch multiple rows at one fetch. You must call DEFINE_ARRAY before using the COLUMN_VALUE procedure to fetch the rows.

EXECUTE Function

Call this function to run your SQL statement.

FETCH_ROWS Function or EXECUTE_AND_FETCH Function

The FETCH_ROWS function retrieves the rows that satisfy the query. Each successive fetch retrieves another set of rows, until the fetch is unable to retrieve anymore rows. Instead of calling EXECUTE and then FETCH_ROWS, you may find it more efficient to call EXECUTE_AND_FETCH if you are calling EXECUTE for a single execution.

VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG

For queries, call COLUMN_VALUE to determine the value of a column retrieved by the FETCH_ROWS call. For anonymous blocks containing calls to PL/SQL procedures or DML statements with returning clause, call VARIABLE_VALUE to retrieve the values assigned to the output variables when statements were run.

To fetch just part of a LONG database column (which can be up to two gigabytes in size), use the COLUMN_VALUE_LONG procedure. You can specify the offset (in bytes) into the column value, and the number of bytes to fetch.

CLOSE_CURSOR Procedure

When you no longer need a cursor for a session, close the cursor by calling CLOSE_CURSOR.

EXCEPTIONS:

• inconsistent_type exception
• pragma exception_init(inconsistent_type, -6562) 

This exception is raised by procedure COLUMN_VALUE or VARIABLE_VALUE
when the type of the given OUT parameter (for where to put the requested  value) is different from the type of the value.      

Other Procedures in DBMS_SQL are:

• DESCRIBE_COLUMNS
• DESCRIBE_COLUMNS2

Other Functions in DBMS_SQL are:

• IS_OPEN
• LAST_ERROR_POSITION
• LAST_ROW_COUNT
• LAST_ROW_ID
• LAST_SQL_FUNCTION_CODE

NOTES : Oracle8i introduces native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can place dynamic SQL statements directly into PL/SQL blocks.In most situations, native dynamic SQL can replace DBMS_SQL. Native dynamic SQL is easier to use and performs better than DBMS_SQL.