Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    8

    Unanswered: problem with the cursor

    hi all

    i have bulid an application that recieve orders with ordered items and then the manager have to check these orders and accept or reject the items.
    if the manager reject any item the item shpuld be deleted from the database.

    first the application consist of two master-detailed datablocks, the master is the orders and the detail is the ordered items. both of the data blocks are muli-record. the managers changes the status of the items and then submit the changes. after submitting the changes any rejected item should be deleted and the order states and check date in the master block shoul be filled.

    here is part of the code:
    ======================

    declare
    cursor order_item_cursor is
    select status,item_id
    from order_item
    where order_id= : orders.order_id;
    v_status order_item.status%type;
    v_id order.item.item_id%type;

    =================
    the problem is that the cursor does not fetch any row although there are many valid records. i think the whare clouse is wrong but i need to put the condition in order to retrieve only the item of the selected order only.
    plz help me with this if u have any idea.

    flona

  2. #2
    Join Date
    Nov 2003
    Posts
    33
    can you plz tell us what kind of errors you are getting. from oracle

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You might want to supply more code to allow us to help you... plus, it is better to actually cut and paste it to the site instead of re-typing it... looks like you have some syntax errors.

    In any event, when I need to build a cursor based on unknown values, I usually do this:

    Code:
    DECLARE
       CURSOR order_item_cursor (order_c NUMBER) IS
          SELECT status,item_id
          FROM order_item
          WHERE order_id := order_c;
    BEGIN
       ...
       FOR order_rec IN order_item_cursor( some_number ) LOOP
          ....
       END LOOP;
    END;

    Hope this helps!

    JoeB

  4. #4
    Join Date
    May 2004
    Posts
    8
    hi all and thanx for ur concerns

    the code gives me error of unhandeled exception which is no_data_found.

    but i think the problem is that the cursor definition can not take the valuse of the order_id from the block (i don't know why) .. may be becoze the block is multi recored..
    i will past my code here

    Code:
    DECLARE
    	CURSOR LIST_STATUS_CURSOR IS
    		SELECT ITEM_STATUS,L_ITEM_ID
    		FROM LIST_ITEM
    		WHERE ORDER_ID= :ORDER_LIST.ORDER_ID;
    	REJECT_COUNT NUMBER:=0;
    	STATUS LIST_ITEM.ITEM_STATUS%TYPE;
    	V_ID LIST_ITEM.L_ITEM_ID%TYPE;
    	V_ACCEPT ORDER_LIST.DEPT_ACCEPTANCE%TYPE;
    	RES NUMBER;
    	
    BEGIN
    	GO_BLOCK('LIST_ITEM');
    	COMMIT;
    		
    	OPEN LIST_STATUS_CURSOR;
    	LOOP
    	 FETCH LIST_STATUS_CURSOR (:order_list.order_id) INTO STATUS,V_ID;
    	 EXIT WHEN LIST_STATUS_CURSOR%NOTFOUND;
    	   IF STATUS = 'REJECTED' THEN
    		REJECT_COUNT:= 	REJECT_COUNT+1;
    	        DELETE FROM LIST_ITEM
    		WHERE L_ITEM_ID= V_ID ;
    		COMMIT;
    	   END IF;
    	END LOOP;
    			
    	SELECT  DEPT_ACCEPTANCE
    	INTO	V_ACCEPT 
    	FROM ORDER_LIST
    	WHERE ORDER_ID= :ORDER_LIST.ORDER_ID;	
    	IF V_ACCEPT= 'UNDER PROCESS'  THEN 
    	    IF 	REJECT_COUNT = LIST_STATUS_CURSOR%ROWCOUNT THEN
    		UPDATE ORDER_LIST
    		   SET DEPT_ACCEPTANCE= 'REJECTED',
    		   DEPT_ACCEPTANCE_DATE= SYSDATE
    	           WHERE ORDER_ID= :LIST_ITEM.ORDER_ID;
                ELSE
    		UPDATE ORDER_LIST
    		   SET DEPT_ACCEPTANCE= 'ACCEPTED',
    		   DEPT_ACCEPTANCE_DATE= SYSDATE
    		   WHERE ORDER_ID= :LIST_ITEM.ORDER_ID;
    	    END IF; 
    	 COMMIT;
    	ELSE
    	   RES:= SHOW_ALERT('ERROR');
    	END IF;
    	
    CLOSE LIST_STATUS_CURSOR;		
    			
    GO_BLOCK('ORDER_LIST');
    EXECUTE_QUERY;
    GO_BLOCK('LIST_ITEM');
    EXECUTE_QUERY;
    
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       RES:=SHOW_ALERT('ERROR2');		
    END;
    THE RED LINES COZE THE EXCEPTION TO WORK AND PREVENT THE REST CODE FROM WORKING.

    thanxs again and hope to find a solution

    flona

  5. #5
    Join Date
    May 2004
    Posts
    8
    dear joebednarz
    i have tried ur method (cursor with parameter) but still am facing the same problem. the cursor does not fetch any record and gives me exception of no_data_found.

    thanx any way

  6. #6
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    flona, how and what is rder_list.order_id being populated. Just a stupid question, but have tried finding out what is being returned to this field and tried running the offending query with the variable explicitly spelled out? Something like:

    SELECT ITEM_STATUS,L_ITEM_ID
    FROM LIST_ITEM
    WHERE ORDER_ID= 12345;

    Could be that the order_id being requested doesn't exist.

    Just a thought.
    JoeB

  7. #7
    Join Date
    May 2004
    Posts
    8
    thanx all for ur concerns and replys.
    finally i have solved the problem


    the code was correct, but the problem was a mismatch between the datatypes od the order_id ..


    soooory guys for buthering u ...

    thanx again


    flona

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •