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: Reference an arbitrary number of tables?

Re: Reference an arbitrary number of tables?

From: Daniel Lisiecki <lisu_at_venus.wmid.amu.edu.pl>
Date: Wed, 29 Sep 1999 09:31:34 GMT
Message-ID: <W%kI3.40719$n4.654533@news.tpnet.pl>


Hi!
To do this you can use INSERT as many times as needed in SQL procedure :-). But better is to use this...
x number
...........
x:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(x,'INSERT INTO FOO_'||table_number||' VALUES (...)',dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(x);

...in a loop clause, that increments table number. Difference is that above instructions can concatenate strings in one sql command. In same way You can use 'CREATE' command in PL/SQL procedure, which is impossible other way.

Daniel Lisiecki
lis9_at_kki.net.pl

Jay <manongjay_at_hotmail.com> wrote in message news:37F1795F.BCE6F613_at_hotmail.com...
> Is the ff. possible?
>
> Suppose we have an arbitrary number of tables:
> create table foo_one (id NUMBER);
> create table foo_two (id NUMBER);
> create table foo_three (id NUMBER);
> ...
> create table foo_N (id NUMBER);
>
> Then suppose we need to insert values into these tables:
> insert into foo_one(id) value(1);
> insert into foo_two(id) value(2);
> insert into foo_three(id) value(3);
> ...
> insert into foo_N(id) value(N);
>
> Is it possible to create a stored procedure that will
> simplify the above insert for N number of tables???
> If so how?
>
> Thanks in advance.
>
>
Received on Wed Sep 29 1999 - 04:31:34 CDT

Original text of this message

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