Variable not working properly inside oracle procedure-CodePudding

I want my procedure to return rows of values using the select query when I provide the gender of the customer but I think the variable is not working correctly. If I provide value to a variable using the procedure section from the connection window it works but when I try to execute the procedure using execute the command it doesn't work.

"Doesn't work" mean for example when i run execute find_data_from('M',:c1); value of v_cust_sex should have been M but instead it just gets assigned NULL value.

create or replace PROCEDURE find_data_from (
    v_cust_sex  IN   VARCHAR,
    c1 IN OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN c1 FOR 
    SELECT
                    *
                FROM
                    customer
                WHERE
                        cust_sex = v_cust_sex
                    AND ROWNUM < 10;
END;

I run the procedure using the following command.

variable c1 refcursor;
execute find_data_from('M',:c1);

CodePudding user response：

When you execute your procedure from the SQL Developer worksheet using a bind variable, you need to print that variable to see the results:

variable c1 refcursor;
execute find_data_from('M',:c1);
print c1

The 'Output Variables' window is only populated by the 'Run PL/SQL' wizard, using its own bind variables, not by the worksheet.