Blog Run Code Remotely Using REST Enabled SQL and APEX_EXEC (Get Tasks)

CNDEMO_GET_TASKS_FROM_REMOTE_INSTANCE.sql

DECLARE
  -- Variable used to store the SQL Statement that will be sent to the Remote Server.
  l_sql_query        CLOB := q'#
    SELECT task_id
    ,      task_number
    ,      task_name
    ,      task_detail
    ,      due_date
    ,      task_status
    FROM   cndemo_tasks
    WHERE  task_status = 'NEW'
    AND    task_owner  = :TASK_OWNER
    #';
  l_context          apex_exec.t_context;
  l_sql_parameters   apex_exec.t_parameters;
  l_task_owner       VARCHAR2(100);
  l_row_count        PLS_INTEGER;
  l_column_info      apex_exec.t_column;
BEGIN

  -- Create an APEX Session. This is necessary to use APEX_EXEC.
  apex_session.create_session (p_app_id => 100, p_page_id => 1, p_username => 'CNDEMO');
  
  -- Populate variable that will be passed into the SQL statement as a bind variable.
  l_task_owner  := 'CNDEMO';

  -- Add the Parameter to the APEX_EXEC parameters PL/SQL table.
  apex_exec.add_parameter (l_sql_parameters, 'TASK_OWNER', l_task_owner);

  -- Execute the SQL Query on the Remote Server.
  l_context := apex_exec.open_remote_sql_query
   (p_server_static_id => 'DEV_CNDEMO',       -- APEX REST Enabled SQL Service Static ID
    p_sql_query        => l_sql_query,
    p_max_rows         => 10,                 -- Only attempt to fetch 10 rows.
    p_auto_bind_items  => FALSE,
    p_sql_parameters   => l_sql_parameters,
    p_total_row_count  => TRUE);              -- Calculate a total row count.

  -- Output the number of rows and the number of columns returned.
  dbms_output.put_line('Row    Count: ' || apex_exec.get_total_row_count (p_context => l_context));
  dbms_output.put_line('Column Count: ' || apex_exec.get_column_count (p_context => l_context));
  
  -- Get detailed information for the Due Date Column.
  l_column_info := apex_exec.get_column(p_context => l_context, p_column_idx => 5);
  dbms_output.put_line('Due Date Column Info: name/data type : '||
                       l_column_info.name || ' / ' || l_column_info.data_type);

  -- Loop through rows returned from the remote database.
  WHILE apex_exec.next_row( l_context ) LOOP
    dbms_output.put_line('task number / name ' || 
                          apex_exec.get_number (p_context => l_context, p_column_name => 'TASK_NUMBER') || ' / ' || 
                          apex_exec.get_varchar2 (p_context => l_context, p_column_name => 'TASK_NAME'));
    dbms_output.put_line('  due date: ' || apex_exec.get_timestamp_ltz (p_context => l_context, p_column_name => 'DUE_DATE'));
  END LOOP;
  -- CLose the query context and release resources
  apex_exec.close( l_context );

EXCEPTION WHEN OTHERS THEN
  dbms_output.put_line('Error: ' || SQLERRM);
  apex_exec.close( l_context );
END;