Re: PL/SQL tablename as variable

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0111281239.70e98db4_at_posting.google.com>...
> 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 --

There is another possibility that I did not think of earlier. Depending on exactly what you want to do you may be able to use a weakly typed reference cursor, which may have been new with 7.3 (possibly 7.2). See the pl/sql manual as it has examples of passing a cursor.

• Mark D Powell --