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 (Create Task)
DECLARE
-- Variable to store the PL/SQL block which will be sen to the Remote Server.
l_plsql_code CLOB := q'#
DECLARE
l_new_task_number cndemo_tasks.task_number%TYPE;
BEGIN
cndemo_tasks_pk.create_task
(p_task_name => :TASK_NAME,
p_task_detail => :TASK_DETAIL,
p_task_owner => :TASK_OWNER,
p_due_date => :DUE_DATE,
x_task_number => l_new_task_number);
-- Populate Return Bind Variable.
:NEW_TASK_NUMBER := l_new_task_number;
END;#';
l_sql_parameters apex_exec.t_parameters;
l_new_task_number NUMBER;
l_task_name VARCHAR2(50);
l_task_detail VARCHAR2(32000);
l_task_owner VARCHAR2(100);
l_due_date TIMESTAMP (6) WITH LOCAL TIME ZONE;
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 local Task variables to send to the remote procedure.
l_task_name := 'JD Test Task';
l_task_detail := 'Please perform this task as soon as possible';
l_task_owner := 'CNDEMO';
l_due_date := CURRENT_TIMESTAMP + INTERVAL '1' DAY;
-- Add Parameters / Bind Variables that will be used by APEX_EXEC.
apex_exec.add_parameter (l_sql_parameters, 'TASK_NAME', l_task_name);
apex_exec.add_parameter (l_sql_parameters, 'TASK_DETAIL', l_task_detail);
apex_exec.add_parameter (l_sql_parameters, 'TASK_OWNER', l_task_owner);
apex_exec.add_parameter (l_sql_parameters, 'DUE_DATE', l_due_date);
-- Add a parameter to receive the new Task Number.
apex_exec.add_parameter (l_sql_parameters, 'NEW_TASK_NUMBER', l_new_task_number);
-- Execute the PL/SQL Block on the Remote Server using the REST Enabled SQL Service 'DEV_CNDEMO'.
apex_exec.execute_remote_plsql
(p_server_static_id => 'DEV_CNDEMO',
p_plsql_code => l_plsql_code,
p_auto_bind_items => false,
p_sql_parameters => l_sql_parameters);
-- Get the value of the outout Bind Variable NEW_TASK_NUMBER
l_new_task_number := apex_exec.get_parameter_number
(p_parameters => l_sql_parameters,
p_name => 'NEW_TASK_NUMBER');
dbms_output.put_line ('New Task # '||l_new_task_number);
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment