NULLs are a bad idea use 'UNKNOWN' cos it gets indexed - another Myth!

I have thought this for a long time and sure enough Tom explains.

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

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.