Results 1 to 5 of 5

Thread: External Tables

  1. #1
    Join Date
    Sep 2006
    Posts
    1

    Unanswered: External Tables

    Hi,

    I am creating a external table. Below is the code:

    DECLARE
    l_err_msg VARCHAR2(300) ;
    l_file_name VARCHAR2(100):='&1';
    l_stmt VARCHAR2(10000) ;
    l_errloc VARCHAR2(20);

    PROCEDURE put_log_message (p_message IN VARCHAR2)
    IS
    BEGIN
    fnd_file.put_line (fnd_file.LOG, p_message);
    END;

    BEGIN
    BEGIN
    BEGIN
    l_errloc := 'DROP TABLE';
    l_stmt := 'DROP TABLE tmp_par042_fdms_recon_info' ;
    EXECUTE IMMEDIATE l_stmt ;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    put_log_message ('Creating Table tmp_par042_jpmchase_recon_info! ');
    l_errloc := 'CREATE TABLE';
    l_stmt := 'CREATE TABLE tmp_par042_fdms_recon_info
    ( record_type VARCHAR2(2),
    account_number VARCHAR2(12),
    card_type VARCHAR2(1),
    posted_date VARCHAR2(8),
    submitted_date VARCHAR2(8),
    payment_date VARCHAR2(8),
    submitted_sales VARCHAR2(10),
    adjustment_record VARCHAR2(10),
    accepted_sales VARCHAR2(10),
    chargeback_record VARCHAR2(9),
    reversal_record VARCHAR2(9),
    outlet_sales_returns_totals VARCHAR2(8),
    store_number VARCHAR2(6),
    outlet_payment VARCHAR2(12),
    Transit_Routing_Number VARCHAR2(15),
    ACH_payments VARCHAR2(14),
    Fee_Credits VARCHAR2(14),
    Fee_Credit_Items VARCHAR2(10),
    Fee_Descriptor VARCHAR2(30),
    Fee_Debits VARCHAR2(14),
    Fee_Debit_Items VARCHAR2(10),
    Number_of_ACH_Payments VARCHAR2(8),
    NYBD_NABG_DDA_Payments_Amount VARCHAR2(14),
    NYBD_NABG_DDA_Payment_TXNS VARCHAR2(8),
    NV_DDA_Payment_Amount VARCHAR2(14),
    NV_DDA_Payment_TXNS VARCHAR2(8),
    Bankwire_Payments VARCHAR2(14),
    Number_of_Bankwire_Payments VARCHAR2(8)
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY dat_dir
    ACCESS PARAMETERS
    (
    RECORDS DELIMITED BY NEWLINE
    badfile bad_dir: '||'''fdms_recon_bad_data'''||
    'logfile log_dir: '||'''fdms_recon_data_log'''||
    'fields
    ( record_type position(32:33),
    account_number position(01:12),
    card_type position(34:34),
    posted_date position(39:46),
    submitted_date position(13:20),
    submitted_sales position(76:85),
    adjustment_record position(76:85),
    accepted_sales position(76:85),
    chargeback_record position(56:64),
    reversal_record position(56:64),
    store_number position(47:52),
    outlet_payment position(82:93),
    Transit_Routing_Number position(94:108),
    ACH_payments position(78:91),
    Fee_Credits decode(position(32:33),''11'',position(56:65),''52 '',position(56:67),''62'',position(56:69),'' ''),
    Fee_Credit_Items decode(position(32:33),''11'',position(66:71),''52 '',position(68:75),''62'',position(70:79),'' ''),
    Fee_Descriptor position(72:101),
    Fee_Debits decode(position(32:33),''11'',position(102:111),'' 52'',position(76:87),''62'',position(80:93),'' ''),
    Fee_Debit_Items decode(position(32:33),''11'',position(112:117),'' 52'',position(88:95),''62'',position(94:103),'' ''),
    Number_of_ACH_Payments position(92:99),
    NYBD_NABG_DDA_Payments_Amount position(100:113),
    NYBD_NABG_DDA_Payment_TXNS position(114:121),
    NV_DDA_Payment_Amount position(122:135),
    NV_DDA_Payment_TXNS position(136:143),
    Bankwire_Payments position(144:157),
    Number_of_Bankwire_Payments position(158:165)
    )
    )
    LOCATION('||''''||l_file_name||''''||')
    )
    PARALLEL 3
    REJECT LIMIT UNLIMITED';

    EXECUTE IMMEDIATE(l_stmt);
    COMMIT ;
    EXCEPTION
    WHEN OTHERS THEN
    raise_application_error (-20000, SQLERRM || ' ' || l_errloc);
    END;
    put_log_message ('Process Completed!');
    END ;
    /

    and i am getting this error :

    TEST1:>select count(*) from tmp_par042_fdms_recon_info
    2 /
    select count(*) from tmp_par042_fdms_recon_info
    *
    ERROR at line 1:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-00554: error encountered while parsing access parameters
    KUP-01005: syntax error: found "identifier": expecting one of: "comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
    KUP-01008: the bad identifier was: decode
    KUP-01007: at line 17 column 22
    ORA-06512: at "SYS.ORACLE_LOADER", line 14
    ORA-06512: at line 1

    I can understand that this problem is coming because of decode, my question is cant we use decode, if yes then how?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I really wouldn't know whether DECODE is possible or not in this case (being too lazy to check the documentation), but - in my opinion - you can't use it that way.

    Are you sure that you coded those positions correctly? I mean, what kind of data do you have when

    -> position (56 - 69) in one case means "Free credit",

    while, at the same time

    -> position(66 - 71) means "Free credit items".

    Those positions overlap (as well as many others). Is that correct? If so, oh well, I don't like it.

    I'd suggest the following: first check those positions. Then, create one column which will hold value of position (32 - 33); you call it a "record_type".
    Another column will be a long one - positions from (56 - 117) - if I saw it right (minimum and maximum value mentioned in your DECODEs). The general idea is to forget about pieces of this string.
    Furthermore, create other columns you need (free_credit, free_credit_items, ...).

    Once you have the table created (BTW, is it necessary to create it on-the-fly, dynamically?) (BTW2, first create it in SQL and make sure it works correctly, then make it fancy and move the whole code to PL/SQL), and when you figure out which position means what, it is a pure SQL to fetch whatever you want:

    SELECT SUBSTR(long_column, 1, 13) free_item
    FROM external_table
    WHERE record_type = '11';

    Of course, variations are possible; now you can easily use DECODE, such as

    SELECT DECODE(record_type, '11', SUBSTR(long_column, 1, 13), ...)

    or, even better, CASE to improve code visibility.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down


    In any case, the following DECODE makes no sense (as well as the other DECODE's:

    Fee_Credits decode(position(32:33),''11'',position(56:65),''52 '',position(56:67),''62'',position(56:69),'' ''),

    Please check for correct syntax.


    PS: Also, there are MANY overlapping fields (more than littlefoot pionted out).


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    That's why I said
    as well as many others

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    Yeah, sorry I missed that.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •