18.10 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 18-8 COPY_DATA Procedure Parameters
Parameter | Description |
---|---|
|
Query context to fetch rows from. |
|
DML context to write rows to. |
|
Column in the query context to indicate the DML operation to execute on the target context. Possible values are:
|
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;
Parent topic: APEX_EXEC