Re: Reference an arbitrary number of tables?

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