0

I am trying to use BULK COLLECT INTO but I get an ORA-00933 SQL command not properly ended. I'm looked at my syntax but not sure what I am doing wrong. Am I doing something wrong?

The issue seems to be with the RETURNING clause. If I remove the returning clause, it compiles just fine

CREATE OR REPLACE PROCEDURE CIMS.QC_PALLET_HOLD_BY_HOUR_A_REL( QC_HOLD_ID_IN IN INTEGER, HOUR_STR IN VARCHAR2, DAY_CODE IN VARCHAR2, TOP_CODE_IN IN VARCHAR2, QC_RLS_DISPOSITION_ID_IN INTEGER, SUCC_PALS_OUT OUT VARCHAR2)
IS
    l_count binary_integer;
    l_array dbms_utility.lname_array;
    curr_prod_hour varchar2(1);     
    TYPE success_hours is TABLE of pallet_hold.pallet_no%type;
    TYPE t_pallet_ids is TABLE of pallet_hold.pallet_hold_id%type;

BEGIN

    dbms_utility.comma_to_table(
        list => regexp_replace(HOUR_STR, '(^|,)','\1x'),
        tablen => l_count,
        tab => l_array
    );

        BEGIN
            forall i in l_array.FIRST .. l_array.last
            INSERT INTO PALLET_HOLD(PALLET_NO, TOP_CODE, BOTTOM_CODE, QC_HOLD_ID)
            SELECT V.PALLET_NO, V.TOP_CODE, V.BOTTOM_CODE, QC_HOLD_ID_IN
            FROM PALLET_MASTER_INQ_VIEW V
            WHERE PROD_HOUR = substr(l_array(i),2) AND SUBSTR(BOTTOM_CODE,0,5) = DAY_CODE AND TOP_CODE = TOP_CODE_IN
            AND NOT EXISTS (SELECT 1 FROM PALLET_HOLD WHERE QC_HOLD_ID = QC_HOLD_ID_IN AND PALLET_NO = V.PALLET_NO)
            RETURNING PALLET_HOLD_ID bulk collect INTO t_pallet_ids;

        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;              

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN 
         RAISE; --raise_application_error(-20333, 'Error QC_PALLET_HOLD_BY_HOUR');

END;
/
elesh.j
  • 192
  • 1
  • 3
  • 15
  • Exception when others then null: love it!!!. Can you make an output there of the exception fired, just to check if something went wrong? – Renato Afonso May 21 '18 at 16:18
  • Ya sorry. I can add an output, but the problem is I'm unable to even compile this procedure, so I'm unable to run it – elesh.j May 21 '18 at 16:29
  • "RETURNING PALLET_HOLD_ID bulk collect INTO t_pallet_ids" ..... this seems like an unwanted line after a INSERT INTO ..... SELECT .... .Clause – shrek May 21 '18 at 16:50
  • I am attemping to get the pallet_hold_id column values that were generated by a sequence trigger. – elesh.j May 21 '18 at 17:11
  • Possible duplicate of [PLSQL Insert into with subquery and returning clause (Oracle)](https://stackoverflow.com/questions/5325033/plsql-insert-into-with-subquery-and-returning-clause-oracle) – Zynon Putney II May 21 '18 at 17:22

1 Answers1

0

It looks like you can't use the returning statement the way you're attempting to. The answer to this question will give you options on how to accomplish your task.

PLSQL Insert into with subquery and returning clause

Zynon Putney II
  • 675
  • 4
  • 12