Use this procedure to specify the datatype and length for each column returned when selecting data. DEFINE_COLUMN is overloaded to accommodate the preceding datatypes. The column size value is not used for the NUMBER, DATE, and MLSLABEL datatypes.

For the CHAR, RAW, and ROWID columns, use DEFINE_COLUMN_CHAR, DEFINE_COLUMN_RAW, and DEFINE_COLUMN_ROWID.

FUNCTION EXECUTE(

     c IN INTEGER)

RETURN INTEGER;

This function returns the number of rows processed for DML statements. For any other type of statement, the return value is undefined and should be discarded, but a variable must be assigned for it. The execution step of the process actually performs the specified SQL statement in the database.

FUNCTION FETCH_ROWS(

     c IN INTEGER)

RETURN INTEGER;

The name of this function is a bit of a misnomer because FETCH_ROWS is used to return query results to a local buffer one row at a time. These row values can then be retrieved into variables using the COLUMN_VALUE procedure. This cycle can be repeated until FETCH_ROWS returns a 0 to indicate it has fetched all the rows.

PROCEDURE COLUMN_VALUE(

     c IN INTEGER,

     position IN INTEGER,

     value OUT <datatype>

     column_error OUT NUMBER

     actual_length OUT INTEGER);

where datatype is one of the following:

NUMBER

VARCHAR2

INTEGER

DATE

MLSLABEL

The COLUMN_VALUE procedure is used to store a column value from a row returned by FETCH_ROWS into a variable. Once a row is stored into variables, the variable values can be processed or stored in a table before the next row is fetched and stored. Usually the variables specified in DEFINE_COLUMN are used. A COLUMN_VALUE call should be made for each column specified with DEFINE_COLUMN.

For data types other than these, use the following procedures. The IN and OUT parameters are the same:

COLUMN_VALUE_CHAR

COLUMN_VALUE_RAW

COLUMN_VALUE_ROWID

Here’s a very simple example of a dynamic SQL statement being issued through DBMS_SQL:

-- This demonstrates dynamic SQL in the most simple select I could come up

with.



SET SERVEROUTPUT ON



DECLARE

    SQLStatement     VARCHAR2(255);

    FileName     dba_data_files.file_name%TYPE;

    TablespaceName     dba_tablespaces.tablespace_name%TYPE;

    CursorID     INTEGER;

    Rows         INTEGER;

BEGIN



    -- Let’s pretend the value below came from somewhere outside this

block.



    TablespaceName := 'SYSTEM’;

    CursorID := DBMS_SQL.OPEN_CURSOR;



    -- Normally the statement below wouldn’t run because the value for

    -- :p1 is unknown.



    SQLStatement := 'SELECT file_name FROM dba_data_files WHERE

tablespace_name = :p1’;



    -- Send it to the server to parse.



    DBMS_SQL.PARSE(CursorID, SQLStatement, DBMS_SQL.V7);



    -- Bind the TablespaceName variable to the :p1 place holder.



DBMS_SQL.BIND_VARIABLE(CursorID, ':p1’, TablespaceName);



    -- Set up a buffer column to hold the retrieved data.

    -- Remember the column_size parameter is required.



    DBMS_SQL.DEFINE_COLUMN(CursorID, 1, FileName, 60);



    -- Run that puppy. 



    Rows := DBMS_SQL.EXECUTE(CursorID);



    -- Now we need to get the data into our local buffer and extract it

    -- into variables.



    LOOP



        -- Get the data and exit if there’s nothing left to get.



        IF DBMS_SQL.FETCH_ROWS(CursorID) = 0 THEN

            EXIT;

        END IF;



        -- Extract the value into our variable.



        DBMS_SQL.COLUMN_VALUE(CursorID, 1, FileName);



        -- Print it to the screen for proof that it worked.



        DBMS_OUTPUT.PUT_LINE(FileName);



    END LOOP;



    DBMS_SQL.CLOSE_CURSOR(CursorID);



EXCEPTION

    WHEN OTHERS THEN

    RAISE;

END;

/

Communicating Between Processes

Oracle provides a number of ways to communicate between PL/SQL processes. The packages range in sophistication and flexibility from the simple DBMS_PIPE to the rather daunting DBMS_AQ. The package you pick will depend on your need for scheduling, security, and dependability, as well as simplicity of use.