What is wrong with this PL/SQL? Bind Variable * is NOT DECLARED

Question

Here is:

declare
  v_str1   varchar2(80);
begin
  v_str1 := 'test';
  print :v_str1;
end

When I run it using SQLDeveloper just in a sql worksheet I get this:

Bind Variable "v_str1" is NOT DECLARED
anonymous block completed

Cupric

What the heck is "print" ? I know of no PL/SQL command of that name. – Forcer

Answer 1

Got it:

set serveroutput on

declare
  v_str1   varchar2(80);    
begin
 v_str1 := 'test';
 dbms_output.put_line(v_str1);
end;

More info here.

Cupric

Answer 2

The bind variables syntax of the form :VARNAME are used primarily in SQL* Plus (except for bind variables for dynamic SQL, I think). For SQL* Developer, PL/SQL Developer, or other apps, there is the "&" for variable substitution:

declare
  v_str1   varchar2(80);
begin
  v_str1 := &v_str;
  print v_str1;
end

EDIT: My bad, the code for Oracle SQL*Developer should have been:

set serveroutput on;
declare
  v_str1   varchar2(80);
begin
  v_str1 := '&v_str';
  dbms_output.put_line(v_str1);
end;

You have to select everything and execute it. The result will appear in the "Script Output" panel.

Presidentship

It prompts for a value for V_str then throws an error ORA-06550. If you change it to print :V_str1; you get Bind Variable "v_str1" is NOT DECLARED. – Cupric

Answer 3

print is not a PLSQL function. If you want to get an output, you can use dbms_output.put_line(v_str1);

set serveroutput on;    
declare v_str1 varchar2(80);
begin
    v_str1 := 'test'; 
    dbms_output.put_line(v_str1);
end;

:v_str1 is a bind variable but you must declare not in a plsql. When you declare it you must use VARIABLE keyword.

Feldt

Answer 4

Try

declare
  v_str1   varchar2(80);
begin
  v_str1 := 'test';
  print v_str1;
end

Austere

Answer 5

variable V_STR1 varchar2(20);--bind variable
--IF You want to use bind variable you must declare before your PL/SQL block.
set serveroutput on;--TO see the output
set autoprint on;--TO SEE THE BIND VARIABLE VALUE WHEREVER WE USED IN PL/SQL CODE
declare
    V_STR2 varchar2(80);--normal variable
begin
    :V_STR1 := 'Hello';--assigning value to bind variable
    V_STR2 := 'test';
    DBMS_OUTPUT.PUT_LINE(:V_STR1);
    DBMS_OUTPUT.PUT_LINE(V_STR2);
end;
/

Waitabit

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Isoniazid