20.35 OPEN_REMOTE_SQL_QUERY Function

This function opens a query context and executes the provided SQL query on the ORDS REST Enabled SQL instance.

Syntax

FUNCTION OPEN_REMOTE_SQL_QUERY(
    p_server_static_id      IN VARCHAR2,
    p_sql_query             IN VARCHAR2,
    p_sql_parameters        IN t_parameters DEFAULT c_empty_parameters,
    p_auto_bind_items       IN BOOLEAN      DEFAULT TRUE,
    --
    p_first_row             IN PLS_INTEGER  DEFAULT NULL,
    p_max_rows              IN PLS_INTEGER  DEFAULT NULL,
    --
    p_total_row_count       IN BOOLEAN      DEFAULT FALSE,
    p_total_row_count_limit IN PLS_INTEGER  DEFAULT NULL )
    RETURN t_context;

Parameters

Table 20-33 OPEN_REMOTE_SQL_QUERY Function Parameters

Parameter	Description
p_server_static_id	Static ID of the ORDS REST Enabled SQL Instance.
p_sql_query	SQL Query to execute.
p_sql_parameters	Bind variables to pass to the remote server.
p_auto_bind_items	Whether to auto-bind all page items.
p_first_row	First row to be fetched from the result set.
p_max_rows	Maximum amount of rows to be fetched.
p_total_row_count	Whether to determine the total row count.
p_total_row_count_limit	Upper boundary for total row count computation.

Returns

The context object representing a cursor for the web source query.

Example

The following example assumes a REST enabled ORDS instance to be configured in Shared Components with the static ID "My_Remote_SQL_Instance". Based on that, the example executes the query on the remote server and prints out the result set. This example code could be used Within a plug-in or within a "Execute PL/SQL" region.

declare
    l_context apex_exec.t_context;

    l_idx_empno    pls_integer;
    l_idx_ename    pls_integer;
    l_idx_job      pls_integer;
    l_idx_hiredate pls_integer;
    l_idx_mgr      pls_integer;
    l_idx_sal      pls_integer;
    l_idx_comm     pls_integer;
    l_idx_deptno   pls_integer;

begin
    l_context := apex_exec.open_remote_sql_query(
         p_server_static_id   => 'My_Remote_SQL_Instance',
         p_sql_query          => 'select * from emp' );

    l_idx_empno    := apex_exec.get_column_position( l_context, 'EMPNO'); 
    l_idx_ename    := apex_exec.get_column_position( l_context, 'ENAME'); 
    l_idx_job      := apex_exec.get_column_position( l_context, 'JOB'); 
    l_idx_hiredate := apex_exec.get_column_position( l_context, 'HIREDATE'); 
    l_idx_mgr      := apex_exec.get_column_position( l_context, 'MGR'); 
    l_idx_sal      := apex_exec.get_column_position( l_context, 'SAL'); 
    l_idx_comm     := apex_exec.get_column_position( l_context, 'COMM'); 
    l_idx_deptno   := apex_exec.get_column_position( l_context, 'DEPTNO'); 

    while apex_exec.next_row( l_context ) loop

          htp.p( 'EMPNO: ' || apex_exec.get_number  ( l_context, l_idx_empno    ) ); 
          htp.p( 'ENAME: ' || apex_exec.get_varchar2( l_context, l_idx_ename    ) );
          htp.p( 'MGR:   ' || apex_exec.get_number  ( l_context, l_idx_mgr      ) );

    end loop;

    apex_exec.close( l_context );
    return;
exception
    when others then
         apex_debug.log_exception;
         apex_exec.close( l_context );
    raise;    
end;