PL/SQL: ORA-00907 : Error in Bulk collect

Question

Is there a way that we use the distinct & alias (as), to achieve the bulk collect query as below.

SELECT  distinct OBJ_TEST  ( EMP_ID as E1, EMP_NAME)
BULK COLLECT INTO LVOB_TEST
FROM TMP_EMP ;

It is an object type, The bulk collect statement is a part of a pipelined function. `CREATE OR REPLACE TYPE SBGMCS.OBJ_TEST AS OBJECT ( EMP_ID NUMBER(10), EMP_NAME VARCHAR2(100) ) ;` — user3359124, Mar 02 '18 at 22:19
So what is `E1`; what is the structure of `TMP_EMP`, and how is `LVOb_TEST` defined? Some context would be helpful... — Alex Poole, Mar 02 '18 at 23:31
You are returning a set of `obj_test` objects, so if you really want to name that (though you don't have to), it would be something like `select distinct obj_test(emp_id,emp_name) as person`. — William Robertson, Mar 03 '18 at 11:39

score 2 · Accepted Answer · answered Mar 03 '18 at 05:21

Is there a way that we use the distinct & alias (as), to achieve the bulk collect query as below.

Not sure why you want to alias since it doesnot make sense to take alias while bulk collect and use it. You will always have to use the column names of the Object which you defined. See below and read my inline comments:

    CREATE OR REPLACE TYPE OBJ_TEST AS OBJECT ( EMP_ID NUMBER(10), EMP_NAME VARCHAR2(100) ) ;

    /
    CREATE OR REPLACE TYPE V_OBJ_TYP IS TABLE OF OBJ_TEST;
    /

    CREATE TABLE TMP_EMP  ( EMP_ID NUMBER(10), EMP_NAME VARCHAR2(100) );
    /

    INSERT INTO TMP_EMP  VALUES (1,        'XXX');
    INSERT INTO TMP_EMP  VALUES (2,        'HHH');
    INSERT INTO TMP_EMP  VALUES (3,        'ZZZ');
    INSERT INTO TMP_EMP  VALUES (4,        'YYY');
    /
    COMMIT;
    /

    DECLARE
         LVOB_TEST                     V_OBJ_TYP;
    BEGIN
        --See below how you can use the alias but it doesnot make sense 
        --since you cannot use the alias name while displaying the result using alias name.
         SELECT OBJ_TEST (EP_ID, E_NAME)
         BULK COLLECT INTO LVOB_TEST
           FROM (SELECT DISTINCT EMP_ID AS EP_ID, 
                                 EMP_NAME AS E_NAME
                            FROM TMP_EMP);

         DBMS_OUTPUT.PUT_LINE ('EMP_ID'||'--' ||'EMP_NAME');

         FOR I IN 1 .. LVOB_TEST.COUNT
         LOOP   
          --Here am displaying the result of the query. But i cannot use alias since i need can only refer the name of the columns defined in the Object. i.e. EMP_ID & EMP_NAME. 
          --IF you want to use that alias then create the object with that alias name.      
              DBMS_OUTPUT.PUT_LINE (LVOB_TEST (I).EMP_ID ||'--' ||LVOB_TEST (I).EMP_NAME );
         END LOOP;
    END;

Output:

SQL> /
EMP_ID--EMP_NAME
4--YYY
2--HHH
3--ZZZ
1--XXX

PL/SQL procedure successfully completed.

score 0 · Answer 2 · answered Mar 03 '18 at 05:11

0

You are very light on detail, but at a guess, you want:

SELECT  distinct OBJ_TEST  ( EMP_ID , EMP_NAME)
BULK COLLECT INTO LVOB_TEST
from
 ( select distinct emp_id, emp_name from tmp_emp )

answered Mar 03 '18 at 05:11

Connor McDonald

10,418
1
11
16

PL/SQL: ORA-00907 : Error in Bulk collect

2 Answers2