OraFAQ Forum: SQL & PL/SQL » Issue with Pipeline table function

Home » SQL & PL/SQL » SQL & PL/SQL » Issue with Pipeline table function (Oracle 11g, Win7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Issue with Pipeline table function [message #560891]

Tue, 17 July 2012 13:10

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: Issue with Pipeline table function [message #560896 is a reply to message #560891]

Tue, 17 July 2012 13:27

John Watson
Messages: 8984
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.

Report message to a moderator

Re: Issue with Pipeline table function [message #560898 is a reply to message #560891]

Tue, 17 July 2012 13:28

matthewmorris68
Messages: 258
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

Report message to a moderator

Re: Issue with Pipeline table function [message #560902 is a reply to message #560898]

Tue, 17 July 2012 13:37

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

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.

Report message to a moderator

Re: Issue with Pipeline table function [message #560917 is a reply to message #560914]

Tue, 17 July 2012 14:51

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Actually, you can simplify FETCH:

Fetch p
  into OUT_REC;

SY.

Report message to a moderator

Re: Issue with Pipeline table function [message #560925 is a reply to message #560917]

Tue, 17 July 2012 15:33

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.

Report message to a moderator

Previous Topic:	Oracle SQL Query - Delete
Next Topic:	What the Open cursor action?.(3 merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 15 16:00:44 CDT 2025