Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Blog Run Code Remotely Using REST Enabled SQL and APEX_EXEC (Get Tasks)
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment