SEO

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

On 12 Jul 2005 10:52:35 -0700, dpafumi@gmail.com (Diego P) wrote:

>Any help is appreciated

Redesign your application. In Oracle you don't need temp tables, and
you don't need to have them created on the fly.


--
Sybrand Bakker, Senior Oracle DBA

dpafumi@gmail.com (Diego P) wrote in
news:5036c371.0507120952.3e1e5b3f@posting.google.c om:

> Hi,
> I have to perform a VERY difficult task. I need to create a TEMP table
> dinamically that will load and external file.
No you don't!
Access the data file as an EXTERNAL table.

Hi,

I would like to work on this to see if I can help...I really don't
think the problem is too difficult...just that a few details need to be
worked out. Right now you have the procedure to create the table ,
which is static , and we need a dynamic procedure. DDL statements can
be run dynamically by using EXECUTE IMMEDITE and i have done it several
dozen times..

I started writing the procedure , but what stopped me from going
further was:

HOW do you decide the data type for each field of the table that you
are building?

You said that table definitions are stored in another table:
Desc Table_Definitions
FIELDID number(4),
FIELDNAME varchar2(20),
STARTPOSITION number(3),
FIELDLENGTH number(3),
DATAFORMAT varchar2(12)

But in this table the data type for each column/field is not
mentioned.. so how can you decide it dynamically?- I mean, for example
how do you know dynamically that payee field has data type varchar2
type and not char? - this info should be in the table_definitions table
but it is not there..

You need to have some column in table_definition table for data type of
each column of the table to be created..or some rules by which to
decide the data type (or some accepted assumptions if nothing else)...
Pl. post back if you have this info . and I can try to take this
further...

Here is my partly written procedure which I had to leave as i did not
know how to decide on the data type:

===========================
create procedure t_build_table as

cursor c is select * from Table_Definitions order by fieldid;
v_sql_string varchar2(2000);
v_last_field_number number;
begin
v_sql_string:='CREATE TABLE DIEGO_TEMP (RECORDTYPE NUMBER(1), ';

select count(*) into v_last_field_number from t_table_definitions;

for cc in c loop

if (cc.fieldid < v_last_field_number) then

v_sql_string:= v_sql_string||cc.fieldname || ' '

================

Hope it helps..Do not hesitate to convey your views, I am quite
familiar with this type of problems...

Shreyaas

Diego P wrote:
> 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

Hi,
Assuming that you want to build a dynamic procedure this is what I
would like to say:

I really don't think the problem is too difficult...just that a few
details need to be worked out. Right now you have the procedure to
create the table , which is static , and we need a dynamic procedure.
DDL statements can be run dynamically by using EXECUTE IMMEDITE and i
have done it several dozen times..

I started writing the procedure , but what stopped me from going
further was:

HOW do you decide the data type for each field of the table that you
are building?

You said that table definitions are stored in another table:
Desc Table_Definitions
FIELDID number(4),
FIELDNAME varchar2(20),
STARTPOSITION number(3),
FIELDLENGTH number(3),
DATAFORMAT varchar2(12)

But in this table the data type for each column/field is not
mentioned.. so how can you decide it dynamically?- I mean, for example
how do you know dynamically that payee field has data type varchar2
type and not char? - this info should be in the table_definitions table
but it is not there..

You need to have some column in table_definition table for data type of
each column of the table to be created..or some rules by which to
decide the data type (or some accepted assumptions if nothing else)...
Pl. post back if you have this info . and I can try to take this
further...

Here is my partly written procedure which I had to leave as i did not
know how to decide on the data type:

===========================
create procedure t_build_table as

cursor c is select * from Table_Definitions order by fieldid;
v_sql_string varchar2(2000);
v_last_field_number number;
begin
v_sql_string:='CREATE TABLE DIEGO_TEMP (RECORDTYPE NUMBER(1), ';

select count(*) into v_last_field_number from t_table_definitions;

for cc in c loop

if (cc.fieldid < v_last_field_number) then

v_sql_string:= v_sql_string||cc.fieldname || ' '

================

Hope it helps..Do not hesitate to convey your views, I am quite
familiar with this type of problems...

Shreyaas

On 13 Jul 2005 07:40:32 -0700, "nirav" <shivam71@gmail.com> wrote:

>Hope it helps..Do not hesitate to convey your views, I am quite
>familiar with this type of problems...

My view is
- tables shouldn't be created on the fly ever
- likely the resulting application should be considered an fully
unscalable piece of crap.


--
Sybrand Bakker, Senior Oracle DBA