How do I validate the filters using substr function and get results from Stored procedure in Oracle PLSQL?

Question

I created a Stored Procedure which has 2 filters in where clause. I have included one filter and am able to get the results.However, when am trying to include the second one, the procedure is not displaying any values.

The user enters the parameter from UI Screen and clicks on search button.

UI Screen:

Emp ID: _____________

Country:__________

                 Search button        Cancel button

The data is coming from a table called Employee in Oracle database.

Employee Table:

Emp_id ---- Name----- Phone Number------ Country

1---- John Smith ---- US-765-234-4567--- USA

2---- Sam Benigal---- AL-978-346-765--- Argentina

3---- Mark Taylor---- AS-987-3987-857--- Australia

4---- Claire Furner--- CA-85-454-5454--- Canada

For the second filter, I need to use the phone number which contains the data beginning with the countrycode ('US-765-234-4567' for country USA; 'AR-978-346-765' for Argentina country; 'AS-987-3987-857' for Australia country... and so on) and match with the input parameter 'pt_country' to generate the results. I have used the Substring function in this scenario because based on the first two characters in the phone number, i need to get the result.

Also, the input parameter "pt_country" can have a value or can be NULL.

Code:

   Procedure search_emp (  pt_id        in     number,
                        pt_name       in     varchar2,
                        pt_country    in     varchar2,
                        empCursor    out    ref cursor)

as

    v_countrycode           CHAR(2);
    v_count                 NUMBER;
begin

    SELECT count(*) INTO v_count FROM r_country where UPPER(country) = UPPER(pt_country); 
    IF v_count > 0 THEN        
    SELECT countrycode INTO v_countrycode FROM r_country where UPPER(country) = UPPER(pt_country);            
    ELSE NULL; 
    END IF;  -- here, am trying to get the country code from the table for the input parameter 'pt_country' that is passed.

    open empCursor for
    select e.emp_id,
    e.name,
    e.phone_number           
    from employee e
    where upper(e.emp_id)    = upper(pt_emp_id)
    and   (UPPER(substr(e.phone_number,1,2)) LIKE UPPER(v_countrycode) or pt_country IS NULL);

--- I'm not sure how to get the results when the User gives a value to the input parameter  'pt_country'.
END;

Please help.

Answer 1

Try this below procedure , need to define cursor as SYS_REFCURSOR to get output

   create or replace Procedure search_emp (  pt_emp_id        in     number,
                        pt_name       in     varchar2,
                        pt_country    in     varchar2,
                        empCursor    out    SYS_REFCURSOR)

as

    v_countrycode           CHAR(2);
    v_count                 NUMBER;
begin

    SELECT count(*) INTO v_count FROM r_country where UPPER(country) = UPPER(pt_country); 
    IF v_count > 0 THEN        
    SELECT countrycode INTO v_countrycode FROM r_country where UPPER(country) = UPPER(pt_country);            
    ELSE NULL; 
    END IF;  -- here, am trying to get the country code from the table for the input parameter 'pt_country' that is passed.

    open empCursor for
    select e.emp_id,
    e.name,
    e.phone_number           
    from employee e
    where upper(e.emp_id)    = upper(pt_emp_id)
    and   (UPPER(substr(e.phone_number,1,2)) LIKE UPPER(v_countrycode) or pt_country IS NULL);

--- I'm not sure how to get the results when the User gives a value to the input parameter  'pt_country'.
END;