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 Go to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Error in PL/SQL package function (table of records) [message #671729 is a reply to message #671728] Mon, 17 September 2018 00:23 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear Solomon,

Right you are, AMOUNT instead of MAX(AMOUNT) was my typo.
I also changed the function parameter names as advised.

Thanks a lot
Ferro
Previous Topic: Processed time for a Job SQL
Next Topic: Adding rownum in pagination query in oralce 12c
Goto Forum:
  


Current Time: Thu Mar 28 18:35:11 CDT 2024