DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Crete temp table dinamycally

  1. #1
    Join Date
    Dec 1999
    Location
    San Ramon, CA, USA
    Posts
    21

    Crete temp table dinamycally

    Hi,
    I have to perform a VERY difficult task. I need to create a TEMP table dinamically that will load and external file. The definition of that TEMP table is loaded in another table. So fo example:

    Desc Table_Definitions
    FIELDID number(4),
    FIELDNAME varchar2(20),
    STARTPOSITION number(3),
    FIELDLENGTH number(3),
    DATAFORMAT varchar2(12)

    Data on Table_Definitions table:
    FIELDNAME STARTPOSITION FIELDLENGTH DATAFORMAT
    AccountNumber 1 16
    SerialNumber 17 10
    Amount 27 10 0.00
    CheckDate 37 6 ##/##/##
    Payee 44 35
    IssueRecordID 79 9

    So What I need to do is to create a procedure that creates dinamically a TEMP table based on the data of "Table_Definitions" table.
    So far I'm doing this but not dinamically, like:
    CREATE TABLE DIEGO_TEMP (
    RECORDTYPE NUMBER(1),
    ACCOUNTNUMBER VARCHAR2(12),
    SERIALNUMBER VARCHAR2(10),
    CHECKDATE DATE,
    AMOUNT NUMBER(15,2),
    Payee VARCHAR2(10)
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DIR1
    ACCESS PARAMETERS
    (
    RECORDS DELIMITED BY NEWLINE
    badfile DIR1:'DIEGO.bad'
    discardfile DIR1:'DIEGO.dis'
    logfile DIR1:'DIEGO.log'
    FIELDS
    MISSING FIELD VALUES ARE NULL
    (
    RECORDTYPE position(1) char,
    ACCOUNTNUMBER position(5:16) char,
    SERIALNUMBER position(17:26) char,
    CHECKDATE position(27:34) date(8) "yyyymmdd",
    AMOUNT position(35:47) char,
    Payee position(48:57) char
    )
    ) LOCATION ('BAC.B01.L01BTX.D050128.S00062.T0356.FRD.PIF')
    ) REJECT LIMIT UNLIMITED NOPARALLEL;

    Any help is appreciated

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What problem are you having in achieving this?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width