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 ;
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.