Executing queries

The sequence of steps required to process a dynamic query is as follows:

1.

Open the cursor. Same as before.

2.

Parse the statement. Same as before.

3.

Bind any input variables. Same as before.

4.

Define the output variables.

  procedure define_column(handle IN INTEGER,
                          position IN INTEGER,
                          column IN NUMBER); 
  procedure define_column(handle IN INTEGER,
                          position IN INTEGER,
                          column IN VARCHAR2,
                          col_size IN INTEGER); 
  procedure define_column(handle IN INTEGER,
                          position IN INTEGER,
                          column IN DATE,
                          col_size IN INTEGER); 
  procedure define_column_char(handle IN INTEGER,
                          position IN INTEGER,
                          column IN CHAR,
                          col_size IN INTEGER); 

Examples:

  DBMS_SQL.DEFINE_COLUMN(handle,1,ccno);
  DBMS_SQL.DEFINE_COLUMN(handle,2,ccname,30);
  DBMS_SQL.DEFINE_COLUMN(handle,3,ccstreet,30);
  DBMS_SQL.DEFINE_COLUMN(handle,4,ccdate,9);

5.

Execute the query. Same as before.

6.

Fetch the rows.

  if DBMS_SQL.FETCH_ROWS(handle) = 0 then
        exit;
  else
    ...

The function returns the number of rows in the result of the query.

7.

Return the results to PL/SQL variables.

  procedure column_value(handle IN INTEGER,
                         position IN INTEGER,
                         value OUT NUMBER);
  procedure column_value(handle IN INTEGER,
                         position IN INTEGER,
                         value OUT VARCHAR2);
  procedure column_value(handle IN INTEGER,
                         position IN INTEGER,
                         value OUT DATE);
  procedure column_value(handle IN INTEGER,
                         position IN INTEGER,
                         value OUT CHAR);
Examples:

  DBMS_SQL.COLUMN_VALUE(handle,1,ccno);
  DBMS_SQL.COLUMN_VALUE(handle,2,ccname);
  DBMS_SQL.COLUMN_VALUE(handle,3,ccstreet);
  DBMS_SQL.COLUMN_VALUE(handle,4,ccdate);

8.

Close the cursor. Same as before.