Re: Dinamyc Temp Table

From: nirav <shivam71_at_gmail.com>
Date: 13 Jul 2005 07:40:32 -0700
Message-ID: <1121265632.385058.40510@g14g2000cwa.googlegroups.com>

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 Received on Wed Jul 13 2005 - 09:40:32 CDT