Variable not working properly inside oracle procedure

Question

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);

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Sep 01 '22 at 08:39
"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. — noicenoicenoice, Sep 01 '22 at 08:41
"but instead it just gets assigned NULL value" - how are you determining that? The code you posted doesn't have any debugging to tell you, so have you added some, or are you guessing based on the results you get? What output/results do you see? — Alex Poole, Sep 01 '22 at 08:44
I tried `dbms_output.put_line(v_cust_sex)` but it gave nothing. — noicenoicenoice, Sep 01 '22 at 08:46
Did you `set serveroutput on`? And are you actually looking at the ref cursor - you haven't shown a `print c1` command after the `exec`? — Alex Poole, Sep 01 '22 at 08:49
Please provide reproducible example: table definition and sample data to work with. — astentx, Sep 01 '22 at 08:55
I have `set serveroutput on` and I am using the output variable section which comes up when you run the procedure to see the output. — noicenoicenoice, Sep 01 '22 at 08:57
The output variable window is only populated when you use the 'Run PL/SQL' wizard, not when you use the worksheet. If you do `print c1` do you then see the result you expect? — Alex Poole, Sep 01 '22 at 09:00
Thank you doing `print c1` worked but the format is all messed up. Is there a way to show the result like in Query Result? If it is possible can you point me towards the resources to do so? — noicenoicenoice, Sep 01 '22 at 09:07
Not directly, but you can [user a wrapper function](https://stackoverflow.com/a/3560635/266304) - or you could turn your procedure into a function that returns a ref cursor instead of of using an OUT parameter, depending on who/what will call this in the future. — Alex Poole, Sep 01 '22 at 09:13
Thank you for your help. I will look at resources you have provided. Also, how do I mark the question as completed here? — noicenoicenoice, Sep 01 '22 at 09:16

score 0 · Accepted Answer · answered Sep 01 '22 at 09:26

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.

Variable not working properly inside oracle procedure

1 Answers1