oracle-databasefunctionbulk-collect

ORA-00947 not enough values with function returning table of records


So I'm trying to build a function that returns the records of items that are included in some client subscription.

So I've been building up the following:

2 types:

CREATE OR REPLACE TYPE PGM_ROW AS OBJECT
    (
        pID      NUMBER(10),
        pName    VARCHAR2(300)
    );

CREATE OR REPLACE TYPE PGM_TAB AS TABLE OF PGM_ROW;

1 function:

CREATE OR REPLACE FUNCTION FLOGIN (USER_ID NUMBER) RETURN PGM_TAB
AS  
    SELECTED_PGM   PGM_TAB;
BEGIN
    FOR RESTRICTION
        IN (  SELECT (SELECT LISTAGG (ID_CHANNEL, ',')
                                 WITHIN GROUP (ORDER BY ID_CHANNEL)
                        FROM (SELECT DISTINCT CHA2.ID_CHANNEL
                                FROM CHANNELS_ACCESSES CHA2
                                     JOIN CHANNELS CH2
                                         ON CH2.ID = CHA2.ID_CHANNEL
                               WHERE     CHA2.ID_ACCESS = CMPA.ID_ACCESS
                                     AND CH2.ID_CHANNELS_GROUP = CG.ID))
                         AS channels,
                     (SELECT LISTAGG (ID_SUBGENRE, ',')
                                 WITHIN GROUP (ORDER BY ID_SUBGENRE)
                        FROM (SELECT DISTINCT SGA2.ID_SUBGENRE
                                FROM SUBGENRES_ACCESSES SGA2
                                     JOIN CHANNELS_ACCESSES CHA2
                                         ON CHA2.ID_ACCESS = SGA2.ID_ACCESS
                                     JOIN CHANNELS CH2
                                         ON CH2.ID = CHA2.ID_CHANNEL
                               WHERE     SGA2.ID_ACCESS = CMPA.ID_ACCESS
                                     AND CH2.ID_CHANNELS_GROUP = CG.ID))
                         AS subgenres,
                     CG.NAME,
                     A.BEGIN_DATE,
                     A.END_DATE,
                     CMP.PREVIEW_ACCESS
                FROM USERS U
                     JOIN COMPANIES_ACCESSES CMPA
                         ON U.ID_COMPANY = CMPA.ID_COMPANY
                     JOIN COMPANIES CMP ON CMP.ID = CMPA.ID_COMPANY
                     JOIN ACCESSES A ON A.ID = CMPA.ID_ACCESS
                     JOIN CHANNELS_ACCESSES CHA
                         ON CHA.ID_ACCESS = CMPA.ID_ACCESS
                     JOIN SUBGENRES_ACCESSES SGA
                         ON SGA.ID_ACCESS = CMPA.ID_ACCESS
                     JOIN CHANNELS CH ON CH.ID = CHA.ID_CHANNEL
                     JOIN CHANNELS_GROUPS CG ON CG.ID = CH.ID_CHANNELS_GROUP
               WHERE U.ID = USER_ID
            GROUP BY CG.NAME,
                     A.BEGIN_DATE,
                     A.END_DATE,
                     CMPA.ID_ACCESS,
                     CG.ID,
                     CMP.PREVIEW_ACCESS)
    LOOP
        SELECT PFT.ID_PROGRAM, PFT.LOCAL_TITLE
          BULK COLLECT INTO SELECTED_PGM
          FROM PROGRAMS_FT PFT
         WHERE     PFT.ID_CHANNEL IN
                       (    SELECT TO_NUMBER (
                                       REGEXP_SUBSTR (RESTRICTION.CHANNELS,
                                                      '[^,]+',
                                                      1,
                                                      ROWNUM))
                              FROM DUAL
                        CONNECT BY LEVEL <=
                                   TO_NUMBER (
                                       REGEXP_COUNT (RESTRICTION.CHANNELS,
                                                     '[^,]+')))
               AND PFT.ID_SUBGENRE IN
                       (    SELECT TO_NUMBER (
                                       REGEXP_SUBSTR (RESTRICTION.SUBGENRES,
                                                      '[^,]+',
                                                      1,
                                                      ROWNUM))
                              FROM DUAL
                        CONNECT BY LEVEL <=
                                   TO_NUMBER (
                                       REGEXP_COUNT (RESTRICTION.SUBGENRES,
                                                     '[^,]+')))
               AND (PFT.LAUNCH_DATE BETWEEN RESTRICTION.BEGIN_DATE
                                        AND RESTRICTION.END_DATE);
    END LOOP;
    RETURN SELECTED_PGM;
END FLOGIN;

I expect the function tu return a table with 2 columns containing all the records from table PROGRAMS_FT that are included in the user access.

For some reason, I'm getting compilation warning ORA-000947. My understanding of the error code is that it occurs when the values inserted does not match the type of the object receiving the values, and I can't see how this can be the case here.


Solution

  • You're selecting two scalar values and trying to put them into an object. That doesn't happen automatically, you need to convert them to an object:

    ...
        LOOP
            SELECT PGM_ROW(PFT.ID_PROGRAM, PFT.LOCAL_TITLE)
              BULK COLLECT INTO SELECTED_PGM
              FROM PROGRAMS_FT PFT
    ...
    

    (It's an unhelpful quirk of PL/SQL that it says 'not enough values' rather than 'too many values', as you might expect when you try to put two things into one; I'm sure I came up with a fairly convincing explanation/excuse for that once but it escapes me at the moment...)

    I'm not sure your loop makes sense though. Assuming your cursor query returns multiple rows, each time around the loop you're replacing the contents of the SELECTED_PGM collection - you might think you are appending to it, but that's not how it works. So you will end up returning a collection based only on the final iteration of the loop.

    Aggregating and then splitting the data seems like a lot of work too. You could maybe use collections for those; but you can probably get rid of the cursor and loop and combine the cursor query with the inner query, which would be more efficient and would allow you to do a single bulk-collect for all the combined data.