Re: Forms 4.5 Create table from Block

From: Yann Chevriaux <chevriaux_at_theleme.com>
Date: Thu, 30 Sep 1999 17:01:33 +0200
Message-ID: <37F37B4D.DE86EC5B_at_theleme.com>

Hi.

You should use dynamic_sql and block and item properties.

Do something like that (I didn't implement it, so it's just a suggestion):

  firstItem := Get_Block_Property('BLOCK_EXTRACT', FIRST_ITEM);   lastItem := Get_Block_Property('BLOCK_EXTRACT', LAST_ITEM);   currentItem := firstItem;

  itemType := Get_Item_Property('block_extract.'||currentItem, DATA_TYPE);   itemLength := Get_Item_Propety('block_extract.'||currentItem, MAX_LENGTH);

  itemRequired := Get_Item_Propety('block_extract.'||currentItem, REQUIRED);

• Construct a statemement creating your table with just the first column
• and depending on itemType, itemLength and itemRequired (NULL or NOT NULL)

loop
  itemType := Get_Item_Property('block_extract.'||currentItem, DATA_TYPE);   itemLength := Get_Item_Propety('block_extract.'||currentItem, MAX_LENGTH);

  itemRequired := Get_Item_Propety('block_extract.'||currentItem, REQUIRED);

• Construct new column statement depending on itemType, itemLength and itemRequired
• and use dynamic_sql : 'alter table add ( column type [not] null)' exit when currentItem = lastItem currentItem := Get_Block_Property('BLOCK_EXTRACT', NEXTITEM); -- NEXTITEM with no space ! end loop;

I create each column separately because statement as any varchar2 can't exced 2000 characters (that's really few for 230 columns !).

I you implemente this, please post me the code ! I'm sure I'll need it some day ...

Yann.

Fraser Boswell a écrit :

> What I meant was to create a table with the specification taken from all
> the fields in the block. Not just create any table.
> I have a block with no base table, with 230 fields, and I need to create
> a table that matches this. i.e. cycles through each item in the block,
> takes the information in each field and returns
>
> create table extract
> (field1 number(5),
> etc etc
> )
>
> Michael Mehling wrote:
>
> > Use DYNAMIC SQL
> > Yours
> > Michael Mehling
> > Fraser Boswell <fraser.boswell_at_ed.ac.uk> schrieb in im Newsbeitrag:
> > 37F34804.A6ABE304_at_ed.ac.uk...
> > >
Received on Thu Sep 30 1999 - 17:01:33 CEST