Dinamyc Temp Table

From: Diego P <dpafumi_at_gmail.com>
Date: 12 Jul 2005 10:52:35 -0700
Message-ID: <5036c371.0507120952.3e1e5b3f@posting.google.com>

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 Received on Tue Jul 12 2005 - 12:52:35 CDT