Re: Problem: PL/SQL procedure that inserts into 1 of 4 tables

Frank Tropschuh wrote:
>
> Consider the problem of storing data of variable type -- one table for
> integers, one for floats, one for strings, and one for dates. Each row
> in those tables has a key associated with it that refers to a table that
> stores the type of each datum:
>
> META key value_name stored_in_table
> 1 a core_integer
> 2 b core_string
> 3 c core_integer
> etc.
>
> CORE_INTEGER key timestamp value
> 1 x/x/x 10
> 1 y/y/y 20
> 3 z/z/z 30
>
> CORE_STRING key timestamp value
> 2 x/x/x 'foobar'
>
> Now, I'd like to write a function that accepts two parameters, a
> value_name and the value itself, and stores that data in the appropriate
> table.
>
> Right now, I built this as a if-then-elsif block that looks rather ugly:
>
> select * into METAREC from META where value_name = param_value_name
>
> if METAREC.storedin = 'core_integer' then
> insert into core_integer ...
> elsif METAREC.storedin = 'core_float' then
> insert into core_float ...
> etc.
> you get the picture...
>
> The problem is also that I should be able to create new META keys and
> store their data in arbitrary tables, which are only common in their
> field names, without having to change the procedure code.
>
> Is there any way to dynamically create and run a PL/SQL block?
>
> Also note that these tables may grow to the order of 300-500MB (yes,
> that's megabyte)...
>
> Thanks,
> trf
>
> =================================================================
> Union Bank of Switzerland | Email: Frank.Tropschuh_at_ubs.ch
> OE EDSZ/EZIU/EPPD TRF VC419 | Tel: +41-1-236-9823
> Performance Management | Where: Flurstrasse 65, Geb. VF
> Decentral Systems | CH-8048 Zurich
> =================================================================

Try dynamic SQL (dbms_sql package).

-- 
Regards. Alexey Neckrasow. Leaves Inc. Russia.
e-mail: nec_at_leaves.spb.su