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?

'10,12' is not a valid number in SQL. 10.12 would be valid decimal number — a_horse_with_no_name
I guess you got OP wrong. He know '10,12' is not a valid number. What he asking is if he can pass it like this and use it in in IN clause — XING
@XING - horse can clarify for himself but my impression was that he was explaining the reason for error to the OP. — William Robertson

XING XING · Accepted Answer · 2017-11-10T08:23:45

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;

Issue with comma seperated varchar and Number field in oracle

2 Answers