This discussion is archived
3 Replies Latest reply: Nov 22, 2012 10:41 AM by William Robertson RSS

need help for cursor

874273 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points