Oracle Bind Variables, Procedures and a headache

Question

I am wondering if you can point me in the right direction. I have spent the last little while trying to work out how to use Bind Variables in block code with declares. My issues is that every time I try run the code block in SQL Developer, its returning the error "Bind Variable "disp" is NOT DECLARED.

In a non-block piece of code I can get the results I am expecting. The following I know works and I do get results for:

var disp varchar2(200);
begin
    test_procedure('test', 100, :disp);
end;
/
print :disp

The above code returns me a value, test100.

However, if I try move this into a block of code, that I could use as a single line in an external application (Java or PHP) I start running into trouble. What I have so far is:

declare
    disp varchar2(200);
begin
    test_procedure('test', 100, :disp);
end;
/
print :disp

When I run the above I am getting:

Bind Variable "disp" is NOT DECLARED

I have tried a few different approaches from using var inside the declare box to trying to reference the procedures variable definitions, however none are working for me

score 5 · Accepted Answer · answered Jun 26 '14 at 05:29

You don't prefix a local variable name with a colon.

declare
    disp varchar2(200);
begin
    test_procedure('test', 100, disp);
end;

is a valid PL/SQL block. print is a SQL*Plus command, however, so it only works with SQL*Plus variables, not variables defined in PL/SQL blocks. If you have enabled it, you could use dbms_output to print the value in PL/SQL

declare
    disp varchar2(200);
begin
    test_procedure('test', 100, disp);
    dbms_output.put_line( disp );
end;

In SQL Developer, you'd need to enable output first (View | DBMS Output to bring up the window in newer versions of SQL Developer).

dbms_output would not be an appropriate way to return data to a client application (or to do something where you're depending on a human to see the output in order to do something). It's a useful package for doing very simple debugging or a quick proof of concept. But you shouldn't assume that the caller of your code has allocated a buffer for dbms_output to write to or that it has any ability to read what you've written to dbms_output.

Oracle Bind Variables, Procedures and a headache

1 Answers1