Home » SQL & PL/SQL » SQL & PL/SQL » PipeLined Function (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: XP Window )
PipeLined Function [message #577162] Wed, 13 February 2013 00:20 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Hi Can we create a Pipelined function in A Package ? I know we can create it standalone function. If yes, can someone give some ex ?

Thanks
Re: PipeLined Function [message #577166 is a reply to message #577162] Wed, 13 February 2013 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, in the same way than
1/ any other function in a package
2/ any other pipelined function
What is your actual problem? Just try it.

Regards
Michel
Re: PipeLined Function [message #577169 is a reply to message #577166] Wed, 13 February 2013 01:07 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Actually while creating its created but while calling in one of the Procedure, its thrwoing the error ORA-00904 Invalid identifier and PLS_00231: Function may not be used in SQL.This is how i am calling the function
FOR I IN (SELECT * FROM TABLE(FN_FETCH_ROW_SQLTAB_1(P_ORD_NO))) 
LOOP
DM_DISP('FN_FETCH_ROW_SQLTAB', P_ORD_NO, I.POL_SYS_ID, I.POL_NO); --to display the value 
END LOOP;
Re: PipeLined Function [message #577170 is a reply to message #577169] Wed, 13 February 2013 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, the WHOLE session including object creation and execution.
Don't forget to INDENT the code.

Regards
Michel

Re: PipeLined Function [message #577171 is a reply to message #577170] Wed, 13 February 2013 01:24 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Micheal, That package contain more than 1000 lines and moreover whole code i cannot paste due to security reasons.
So i have manipulated the code and pasting here.I hope that should be sufficient.
SQL> CREATE OR REPLACE PACKAGE T_TEST AS 
  2  FN_FETCH_ROW_SQLTAB_1
  2  TYPE TAB_SYS_ID IS RECORD(
  3      POL_SYS_ID  NUMBER,
  4      POL_NO      VARCHAR2(60),
  5      SEC_CODE    VARCHAR2(30),
  6      SEC_SYS_ID  NUMBER,
  7      RISK_ID     VARCHAR2(10),
  8      RISK_SYS_ID NUMBER);
  9  
 10    TYPE DM_SYSID IS TABLE OF TAB_SYS_ID;
 11    DM_SYS_ID DM_SYSID;
 12  END;
 13  /
 
Package created


Body is below
SQL> CREATE OR REPLACE PACKAGE BODY T_TEST AS 
  2  
  2  FUNCTION FN_FETCH_ROW_SQLTAB1(P_ORD_NO NUMBER)
  3      RETURN PGIPK_DM_PROCESS.DM_SYSID as
  4      T_SYS_ID PGIPK_DM_PROCESS.DM_SYSID := PGIPK_DM_PROCESS.DM_SYSID();
  5    BEGIN
  6  
  7      FOR I IN 1 .. P_ORD_NO LOOP
  8        T_SYS_ID.EXTEND(I);
  9        T_SYS_ID(I) := PGIPK_DM_PROCESS.DM_SYS_ID(I);
 10        --PIPE ROW(T_SYS_ID(I));
 11      END LOOP T_SYS_ID;
 12      RETURN T_SYS_ID;
 13    END;
 14  PROCEDURE PR_TEST IS 
 15  BEGIN 
 16  
 16  FOR I IN (SELECT * FROM TABLE(FN_FETCH_ROW_SQLTAB_1(1)))
 17  LOOP
 18  DM_DISP('FN_FETCH_ROW_SQLTAB', P_ORD_NO, I.POL_SYS_ID, I.POL_NO); --to display the value
 19  END LOOP;
 20  END PR_TEST;
 21  END;
 22  /



Re: PipeLined Function [message #577176 is a reply to message #577171] Wed, 13 February 2013 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FN_FETCH_ROW_SQLTAB1 (second line 2) or FN_FETCH_ROW_SQLTAB_1 (line 16)?

Also DM_DISP does not exist.

Regards
Michel

[Updated on: Wed, 13 February 2013 01:52]

Report message to a moderator

Re: PipeLined Function [message #577180 is a reply to message #577176] Wed, 13 February 2013 02:05 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Change the below code
DM_DISP('FN_FETCH_ROW_SQLTAB', P_ORD_NO, I.POL_SYS_ID, I.POL_NO);


to
dbms_output.put_line('FN_FETCH_ROW_SQLTAB->'|| I.POL_NO);


Even if require make it null..

Use FN_FETCH_ROW_SQLTAB1 function name itself..

[Updated on: Wed, 13 February 2013 02:06]

Report message to a moderator

Re: PipeLined Function [message #577184 is a reply to message #577180] Wed, 13 February 2013 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then there is no pipelined function in your code.

Regards
Michel
Re: PipeLined Function [message #577189 is a reply to message #577184] Wed, 13 February 2013 03:36 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Please check below URL which may help you.

http://www.oracle-developer.net/display.php?id=207
Add PIPELINED and uncomment pipe row(i).

FUNCTION FN_FETCH_ROW_SQLTAB1(P_ORD_NO NUMBER)
3 RETURN PIPELINED PGIPK_DM_PROCESS.DM_SYSID as

[Updated on: Wed, 13 February 2013 04:06]

Report message to a moderator

Re: PipeLined Function [message #577205 is a reply to message #577189] Wed, 13 February 2013 06:44 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Hi thanks figured it out the problem.
Re: PipeLined Function [message #577211 is a reply to message #577205] Wed, 13 February 2013 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So please post the solution.

Regards
Michel
Re: PipeLined Function [message #577310 is a reply to message #577211] Thu, 14 February 2013 03:27 Go to previous message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Smile
There is silly mistake i did.I didn't declare the function in package spec.So when i was writing
SELECT * FROM TABLE(FN_FETCH_ROW_SQLTAB_1)


Oracle was throwing error as Invalid identifier and PLS-00231 It may not SQL.

After declaring it in Spec it worked fine.
Previous Topic: Extracted data is displayed in wrong format
Next Topic: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion
Goto Forum:
  


Current Time: Sun Apr 20 22:45:30 CDT 2014

Total time taken to generate the page: 1.74574 seconds