Re: ?Table_name as a procedure parameter

You need to use dynamic sql, either by means of the dbms_sql package (Oracle 7, 8.0) or native dynamic sql in Oracle 8i. As you don't specify which version you are using, my answer must be necessarily this vague.

Hth,

Sybrand Bakker, Oracle DBA

<WiltonD_at_state.mi.us> wrote in message news:8ooavc$gb1$1_at_nnrp1.deja.com...
> Hi, does anyone know how to accept table and column names as
> arguements. I created a view from user_tab_columns so I can see the
> table (parameter) in the procedure but I can't update or insert because
> Oracle can't see the table.
> The code looks something like this so far!
> CURSOR MYCURSOR IS
> SELECT p_column_name, p_table_name| |'_REC' --primary key for all tables
> FROM my_view
> WHERE table_name = p_table_name
> AND column_name = p_column_name;
>
> FOR record in cursor LOOP
> pl/sql code to change all records
>
> (Here's the problem)
> UPDATE p_table_name set p_column_name = v_new_column
> WHERE p_table_name| |'_REC' = record.p_table_name| |'_REC';
>
> ERROR p_table_name must be declared!
>
> Any help?
> Thanks
> Dave
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Sep 01 2000 - 17:16:29 CDT