Datapump directly from PL/sql

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

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;
/

Reading XML from database

Can use sqlloader
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

sql dev 4 ubuntu core dump - fix

ulimit -c unlimited
unset -v GNOME_DESKTOP_SESSION_ID
./sqlveveloper.sh

https://forums.oracle.com/thread/2559937

sys.odciNumberList

declare
     l_data sys.odciNumberList :=
            sys.odciNumberList
            ( 35, 34, 33, 34, 35,
            36, 37, 36, 35, 34, 35,
                              36, 37 );
     l_cnt  number := l_data.count;
   begin
     for i in 1 .. l_cnt
     loop
         insert into stocks
         ( symbol, tstamp, price )
         values
         ('XYZ', sysdate-l_cnt+i,
                           l_data(i) );
     end loop;
    commit;
   end;
 /

Misc

ref_cursors,

global temp table

RELY constraints, even though they are not used for data validation, can:

• Enable more sophisticated query rewrites for materialized views. See Chapter 22, "Query Rewrite" for further details.
• Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

Using fake values for NULL is a BAD idea

Quote from Tom Kyte


But what does this have to do with NULL values? Nothing really—it has to do with what can happen when developers do not use a NULL value when they should have. Many times developers fear using NULLs: they do not understand them, and they do not believe they can be indexed, so they avoid them. They will use a “fake” value—such as 01-JAN-9999—to represent a missing date value. This is a bad idea for many reasons; the first I’ll show you is how it can throw off cardinality estimates.

Taken from 
http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-1867739.html