Home » SQL & PL/SQL » SQL & PL/SQL » Issue with Pipeline table function (Oracle 11g, Win7)
Issue with Pipeline table function [message #560891] Tue, 17 July 2012 13:10 Go to next message
mikay1814
Messages: 3
Registered: July 2012
Location: United States
Junior Member
Hi,
I'm trying to use a pipeline function so that I can cast the collection that is returned by the procedure I call as table. The problem is I get this "PLS-00049: Bad bind variable 'P'" error that I'm not sure is even related. I've searched and looked through many examples but can't figure it out. Any help would be appreciated.

I've posted the package below, I'm fairly new to pl/sql so bear with me.

create or replace
Package pkg1     
  IS  

   TYPE OUTREC_TYPE IS RECORD 
(	
         ID VARCHAR2(5)
	,BLD_YR VARCHAR2(4)
	,SP_CD VARCHAR2(5) 
 );
      
 type OUTRECSET is table OF OUTREC_TYPE;

  FUNCTION GET_REC  
  (	param1       IN      VARCHAR
	,param2       IN      VARCHAR
        ,param3       IN      VARCHAR
        ,param4       IN      VARCHAR
        ,param5       IN      VARCHAR 
	   ) 
RETURN OUTRECSET  
PIPELINED
 is     
  OUT_REC OUTREC_TYPE;
  P REFCURSOR;
  IN_REC REFCURSOR;

begin	
   exec SP_BRS_MAINT_AREA('0', '5', '@', '0', '9999',:P); //here is where I'm getting the error
 --exec SP_BRS_MAINT_AREA(param1, param2, param3, param4, param5,:P);	
LOOP
Fetch p into in_rec;
	 EXIT WHEN p%NOTFOUND;
        out_rec.ID := IN_REC.ID;
	   	OUT_REC.BLD_YR := IN_REC.BLD_YR;
	   	OUT_REC.SP_CD := IN_REC.SP_CD; 
	   PIPE ROW(OUT_REC);
     END LOOP;
     return;
   END GET_REC;
end pkg1;

[Updated on: Tue, 17 July 2012 13:17]

Report message to a moderator

Re: Issue with Pipeline table function [message #560893 is a reply to message #560891] Tue, 17 July 2012 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 22808
Registered: January 2009
Senior Member
Since we don't have your tables or data,
we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Issue with Pipeline table function [message #560896 is a reply to message #560891] Tue, 17 July 2012 13:27 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
I think your error message is misleading: the problem could be "exec", which is not a pl/sql command. It is a SQL*Plus command. Are you trying to invoke a procedure? If so, just get rid of the exec.
Re: Issue with Pipeline table function [message #560898 is a reply to message #560891] Tue, 17 July 2012 13:28 Go to previous messageGo to next message
matthewmorris68
Messages: 211
Registered: May 2012
Location: Orlando, FL
Senior Member

The colon-P notation in your variable list indicates that a bind variable P must be declared. I don't know why the code is using a bind variable. If you need one, you can define it prior to running the PL/SQL block.

VAR P PKG1.OUTREC_TYPE

Re: Issue with Pipeline table function [message #560902 is a reply to message #560898] Tue, 17 July 2012 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to John's answer:
PL/SQL User's Guide and Reference
Chapter 8 Using PL/SQL Subprograms
Section Understanding PL/SQL Procedures

Quote:
A procedure is called as a PL/SQL statement. For example, you might call the procedure raise_salary as follows:
raise_salary(emp_id, amount);


Regards
Michel

[Updated on: Tue, 17 July 2012 13:40]

Report message to a moderator

Re: Issue with Pipeline table function [message #560906 is a reply to message #560902] Tue, 17 July 2012 13:50 Go to previous messageGo to next message
mikay1814
Messages: 3
Registered: July 2012
Location: United States
Junior Member
Thanks for the responses

Unfortunately, after I made those changes... it gave me several more completely different errors.
Error(30,11): PLS-00201: identifier 'REFCURSOR' must be declared
Error(30,11): PL/SQL: Item ignored
Error(31,6): PLS-00201: identifier 'REFCURSOR' must be declared
Error(31,6): PL/SQL: Item ignored
Error(35,2): PL/SQL: Statement ignored
Error(35,138): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(38,1): PL/SQL: SQL Statement ignored
Error(38,7): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(39,3): PL/SQL: Statement ignored
Error(39,13): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(40,7): PL/SQL: Statement ignored
Error(40,24): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(41,6): PL/SQL: Statement ignored
Error(41,24): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(42,6): PL/SQL: Statement ignored
Error(42,28): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(43,6): PL/SQL: Statement ignored
Error(43,26): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(44,6): PL/SQL: Statement ignored
Error(44,23): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Warning(1,1): Only first 20 issues are reported

[Updated on: Tue, 17 July 2012 13:51]

Report message to a moderator

Re: Issue with Pipeline table function [message #560914 is a reply to message #560906] Tue, 17 July 2012 14:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
create or replace
Package pkg1     
  IS  

   TYPE OUTREC_TYPE IS RECORD 
(	
         ID VARCHAR2(5)
	,BLD_YR VARCHAR2(4)
	,SP_CD VARCHAR2(5) 
 );
      
 type OUTRECSET is table OF OUTREC_TYPE;

  FUNCTION GET_REC  
  (	param1       IN      VARCHAR
	,param2       IN      VARCHAR
        ,param3       IN      VARCHAR
        ,param4       IN      VARCHAR
        ,param5       IN      VARCHAR 
	   ) 
RETURN OUTRECSET  
PIPELINED
 is     
  OUT_REC OUTREC_TYPE;
  P SYS_REFCURSOR;

begin	
   exec SP_BRS_MAINT_AREA(param1, param2, param3, param4, param5,P);	
LOOP
Fetch p
  into OUT_REC.ID,
       OUT_REC.BLD_YR,
       OUT_REC.SP_CD;
	 EXIT WHEN p%NOTFOUND;
	   PIPE ROW(OUT_REC);
     END LOOP;
     return;
   END GET_REC;
end pkg1;
/


SY.
Re: Issue with Pipeline table function [message #560917 is a reply to message #560914] Tue, 17 July 2012 14:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
Actually, you can simplify FETCH:

Fetch p
  into OUT_REC;


SY.
icon7.gif  Re: Issue with Pipeline table function [message #560925 is a reply to message #560917] Tue, 17 July 2012 15:33 Go to previous message
mikay1814
Messages: 3
Registered: July 2012
Location: United States
Junior Member
Perfect! Thanks!

I'm impressed, that simplifies the function quite a bit for me because I had like 20 other variables I just didn't include in the topic that were a part of that record. Thanks, again.
Previous Topic: Oracle SQL Query - Delete
Next Topic: What the Open cursor action?.(3 merged)
Goto Forum:
  


Current Time: Tue Sep 23 15:43:28 CDT 2014

Total time taken to generate the page: 0.08475 seconds