How to get single output from PL/SQL procedure having select statement

Question

I am trying to execute one PL/SQL procedure. I am getting nullpointerexception every time. Might be I am returning the procedure in wrong way.

Can you please help me in this procedure.

PROCEDURE p_regidexport(countryid IN varchar2, cropid IN varchar2, productid IN VARCHAR2, pregid out varchar)
IS
 fnc       VARCHAR2(30) := 'P_REGIDEXPORT';
 query     VARCHAR2(10000);
 regid varchar(20);


 BEGIN

select REG_ID into regid from GRS_Registration where LOC_ID =(select loc_id from GRS_location where Country = ' || countryid || ') AND CROP_ID = (select crop_id from GRS_crop where CROP_NM = ' || cropid || ')AND REG_NAME =' || '''' || productid || ''';

pregid := regid;
 sub_log('P_REGIDEXPORT:'||pregid);
dbms_output.put_line(pregid);
EXCEPTION
   WHEN no_data_found THEN
dbms_output.put_line('No record present');      


END P_REGIDEXPORT;

Answer 1

you need not to concatenate in parameter value. because its not dynamic query. so, you can directly pass the parameter variable into ur query. make sure that your qry will return one single value.

its just idea based upon ur code, u can try based upon ur requirement. Hope it will help you!!

   create or replace  PROCEDURE p_regidexport(countryid IN varchar2, cropid IN varchar2, productid IN VARCHAR2, pregid out varchar)
    IS
    fnc       VARCHAR2(30) := 'P_REGIDEXPORT';
    query     VARCHAR2(10000);
    regid varchar(20);

    BEGIN
        begin
          select nvl(REG_ID,'0') into regid from GRS_Registration 
          where 
          LOC_ID =(select loc_id from GRS_location where Country = countryid ) AND 
          CROP_ID = (select crop_id from GRS_crop where CROP_NM =  cropid)AND 
          REG_NAME = productid ;
          EXCEPTION
            WHEN no_data_found THEN
                  dbms_output.put_line('No record ');  --- or regid  ='0';  
        end;
        pregid := regid;
        --sub_log('P_REGIDEXPORT:'||pregid);
        dbms_output.put_line(pregid);
        EXCEPTION
        WHEN others THEN
        dbms_output.put_line('No record present' || ' - ' || sqlerrm);      
    END P_REGIDEXPORT;

All the best!! if it is useful click up button which is in left side of this answer