DECLARE l_dp_handle NUMBER; l_last_job_state VARCHAR2(30) := 'UNDEFINED'; l_job_state VARCHAR2(30) := 'UNDEFINED'; l_sts KU$_STATUS; BEGIN l_dp_handle := DBMS_DATAPUMP.open( operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => 'EMP_EXPORT', version => 'LATEST'); DBMS_DATAPUMP.add_file( handle => l_dp_handle, filename => 'SCOTT.dmp', directory => 'TEST_DIR'); DBMS_DATAPUMP.add_file( handle => l_dp_handle, filename => 'SCOTT.log', directory => 'TEST_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); DBMS_DATAPUMP.metadata_filter( handle => l_dp_handle, name => 'SCHEMA_EXPR', value => '= ''SCOTT'''); DBMS_DATAPUMP.start_job(l_dp_handle); DBMS_DATAPUMP.detach(l_dp_handle); END; /
Wednesday, 23 April 2014
Datapump directly from PL/sql
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
Thursday, 3 April 2014
Reading XML from database
Can use sqlloader
or
or
/*
<?xml version="1.0"
encoding="UTF-8"?>
<INCOME_ACCESS_ROOT_SALES>
<INCOME_ACCESS_CHILD_SALES>
<TRANSACTION_DATE>20140401</TRANSACTION_DATE>
<BTAG>a_9947b_5033c_GALA_UK_BINGO_B10G50_2565_LLK_GALBLLKBOTH_iPhone_Female_LinkP_BingoFlo40Free_CPC</BTAG>
</INCOME_ACCESS_CHILD_SALES>
</INCOME_ACCESS_ROOT_SALES>
*/
select
extractvalue(column_value,'/INCOME_ACCESS_ROOT_SALES/INCOME_ACCESS_CHILD_SALES/TRANSACTION_DATE'),
extractvalue(column_value,'/INCOME_ACCESS_ROOT_SALES/INCOME_ACCESS_CHILD_SALES/BTAG')
from
table(xmlsequence(xmltype(bfilename('JDE','IGB_SALES_TEST.xml'),
nls_charset_id('WE8ISO8859P1'))));
ODI Studio connection file
c:\users\richard.hall\AppData\Roaming\odi\oracleodi\snps_login_work.xml
or search for snps_login_work.xml
or search for snps_login_work.xml
Subscribe to:
Posts (Atom)