20.11 COPY_DATA Procedure

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

Syntax

PROCEDURE 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

Table 20-8 COPY_DATA Procedure 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 the
         apex_exec.close( l_dml_context );
         apex_exec.close( l_query_context );
         raise;
            
end;