Blog Run Code Remotely Using REST Enabled SQL and APEX_EXEC (Create Task)

CNDEMO_CREATE_TASK_IN_REMOTE_INSTANCE.sql

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;