Dynamic SQL (was: Re: deleted files)

From: Lothar Armbrüster <lothar.armbruester_at_rheingau.netsurf.de>
Date: 17 Nov 98 19:54:49 +0100
Message-ID: <1952.625T2494T11944949@rheingau.netsurf.de>

On 16-Nov-98 20:33:24 Diego Pafumi wrote:

>Hi my friends, I come here again because i have a problem.
>I have to insert a lot of data in different applications. So all the
>data that I need I put in a Table like this: MY_TABLE
>----------------------------------------
>OWNER
>TABLE_NAME
>COLUMN
>DATA_TYPE

>The problem is that I don't know how to create a PL/SQL to take al the
>data from that table and create the insert command. Someone told me to
>try using the DBMS_SQL command, but I don't know where to find it.
>Could anybody help me with this, please??? Thanks a lot and see you
>soon

Hello Diego,

first, you chose a strange subject for your question, so I changed it. (Hope you find this posting nevertheless :-)

Now to your question:
DBMS_SQL is not a single command but a whole package that is used for issuing dynamic SQL to the database.
In your case the code would look like

declare

sql_cur integer;
ignore integer;

begin

   sql_cur := dbms_sql.open_cursor;

   for my_rec in (select * from my_table) loop

      dbms_sql.parse('insert into '||my_rec.owner||'.'||
                     my_rec.table_name||' ('||
                     my_rec.column||') '||
                     'values ('''||my_rec.value||''')',
                     dbms_sql.v7);
      ignore := dbms_sql.execute(sql_cur);

   end loop;

   dbms_sql.close_cursor(sql_cur);
end;

This assumes the data to be inserted is in a column named value. You eventually have to do some magic to handle the different data types. You can find more information in the Oracle Server PL/SQL Reference Manual or the Oracle Server Application Developer Manual (or what the heck they are called ;-)

Also beware! Since I'm writing this at home with no Oracle at hand, the actual Syntax may differ slightly.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |

Received on Tue Nov 17 1998 - 12:54:49 CST