Executing non-query statements

The steps involved to execute an insert, delete or update statement are as follows:

1.

Open the cursor.

  handle := DBMS_SQL.OPEN_CURSOR;

2.

Parse the statement.

  procedure parse(handle IN INTEGER,
                  stmt IN VARCHAR2,
                  language IN INTEGER);

language is one of the following constants

• DBMS_SQL.V6 for Oracle Version 6.
• DBMS_SQL.V7 for Oracle Version 7.
• DBMS_SQL.NATIVE for database to which the program is connected.

3.

Bind any input variables (place holders).

  procedure bind_variable(handle IN INTEGER, 
                          name IN VARCHAR2,
                          value IN NUMBER);
  procedure bind_variable(handle IN INTEGER, 
                          name IN VARCHAR2,
                          value IN VARCHAR2);
  procedure bind_variable(handle IN INTEGER, 
                          name IN VARCHAR2,
                          value IN DATE);
  procedure bind_variable_char(handle IN INTEGER, 
                               name IN VARCHAR2,
                               value IN CHAR);
Examples:

  DBMS_SQL.BIND_VARIABLE(handle,':n',10);
  DBMS_SQL.BIND_VARIABLE(handle,':n',enum);
  DBMS_SQL.BIND_VARIABLE(handle,':c','Jones');
  DBMS_SQL.BIND_VARIABLE(handle,':c',ename);
  DBMS_SQL.BIND_VARIABLE(handle,':d',hdate);

4.

Execute the statement.

  nrows := DBMS_SQL.EXECUTE(handle);

5.

Close the cursor.

  DBMS_SQL.CLOSE_CURSOR(handle);