3 Replies Latest reply: Nov 22, 2012 12:41 PM by William Robertson RSS

    need help for cursor

    874273
      Hi

      I have a pl/sql block..which is having one cursor with a select statement. This select statement will return values when proper values passed..sometimes it will return null. When it return null, I have to insert "invalid code " value into a table. Please find below pl/sql ..

      Please let me know any syntax errors....!!

      Begin
      
      v_sqlstmt:= ' select  distinct (oip.item_number) from
      ODM_ITEM_PARTS_PRDLINE_MAP map, ITEM_PRODU_292_D dim, odm_item_parts oip, listentry le
      where map.entry_id = dim.entry_id
      and le.entryid = dim.entry_id
      and map.object_id = oip.id
      and oip.subclass_id = 267726
      and le.entryvalue = '''|| v_pa||'''' ;
      
      
       dbms_output.put_line('v_sqlstmt' || v_sqlstmt);
      
      OPEN P_OUT_RESULT for v_sqlstmt ;
      
      v_out:=NULL;
                     LOOP
                     
                      BEGIN
                      
                       FETCH P_OUT_RESULT INTO v_item_number; --  already declared
                       exit when p_out_result%notfound;
                        
                          IF  v_item_number is null then
                      
                       insert into AIC_PPA_INFO (ITEM_NO,PPA_VAL,model_name) Values('Invalid Code',v_pa,v_modelname);
                        Commit;
                        
                      END if;
                             end loop;
                 close cursor;
      end ;
                    
      Thanks & Regards
      SA