
  native constant integer := 1;
  v7 constant integer := 2;
  --
  --------------
  --  TYPES
  --
  type varchar2s is table of varchar2(256) index by binary_integer;
  type desc_rec is record (
        col_type            binary_integer := 0,
        col_max_len         binary_integer := 0,
        col_name            varchar2(32)   := '',
        col_name_len        binary_integer := 0,
        col_schema_name     varchar2(32)   := '',
        col_schema_name_len binary_integer := 0,
        col_precision       binary_integer := 0,
        col_scale           binary_integer := 0,
        col_charsetid       binary_integer := 0,
        col_charsetform     binary_integer := 0,
        col_null_ok         boolean        := TRUE);
  type desc_tab is table of desc_rec index by binary_integer;
  ------------
  -- Bulk SQL Types
  --
  type Number_Table   is table of number         index by binary_integer;
  type Varchar2_Table is table of varchar2(2000) index by binary_integer;
  type Date_Table     is table of date           index by binary_integer;

  type Blob_Table     is table of Blob           index by binary_integer;
  type Clob_Table     is table of Clob           index by binary_integer;
  type Bfile_Table    is table of Bfile          index by binary_integer;
--  type Cfile_Table    is table of Cfile          index by binary_integer;
  --------------
  --  EXCEPTIONS
  --
  inconsistent_type exception;
    pragma exception_init(inconsistent_type, -6562);
  --  This exception is raised by procedure "column_value" or
  --  "variable_value" if the type of the given out argument where
  --  to put the requested value is different from the type of the value.

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  function open_cursor return integer;
  pragma restrict_references(open_cursor,RNDS,WNDS);
  --  Open a new cursor.
  --  When no longer needed, this cursor MUST BE CLOSED explicitly by
  --  calling "close_cursor".
  --  Return value:
  --    Cursor id number of the new cursor.
  --
  function is_open(c in integer) return boolean;
  pragma restrict_references(is_open,RNDS,WNDS);
  --  Return TRUE is the given cursor is currently open.
  --  Input parameters:
  --    c
  --      Cursor id number of the cursor to check.
  --  Return value:
  --    TRUE if the given cursor is open,
  --    FALSE if it is not.
  --
  procedure close_cursor(c in out integer);
  pragma restrict_references(close_cursor,RNDS,WNDS);
  --  Close the given cursor.
  --  Input parameters:
  --    c
  --      Cursor id number of the cursor to close.
  --  Output parameters:
  --    c
  --      Will be nulled.
  --
  procedure parse(c in integer, statement in varchar2, 
                  language_flag in integer);
  --  Parse the given statement in the given cursor. NOTE THAT PARSING AND
  --  EXECUTING DDL STATEMENTS CAN CAUSE HANGS! 
  --  Currently, the deferred parsing feature of the Oracle  Call Interface
  --  is not used. As a result, statements are parsed immediately. In addition,
  --  DDL statements are executed immediately when parsed. However, 
  --  the behavior may
  --  change in the future so that the actual parsing (and execution of DDL
  --  statement) do not occur until the cursor is executed with "execute".
  --  DO NOT RELY ON THE CURRENT TIMING OF THE ACTUAL PARSING!
  --  Input parameters:
  --    c
  --      Cursor id number of the cursor in where to parse the statement.
  --    statement
  --      Statement to parse.
  --    language_flag
  --      Specifies behavior for statement. Valid values are v6, v7 and NATIVE.
  --      v6 and v7 specifies behavior according to Version 6 and ORACLE7,
  --      respectively. NATIVE specifies behavior according to the version
  --      of the database the program is connected to.
  --    
  procedure parse(c in integer, statement in varchar2s,
                  lb in integer, ub in integer,
                  lfflg in boolean, language_flag in integer);
  --  Parse the given statement in the given cursor.  The statement is not in
  --  one piece but resides in little pieces in the PL/SQL table "statement".
  --  Conceptually what happens is that the SQL string is put together as
  --  follows:
  --  String := statement(lb) || statement(lb + 1) || ... || statement(ub);
  --  Then a regular parse follows.
  --  If "lfflg" is TRUE then a newline is inserted after each piece.
  --  For further information and for documentation on the rest of the
  --  arguments see the regular parse procedure below.
  --
  procedure bind_variable(c in integer, name in varchar2, value in number);
  pragma restrict_references(bind_variable,WNDS);
  procedure bind_variable(c in integer, name in varchar2, 
                          value in varchar2 character set any_cs);
  pragma restrict_references(bind_variable,WNDS);
  procedure bind_variable(c in integer, name in varchar2,
                          value in varchar2 character set any_cs,
                          out_value_size in integer);
  pragma restrict_references(bind_variable,WNDS);
  procedure bind_variable(c in integer, name in varchar2, value in date);
  pragma restrict_references(bind_variable,WNDS);
  procedure bind_variable(c in integer, name in varchar2, value in blob);
  pragma restrict_references(bind_variable,WNDS);
  procedure bind_variable(c in integer, name in varchar2,
                          value in clob character set any_cs);
  pragma restrict_references(bind_variable,WNDS);
  procedure bind_variable(c in integer, name in varchar2, value in bfile);
  pragma restrict_references(bind_variable,WNDS);

  procedure bind_variable_char(c in integer, name in varchar2,
                               value in char character set any_cs);
  pragma restrict_references(bind_variable_char,WNDS);
  procedure bind_variable_char(c in integer, name in varchar2,
                               value in char character set any_cs,
                               out_value_size in integer);
  pragma restrict_references(bind_variable_char,WNDS);
  procedure bind_variable_raw(c in integer, name in varchar2,
                              value in raw);
  pragma restrict_references(bind_variable_raw,WNDS);
  procedure bind_variable_raw(c in integer, name in varchar2,
                              value in raw, out_value_size in integer);
  pragma restrict_references(bind_variable_raw,WNDS);
  procedure bind_variable_rowid(c in integer, name in varchar2,
                              value in rowid);
  pragma restrict_references(bind_variable_rowid,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       n_tab in Number_Table);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       c_tab in Varchar2_Table);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       d_tab in Date_Table);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       bl_tab in Blob_Table);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       cl_tab in Clob_Table);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       bf_tab in Bfile_Table);
  pragma restrict_references(bind_array,WNDS);

  procedure bind_array(c in integer, name in varchar2,
                       n_tab in Number_Table,
                       index1 in integer, index2 in integer);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       c_tab in Varchar2_Table,
                       index1 in integer, index2 in integer);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       d_tab in Date_Table,
                       index1 in integer, index2 in integer);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       bl_tab in Blob_Table,
                       index1 in integer, index2 in integer);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       cl_tab in Clob_Table,
                       index1 in integer, index2 in integer);
  pragma restrict_references(bind_array,WNDS);
  procedure bind_array(c in integer, name in varchar2,
                       bf_tab in Bfile_Table,
                       index1 in integer, index2 in integer);
  pragma restrict_references(bind_array,WNDS);

  pragma restrict_references(define_column,RNDS,WNDS);
  procedure define_column(c in integer, position in integer, 
                          column in varchar2 character set any_cs,
                          column_size in integer);
  pragma restrict_references(define_column,RNDS,WNDS);
  procedure define_column(c in integer, position in integer, column in date);
  pragma restrict_references(define_column,RNDS,WNDS);
  procedure define_column(c in integer, position in integer, column in blob);
  pragma restrict_references(define_column,RNDS,WNDS);
  procedure define_column(c in integer, position in integer,
                          column in clob character set any_cs);
  pragma restrict_references(define_column,RNDS,WNDS);
  procedure define_column(c in integer, position in integer, column in bfile);
  pragma restrict_references(define_column,RNDS,WNDS);

  procedure define_column_char(c in integer, position in integer,
                               column in char character set any_cs,
                               column_size in integer);
  pragma restrict_references(define_column_char,RNDS,WNDS);
  procedure define_column_raw(c in integer, position in integer, 
                              column in raw,
                              column_size in integer);
  pragma restrict_references(define_column_raw,RNDS,WNDS);
  procedure define_column_rowid(c in integer, position in integer,
                                column in rowid);
  pragma restrict_references(define_column_rowid,RNDS,WNDS);

  procedure define_array(c in integer, position in integer,
                         n_tab in Number_Table,
                         cnt in integer, lower_bound in integer);
  pragma restrict_references(define_array,RNDS,WNDS);
  procedure define_array(c in integer, position in integer,
                         c_tab in Varchar2_Table,
                         cnt in integer, lower_bound in integer);
  pragma restrict_references(define_array,RNDS,WNDS);
  procedure define_array(c in integer, position in integer,
                         d_tab in Date_Table,
                         cnt in integer, lower_bound in integer);
  pragma restrict_references(define_array,RNDS,WNDS);
  procedure define_array(c in integer, position in integer,
                         bl_tab in Blob_Table,
                         cnt in integer, lower_bound in integer);
  pragma restrict_references(define_array,RNDS,WNDS);
  procedure define_array(c in integer, position in integer,
                         cl_tab in Clob_Table,
                         cnt in integer, lower_bound in integer);
  pragma restrict_references(define_array,RNDS,WNDS);
  procedure define_array(c in integer, position in integer,
                         bf_tab in Bfile_Table,
                         cnt in integer, lower_bound in integer);
  pragma restrict_references(define_array,RNDS,WNDS);


  --  Execute the given cursor and return the number of rows processed
  --  (valid and meaningful only for INSERT, DELETE or UPDATE statements;
  --  for other types of statements, the return value is undefined and
  --  should be ignored).
  --  Input parameters:
  --    c
  --      Cursor id number of the cursor to execute.
  --  Return value:
  --    Number of rows processed if the statement in the cursor is
  --    either an INSERT, DELETE or UPDATE statement or undefined otherwise.
  --
  function fetch_rows(c in integer) return integer;
  pragma restrict_references(fetch_rows,WNDS);
  --  Fetch rows from the given cursor. The function tries to fetch a
  --  row. As long as "fetch_rows" is able to fetch a
  --  row, it can be called repeatedly to fetch additional rows. If no
  --  row was actually fetched, "fetch_rows"
  --  cannot be called to fetch additional rows.
  --  Input parameters:
  --    c
  --      Cursor id number of the cursor to fetch.
  --  Return value:
  --    The number of rows actually fetched.
  --
  function execute_and_fetch(c in integer, exact in boolean default false) 
  return integer;
  pragma restrict_references(execute_and_fetch,WNDS);
  --  Execute the given cursor and fetch rows. Gives the same functionality
  --  as a call to "execute" 
  --  followed by a call to "fetch_rows". However, this function can 
  --  potentially cut down on the number of message round-trips compared to
  --  calling "execute" and "fetch_rows" separately.
  --  Input parameters:
  --    c
  --      Cursor id number of the cursor to execute and fetch.
  --    exact 
  --      Raise an exception if the number of rows matching the query 
  --      differs from one.
  --  Return value:
  --    The number of rows actually fetched.
  --    
  procedure column_value(c in integer, position in integer, value out number);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer, 
                         value out varchar2 character set any_cs);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer, value out date);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer, value out blob);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer,
                         value out clob character set any_cs);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer, value out bfile);
  pragma restrict_references(column_value,RNDS,WNDS);

  procedure column_value_char(c in integer, position in integer,
                              value out char character set any_cs);
  pragma restrict_references(column_value_char,RNDS,WNDS);
  procedure column_value_raw(c in integer, position in integer, value out raw);
  pragma restrict_references(column_value_raw,RNDS,WNDS);
  procedure column_value_rowid(c in integer, position in integer, 
                               value out rowid);
  pragma restrict_references(column_value_rowid,RNDS,WNDS);
  procedure column_value(c in integer, position in integer, value out number,
                         column_error out number, actual_length out integer);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer, 
                         value out varchar2 character set any_cs,
                         column_error out number, actual_length out integer);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer, value out date,
                         column_error out number, actual_length out integer);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value_char(c in integer, position in integer,
                              value out char character set any_cs,
                              column_error out number, 
                              actual_length out integer);
  pragma restrict_references(column_value_char,RNDS,WNDS);
  procedure column_value_raw(c in integer, position in integer, value out raw,
                             column_error out number, 
                             actual_length out integer);
  pragma restrict_references(column_value_raw,RNDS,WNDS);
  procedure column_value_rowid(c in integer, position in integer, 
                             value out rowid, column_error out number,
                             actual_length out integer);
  pragma restrict_references(column_value_rowid,RNDS,WNDS);

  procedure column_value(c in integer, position in integer,
                         n_tab in Number_table);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer,
                         c_tab in Varchar2_table);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer,
                         d_tab in Date_table);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer,
                         bl_tab in Blob_table);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer,
                         cl_tab in Clob_table);
  pragma restrict_references(column_value,RNDS,WNDS);
  procedure column_value(c in integer, position in integer,
                         bf_tab in Bfile_table);
  pragma restrict_references(column_value,RNDS,WNDS);


  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2,
                           value out varchar2 character set any_cs);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2,
                           value out date);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2, value out blob);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2,
                           value out clob character set any_cs);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2, value out bfile);
  pragma restrict_references(variable_value,RNDS,WNDS);

  procedure variable_value(c in integer, name in varchar2, 
                           value in Number_table);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2, 
                           value in Varchar2_table);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2, 
                           value in Date_table);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2, 
                           value in Blob_table);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2, 
                           value in Clob_table);
  pragma restrict_references(variable_value,RNDS,WNDS);
  procedure variable_value(c in integer, name in varchar2, 
                           value in Bfile_table);
  pragma restrict_references(variable_value,RNDS,WNDS);

  procedure variable_value_char(c in integer, name in varchar2,
                                value out char character set any_cs);
  pragma restrict_references(variable_value_char,RNDS,WNDS);
  procedure variable_value_raw(c in integer, name in varchar2,
                               value out raw);
  pragma restrict_references(variable_value_raw,RNDS,WNDS);
  procedure variable_value_rowid(c in integer, name in varchar2,
                                 value out rowid);
  pragma restrict_references(variable_value_rowid,RNDS,WNDS);

  pragma restrict_references(last_error_position,RNDS,WNDS);
  function last_sql_function_code return integer;
  pragma restrict_references(last_sql_function_code,RNDS,WNDS);
  function last_row_count return integer;
  pragma restrict_references(last_row_count,RNDS,WNDS);
  function last_row_id return rowid;
  pragma restrict_references(last_row_id,RNDS,WNDS);
  --  Get various information for the last-operated cursor in the session.
  --  To ensure that the information relates to a particular cursor,
  --  the functions should be called after an operation on that cursor and
  --  before any other operation on any other cursor.
  --  Return value:
  --    last_error_position
  --      Relative position in the statement when the error occurs.
  --    last_sql_function_code
  --      SQL function code of the statement. See list in OCI manual.
  --    last_row_count
  --      Cumulative count of rows fetched.
  --    last_row_id
  --      Rowid of the last processed row.
  --
 ------------
  --  EXAMPLES
  --
  --  create or replace procedure copy(source in varchar2,
  --                                   destination in varchar2) is
  --    --  This procedure copies rows from a given source table to
  --        a given destination table assuming that both source and destination
  --    --  tables have the following columns:
  --    --    - ID of type NUMBER,
  --    --    - NAME of type VARCHAR2(30),
  --    --    - BIRTHDATE of type DATE.
  --    id number;
  --    name varchar2(30);
  --    birthdate date;
  --    source_cursor integer;
  --    destination_cursor integer;
  --    rows_processed integer;
  --  begin
  --    -- prepare a cursor to select from the source table
  --    source_cursor := dbms_sql.open_cursor;
  --    dbms_sql.parse(source_cursor,
  --                   'select id, name, birthdate from ' || source);
  --    dbms_sql.define_column(source_cursor, 1, id);
  --    dbms_sql.define_column(source_cursor, 2, name, 30);
  --    dbms_sql.define_column(source_cursor, 3, birthdate);
  --    rows_processed := dbms_sql.execute(source_cursor);
  --
  --    -- prepare a cursor to insert into the destination table
  --    destination_cursor := dbms_sql.open_cursor;
  --    dbms_sql.parse(destination_cursor,
  --                   'insert into ' || destination ||
  --                   ' values (:id, :name, :birthdate)');
  --
  --    -- fetch a row from the source table and
  --    -- insert it into the destination table
  --    loop
  --      if dbms_sql.fetch_rows(source_cursor)>0 then
  --        -- get column values of the row
  --        dbms_sql.column_value(source_cursor, 1, id);
  --        dbms_sql.column_value(source_cursor, 2, name);
  --        dbms_sql.column_value(source_cursor, 3, birthdate);
  --        -- bind the row into the cursor which insert
  --        -- into the destination table
  --        dbms_sql.bind_variable(destination_cursor, 'id', id);
  --        dbms_sql.bind_variable(destination_cursor, 'name', name);
  --        dbms_sql.bind_variable(destination_cursor, 'birthdate', birthdate);
  --        rows_processed := dbms_sql.execute(destination_cursor);
  --      else
  --        -- no more row to copy
  --        exit;
  --      end if;
  --    end loop;
  --
  --    -- commit and close all cursors
  --    commit;
  --    dbms_sql.close_cursor(source_cursor);
  --    dbms_sql.close_cursor(destination_cursor);
  --  exception
  --    when others then
  --      if dbms_sql.is_open(source_cursor) then
  --        dbms_sql.close_cursor(source_cursor);
  --      end if;
  --      if dbms_sql.is_open(destination_cursor) then
  --        dbms_sql.close_cursor(destination_cursor);
  --      end if;
  --      raise;
  --  end;
  --
  procedure column_value_long(c in integer, position in integer, 
                              length in integer, offset in integer,
                              value out varchar2, value_length out integer);
  pragma restrict_references(column_value_long,RNDS,WNDS);
  --  Get (part of) the value of a long column.
  --  Input parameters:
  --    c
  --      Cursor id number of the cursor from which to get the value.
  --    position
  --      Position of the column of which to get the value.
  --    length
  --      Number of bytes of the long value to fetch.
  --    offset
  --      Offset into the long field for start of fetch. 
  --  Output parameters:
  --    value
  --      Value of the column as a varchar2.
  --    value_length
  --      The number of bytes actually returned in value.
  --
  procedure define_column_long(c in integer, position in integer);
  pragma restrict_references(define_column_long,RNDS,WNDS);

  pragma restrict_references(describe_columns,WNDS);
  -- Get the description for the specified column.
  -- Input parameters:
  --    c
  --      Cursor id number of the cursor from which to describe the column.
  -- Output Parameters:
  --     col_cnt
  --      The number of columns in the select list of the query.
  --    desc_tab
  --      The describe table to fill in with the description of each of the
  --      columns of the query.  This table is indexed from one to the number
  --      of elements in the select list of the query.
  --

end;
/

drop public synonym dbms_sql
/
create public synonym dbms_sql for sys.dbms_sql
/
grant execute on dbms_sql to public
/
