Home » SQL & PL/SQL » SQL & PL/SQL » Error in PL/SQL package function (table of records) (11.1.2.10)
Error in PL/SQL package function (table of records) [message #671727] |
Sun, 16 September 2018 06:21 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear All,
I am trying to create a package that has a function that returns a table of records. I fail for 1 "PL/SQL: ORA-00913: too many values" and need help with that:
CREATE TABLE MYTEST
( AR_SERIAL NUMBER(10),
ITEM_SERIAL NUMBER(6),
C_CODE NUMBER(3),
C_NAME VARCHAR2(4),
AMOUNT NUMBER
);
CREATE OR REPLACE PACKAGE CC AS
TYPE REC_TEST IS RECORD
(
AR_SERIAL NUMBER(10),
ITEM_SERIAL NUMBER(6),
C_CODE NUMBER(3),
C_NAME VARCHAR2(4),
AMOUNT NUMBER
);
TYPE TBL_TEST IS
TABLE OF REC_TEST;
FUNCTION F_TEST (AR_SERIAL IN NUMBER, ITEM_SERIAL IN NUMBER, C_CODE IN NUMBER)
RETURN TBL_TEST;
END CC;
CREATE OR REPLACE PACKAGE BODY CC AS
FUNCTION F_TEST (AR_SERIAL IN NUMBER, ITEM_SERIAL IN NUMBER, CUR_CODE IN NUMBER)
RETURN TBL_TEST AS
TEST CC.TBL_TEST; -- TABLE OF AR_WITHDRAWAL RECORD
/*
*/
BEGIN
SELECT CC.REC_TEST(
AR_SERIAL,
ITEM_SERIAL,
C_CODE,
C_NAME,
AMOUNT
)
BULK COLLECT INTO TEST
FROM
(
--
SELECT AR_SERIAL,
ITEM_SERIAL,
C_CODE,
C_NAME,
AMOUNT
FROM MYTEST
WHERE
AR_SERIAL = AR_SERIAL
AND ITEM_SERIAL = ITEM_SERIAL
AND CUR_CODE = CUR_CODE
GROUP BY AR_SERIAL,ITEM_SERIAL, C_CODE, C_NAME
);
RETURN(TEST);
END F_TEST;
END CC;
and my final target is:
select * from table (F_TEST (1,2,3));
Thanks,
[Updated on: Sun, 16 September 2018 06:23] Report message to a moderator
|
|
|
Re: Error in PL/SQL package function (table of records) [message #671728 is a reply to message #671727] |
Sun, 16 September 2018 07:28 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
In order to return table of records to SQL package function must be pipelined. And you completely misunderstand records. Also, you group by AR_SERIAL,ITEM_SERIAL, C_CODE, C_NAME but also select AMOUNT which will obviously fail since it is not group by expression. I'll assume MAX(AMOUNT). And you named function parameters same as column names which creates ambiguity and rule is column name prevails, so you are comparing column to itself, not to parameter as, I assume, you intended. I'll fix all but parameter names - you'll have to figure that one out yourself:
CREATE OR REPLACE PACKAGE CC AS
TYPE REC_TEST IS RECORD
(
AR_SERIAL NUMBER(10),
ITEM_SERIAL NUMBER(6),
C_CODE NUMBER(3),
C_NAME VARCHAR2(4),
AMOUNT NUMBER
);
TYPE TBL_TEST IS
TABLE OF REC_TEST;
FUNCTION F_TEST (AR_SERIAL IN NUMBER, ITEM_SERIAL IN NUMBER, C_CODE IN NUMBER)
RETURN TBL_TEST
PIPELINED;
END CC;
/
CREATE OR REPLACE PACKAGE BODY CC AS
FUNCTION F_TEST (AR_SERIAL IN NUMBER, ITEM_SERIAL IN NUMBER, C_CODE IN NUMBER)
RETURN TBL_TEST
PIPELINED
AS
BEGIN
FOR V_REC IN (
SELECT AR_SERIAL,
ITEM_SERIAL,
C_CODE,
C_NAME,
MAX(AMOUNT)
FROM MYTEST
WHERE
AR_SERIAL = AR_SERIAL
AND ITEM_SERIAL = ITEM_SERIAL
AND C_CODE = C_CODE
GROUP BY AR_SERIAL,ITEM_SERIAL, C_CODE, C_NAME) LOOP
PIPE ROW(V_REC);
END LOOP;
END F_TEST;
END CC;
/
Now:
SQL> insert into mytest values(1,2,3,'XYZ',99)
2 /
1 row created.
SQL> select * from table(CC.F_TEST(1,2,3))
2 /
AR_SERIAL ITEM_SERIAL C_CODE C_NA AMOUNT
---------- ----------- ---------- ---- ----------
1 2 3 XYZ 99
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 12:40:02 CDT 2024
|