1

This is my function:

FUNCTION GET(V_IN IN NUMBER) RETURN VARCHAR2 AS
    V_OUT VARCHAR2(1000);
BEGIN
    function body
END;

When I right click the function and click on test, I am getting the following:

begin
  -- Call the function
  :result := pkg.get(V_IN => :V_IN);
end;

How do I substitute a value for this variable V_IN? I need to test it for a number, say 940.

When I try the code:

declare
  r varchar2(2000);
begin
  -- Call the function
  r := pkg.get(940);
end;

I am getting an error:

ORA-01036: illegal variable name/number

Can you suggest various ways of calling this function?

PS: Tool used: PL/SQL Developer Allround Automations. Version 8.0.1.1502

Oracle Database 11g Enterprise Edition

Improve this question
2
  • Which tool are you using: PL/SQL Developer from Allround Automations, or SQL Developer from Oracle? Commented May 9, 2017 at 11:01
  • PL/SQL Developer from Allround Automations. Commented May 9, 2017 at 11:03

4 Answers 4

Reset to default
6

Once you've clicked on your function and clicked on Test in the context menu a Test window is brought up with the code you've shown. There are two panes in this window - the top pane shows the code which PL/SQL Developer generated to invoke the function, and the lower pane contains a list of the parameters to your function. In the lower pane of the Test window there's a list of the parameters to the function with three columns - Variable, Type, and Value. Type the value you want into the Value column in the row with your parameter's name, then click on the Start Debugger button (top left of the Test window, under the 'Test Script' tab's name), then click on the Run button (immediately to the right of the Start Debugger button).

Best of luck.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is exactly what I was expecting from a noobie's shoes. Thanks a lot, but i have figured it out myself with some self research.
1

"How do I substitute a value for this variable V_IN? I "

When you run the test in PLSQL Developer the bottom pane of the test window is a property list for all the substitution variables. You define the datatype and the input values (when appropriate). Output values are available in this window after the test is run.

"ORA-01036: illegal variable name/number"

Not sure what causes this. It's probably not PLSQL Developer but a bug in your function code (which you have not posted).

Improve this answer

4 Comments

I don't get you.
What don't you get?
I am now able understand what you were trying to say. I am new to this tool and it took some time to figure it out. THANKS!
Possibly PL/SQL Dev version 8 could not handle the case where a bind variable is declared but not used. I only have version 11 which automatically deselects unused bind variables in the lower panel when you execute the code.
1

I was able to run the function as follows:

 declare 
     v varchar2(1000);
 begin
     select pkg.get(940) into v from dual;
     dbms_output.put_line(v);
 end;

Also, as per APC's comment, there is a property list(the small window appearing below the PL/SQL worksheet, having Variable, Type and Value fields). You need to enter the value which you wish to pass in the value field and click on Execute (shortcut-F8). The output will be shown and highlighted in yellow in the same property list window. Click below link to refer the screenshot:

Function Call with single parameter

Improve this answer

Comments

-1

I assume you are still in pl/sql "Test window", when you modified the original test code to your custom. Pl/sql sometimes is buggy. Open a new "SQL window" and try to run there with dbms_output.put_line() to see the result.

Improve this answer

3 Comments

PL/SQL sometimes is buggy? Why would a SQL window work better than a Test window?
First, I didn't claim, that SQL window "works better" then Test window. He is using older version of plsql developer. By initiating Test window, you are setting a specific test window with specific background. And doing modifications in that window, plsql can get "confused". On version 12+, its works fine, even when you modify it.
PL/SQL is not "buggy" and does not get "confused". Perhaps you meant "PL/SQL Developer". And I can't see what the window background has to do with bind variable handling.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.