DBE_SQL

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

The prototype of the DBE_SQL.BLOB_TABLE type is as follows:

1

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

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 calling 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;

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:

1
2
3
4

DBE_SQL.SQL_UNREGISTER_CONTEXT(
    context_id    IN INTEGER
)
RETURN INTEGER;

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.

1
2
3
4
5
6

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

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

1
2
3
4

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

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.

1
2
3
4

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

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.

1
2
3
4
5
6
7

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

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.

1
2
3
4
5
6
7

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5
6
7

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

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.

1
2
3
4
5
6
7

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

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.

1
2
3
4
5
6

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

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.

1
2
3
4
5
6

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

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

1
2
3
4
5
6

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

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.

1
2
3
4
5
6
7

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

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.

1
2
3
4
5
6
7
8
9

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;

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

1
2
3
4
5
6
7

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

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

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5
6

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5
6

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

This function returns the status of a cursor. When a cursor is opened, parsed, executed, or defined, the value is TRUE. When a cursor is closed, the value is FALSE. If the status is unknown, an error is reported. In other cases, the cursor is closed by default.

1
2
3
4

DBE_SQL.IS_ACTIVE(
    context_id     IN   INTEGER
)
RETURN BOOLEAN;

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;

Equivalent to calling SQL_RUN and NEXT_ROW in sequence.

1
2
3
4

DBE_SQL.RUN_AND_NEXT(
    context_id IN INTEGER
)
RETURNS INTEGER;

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.

1
2
3
4
5
6
7

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

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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;

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

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:

1
2
3
4
5
6
7
8

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;

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.

1
2
3
4
5
6
7
8
9

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;

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.

1
2
3
4
5
6
7
8
9

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;

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.

1
2
3
4
5
6
7
8
9

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;

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.

1
2
3
4
5
6
7
8
9

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;

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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
);

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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
);

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

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

1
2
3
4
5

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

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:

1
2
3
4
5

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

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

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

1
2
3
4
5
6
7
8
9

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;

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

1
2
3
4
5
6

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

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

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5
6

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5
6

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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.

1
2
3
4
5

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

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

1
2
3
4
5
6
7
8
9

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;

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

1
2
3
4
5
6

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