14.19 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

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;