Re: PL/SQL tablename as variable

thomas.hiller_at_warema.de (Thomas) wrote in message news:<6e366956.0111280727.4123d35c_at_posting.google.com>...
> hello!
>
> the following statement compilation fails within a stored procedure in
> a package (Oracle Version 7.3.4.1):
>
> insert into table_name (select * from table_name_2);
>
> table_name and table_name_2 are variables. Oracle does not accept the
> statement with the following error message:
>
> PLS-00356: 'TABLE_NAME' must name a table to which the user has access
>
> how can I fix the problem. i do need the dynamic way of assigning a
> value to table_name!
>
> regards
>
> thomas

You will need to use the dbms_sql package. Write a procedure that accepts the table_name as a variable and concatenate the variable to a string that makes up your sql command, parse this, and then execute:

create
procedure TRUNCATE_TABLE
  (
   table_name varchar2,
   storage_type varchar2
  )
as
-- crsr integer;
rval integer;
begin
crsor := dbms_sql.open_cursor ;
dbms_sql.parse(crsr,'truncate table '|| table_name ||

               ' '|| storage_type , dbms_sql.v7) ; rval := dbms_sql.execute(crsr) ;
dbms_sql.close_cursor(crsr) ;
end ;

• Mark D Powell --