Re: PL/SQL Variables
Date: 1995/10/21
Message-ID: <469na4$c92_at_ixnews4.ix.netcom.com>#1/1
In <463t6a$999_at_spk41.usace.mil> c0imijka_at_mrd61.mrd-wc.usace.army.mil
(James K. Anderson) writes:
>
>I'm trying to use a pl/sql variable in place of a table name in an
update. The code looks something like:
>
>
> PROCEDURE update_multiple_values (
> tssids IN IntArrayTyp,
> err_num OUT INTEGER) IS
> table_name VARCHAR2(18);
> BEGIN
> table_name := 'TIME_SERIES_VALUES';
> UPDATE table_name
> SET ...
>
>When I try to compile the package I get:
>
>LINE/COL ERROR
>--------
>143/7 PL/SQL: SQL Statement ignored
>143/14 PLS-00356: 'TABLE_NAME' must name a table to which the user
has access
>
>Is what I am trying to do legal? It so, any ideas as to what is wrong?
>
>Thanks,
>Jim Anderson
>anderson_at_mrd62.mrd.usace.army.mil
Jim,
Dynamic SQL, such as is available in Pro*C, isn't supported in native PL/SQL. However, you can perform dynamic SQL in a PL/SQL block by using the DBMS_SQL package. The source code for same is located in file $ORACLE_HONE/rdbms/admin/dbmssql.sql. The file is well documented and should provide usage examples.
Rich Woods
Oracle Worldwide Customer Support
Received on Sat Oct 21 1995 - 00:00:00 CET