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
