12 Replies Latest reply: Feb 21, 2013 10:43 AM by SomeoneElse RSS

    PL/SQL Like

    992549
      Can anyone help me whats wrong in this procedure. When im calling the procedure like call UR('0010','%02','King',225) its not updating the values for tuples with HOTELID = hId and roomno having pattern '%02'!!



      CREATE OR REPLACE PROCEDURE UR(hId IN CHAR, rIdPattern IN CHAR, rType IN CHAR, rPrice IN INT) IS
      thId ROOM.HOTELID%TYPE;
      trId ROOM.ROOMNO%TYPE;
      CURSOR c IS
      SELECT HOTELID, ROOMNO FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE rIdPattern
      FOR UPDATE;
      BEGIN
      OPEN c;
      LOOP
      FETCH c INTO thId, trId;
      EXIT WHEN c%NOTFOUND;
      UPDATE ROOM SET TYPE=rType , PRICE = rPrice WHERE HOTELID = thId AND ROOMNO = trId;
      COMMIT;
      END LOOP;
      CLOSE c;
      END;
        • 1. Re: PL/SQL Like
          user503635
          Put COMMIT after END LOOP.
          • 2. Re: PL/SQL Like
            user503635
            LOOP
            FETCH c INTO thId, trId;
            EXIT WHEN c%NOTFOUND;
            UPDATE ROOM SET TYPE=rType , PRICE = rPrice WHERE HOTELID = thId AND ROOMNO = trId;
            END LOOP;
            *bold*COMMIT;*bold*
            Edited by: user503635 on Feb 21, 2013 7:33 AM
            • 3. Re: PL/SQL Like
              sb92075
              989546 wrote:
              Can anyone help me whats wrong in this procedure. When im calling the procedure like call UR('0010','%02','King',225) its not updating the values for tuples with HOTELID = hId and roomno having pattern '%02'!!



              CREATE OR REPLACE PROCEDURE UR(hId IN CHAR, rIdPattern IN CHAR, rType IN CHAR, rPrice IN INT) IS
              thId ROOM.HOTELID%TYPE;
              trId ROOM.ROOMNO%TYPE;
              CURSOR c IS
              SELECT HOTELID, ROOMNO FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE rIdPattern
              FOR UPDATE;
              BEGIN
              OPEN c;
              LOOP
              FETCH c INTO thId, trId;
              EXIT WHEN c%NOTFOUND;
              UPDATE ROOM SET TYPE=rType , PRICE = rPrice WHERE HOTELID = thId AND ROOMNO = trId;
              COMMIT;
              END LOOP;
              CLOSE c;
              END;
              what is result from SQL below?

              SELECT COUNT(*) FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE rIdPattern;
              • 4. Re: PL/SQL Like
                992549
                Nope relocating COMMIT is not working.
                • 5. Re: PL/SQL Like
                  Pritesh Jani
                  Don't pass % as argument. Instead in procedure use '%' with LIKE as below.

                  CURSOR c IS
                  SELECT HOTELID, ROOMNO FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE '%' || rIdPattern
                  • 6. Re: PL/SQL Like
                    992549
                    its returning 0 when i run the query

                    SELECT COUNT(*) FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE rIdPattern;

                    So probably there is some problem with this statement.
                    • 7. Re: PL/SQL Like
                      sb92075
                      989546 wrote:
                      its returning 0 when i run the query

                      SELECT COUNT(*) FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE rIdPattern;

                      So probably there is some problem with this statement.
                      BRILLIANT deduction!
                      • 8. Re: PL/SQL Like
                        Pritesh Jani
                        If you don't take COMMIT out of LOOP, you will get ORA-01002: fetch out of sequence while executing code.
                        • 9. Re: PL/SQL Like
                          992549
                          Is there any other way i can use wildcard?

                          Even

                          SELECT count(*) FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE '%' || rIdPattern

                          is returning 0!
                          • 10. Re: PL/SQL Like
                            Pritesh Jani
                            Post some dummy data from table. You might have column ROOMNO with value like '00A10', in that case if you say '%' || 'A', it won't work.
                            • 11. Re: PL/SQL Like
                              sb92075
                              989546 wrote:
                              Is there any other way i can use wildcard?

                              Even

                              SELECT count(*) FROM ROOM WHERE HOTELID = hId AND ROOMNO LIKE '%' || rIdPattern

                              is returning 0!
                              what proof do you have that the problem only involves the LIKE clause.
                              • 12. Re: PL/SQL Like
                                SomeoneElse
                                Once you've figured out the data problem, discard the cursor and just do a single update statement.

                                No need for a loop at all.