REMOTE Functions Supportive of Binding
REMOTE Functions Supportive of Binding#
This section explains pass-through REMOTE functions that support the binding of parameters.
REMOTE_ALLOC_STATEMENT#
This function prepares a SQL statement to be executed on the remote server. This function can only be used within stored procedures and stored functions.
Syntax#
BIGINT REMOTE_ALLOC_STATEMENT (
dblink_name IN VARCHAR,
statement_text IN VARCHAR );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_text | Input | VARCHAR | The SQL statement to be executed on the remote server. |
Return Value#
If the function executes successfully, the statement ID is returned; if not, a negative integer is returned.
Example#
<Query 1> Create a stored procedure which returns the value of column c2 of the record where the value of column c1 is 20 from table t1 of the remote server, pointed to by link1. Use parameter binding for the search condition of the value of the column c1.
EXEC REMOTE_EXECUTE_IMMEDIATE('link1', 'CREATE TABLE t1(c1 INTEGER, c2 FLOAT(38))');
EXEC REMOTE_EXECUTE_IMMEDIATE('link1', 'INSERT INTO t1 VALUES(20, 30.001)');
CREATE OR REPLACE PROCEDURE proc1()
AS
statement_id BIGINT;
row_cnt INTEGER;
result INTEGER;
col_value FLOAT(38);
BEGIN
statement_id := REMOTE_ALLOC_STATEMENT('link1', 'SELECT * FROM t1 where c1 = ?');
result := REMOTE_BIND_VARIABLE( 'link1', statement_id, 1, '20' );
IF result >= 0 THEN
result := REMOTE_EXECUTE_STATEMENT('link1', statement_id );
LOOP
result := REMOTE_NEXT_ROW( 'link1', statement_id );
EXIT WHEN result < 0;
col_value := REMOTE_GET_COLUMN_VALUE_FLOAT( 'link1', statement_id, 2, 38 );
SYSTEM_.PRINTLN(col_value);
END LOOP;
result := REMOTE_FREE_STATEMENT('link1', statement_id);
IF result < 0 THEN
SYSTEM_.PRINTLN('Free failed');
END IF;
END IF;
END;
/
REMOTE_BIND_VARIABLE#
This function binds values to parameter markers in SQL statements. This function can only be used within stored procedures and stored functions.
Syntax#
INTEGER REMOTE_BIND_VARIABLE (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
parameter_number IN VARCHAR,
value IN VARCHAR );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID |
| parameter_number | Input | INTEGER | The numerical order of the parameters in SQL statements to which values are bound. Starts from 1. |
| value | Input | VARCHAR | The value to be bound |
Return Value#
The returner of a negative number indicates failure; otherwise, success.
Example#
Please refer to the examples of REMOTE_ALLOC_STATEMENT.
REMOTE_EXECUTE_STATEMENT#
This function executes the prepared SQL statement on the remote server. This function can only be used within stored procedures and stored functions
Syntax#
INTEGER REMOTE_EXECUTE_STATEMENT (
dblink_name IN VARCHAR,
statement_id IN BIGINT );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | Statement ID |
Return Value#
The returner of a negative number indicates failure. If the returner is a non-negative number on the execution of a DML statement, the non-negative number indicates the number of affected rows; on the execution of a DDL or SELECT statement, this indicates success.
Example#
Please refer to the examples of REMOTE_ALLOC_STATEMENT.
REMOTE_FREE_STATEMENT#
This function releases the statements that have completed execution. This function can only be used within stored procedures and stored functions
Syntax#
INTEGER REMOTE_FREE_STATEMENT (
dblink_name IN VARCHAR,
statement_id IN BIGINT );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | Statement ID |
Return Value#
The returner of a negative number indicates failure; otherwise, success.
Example#
Please refer to the examples of REMOTE_ALLOC_STATEMENT.
REMOTE_GET_COLUMN_VALUE_CHAR#
This function returns the CHAR type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
CHAR(char_size) REMOTE_GET_COLUMN_CHAR (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER,
char_size IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
| char_size | Input | INTEGER | The size of the CHAR type to be returned |
Return Value#
The value of the specified column is returned.
REMOTE_GET_COLUMN_VALUE_VARCHAR#
This function returns the VARCHAR type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
VARCHAR(char_size) REMOTE_GET_COLUMN_VARCHAR (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER,
varchar_size IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns with the row. Starts from 1. |
| varchar_size | Input | INTEGER | The size of the VARCHAR type to be returned |
Return Value#
The value of the specified column is returned.
REMOTE_GET_COLUMN_VALUE_FLOAT#
This function returns the FLOAT type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
FLOAT(precision) REMOTE_GET_COLUMN_FLOAT (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER,
precision IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
| precision | Input | INTEGER | The precision of the FLOAT type to be returned |
Return Value#
The value of the specified column is returned.
Example#
Please refer to the examples of REMOTE_ALLOC_STATEMENT.
REMOTE_GET_COLUMN_VALUE_SMALLINT#
This function returns the FLOAT type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
SMALLINT REMOTE_GET_COLUMN_SMALLINT (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
Return Value#
The value of the specified column is returned.
REMOTE_GET_COLUMN_VALUE_INTEGER#
This function returns the INTEGER type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
INTEGER REMOTE_GET_COLUMN_INTEGER (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
Return Value#
The value of the specified column is returned.
REMOTE_GET_COLUMN_VALUE_BIGINT#
This function returns the BIGINT type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
BIGINT REMOTE_GET_COLUMN_BIGINT (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
Return Value#
The value of the specified column is returned.
REMOTE_GET_COLUMN_VALUE_REAL#
This function returns the REAL type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
REAL REMOTE_GET_COLUMN_REAL (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
Return Value#
The value of the specified column is returned.
REMOTE_GET_COLUMN_VALUE_DOUBLE#
This function returns the DOUBLE type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
DOUBLE REMOTE_GET_COLUMN_DOUBLE (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
Return Value#
The value of the specified column is returned.
REMOTE_GET_COLUMN_VALUE_DATE#
This function returns the DATE type column value among the columns that compose the row, after the row position has been fixed with the REMOTE_NEXT_ROW function. This function can only be used within stored procedures and stored functions.
Syntax#
DATE REMOTE_GET_COLUMN_DATE (
dblink_name IN VARCHAR,
statement_id IN BIGINT,
column_number IN INTEGER );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
| column_number | Input | INTEGER | The order of columns within the row. Starts from 1. |
Return Value#
The value of the specified column is returned.
REMOTE_NEXT_ROW#
This function moves the row position to the next position in the result set when the SELECT statement has been executed with the REMOTE_EXECUTE_STATEMENT function. This function can only be used within stored procedures and stored functions.
Syntax#
INTEGER REMOTE_NEXT_ROW (
dblink_name IN VARCHAR,
statement_id IN BIGINT );
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| dblink_name | Input | VARCHAR | The name of the database link object |
| statement_id | Input | BIGINT | The statement ID to be executed |
Return Value#
The returner of a negative number indicates failure; otherwise, success.
Example#
Please refer to the examples of REMOTE_ALLOC_STATEMENT.