Issue with comma seperated varchar and Number field in oracle

Question

i have written this stored procedure in Oracle:

CREATE OR REPLACE PROCEDURE TMS.SP_BOOKING_CANCEL_SMPL(P_BOOK_TERMINAL      NUMBER,
                                                       P_BOOK_CODE          NUMBER,
                                                       P_BOOK_NO            VARCHAR2,
                                                       P_CANCELLATION_SEATS VARCHAR2,
                                                       P_CANCEL_QTY         NUMBER,
                                                       P_CANCEL_AMOUNT      NUMBER,
                                                       P_CANCEL_SEAT_QTY    NUMBER,
                                                       P_SEAT_QTY           NUMBER,
                                                       P_UNCANCELLED_ID     VARCHAR2,
                                                       P_UNCANCELLED_QTY    NUMBER,
                                                       P_CANCEL_TERMINAL    NUMBER,
                                                       P_CANCEL_SITE        NUMBER,
                                                       P_CANCEL_SEQ         NUMBER,
                                                       P_CANCEL_TYPE        CHAR,
                                                       P_USER_ID            VARCHAR2,
                                                       P_SYNC               CHAR,
                                                       P_CREATE_IP          VARCHAR2,
                                                       P_CREATE_PC          VARCHAR2)
  IS
    d_sql         VARCHAR2(32767);
    V_CANCEL_CODE NUMBER;
  BEGIN
    d_sql := 'UPDATE TMS_BOOKD SET BOOKD_CANCEL_YN = ''Y'', BOOKD_CANCEL_DATE = SYSDATE, BOOKD_CANCEL_USER = :UserId, BOOKD_GENDER = NULL
              WHERE BOOKD_TERMINAL = :BookDTerminal AND BOOKD_CODE = :BookDCode AND BOOKD_SEAT in (:cancellationIds)';

    EXECUTE IMMEDIATE d_sql
    USING P_USER_ID, P_BOOK_TERMINAL, P_BOOK_CODE,P_CANCELLATION_SEATS ;
      --;

    IF P_CANCEL_SEAT_QTY = P_SEAT_QTY
    THEN
      d_sql := 'UPDATE TMS_BOOKM SET BOOKM_SET_SEATS = NULL, BOOKM_SET_QTY = NULL, BOOKM_SET_AMOUNT = NULL, BOOKM_CANCEL = 1 WHERE BOOKM_TERMINAL = :BookDTerminal
                AND BOOKM_CODE = :BookM_Code AND BOOKM_BOOKNO = :BookM_No';

      EXECUTE IMMEDIATE d_sql
      USING P_BOOK_TERMINAL, P_BOOK_CODE, P_BOOK_NO;
    ELSE
      d_sql := 'UPDATE TMS_BOOKM SET BOOKM_SET_SEATS = :BOOKM_SET_SEATS, BOOKM_SET_QTY = :BOOK_SET_QTY, BOOKM_CANCEL = 1 WHERE BOOKM_TERMINAL = :BookDTerminal
                AND BOOKM_CODE = :BookM_Code AND BOOKM_BOOKNO = :BookM_No';

      EXECUTE IMMEDIATE d_sql
      USING P_UNCANCELLED_ID, P_UNCANCELLED_QTY, P_BOOK_TERMINAL, P_BOOK_CODE, P_BOOK_NO;
    END IF;
  END;
/

i am executing this stored procedure with parameter 'P_CANCELLATION_SEATS ' with value: '10,12' however it throws exceptions: Ora-01722(invalid number) in first query, the issue is most probably with the IN clause comparing Number type column value with string type value in my parameter. can anyone tell me how can i resolve this issue?

Answer 1

There are few thing which you must keep in mind before running a Proc. First one is whether you need a DYNAMIC SQL . In your case Dynamic SQL is not at all needed. You can directly do it as shown in my code below. Secondly you already identified that you table column BOOKD_SEAT is a NUMBER coulmn and you are trying to compare with a String so its obvious it will through error. You need to pass these values as collection. See below:

You modified code :

--Create a type of Number to hold your input values
CREATE OR REPLACE TYPE var IS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE TMS.SP_BOOKING_CANCEL_SMPL (
   P_BOOK_TERMINAL         NUMBER,
   P_BOOK_CODE             NUMBER,
   P_BOOK_NO               VARCHAR2,
   P_CANCELLATION_SEATS    var, -- Declare the input as  type of NUMBER
   P_CANCEL_QTY            NUMBER,
   P_CANCEL_AMOUNT         NUMBER,
   P_CANCEL_SEAT_QTY       NUMBER,
   P_SEAT_QTY              NUMBER,
   P_UNCANCELLED_ID        VARCHAR2,
   P_UNCANCELLED_QTY       NUMBER,
   P_CANCEL_TERMINAL       NUMBER,
   P_CANCEL_SITE           NUMBER,
   P_CANCEL_SEQ            NUMBER,
   P_CANCEL_TYPE           CHAR,
   P_USER_ID               VARCHAR2,
   P_SYNC                  CHAR,
   P_CREATE_IP             VARCHAR2,
   P_CREATE_PC             VARCHAR2)
IS  
   V_CANCEL_CODE   NUMBER;
BEGIN
   UPDATE TMS_BOOKD
      SET BOOKD_CANCEL_YN = 'Y',
          BOOKD_CANCEL_DATE = SYSDATE,
          BOOKD_CANCEL_USER = P_USER_ID,
          BOOKD_GENDER = NULL
    WHERE     BOOKD_TERMINAL = P_BOOK_TERMINAL
          AND BOOKD_CODE = P_BOOK_CODE
          AND BOOKD_SEAT IN (select column_value from table(P_CANCELLATION_SEATS) );
       -- Note you can also use MEMBER of operator and change query as 
       --BOOKD_SEAT MEMBER OF P_CANCELLATION_SEATS

   IF P_CANCEL_SEAT_QTY = P_SEAT_QTY
   THEN
      UPDATE TMS_BOOKM
         SET BOOKM_SET_SEATS = NULL,
             BOOKM_SET_QTY = NULL,
             BOOKM_SET_AMOUNT = NULL,
             BOOKM_CANCEL = 1
       WHERE     BOOKM_TERMINAL = P_BOOK_TERMINAL
             AND BOOKM_CODE = P_BOOK_CODE
             AND BOOKM_BOOKNO = P_BOOK_NO;
   ELSE
      UPDATE TMS_BOOKM
         SET BOOKM_SET_SEATS = P_UNCANCELLED_ID,
             BOOKM_SET_QTY = P_UNCANCELLED_QTY,
             BOOKM_CANCEL = 1
       WHERE     BOOKM_TERMINAL = P_BOOK_TERMINAL
             AND BOOKM_CODE = P_BOOK_CODE
             AND BOOKM_BOOKNO = P_BOOK_NO;
   END IF;
END;
/

Execution:

DECLARE
   v_var   var := var ();
BEGIN
   v_var.EXTEND (2);

   --Populate all the values which you want to evalued in IN calsue.
   v_var (1) := 1;
   v_var (2) := 2;

   TMS.SP_BOOKING_CANCEL_SMPL (P_BOOK_TERMINAL => <give your value>
                                P_BOOK_CODE   =>  <give your value>
                                P_BOOK_NO     => <give your value>
                                --- pass all the value which you want to be evaluted in IN clause of your query
                                P_CANCELLATION_SEATS  => v_var
                                P_CANCEL_QTY     =>  <give your value>
                               P_CANCEL_AMOUNT    =>  <give your value>
                                P_CANCEL_SEAT_QTY    =>  <give your value>
                                P_SEAT_QTY          =>  <give your value>
                                P_UNCANCELLED_ID     =>  <give your value>
                                P_UNCANCELLED_QTY    => <give your value>
                                P_CANCEL_TERMINAL    => <give your value>
                                P_CANCEL_SITE      =>  <give your value>
                                P_CANCEL_SEQ      =>  <give your value>
                                P_CANCEL_TYPE     =>  <give your value>
                                P_USER_ID     =>  <give your value>
                                P_SYNC        =>  <give your value>
                                P_CREATE_IP    =>  <give your value>
                                P_CREATE_PC     => <give your value>      )

end;

Answer 2

The parameter P_CANCELLATION_SEAT is a list of comma separated values that you want to pass inside the IN clause, the way you are trying to achieve is technically wrong, you have to break the comma separated valued into list of values before using it in the IN clause.

d_sql := 'UPDATE TMS_BOOKD SET BOOKD_CANCEL_YN = ''Y'', BOOKD_CANCEL_DATE = SYSDATE, BOOKD_CANCEL_USER = :UserId, BOOKD_GENDER = NULL
                  WHERE BOOKD_TERMINAL = :BookDTerminal AND BOOKD_CODE = :BookDCode AND BOOKD_SEAT in (SELECT to_number(regexp_substr(vlist, ''[^,]+'', 1, LEVEL))
                                                                                                         FROM (SELECT :cancellationIds AS vlist FROM dual)
                                                                                                       CONNECT BY regexp_substr(vlist, ''[^,]+'', 1, LEVEL) IS NOT NULL)';

BTW, why are you using dynamic SQL? All your operations can be performed using simple SQL statements.