Re: Need help: PL/SQL and Cursors

You can't use bind variables for names of Oracle objects, such as tables or columns. You need to use dynamic SQL for this (production in Oracle 7.1 and higher). Check out the DBMS_SQL package.

In article <3iu7nf$7pr_at_pipe3.pipeline.com>, mattesp_at_pipeline.com (Paul Mattes) writes:
|> *,
|> I've got a proc in which I need to dynamically create a table name and pass
|> it into a cursor to select
|> stuff out. When I do, however, I get this thing:
|>
|> Select count(*) from search_prot.search_tab
|> *
|> ERROR at line 12:
|> ORA-06550: line 12, column 23:
|> PLS-00487: Invalid reference to variable 'SEARCH_PROT'
|> ORA-06550: line 12, column 2:
|> PL/SQL: SQL Statement ignored
|> ORA-06550: line 12, column 9:
|> PLS-00320: the declaration of the type of this expression is incomplete or
|> malformed
|> ORA-06550: line 22, column 2:
|> PL/SQL: SQL Statement ignored
|>
|>
|> I've got all the variable declared BEFORE the cursor declaration.
|>
|> Here's what the declaration looks like :
|>
|> CURSOR c2 (search_prot CHAR, search_tab CHAR) is
|> Select count(*) from search_prot.search_tab
|> where status=2 ;
|>
|> and the open is
|>
|> OPEN c2('acct1','xxx').
|>
|> So I'd expect the cursor to resolve the incoming variables and parse the
|> select statement
|> as
|>
|> SELECT COUNT(*) from acct1.xxx
|>
|>
|> Obviously that isn't happening so if anyone has been down this path
|> before.....can you
|> shine your flashlight this way ??
|>
|> Thanks!!
|>
|> --
|> Paul Mattes
|> "Life's too short to drink cheap wine"
Received on Wed Mar 01 1995 - 07:05:23 CET