Some nuances working with Oracle

    1. Using preceding load and aliases.

    This script will fail in Oracle:
    LOAD
    ID,
    Amount;
    SQL SELECT
    ID,
    AMT as Amount
    FROM MY_ORACLE_TABLE;
    The reason - alias Amount in SQL actually will be created in upper case - AMOUNT, and the LOAD doesn't find the field Amount.
    There are two ways to fix it:

    a) Use alias in the LOAD part:
    LOAD
    ID,
    AMT as Amount;
    SQL SELECT
    ID,
    AMT
    FROM MY_ORACLE_TABLE;

    b) Use quotes:
    LOAD
    ID,
    Amount;
    SQL SELECT
    ID,
    AMT as "Amount"
    FROM MY_ORACLE_TABLE;

    2. Date format.

    Sometimes we want to limit data load based on a date:
    LOAD
    ID,
    AMT as Amount;
    SQL SELECT
    ID,
    AMT
    FROM MY_ORACLE_TABLE
    WHERE DATEFIELD <= '$(StartDate)';
    This will work fine in SQL Server, but not in Oracle. We have to format the date explicitly, for example:

    LOAD
    ID,
    AMT as Amount;
    SQL SELECT
    ID,
    AMT
    FROM MY_ORACLE_TABLE
    WHERE DATE_KEY <= TO_DATE('$(StartDate)', 'MM/DD/YYYY HH24:MI:SS');

    Certainly it helps to have the StartDate variable in the same format, here it will be 'MM/DD/YYYY hh:mm:ss'.


    Enjoy Oracling,
    Michael S