Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problem: PL/SQL procedure that inserts into 1 of 4 tables

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

From: Graham Miller <lgmiller_at_elmrd.u-net.com>
Date: 1997/03/29
Message-ID: <334045ea.3971461@news.u-net.com>#1/1

Hello,
  Another approach is to store the different data types in one (or more) common formats and an indicator column (or two) as to which type they are and convert accordingly. All in the one table of course.

e.g
Create table All_Values (
META_Type_Id number(7) not null,
Numeric_Value number,
String_Value varchar2(255)
);

Create table Meta_Types (
META_Type_Name varchar2(30) not null,
META_Type_Id number(7) not null,
Value_Type char(1) not null, /* string or number */ Value_Format
...
etc.
);

The disadvantage is the conversion to the appropriate type at run-time. Maintenance and expansion is fairly straightforward. This is the approach i would use.

I would use your approach if run-time efficiency was important.

Dynamic SQL will answer a lot of your needs but will increase the maintenance overhead.

I am not criticising your approach. I don't know the full details of the problem.

These comments are just that.

Graham (aka Grumpy)

Frank Tropschuh <Frank.Tropschuh_at_ubs.ch>, thoughtfully produced:

>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
>=================================================================

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost. Received on Sat Mar 29 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US