Home » SQL & PL/SQL » SQL & PL/SQL » getting random results from a procedure (oracle 10g)
getting random results from a procedure [message #313703] Mon, 14 April 2008 04:33 Go to next message
bluerratiq
Messages: 22
Registered: March 2008
Location: Bucharest
Junior Member
so i've thought to create this procedure to update a field in a table
by default STOC=STOC_EXISTENT

CREATE OR REPLACE PROCEDURE BIBLIOTECA.UPDATE_STOC_EXISTENT(P_COD_CARTE IN NUMBER) IS
C NUMBER(10) DEFAULT 0;
V_COD_CARTE NUMBER(10);
V_STOC NUMBER(10);
V_STOC_EXISTENT NUMBER(10);
V_ADUSA_LA DATE;

CURSOR U_S_E_CURSOR IS
SELECT CI.COD_CARTE, STOC, STOC_EXISTENT, ADUSA_LA FROM CARTI_INCHIRIATE CI, CARTI C
WHERE CI.COD_CARTE=C.COD_CARTE;

BEGIN
OPEN U_S_E_CURSOR;
    LOOP
    FETCH U_S_E_CURSOR INTO V_COD_CARTE, V_STOC, V_STOC_EXISTENT, V_ADUSA_LA;
    IF V_COD_CARTE=P_COD_CARTE AND V_ADUSA_LA IS NULL THEN C:=C+1;
    END IF;
    EXIT WHEN U_S_E_CURSOR%NOTFOUND;
    END LOOP;
    CLOSE U_S_E_CURSOR;
    dbms_output.put_line('C:'||C);
    UPDATE CARTI SET STOC_EXISTENT=STOC-C
    WHERE COD_CARTE=P_COD_CARTE;
    COMMIT; 
END;


var C counts how many times a line appears in CARTI_INCHIRIATE table with the matching parameter ...

although I am having just one line in the CARTI_INCHIRIATE table with the matching parameter ... the value of C is 2 and the update will not be correct - if STOC=6 then STOC_EXISTENT will be updated to 4, which is wrong


anyone can figure out what am I doing wrong?
I must say that this is happening randomly ... since I've already got some good results .. its quite annoying because I cant figure out the cause of this problem

thanks a lot!

[Updated on: Mon, 14 April 2008 04:34]

Report message to a moderator

Re: getting random results from a procedure [message #313711 is a reply to message #313703] Mon, 14 April 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the EXIT statement before IF one.

Regards
Michel
Re: getting random results from a procedure [message #313714 is a reply to message #313711] Mon, 14 April 2008 05:02 Go to previous messageGo to next message
bluerratiq
Messages: 22
Registered: March 2008
Location: Bucharest
Junior Member
It seems that is working this way.
Anyway I can't figure out why it didn't work with the initial code, the increasing of C was done only if that condition was met.

Thanks Michel for the tip.
Cheers Smile
Re: getting random results from a procedure [message #313731 is a reply to message #313714] Mon, 14 April 2008 06:28 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In order to avoid similar problems, try to get used to use a cursor FOR loop; you won't have to declare a cursor variable(s), open the cursor, worry when to exit the loop and close the cursor. Code will be easier to maintain; see an example (also, just to mention: you "forgot" to prefix all columns in the SELECT list with table alias. Although you know which column belongs to which table (at the moment), you (and, especially, someone who will some day maintain your code) will forget it and regret you didn't do that at the time of writing the code):
CREATE OR REPLACE PROCEDURE BIBLIOTECA.UPDATE_STOC_EXISTENT (P_COD_CARTE IN NUMBER) IS
  c NUMBER(10) DEFAULT 0;
BEGIN
  FOR u_s_e_cursor IN (SELECT CI.COD_CARTE, STOC, STOC_EXISTENT, ADUSA_LA 
                       FROM CARTI_INCHIRIATE CI, CARTI C
                       WHERE CI.COD_CARTE = C.COD_CARTE)
  LOOP
    IF u_s_e_cursor.v_cod_carte = P_COD_CARTE AND 
       u_s_e_cursor.v_adusa_la IS NULL 
    THEN
       c := c + 1;
    END IF;
  END LOOP;

  UPDATE CARTI SET
    STOC_EXISTENT = u_s_e_cursor.stoc - c
    WHERE COD_CARTE = P_COD_CARTE;
  COMMIT;
END;
Re: getting random results from a procedure [message #313780 is a reply to message #313731] Mon, 14 April 2008 09:31 Go to previous message
bluerratiq
Messages: 22
Registered: March 2008
Location: Bucharest
Junior Member
thanks littlefoot ... your idea is simple and smart! Smile
great! thanks again
Previous Topic: Displaying records as it appears in the IN
Next Topic: Trigger is not showing expected behaviour
Goto Forum:
  


Current Time: Fri Dec 02 22:39:34 CST 2016

Total time taken to generate the page: 0.13785 seconds