DBE_SQL

• 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.

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

  Table 2 DBE_SQL.SQL_UNREGISTER_CONTEXT interface parameters

  Parameter	Description
  context_id	ID of the cursor to be closed

• DBE_SQL.SQL_SET_SQL

  This function is used to parse the query statement of a specified 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:

  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;

  Table 3 DBE_SQL.SQL_SET_SQL interface parameters

  Parameter	Description
  context_id	ID of the cursor whose query statement is to be parsed
  query_string	Query statement to be parsed
  language_flag	Version language number. Currently, only 1 is supported.

• DBE_SQL.SQL_RUN

  This function executes a given cursor. It receives a cursor ID first, and the data obtained after execution is used for subsequent operations.

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

  1 2 3 4

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

  Table 4 DBE_SQL.SQL_RUN interface 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 interface 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:

  1 2 3 4

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

  Table 5 DBE_SQL.NEXT_ROW interface 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:

  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;

  Table 6 DBE_SQL.SET_RESULT_TYPE interface parameters

  Parameter	Description
  context_id	ID of the cursor to be executed
  pos	Position of a dynamically defined column in the query
  column_ref	Variable of any type. You can select an appropriate interface to dynamically define columns based on variable types.
  maxsize	Length of a defined column

• 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:

  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;

  Table 7 DBE_SQL.SET_RESULT_TYPE_CHAR interface 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:

  1 2 3 4 5

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

  Table 8 DBE_SQL.SET_RESULT_TYPE_INT interface 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:

  1 2 3 4 5

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

  Table 9 DBE_SQL.SET_RESULT_TYPE_LONG interface 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:

  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;

  Table 10 DBE_SQL.SET_RESULT_TYPE_RAW interface 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 of the RAW type
  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:

  1 2 3 4 5 6

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

  Table 11 DBE_SQL.SET_RESULT_TYPE_TEXT interface 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:

  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;

  Table 12 DBE_SQL.SET_RESULT_TYPE_UNKNOWN interface 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:

  1 2 3 4 5 6

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

  Table 13 DBE_SQL.GET_RESULT interface parameters

  Parameter	Description
  context_id	ID of the cursor to be executed
  pos	Position of a dynamically defined column in the query
  column_value	Return value of a defined 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 function is as follows:

  1 2 3 4 5 6 7

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

  Table 14 DBE_SQL.GET_RESULT_CHAR interface 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
  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 overloaded function of the DBE_SQL.GET_RESULT_CHAR function is as follows:

  1 2 3 4 5

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

  Table 15 DBE_SQL.GET_RESULT_CHAR interface 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:

  1 2 3 4 5

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

  Table 16 DBE_SQL.GET_RESULT_INT interface 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:

  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;

  Table 17 DBE_SQL.GET_RESULT_LONG interface 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

  This stored procedure 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:

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

  Table 18 DBE_SQL.GET_RESULT_RAW interface 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 overloaded function of the DBE_SQL.GET_RESULT_RAW function is as follows:

  1 2 3 4 5

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

  Table 19 DBE_SQL.GET_RESULT_RAW interface 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_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:

  1 2 3 4 5

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

  Table 20 DBE_SQL.GET_RESULT_TEXT interface 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 interface when the type is not unknown.

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

  1 2 3 4 5 6

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

  Table 21 DBE_SQL.GET_RESULT_UNKNOWN interface 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:

  1 2 3 4 5

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

  Table 22 DBE_SQL.GET_RESULT_CHAR interface 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:

  1 2 3 4 5

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

  Table 23 DBE_SQL.DBE_SQL_GET_RESULT_LONG interface 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:

  1 2 3 4 5 6

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

  Table 24 DBE_SQL.GET_RESULT_RAW interface 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.

1
2
3
4

DBE_SQL.IS_ACTIVE(
context_id                 IN    INTEGER
)
RETURN BOOLEAN;

• 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:

  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;

  Table 26 DBE_SQL.SQL_BIND_VARIABLE interface parameters

  Parameter	Description
  context_id	ID of the cursor to be queried
  query_string	Name of the bound variable
  language_flag	Bound value
  out_value_size	Size of the return value. Default: null

• 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:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

  Table 27 DBE_SQL.SQL_BIND_ARRAY interface 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.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:

  1 2 3 4 5 6 7 8

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

  Table 28 DBE_SQL.SET_RESULT_TYPE_INTS interface 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	The 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:

  1 2 3 4 5 6 7 8 9

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

  Table 29 DBE_SQL.SET_RESULT_TYPE_TEXTS interface 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	The 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:

  1 2 3 4 5 6 7 8 9

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

  Table 30 DBE_SQL.SET_RESULT_TYPE_RAWS interface 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	The 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:

  1 2 3 4 5 6 7 8 9

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

  Table 31 DBE_SQL.SET_RESULT_TYPE_BYTEAS interface 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	The 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:

  1 2 3 4 5 6 7 8 9

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

  Table 32 DBE_SQL.SET_RESULT_TYPE_CHARS interface 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	The start subscript when an array is returned
  column_size	Column length

• DBE_SQL.SET_RESULTS_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_RESULTS_TYPE function is as follows:

  1 2 3 4 5 6 7 8

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

  Table 33 DBE_SQL.SET_RESULTS_TYPE interface 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	The start subscript when an array is returned
  maxsize	Maximum length of the defined type

• 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:

  1 2 3 4 5

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

  Table 34 DBE_SQL.GET_RESULTS_INT interface 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:

  1 2 3 4 5

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

  Table 35 DBE_SQL.GET_RESULTS_TEXT interface 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:

  1 2 3 4 5

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

  Table 36 DBE_SQL.GET_RESULTS_RAW interface 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:

  1 2 3 4 5

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

  Table 37 DBE_SQL.GET_RESULTS_BYTEA interface 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:

  1 2 3 4 5

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

  Table 38 DBE_SQL.GET_RESULTS_CHAR interface 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:

  1 2 3 4 5

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

  Table 39 DBE_SQL.GET_RESULTS interface 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.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:

  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 ;

  Table 40 DBE_SQL.SQL_DESCRIBE_COLUMNS interface 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.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:

  1 2 3 4 5 6 7 8 9 10 11 12 13

  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:

  1	CREATE TYPE DBE_SQL.DESC_TAB AS TABLE OF DBE_SQL.DESC_REC;

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

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

• 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 function is as follows:

  1	CREATE TYPE DBE_SQL.VARCHAR2_TABLE AS TABLE OF VARCHAR2(2000);

• 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:

  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;

  Table 41 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:

  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;

  Table 42 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:

  1 2 3 4 5

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

  Table 43 DBE_SQL.GET_VARIABLE_RESULT interface 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:

  1 2 3 4 5

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

  Table 44 DBE_SQL.GET_VARIABLE_RESULT_CHAR interface 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:

  1 2 3 4 5 6

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

  Table 45 DBE_SQL.GET_VARIABLE_RESULT_RAW interface 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:

  1 2 3 4 5

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

  Table 46 DBE_SQL.GET_VARIABLE_RESULT_TEXT interface 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:

  1 2 3 4 5 6

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

  Table 47 DBE_SQL.GET_VARIABLE_RESULT_INT interface 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:

  1 2 3 4 5

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

  Table 48 DBE_SQL.GET_ARRAY_RESULT_TEXT interface 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:

  1 2 3 4 5

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

  Table 49 DBE_SQL.GET_ARRAY_RESULT_RAW interface 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:

  1 2 3 4 5

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

  Table 50 DBE_SQL.GET_ARRAY_RESULT_CHAR interface 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:

  1 2 3 4 5

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

  Table 51 DBE_SQL.GET_ARRAY_RESULT_INT interface parameters

  Parameter	Description
  context_id	ID of the cursor to be queried
  pos	Name of the bound parameter
  column_value	Return value

 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

-- Perform operations on RAW data in a stored procedure.
openGauss=# create or replace procedure pro_dbe_sql_all_02(in_raw raw,v_in int,v_offset int)
as 
context_id int;
v_id int;
v_info bytea :=1;
query varchar(2000);
execute_ret int;
define_column_ret_raw bytea :='1';
define_column_ret int;
begin
drop table if exists pro_dbe_sql_all_tb1_02 ;
create table pro_dbe_sql_all_tb1_02(a int ,b blob);
insert into pro_dbe_sql_all_tb1_02 values(1,HEXTORAW('DEADBEEE'));
insert into pro_dbe_sql_all_tb1_02 values(2,in_raw);
query := 'select * from pro_dbe_sql_all_tb1_02 order by 1';
-- Open a cursor.
context_id := dbe_sql.register_context();
-- Compile the cursor.
dbe_sql.sql_set_sql(context_id, query, 1);
-- Define columns.
define_column_ret:= dbe_sql.set_result_type(context_id,1,v_id);
define_column_ret_raw:= dbe_sql.set_result_type_raw(context_id,2,v_info,10);
-- Execute the cursor.
execute_ret := dbe_sql.sql_run(context_id);
loop 
exit when (dbe_sql.next_row(cursorid) <= 0);
-- Obtain values.
dbe_sql.get_result(context_id,1,v_id);
dbe_sql.get_result_raw(context_id,2,v_info,v_in,v_offset);
-- Output the result.
dbe_output.print_line('id:'|| v_id || ' info:' || v_info);
end loop;
-- Close the cursor.
dbe_sql.sql_unregister_context(context_id);
end;
/
-- Call the stored procedure.
openGauss=# call pro_dbe_sql_all_02(HEXTORAW('DEADBEEF'),0,1);

-- Delete the stored procedure.
openGauss=# DROP PROCEDURE pro_dbe_sql_all_02;