COPY_DATA Procedure

This procedure fetches all rows from the source context and writes to the target context. Useful for copying data between different data sources (such as local to remote, remote to web source).

Array columns are not supported by the COPY_DATA procedure at this time. In the future, these will be handled as CLOBs in JSON format.

Syntax

APEX_EXEC.COPY_DATA (
    p_from_context          IN OUT NOCOPY t_context,
    p_to_context            IN OUT NOCOPY t_context,
    p_operation_column_name IN            VARCHAR2 DEFAULT NULL );

Parameters

Parameter	Description
`p_from_context`	Query context to fetch rows from.
`p_to_context`	DML context to write rows to.
`p_operation_column_name`	Column in the query context to indicate the DML operation to execute on the target context. Possible values are: `"I"`: insert the row on the target (DML) context `"U"`: update the row on the target (DML) context `"D"`: delete the row on the target (DML) context

Example

DECLARE
    l_columns        apex_exec.t_columns;
    l_dml_context    apex_exec.t_context;
    l_query_context  apex_exec.t_context;
BEGIN
    -- I. Define DML columns
    apex_exec.add_column(
        p_columns        => l_columns,
        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_columns,
        p_column_name    => 'ENAME',
        p_data_type      => apex_exec.c_data_type_varchar2 );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'JOB',
        p_data_type      => apex_exec.c_data_type_varchar2 );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'HIREDATE',
        p_data_type      => apex_exec.c_data_type_date );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'MGR',
        p_data_type      => apex_exec.c_data_type_number );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'SAL',
        p_data_type      => apex_exec.c_data_type_number );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'COMM',
        p_data_type      => apex_exec.c_data_type_number );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'DEPTNO',
        p_data_type      => apex_exec.c_data_type_number );

    -- II. Open the Query Context object
    l_query_context := apex_exec.open_remote_sql_query(
        p_server_static_id  => 'DevOps_Remote_SQL',
        p_sql_query         => 'select * from emp',
        p_columns           => l_columns );

    -- III. Open the DML context object
    l_dml_context := apex_exec.open_remote_dml_context(
        p_server_static_id      => '{remote server static id}',
        p_columns               => l_columns,
        p_query_type            => apex_exec.c_query_type_sql_query,
        p_sql_query             => 'select * from emp' );

    -- IV. Copy rows
    apex_exec.copy_data(
        p_from_context => l_query_context,
        p_to_context   => l_dml_context );

    -- V. Close contexts and free resources
    apex_exec.close( l_dml_context );
    apex_exec.close( l_query_context );
EXCEPTION
    WHEN others THEN
         apex_exec.close( l_dml_context );
         apex_exec.close( l_query_context );
         RAISE;

END;