13.5.1.2.1 Inserting a REST Source Row in Code

Review an example of inserting data using a REST Data Source.

A simple example that inserts two employees using the Employees (Simple HTTP) REST Data Source is below. Notice the code references the REST Data Source using its Static ID employees_simple_http.

declare
    l_cols     apex_exec.t_columns;
    l_dml_ctx  apex_exec.t_context;
begin
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'EMPNO',
        p_data_type      => apex_exec.c_data_type_number,
        p_is_primary_key => true);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'MGR',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'SAL',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'COMM',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'DEPTNO',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns     => l_cols,        
        p_column_name => 'ENAME',
        p_data_type   => apex_exec.c_data_type_varchar2);
    apex_exec.add_column(
        p_columns     => l_cols,        
        p_column_name => 'JOB',
        p_data_type   => apex_exec.c_data_type_varchar2);
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'HIREDATE',
        p_data_type   => apex_exec.c_data_type_date);
    l_dml_ctx := apex_exec.open_rest_source_dml_context(
                    p_static_id  => 'employees_simple_http',
                    p_columns    => l_cols);
    -- Insert LUCY
    apex_exec.add_dml_row(l_dml_ctx,apex_exec.c_dml_operation_insert);
    apex_exec.set_value(l_dml_ctx,'EMPNO',1234);
    apex_exec.set_value(l_dml_ctx,'ENAME','LUCY');
    apex_exec.set_value(l_dml_ctx,'HIREDATE',date '2025-06-27');
    -- Insert LEO
    apex_exec.add_dml_row(l_dml_ctx,apex_exec.c_dml_operation_insert);
    apex_exec.set_value(l_dml_ctx,'EMPNO',1235);
    apex_exec.set_value(l_dml_ctx,'ENAME','LEO');
    apex_exec.set_value(l_dml_ctx,'HIREDATE',date '2025-05-14');
    -- Process the rows in the DML context
    apex_exec.execute_dml(l_dml_ctx);  
    apex_exec.close(l_dml_ctx);
exception
    when others then
        apex_exec.close(l_dml_ctx);
        raise;
end;