Re: insert into table , where the table name is a parameter

On Sun, 23 Jun 2002 00:16:56 -0400, C Chang <cschang_at_maxinter.net> wrote:

>Michael Kuznetsov wrote:
>>
>> Hi
>>
>> You can use dbms_sql package to create sql satement at runtime. It
>> looks like:
>>
>> CREATE OR REPLACE PROCEDURE insert_into_table(tab_name IN VARCHAR2) AS
>> c INTEGER;
>> rows INTEGER;
>> BEGIN
>> c := dbms_sql.open_cursor;
>> dbms_sql.parse(c, &#8217;insert into &#8216;||tab_name||&#8217;
>> values <your_values>&#8217;, dbms_sql.native);
>> rows := dbms_sql.execute(c);
>> dbms_sql.close_cursor(c);
>> END;
>>
>> Regards,
>> Michael
>> Brainbench MVP for Oracle Programming
>> http://www.brainbench.com
>>
>> tsachis_at_boi.gov.il (zachi) wrote in message news:<6af31984.0206062226.10454d6c_at_posting.google.com>...
>> > Hi
>> > I want to write a procedure that insert into table which its name is pass
>> > to the procedure as a parameter
>> > i.e
>> > procedute temp(tab varchr2) is
>> > begin
>> > insert into temp values(etc...)
>> > ofcourse that the tab name erresent a table which has the appropiate coloums
>> > please help me
>> > p.s
>> > what is IN parameter
>I have a similar situation needs to use the Table name as a parameter
>rather where strings. From your solution, I have a little problem. IF
>there are multiple insert and it uses the LOOP.. END LOOP to
>accomplish. Will it be too resource costly with you procedure?
>
>C chang

When not using bind variables everything will end up in a hard parse. You should use bind variables, as you can also use bind_arrays in dbms_sql. That would constitute a bulk insert in pl/sql. I might try setting up an example this afternoon.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Jun 23 2002 - 05:44:53 CDT