| PipeLined Function [message #577162] |
Wed, 13 February 2013 00:20  |
|
|
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 #577169 is a reply to message #577166] |
Wed, 13 February 2013 01:07   |
|
|
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 #577171 is a reply to message #577170] |
Wed, 13 February 2013 01:24   |
|
|
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 #577310 is a reply to message #577211] |
Thu, 14 February 2013 03:27  |
|
|
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.
|
|
|
|