Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ Stored Procedure/ Advanced Packages/ Secondary Encapsulation Interfaces (Recommended)/ DBE_SQL

Updated on 2024-05-07 GMT+08:00

View PDF

DBE_SQL

Data Types

DBE_SQL.DESC_REC

This type is a composite type and is used to store the description of the SQL_DESCRIBE_COLUMNS API.

The prototype of the DBE_SQL.DESC_REC function is as follows:

      
           CREATE TYPE DBE_SQL.DESC_REC AS (
      col_type            int,
      col_max_len         int,
      col_name            VARCHAR2(32),
      col_name_len        int,
      col_schema_name     VARCHAR2(32),
      col_schema_name_len int,
      col_precision       int,
      col_scale           int,
      col_charsetid       int,
      col_charsetform     int,
      col_null_ok         BOOLEAN
);

DBE_SQL.DESC_TAB

This type is the TABLE type of DESC_REC and is implemented through the TABLE OF syntax.

The prototype of the DBE_SQL.DESC_TAB function is as follows:

      
           CREATE TYPE DBE_SQL.DESC_TAB AS TABLE OF DBE_SQL.DESC_REC INDEX BY INTEGER;

DBE_SQL.DATE_TABLE
This type is the TABLE type of DATE and is implemented through the TABLE OF syntax.

The prototype of the DBE_SQL.DATE_TABLE function is as follows:
1

CREATE TYPE DBE_SQL.DATE_TABLE AS TABLE OF DATE INDEX BY INTEGER;
DBE_SQL.NUMBER_TABLE
This type is the TABLE type of NUMBER and is implemented through the TABLE OF syntax.

The prototype of the DBE_SQL.NUMBER_TABLE function is as follows:
1

CREATE TYPE DBE_SQL.NUMBER_TABLE AS TABLE OF NUMBER INDEX BY INTEGER;

DBE_SQL.VARCHAR2_TABLE

This type is the TABLE type of VARCHAR2 and is implemented through the TABLE OF syntax.

The prototype of the DBE_SQL.VARCHAR2_TABLE function is as follows:

      
           CREATE TYPE DBE_SQL.VARCHAR2_TABLE AS TABLE OF VARCHAR2(32767) INDEX BY INTEGER;

DBE_SQL.BLOB_TABLE
This type is the TABLE type of BLOB and is implemented through the TABLE OF syntax.

The prototype of the DBE_SQL.BLOB_TABLE function is as follows:
1

CREATE TYPE DBE_SQL.BLOB_TABLE AS TABLE OF BLOB INDEX BY INTEGER;

API Description

Table 1 lists APIs supported by the DBE_SQL package.

**Table 1** DBE_SQL
API	Description
DBE_SQL.REGISTER_CONTEXT	Opens a cursor.
DBE_SQL.SQL_UNREGISTER_CONTEXT	Closes an open cursor.
DBE_SQL.SQL_SET_SQL	Passes a set of SQL statements or anonymous blocks to a cursor.
DBE_SQL.SQL_RUN	Executes SQL statements or anonymous blocks in a given cursor.
DBE_SQL.NEXT_ROW	Reads a row of cursor data.
DBE_SQL.SET_RESULT_TYPE	Dynamically defines a column.
DBE_SQL.SET_RESULT_TYPE_CHAR	Dynamically defines a column of the CHAR type.
DBE_SQL.SET_RESULT_TYPE_INT	Dynamically defines a column of the INT type.
DBE_SQL.SET_RESULT_TYPE_LONG	Dynamically defines a column of the LONG type.
DBE_SQL.SET_RESULT_TYPE_RAW	Dynamically defines a column of the RAW type.
DBE_SQL.SET_RESULT_TYPE_BYTEA	Dynamically defines a column of the bytea type.
DBE_SQL.SET_RESULT_TYPE_TEXT	Dynamically defines a column of the TEXT type.
DBE_SQL.SET_RESULT_TYPE_UNKNOWN	Dynamically defines a column of an unknown type.
DBE_SQL.GET_RESULT	Reads a dynamically defined column value.
DBE_SQL.GET_RESULT_CHAR	Reads a dynamically defined column value of the CHAR type.
DBE_SQL.GET_RESULT_INT	Reads a dynamically defined column value of the INT type.
DBE_SQL.GET_RESULT_LONG	Reads a dynamically defined column value of the LONG type.
DBE_SQL.GET_RESULT_RAW	Reads a dynamically defined column value of the RAW type.
DBE_SQL.GET_RESULT_BYTEA	Reads a dynamically defined column value of the bytea type.
DBE_SQL.GET_RESULT_TEXT	Reads a dynamically defined column value of the TEXT type.
DBE_SQL.GET_RESULT_UNKNOWN	Reads a dynamically defined column value of an unknown type.
DBE_SQL.DBE_SQL_GET_RESULT_CHAR	Reads a dynamically defined column value of the CHAR type.
DBE_SQL.DBE_SQL_GET_RESULT_LONG	Reads a dynamically defined column value of the LONG type.
DBE_SQL.DBE_SQL_GET_RESULT_RAW	Reads a dynamically defined column value of the RAW type.
DBE_SQL.IS_ACTIVE	Checks whether a cursor is opened.
DBE_SQL.LAST_ROW_COUNT	Returns the cumulative count of obtained rows.
DBE_SQL.RUN_AND_NEXT	Reads data of a cursor after a set of dynamically defined operations are performed on the cursor.
DBE_SQL.SQL_BIND_VARIABLE	Binds a value to a variable in a statement.
DBE_SQL.SQL_BIND_ARRAY	Binds a group of values to a variable in a statement.
DBE_SQL.SET_RESULT_TYPE_INTS	Dynamically defines a column of the INT array type.
DBE_SQL.SET_RESULT_TYPE_TEXTS	Dynamically defines a column of the TEXT array type.
DBE_SQL.SET_RESULT_TYPE_RAWS	Dynamically defines a column of the RAW array type.
DBE_SQL.SET_RESULT_TYPE_BYTEAS	Dynamically defines a column of the BYTEA array type.
DBE_SQL.SET_RESULT_TYPE_CHARS	Dynamically defines a column of the CHAR array type.
DBE_SQL.SET_RESULTS_TYPE	Dynamically defines a column of the array type.
DBE_SQL.GET_RESULTS_INT	Reads a dynamically defined column value of the INT array type.
DBE_SQL.GET_RESULTS_TEXT	Reads a dynamically defined column value of the TEXT array type.
DBE_SQL.GET_RESULTS_RAW	Reads a dynamically defined column value of the RAW array type.
DBE_SQL.GET_RESULTS_BYTEA	Reads a dynamically defined column value of the BYTEA array type.
DBE_SQL.GET_RESULTS_CHAR	Reads a dynamically defined column value of the CHAR array type.
DBE_SQL.GET_RESULTS	Reads a dynamically defined column value.
DBE_SQL.SQL_DESCRIBE_COLUMNS	Describes the column information read by the cursor.
DBE_SQL.DESCRIBE_COLUMNS	Describes the column information read by the cursor.
DBE_SQL.BIND_VARIABLE	Binds parameters.
DBE_SQL.SQL_SET_RESULTS_TYPE_C	Dynamically defines a column of the array type.
DBE_SQL.SQL_GET_VALUES_C	Reads a dynamically defined column value.
DBE_SQL.GET_VARIABLE_RESULT	Reads the return value of an SQL statement.
DBE_SQL.GET_VARIABLE_RESULT_CHAR	Reads the return value (of the char type) of an SQL statement.
DBE_SQL.GET_VARIABLE_RESULT_RAW	Reads the return value (of the raw type) of an SQL statement.
DBE_SQL.GET_VARIABLE_RESULT_TEXT	Reads the return value (of the text type) of an SQL statement.
DBE_SQL.GET_VARIABLE_RESULT_INT	Reads the return value (of the int type) of an SQL statement.
DBE_SQL.GET_VARIABLE_RESULT_TEXT	Reads the return value (of the text array type) of an SQL statement.
DBE_SQL.GET_ARRAY_RESULT_RAW	Reads the return value (of the raw array type) of an SQL statement.
DBE_SQL.GET_ARRAY_RESULT_CHAR	Reads the return value (of the char array type) of an SQL statement.
DBE_SQL.GET_ARRAY_RESULT_INT	Reads the return value (of the int array type) of an SQL statement.
DBE_SQL.SQL_SET_TABLEOF_RESULTS_TYPE_C	Dynamically defines a column of the tableof type.
DBE_SQL.SQL_GET_TABLEOF_VALUES_C	Reads a dynamically defined column value of the tableof type.

You are advised to use dbe_sql.set_result_type and dbe_sql.get_result to define columns.
If the size of the result set is greater than the value of work_mem, the result set will be spilled to a disk temporarily. The value of work_mem must be no greater than 512 MB.

DBE_SQL.REGISTER_CONTEXT
This function opens a cursor, which is the prerequisite for the subsequent dbe_sql operations. This function does not transfer any parameter. It automatically generates cursor IDs in an ascending order and returns values to integer variables.

Cursors opened by DBE_SQL are session-level variables. Cross-session call of opened cursors (such as autonomous transactions) is not supported. If a cross-session cursor is called, the behavior is unpredictable.

The prototype of the DBE_SQL.REGISTER_CONTEXT function is as follows:
1 2 3

DBE_SQL.REGISTER_CONTEXT( ) RETURN INTEGER;

DBE_SQL.SQL_UNREGISTER_CONTEXT

This function closes a cursor, which is the end of each dbe_sql operation. If this function is not called when the stored procedure ends, the memory is still occupied by the cursor. Therefore, remember to close a cursor when you do not need to use it. If an exception occurs, the stored procedure exits but the cursor is not closed. Therefore, you are advised to include this API in the exception handling of the stored procedure.

The prototype of the DBE_SQL.SQL_UNREGISTER_CONTEXT function is as follows:

     
          DBE_SQL.SQL_UNREGISTER_CONTEXT(
    context_id    IN INTEGER
)
RETURN INTEGER;

**Table 2** DBE_SQL.SQL_UNREGISTER_CONTEXT parameters
Parameter	Description
context_id	ID of the cursor to be closed

DBE_SQL.SQL_SET_SQL

Parses SQL statements or anonymous blocks in a given cursor. The statement parameters can be transferred only through the text type. The length cannot exceed 1 GB.

The prototype of the DBE_SQL.SQL_SET_SQL function is as follows:

      
           DBE_SQL.SQL_SET_SQL(
    context_id    IN INTEGER,
    query_string  IN TEXT,
    language_flag IN INTEGER
)
RETURN BOOLEAN;

**Table 3** DBE_SQL.SQL_SET_SQL parameters
Parameter	Description
context_id	ID of the cursor whose query statement is to be parsed
query_string	Query statement to be executed for parsing
language_flag	Version language. The value 1 indicates an incompatible version, and the value 2 indicates a compatible version.

DBE_SQL.SQL_RUN

This function executes a given cursor. This function receives a cursor ID and executes SQL statements or anonymous blocks in a given cursor.

The prototype of the DBE_SQL.SQL_RUN function is as follows:

      
           DBE_SQL.SQL_RUN(
    context_id    IN INTEGER,
)
RETURN INTEGER;

**Table 4** DBE_SQL.SQL_RUN parameters
Parameter	Description
context_id	ID of the cursor whose query statement is to be parsed

DBE_SQL.NEXT_ROW

This function returns the number of data rows that meet query conditions. Each time the API is executed, the system obtains a set of new rows until all data is read.

The prototype of the DBE_SQL.NEXT_ROW function is as follows:

      
           DBE_SQL.NEXT_ROW(
    context_id    IN INTEGER,
)
RETURN INTEGER;

**Table 5** DBE_SQL.NEXT_ROW parameters
Parameter	Description
context_id	ID of the cursor to be executed

DBE_SQL.SET_RESULT_TYPE

This function defines columns returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE(
    context_id IN INTEGER,
    pos        IN INTEGER,
    column_ref IN ANYELEMENT,
    maxsize    IN INTEGER default 1024
)
RETURN INTEGER;

**Table 6** DBE_SQL.SET_RESULT_TYPE parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Relative position of the queried columns in the returned result. The value starts from 1.
column_ref	Variable of any type. You can select an appropriate API to dynamically define columns based on variable types.
maxsize	Length of the defined column return type.

DBE_SQL.SET_RESULT_TYPE_CHAR

This function defines columns of the CHAR type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_CHAR function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_CHAR(
    context_id  IN INTEGER,
    pos         IN INTEGER,
    column_ref  IN TEXT,
    column_size IN INTEGER
)
RETURN INTEGER;

**Table 7** DBE_SQL.SET_RESULT_TYPE_CHAR parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
column_ref	Parameter to be defined
column_size	Length of a dynamically defined column

DBE_SQL.SET_RESULT_TYPE_INT

This function defines columns of the INT type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_INT function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_INT(
    context_id IN INTEGER,
    pos        IN INTEGER
)
RETURN INTEGER;

**Table 8** DBE_SQL.SET_RESULT_TYPE_INT parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.SET_RESULT_TYPE_LONG

This function defines columns of a long type (not LONG) returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type. The maximum size of a long column is 1 GB.

The prototype of the DBE_SQL.SET_RESULT_TYPE_LONG function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_LONG(
    context_id IN INTEGER,
    pos        IN INTEGER
)
RETURN INTEGER;

**Table 9** DBE_SQL.SET_RESULT_TYPE_LONG parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.SET_RESULT_TYPE_RAW

This function defines columns of the RAW type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_RAW function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_RAW(
    context_id  IN INTEGER,
    pos         IN INTEGER,
    column_ref  IN RAW,
    column_size IN INTEGER
)
RETURN INTEGER;

**Table 10** DBE_SQL.SET_RESULT_TYPE_RAW parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
column_ref	RAW variable
column_size	Column length

DBE_SQL.SET_RESULT_TYPE_BYTEA

Defines columns of the BYTEA type returned from a given cursor and can be used only for cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_BYTEA function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_BYTEA(
    context_id  IN INTEGER,
    pos         IN INTEGER,
    column_ref  IN BYTEA,
    column_size IN INTEGER
)
RETURN INTEGER;

**Table 11** DBE_SQL.SET_RESULT_TYPE_BYTEA parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
column_ref	BYTEA variable
column_size	Column length

DBE_SQL.SET_RESULT_TYPE_TEXT

This function defines columns of the TEXT type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_TEXT function is as follows:

      
           DBE_SQL.DEFINE_COLUMN_CHAR(
    context_id IN INTEGER,
    pos        IN INTEGER,
    maxsize    IN INTEGER
)
RETURN INTEGER;

**Table 12** DBE_SQL.SET_RESULT_TYPE_TEXT parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
maxsize	Maximum length of the defined TEXT type

DBE_SQL.SET_RESULT_TYPE_UNKNOWN

This function processes columns of unknown data types returned from a given cursor. It is used only for the system to report an error and exist when the type cannot be identified.

The prototype of the DBE_SQL.SET_RESULT_TYPE_UNKNOWN function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_UNKNOWN(
    context_id IN INTEGER,
    pos        IN INTEGER,
    col_type   IN TEXT
)
RETURN INTEGER;

**Table 13** DBE_SQL.SET_RESULT_TYPE_UNKNOWN parameters
Parameter	Description
context_id	ID of the cursor to be executed
posn	Position of a dynamically defined column in the query
col_type	Dynamically defined parameter

DBE_SQL.GET_RESULT

This function returns the cursor element value in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULT function is as follows:

      
           DBE_SQL.GET_RESULT(
    context_id   IN    INTEGER,
    pos          IN    INTEGER,
    column_value INOUT ANYELEMENT
)
RETURN ANYELEMENT;

**Table 14** DBE_SQL.GET_RESULT parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Relative position of the queried columns in the returned result. The value starts from 1.
column_value	Return value of the query result of a specified column

DBE_SQL.GET_RESULT_CHAR

This stored procedure returns the value of the CHAR type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULT_CHAR stored procedure is as follows:

      
           DBE_SQL.GET_RESULT_CHAR(
    context_id    IN    INTEGER,
    pos           IN    INTEGER,
    tr            INOUT CHARACTER,
    err           INOUT NUMERIC,
    actual_length INOUT INTEGER
);

**Table 15** DBE_SQL.GET_RESULT_CHAR parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Relative position of the queried columns in the returned result. The value starts from 1.
tr	Return value
err	Error No. It is an output parameter. The input parameter must be a variable. Currently, the output value is –1 regardless of the input parameter.
actual_length	Length of a return value

The overloading of the DBE_SQL.GET_RESULT_CHAR stored procedure is as follows:

      
           DBE_SQL.GET_RESULT_CHAR(
    context_id IN    INTEGER,
    pos        IN    INTEGER,
    tr         INOUT CHARACTER
);

**Table 16** DBE_SQL.GET_RESULT_CHAR parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
tr	Return value

DBE_SQL.GET_RESULT_INT

This function returns the value of the INT type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW. The prototype of the DBE_SQL.GET_RESULT_INT function is as follows:

      
           DBE_SQL.GET_RESULT_INT(
    context_id IN INTEGER,
    pos        IN INTEGER
)
RETURN INTEGER;

**Table 17** DBE_SQL.GET_RESULT_INT parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.GET_RESULT_LONG

This function returns the value of a long type (not LONG or BIGINT) in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULT_LONG function is as follows:

      
           DBE_SQL.GET_RESULT_LONG(
    context_id IN INTEGER,
    pos        IN    INTEGER,
    lgth       IN    INTEGER,
    off_set    IN    INTEGER,
    vl         INOUT TEXT,
    vl_length  INOUT INTEGER
)
RETURN RECORD;

**Table 18** DBE_SQL.GET_RESULT_LONG parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
lgth	Length of a return value
off_set	Start position of a return value
vl	Return value
vl_length	Length of a return value

DBE_SQL.GET_RESULT_RAW

Returns the value of the RAW type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULT_RAW stored procedure is as follows:

      
           DBE_SQL.GET_RESULT_RAW(
    context_id    IN    INTEGER,
    pos           IN    INTEGER,
    tr            INOUT RAW,
    err           INOUT NUMERIC,
    actual_length INOUT INTEGER
);

**Table 19** DBE_SQL.GET_RESULT_RAW parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
tr	Returned column value
err	Error No. It is an output parameter. The input parameter must be a variable. Currently, the output value is –1 regardless of the input parameter.
actual_length	Length of a return value. The value longer than this length will be truncated.

The overloading of the DBE_SQL.GET_RESULT_RAW stored procedure is as follows:

      
           DBE_SQL.GET_RESULT_RAW(
    context_id IN    INTEGER,
    pos        IN    INTEGER,
    tr         INOUT RAW
);

**Table 20** DBE_SQL.GET_RESULT_RAW parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
tr	Returned column value

DBE_SQL.GET_RESULT_BYTEA

Returns the value of the BYTEA type in a specified position of a cursor that is obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULT_BYTEA stored procedure is as follows:

      
           DBE_SQL.GET_RESULT_BYTEA(
    context_id IN INTEGER,
    pos        IN INTEGER
)
RETURN BYTEA;

**Table 21** DBE_SQL.GET_RESULT_BYTEA parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.GET_RESULT_TEXT

This function returns the value of the TEXT type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULT_TEXT function is as follows:

      
           DBE_SQL.GET_RESULT_TEXT(
    context_id IN  INTEGER,
    pos        IN  INTEGER
)
RETURN TEXT;

**Table 22** DBE_SQL.GET_RESULT_TEXT parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.GET_RESULT_UNKNOWN

This function returns the value of an unknown type in a specified position of a cursor. It serves as an error handling API when the type is not unknown.

The prototype of the DBE_SQL.GET_RESULT_UNKNOWN function is as follows:

      
           DBE_SQL.GET_RESULT_UNKNOWN(
    context_id IN INTEGER,
    pos        IN NTEGER,
    col_type   IN TEXT
)
RETURN TEXT;

**Table 23** DBE_SQL.GET_RESULT_UNKNOWN parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query
col_type	Returned parameter type

DBE_SQL.DBE_SQL_GET_RESULT_CHAR

This function returns the value of the CHAR type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW. Different from DBE_SQL.GET_RESULT_CHAR, the length of the return value is not set and the entire string is returned.

The prototype of the DBE_SQL.DBE_SQL_GET_RESULT_CHAR function is as follows:

      
           DBE_SQL.DBE_SQL_GET_RESULT_CHAR(
    context_id IN INTEGER,
    pos        IN INTEGER
)
RETURN CHARACTER;

**Table 24** DBE_SQL.DBE_SQL_GET_RESULT_CHAR parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.DBE_SQL_GET_RESULT_LONG

This function returns the value of a long type (not LONG or BIGINT) in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

Different from DBE_SQL.GET_RESULT_LONG, the length of the return value is not set and the entire BIGINT value is returned.

The prototype of the DBE_SQL.DBE_SQL_GET_RESULT_LONG function is as follows:

      
           DBE_SQL.DBE_SQL_GET_RESULT_LONG(
    context_id IN INTEGER,
    pos        IN INTEGER
)
RETURN BIGINT;

**Table 25** DBE_SQL.DBE_SQL_GET_RESULT_LONG parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.DBE_SQL_GET_RESULT_RAW

This function returns the value of the RAW type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

Different from DBE_SQL.GET_RESULT_RAW, the length of the return value is not set and the entire string is returned.

The prototype of the DBE_SQL.DBE_SQL_GET_RESULT_RAW function is as follows:

      
           DBE_SQL.GET_RESULT_RAW(
    context_id IN    INTEGER,
    pos        IN    INTEGER,
    tr         INOUT RAW
)
RETURN RAW;

**Table 26** DBE_SQL.GET_RESULT_RAW parameters
Parameter	Description
context_id	ID of the cursor to be executed
pos	Position of a dynamically defined column in the query

DBE_SQL.IS_ACTIVE

This function returns the status of a cursor. The status can be open, parse, execute, or define. If the status is open, the value is TRUE. If the status is unknown, an error is reported. In other cases, the value is FALSE.

The prototype of the DBE_SQL.IS_ACTIVE function is as follows:

      
           DBE_SQL.IS_ACTIVE(
    context_id     IN   INTEGER
)
RETURN BOOLEAN;

**Table 27** DBE_SQL.IS_ACTIVE parameters
Parameter	Description
context_id	ID of the cursor to be queried

DBE_SQL.LAST_ROW_COUNT
Returns the accumulated count of data rows obtained after the latest NEXT_ROW execution.

The prototype of the DBE_SQL.LAST_ROW_COUNT function is as follows:
1 2 3

DBE_SQL.LAST_ROW_COUNT( ) RETURN INTEGER;

DBE_SQL.RUN_AND_NEXT

Equivalent to calling SQL_RUN and NEXT_ROW in sequence.

The prototype of the DBE_SQL.RUN_AND_NEXT function is as follows:

      
           DBE_SQL.RUN_AND_NEXT(
    context_id IN INTEGER
)
RETURNS INTEGER;

**Table 28** DBE_SQL.RUN_AND_NEXT parameters
Parameter	Description
context_id	ID of the cursor whose query statement is to be parsed

DBE_SQL.SQL_BIND_VARIABLE

This function is used to bind a parameter to an SQL statement. When an SQL statement is executed, the SQL statement is executed based on the bound value.

The prototype of the DBE_SQL.SQL_BIND_VARIABLE function is as follows:

      
           DBE_SQL.SQL_BIND_VARIABLE(
    context_id     IN int,
    query_string   IN text,
    value          IN anyelement,
    out_value_size IN int default null
)
RETURNS void;

**Table 29** DBE_SQL.SQL_BIND_VARIABLE parameters
Parameter	Description
context_id	ID of the cursor to be queried
query_string	Name of the bound variable
value	Bound value
out_value_size	Size of the return value. Default value: NULL. In A-compatible mode, the parameter behavior is the same as that of database A only when the parameter type of the value is VARCHAR or CHAR.

DBE_SQL.SQL_BIND_ARRAY

This function is used to bind a set of parameters to an SQL statement. When an SQL statement is executed, the SQL statement is executed based on the bound array.

The prototype of the DBE_SQL.SQL_BIND_ARRAY function is as follows:

      
       
         
         
           DBE_SQL.SQL_BIND_ARRAY(
    context_id   IN int,
    query_string IN text,
    value        IN anyarray
)
RETURNS void;
DBE_SQL.SQL_BIND_ARRAY(
    context_id   IN int,
    query_string IN text,
    value        IN anyarray,
    lower_index  IN int, 
    higher_index IN int
)
RETURNS void;

DBE_SQL.SQL_BIND_ARRAY(
    context_id   IN int,
    query_string IN text,
    value        IN anyindexbytable
)
RETURNS void;
DBE_SQL.SQL_BIND_ARRAY(
    context_id   IN int,
    query_string IN text,
    value        IN anyindexbytable,
    lower_index  IN int, 
    higher_index IN int
)
RETURNS void;

          

        

      
     

**Table 30** DBE_SQL.SQL_BIND_ARRAY parameters
Parameter	Description
context_id	ID of the cursor to be queried
query_string	Name of the bound variable
value	Bound array
lower_index	Minimum subscript of the bound array
higher_index	Maximum subscript of the bound array

DBE_SQL.SQL_BIND_ARRAY does not support user-defined table types. Use the table types provided in Data Types.

DBE_SQL.SET_RESULT_TYPE_INTS

This function defines columns of the INT array type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_INTS function is as follows:

     
          DBE_SQL.SET_RESULT_TYPE_INTS(
    context_id IN int,
    pos        IN int,
    column_ref IN anyarray,
    cnt        IN int,
    lower_bnd  IN int
)
RETURNS integer;

**Table 31** DBE_SQL.SET_RESULT_TYPE_INTS parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned

DBE_SQL.SET_RESULT_TYPE_TEXTS

This function defines columns of the TEXT array type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_TEXTS function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_TEXTS(
    context_id IN int,
    pos        IN int,
    column_ref IN anyarray,
    cnt        IN int,
    lower_bnd  IN int,
    maxsize    IN int
)
RETURNS integer;

**Table 32** DBE_SQL.SET_RESULT_TYPE_TEXTS parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned
maxsize	Maximum length of the defined TEXT type

DBE_SQL.SET_RESULT_TYPE_RAWS

This function defines columns of the RAW array type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_RAWS function is as follows:

      
           DBE_SQL.set_result_type_raws(
    context_id  IN int,
    pos         IN int,
    column_ref  IN anyarray,
    cnt         IN int,
    lower_bnd   IN int,
    column_size IN int
)
RETURNS integer;

**Table 33** DBE_SQL.SET_RESULT_TYPE_RAWS parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned
column_size	Column length

DBE_SQL.SET_RESULT_TYPE_BYTEAS

This function defines columns of the BYTEA array type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_BYTEAS function is as follows:

      
           DBE_SQL.set_result_type_byteas(
    context_id  IN int,
    pos         IN int,
    column_ref  IN anyarray,
    cnt         IN int,
    lower_bnd   IN int,
    column_size IN int
)
RETURNS integer;

**Table 34** DBE_SQL.SET_RESULT_TYPE_BYTEAS parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned
column_size	Column length

DBE_SQL.SET_RESULT_TYPE_CHARS

This function defines columns of the CHAR array type returned from a given cursor and can be used only for the cursors defined by SELECT. The defined columns are identified by the relative positions in the query list. The data type of an input variable determines the corresponding column type.

The prototype of the DBE_SQL.SET_RESULT_TYPE_CHARS function is as follows:

      
           DBE_SQL.SET_RESULT_TYPE_CHARS(
    context_id  IN int, 
    pos         IN int,
    column_ref  IN anyarray,
    cnt         IN int,
    lower_bnd   IN int,
    column_size IN int
)
RETURNS integer;

**Table 35** DBE_SQL.SET_RESULT_TYPE_CHARS parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned
column_size	Column length

DBE_SQL.SET_RESULTS_TYPE

The prototype of the DBE_SQL.SET_RESULTS_TYPE function is as follows:

      
       
         
         
           DBE_SQL.SET_RESULTS_TYPE(
    context_id IN int,
    pos        IN int,
    column_ref IN anyarray,
    cnt        IN int,
    lower_bnd  IN int,
    maxsize    IN int DEFAULT 1024
) returns void;

DBE_SQL.SET_RESULTS_TYPE(
    context_id IN int,
    pos        IN int,
    column_ref IN dbe_sql.number_table,
    cnt        IN int,
    lower_bnd  IN int,
    maxsize    IN int DEFAULT 1024
);

DBE_SQL.SET_RESULTS_TYPE(
    context_id IN int,
    pos        IN int,
    column_ref IN dbe_sql.varchar2_table,
    cnt        IN int,
    lower_bnd  IN int,
    maxsize    IN int DEFAULT 32767
);

DBE_SQL.SET_RESULTS_TYPE(
    context_id IN int,
    pos        IN int,
    column_ref IN dbe_sql.date_table,
    cnt        IN int,
    lower_bnd  IN int,
    maxsize    IN int DEFAULT 1024
);

DBE_SQL.SET_RESULTS_TYPE(
    context_id IN int,
    pos        IN int,
    column_ref IN dbe_sql.blob_table,
    cnt        IN int,
    lower_bnd  IN int,
    maxsize    IN int DEFAULT 32767
);

          

        

      
     

**Table 36** DBE_SQL.SET_RESULTS_TYPE parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned
maxsize	Maximum length of the defined type

DBE_SQL.SET_RESULTS_TYPE does not support user-defined table types. Use the table types provided in "Data Types".

DBE_SQL.GET_RESULTS_INT

This function returns the value of the INT array type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULTS_INT function is as follows:

      
           DBE_SQL.GET_RESULTS_INT(
    context_id   IN    int, 
    pos          IN    int,  
    column_value INOUT anyarray
);

**Table 37** DBE_SQL.GET_RESULTS_INT parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_value	Return value

DBE_SQL.GET_RESULTS_TEXT

This function returns the value of the TEXT array type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULTS_TEXT function is as follows:

      
           DBE_SQL.GET_RESULTS_TEXT(
    context_id   IN    int,
    pos          IN    int,  
    column_value INOUT anyarray
);

**Table 38** DBE_SQL.GET_RESULTS_TEXT parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_value	Return value

DBE_SQL.GET_RESULTS_RAW

This function returns the value of the RAW array type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULTS_RAW function is as follows:

      
           DBE_SQL.GET_RESULTS_RAW(
    context_id   IN    int,
    pos          IN    int, 
    column_value INOUT anyarray
);

**Table 39** DBE_SQL.GET_RESULTS_RAW parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_value	Return value

DBE_SQL.GET_RESULTS_BYTEA

This function returns the value of the BYTEA array type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULTS_BYTEA function is as follows:

      
           DBE_SQL.GET_RESULTS_BYTEA(
    context_id   IN    int,
    pos          IN    int, 
    column_value INOUT anyarray
);

**Table 40** DBE_SQL.GET_RESULTS_BYTEA parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_value	Return value

DBE_SQL.GET_RESULTS_CHAR

This function returns the value of the CHAR array type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The prototype of the DBE_SQL.GET_RESULTS_CHAR function is as follows:

      
           DBE_SQL.GET_RESULTS_CHAR(
    context_id   IN    int,
    pos          IN    int, 
    column_value INOUT anyarray
);

**Table 41** DBE_SQL.GET_RESULTS_CHAR parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_value	Return value

DBE_SQL.GET_RESULTS

This function returns the value of the array type in a specified position of a cursor and accesses the data obtained by DBE_SQL.NEXT_ROW.

The bottom-layer mechanism of DBE_SQL.GET_RESULTS is implemented through arrays. When different arrays are used to obtain the return value of the same column, NULL values are filled in the array due to discontinuous internal indexes to ensure the continuity of array indexes. As a result, the length of the returned result array is different from that of the Oracle database.

The prototype of the DBE_SQL.GET_RESULTS function is as follows:

      
       
         
         
           DBE_SQL.GET_RESULTS(
    context_id   IN    int,
    pos          IN    int,
    column_value INOUT anyarray
);

DBE_SQL.GET_RESULTS(
    context_id   IN    int,
    pos          IN    int,
    column_value INOUT dbe_sql.varchar2_table
);

DBE_SQL.GET_RESULTS(
    context_id   IN    int,
    pos          IN    int,
    column_value INOUT dbe_sql.number_table
);

DBE_SQL.GET_RESULTS(
    context_id   IN    int,
    pos          IN    int,
    column_value INOUT dbe_sql.date_table
);

DBE_SQL.GET_RESULTS(
    context_id   IN    int,
    pos          IN    int,
    column_value INOUT dbe_sql.blob_table
);

          

        

      
     

**Table 42** DBE_SQL.GET_RESULTS parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_value	Return value

DBE_SQL.GET_RESULTS does not support user-defined table types. Use the table types provided in Data Types.

DBE_SQL.SQL_DESCRIBE_COLUMNS

This function is used to describe column information and can be used only for cursors defined by SELECT.

The prototype of the DBE_SQL.SQL_DESCRIBE_COLUMNS function is as follows:

      
           DBE_SQL.SQL_DESCRIBE_COLUMNS(
    context_id IN    int, 
    col_cnt    INOUT int, 
    desc_t     INOUT dbe_sql.desc_tab
)RETURNS record ;

**Table 43** DBE_SQL.SQL_DESCRIBE_COLUMNS parameters
Parameter	Description
context_id	ID of the cursor to be queried
col_cnt	Number of columns returned
desc_t	Description of the returned column

DBE_SQL.DESCRIBE_COLUMNS

Describes column information and can be used only for cursors defined by SELECT. This API is developed for compatibility purposes.

The prototype of the DBE_SQL.DESCRIBE_COLUMNS function is as follows:

     
          DBE_SQL.DESCRIBE_COLUMNS(
    context_id IN  int, 
    col_cnt    OUT int, 
    desc_t     OUT dbe_sql.desc_tab
)

**Table 44** DBE_SQL.DESCRIBE_COLUMNS parameters
Parameter	Description
context_id	ID of the cursor to be queried
col_cnt	Number of columns returned
desc_t	Description of the returned column

DBE_SQL.BIND_VARIABLE
This function is used to bind parameters. You are advised to use DBE_SQL.SQL_BIND_VARIABLE.

DBE_SQL.SQL_SET_RESULTS_TYPE_C

This function is used to dynamically define a column of the array type. You are not advised to use it.

The prototype of the DBE_SQL.SQL_SET_RESULTS_TYPE_C function is as follows:

      
           DBE_SQL.sql_set_results_type_c(
    context_id IN int,
    pos        IN int,
    column_ref IN anyarray,
    cnt        IN int,
    lower_bnd  IN int,
    col_type   IN anyelement,
    maxsize    IN int
)return integer;

**Table 45** DBE_SQL.SQL_SET_RESULTS_TYPE_C parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned
col_type	Variable type corresponding to the returned array type
maxsize	Maximum length of the defined type

DBE_SQL.SQL_GET_VALUES_C

This function is used to read a dynamically defined column value. You are not advised to use it.

The prototype of the DBE_SQL.SQL_GET_VALUES_C function is as follows:

      
           DBE_SQL.sql_get_values_c(
    context_id   IN    int,
    pos          IN    int,
    results_type INOUT anyarray,
    result_type  IN    anyelement
)return anyarray;

**Table 46** DBE_SQL.SQL_GET_VALUES_C parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Parameter position
results_type	Obtained result
result_type	Type of the obtained result

DBE_SQL.GET_VARIABLE_RESULT

This function is used to return the value of the bound OUT parameter and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_VARIABLE_RESULT function is as follows:

      
           DBE_SQL.get_variable_result(
    context_id   IN    int,
    pos          IN    VARCHAR2,
    column_value INOUT anyelement
);

**Table 47** DBE_SQL.GET_VARIABLE_RESULT parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter
column_value	Return value

DBE_SQL.GET_VARIABLE_RESULT_CHAR

This function is used to return the value of the bound OUT parameter of the CHAR type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_VARIABLE_RESULT_CHAR function is as follows:

      
           DBE_SQL.get_variable_result_char(
    context_id IN int,
    pos        IN VARCHAR2
)
RETURNS char

**Table 48** DBE_SQL.GET_VARIABLE_RESULT_CHAR parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter

DBE_SQL.GET_VARIABLE_RESULT_RAW

This function is used to return the value of the bound OUT parameter of the RAW type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_VARIABLE_RESULT_RAW function is as follows:

      
           CREATE OR REPLACE FUNCTION DBE_SQL.get_variable_result_raw(
    context_id IN    int,
    pos        IN    VARCHAR2,
    value      INOUT anyelement
)
RETURNS anyelement

**Table 49** DBE_SQL.GET_VARIABLE_RESULT_RAW parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter
value	Return value

DBE_SQL.GET_VARIABLE_RESULT_TEXT

This function is used to return the value of the bound OUT parameter of the TEXT type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_VARIABLE_RESULT_TEXT function is as follows:

      
           CREATE OR REPLACE FUNCTION DBE_SQL.get_variable_result_text(
    context_id IN int,
    pos        IN VARCHAR2
)
RETURNS text

**Table 50** DBE_SQL.GET_VARIABLE_RESULT_TEXT parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter

DBE_SQL.GET_VARIABLE_RESULT_INT

This function is used to return the value of the bound OUT parameter of the INT type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_VARIABLE_RESULT_INT function is as follows:

      
           DBE_SQL.get_variable_result_int(
    context_id IN    int,
    pos        IN    VARCHAR2,
    value      INOUT anyelement
)
RETURNS anyelement

**Table 51** DBE_SQL.GET_VARIABLE_RESULT_INT parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter
value	Return value

DBE_SQL.GET_ARRAY_RESULT_TEXT

This function is used to return the value of the bound OUT parameter of the TEXT array type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_ARRAY_RESULT_TEXT function is as follows:

      
           DBE_SQL.get_array_result_text(
    context_id   IN    int,
    pos          IN    VARCHAR2,
    column_value INOUT anyarray
)

**Table 52** DBE_SQL.GET_ARRAY_RESULT_TEXT parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter
column_value	Return value

DBE_SQL.GET_ARRAY_RESULT_RAW

This function is used to return the value of the bound OUT parameter of the RAW array type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_ARRAY_RESULT_RAW function is as follows:

      
           DBE_SQL.get_array_result_raw(
    context_id   IN    int,
    pos          IN    VARCHAR2,
    column_value INOUT anyarray
)

**Table 53** DBE_SQL.GET_ARRAY_RESULT_RAW parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter
column_value	Return value

DBE_SQL.GET_ARRAY_RESULT_CHAR

This function is used to return the value of the bound OUT parameter of the CHAR array type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_ARRAY_RESULT_CHAR function is as follows:

      
           DBE_SQL.get_array_result_char(
    context_id   IN    int,
    pos          IN    VARCHAR2,
    column_value INOUT anyarray
)

**Table 54** DBE_SQL.GET_ARRAY_RESULT_CHAR parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter
column_value	Return value

DBE_SQL.GET_ARRAY_RESULT_INT

This function is used to return the value of the bound OUT parameter of the INT array type and obtain the OUT parameter in a stored procedure.

The prototype of the DBE_SQL.GET_ARRAY_RESULT_INT function is as follows:

      
           DBE_SQL.get_array_result_int(
    context_id   IN    int,
    pos          IN    VARCHAR2,
    column_value INOUT anyarray
)

**Table 55** DBE_SQL.GET_ARRAY_RESULT_INT parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Name of the bound parameter
column_value	Return value

DBE_SQL.SQL_SET_TABLEOF_RESULTS_TYPE_C

Dynamically defines a column of the tableof type. You are not advised to use it.

The prototype of the DBE_SQL.SQL_SET_TABLEOF_RESULTS_TYPE_C function is as follows:

      
           DBE_SQL.SQL_SET_TABLEOF_RESULTS_TYPE_C(
    context_id IN int,
    pos        IN int,
    column_ref IN anyindexbytable,
    cnt        IN int,
    lower_bnd  IN int,
    col_type   IN anyelement,
    maxsize    IN int
)return integer;

**Table 56** DBE_SQL.SQL_SET_TABLEOF_RESULTS_TYPE_C parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Position of a dynamically defined column in the query
column_ref	Type of the returned array
cnt	Number of values obtained at a time
lower_bnd	Start subscript when an array is returned
col_type	Variable type corresponding to the returned array type
maxsize	Maximum length of the defined type

DBE_SQL.SQL_GET_TABLEOF_VALUES_C

Reads a dynamically defined column value of the tableof type. You are not advised to use it.

The prototype of theDBE_SQL.SQL_GET_TABLEOF_VALUES_C function is as follows:

      
           DBE_SQL.SQL_GET_TABLEOF_VALUES_C(
    context_id   IN    int,
    pos          IN    int,
    results_type INOUT anyindexbytable,
    result_type  IN    anyelement
)return anyindexbytable;

**Table 57** DBE_SQL.SQL_GET_TABLEOF_VALUES_C parameters
Parameter	Description
context_id	ID of the cursor to be queried
pos	Parameter position
results_type	Obtained result
result_type	Type of the obtained result

Examples

    
     
       
       
         -- Example 1
-- Create a table and insert data into the table.
CREATE TABLE test_desc_cols(
    id NUMBER,
    name VARCHAR2(50)
);
INSERT INTO test_desc_cols(id, name) VALUES (1, 'xiaoming');
INSERT INTO test_desc_cols(id, name) VALUES (2, 'xiaohong');
INSERT INTO test_desc_cols(id, name) VALUES (3, 'xiaolan');

DECLARE
context_id  INTEGER;
col_cnt     INTEGER;
v_id int;
v_name varchar2;
execute_ret  INTEGER;
BEGIN
-- Open a cursor.
context_id := DBE_SQL.REGISTER_CONTEXT();
-- Compile the cursor.
DBE_SQL.SQL_SET_SQL(context_id, 'SELECT * FROM test_desc_cols', 2);
-- Set the return value type of a column.
DBE_SQL.SET_RESULT_TYPE(context_id, 1, v_id);
DBE_SQL.SET_RESULT_TYPE(context_id, 2, v_name);
execute_ret := DBE_SQL.SQL_RUN(context_id);

loop 
exit when (DBE_SQL.NEXT_ROW(context_id) <= 0);
-- Obtain values.
DBE_SQL.GET_RESULT(context_id, 1, v_id);
DBE_SQL.GET_RESULT(context_id, 2, v_name);
-- Output the result.
dbe_output.print_line('id:'|| v_id || ' name:' || v_name);
end loop;

DBE_SQL.SQL_UNREGISTER_CONTEXT(context_id);
END;
/

CREATE OR REPLACE PROCEDURE test_square(n NUMBER, square OUT NUMBER) IS
BEGIN
  square := n * n;
END;
/

DECLARE
cur NUMBER;
query varchar(2000);
ret integer;
n NUMBER;
square Integer;
BEGIN
  n := 2;
  cur := DBE_SQL.REGISTER_CONTEXT();
  query := 'BEGIN test_square(:n_bnd, :square_bnd); END;';
  DBE_SQL.SQL_SET_SQL(cur, query, 2);
  DBE_SQL.SQL_BIND_VARIABLE(cur, 'n_bnd', n);
  DBE_SQL.SQL_BIND_VARIABLE(cur, 'square_bnd', square);
  ret := DBE_SQL.SQL_RUN(cur);
  DBE_SQL.GET_VARIABLE_RESULT(cur, 'square_bnd', square);
  DBE_OUTPUT.PRINT_LINE('square = ' || square);
  DBE_SQL.SQL_UNREGISTER_CONTEXT(cur);
END;
/

-- Example 2
-- Examples of executing DESCRIBE_COLUMNS, RUN_AND_NEXT, and LAST_ROW_COUNT
-- Create a stored procedure for printing column description information.
CREATE OR REPLACE PROCEDURE print_rec(
    rec in DBE_SQL.DESC_REC
)package AS
BEGIN
    raise INFO 'col_type            =    %', rec.col_type;
    raise INFO 'col_name            =    %', rec.col_name;
    raise INFO 'col_name_len        =    %', rec.col_name_len;
END;
/

-- Verify functions.
DECLARE
context_id  INTEGER;
col_cnt     INTEGER;
rec_tab     DBE_SQL.DESC_TAB;
excute_ret  INTEGER;
nextrow_ret INTEGER;
last_row_count INTEGER;
BEGIN
-- Open a cursor.
context_id := DBE_SQL.REGISTER_CONTEXT();
-- Compile the cursor.
DBE_SQL.SQL_SET_SQL(context_id, 'SELECT * FROM test_desc_cols', 2);
-- Print the column description information.
DBE_SQL.DESCRIBE_COLUMNS(context_id, col_cnt, rec_tab);
FOR var IN 1..col_cnt LOOP
    print_rec(rec_tab(var));
END LOOP;
-- Execute and obtain a row of data.
excute_ret := DBE_SQL.RUN_AND_NEXT(context_id);
-- Obtain a row of data.
nextrow_ret := DBE_SQL.NEXT_ROW(context_id);
-- Obtain the number of obtained data rows.
last_row_count := DBE_SQL.LAST_ROW_COUNT;
DBE_OUTPUT.PRINT_LINE('last_row_count =  '|| last_row_count);
DBE_SQL.SQL_UNREGISTER_CONTEXT(context_id);
END;
/

        

      

    
   