Need help: PL/SQL and Cursors

From: Paul Mattes <mattesp_at_pipeline.com>
Date: 27 Feb 1995 23:10:23 -0500
Message-ID: <3iu7nf$7pr_at_pipe3.pipeline.com>

*,
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 Tue Feb 28 1995 - 05:10:23 CET