Having error while declaring binding variable in pl/sql

Question

Hi I am getting the error, need advise. The code is:

VARIABLE v_bind1 VARCHAR2(10); --declare bind variable

exec : v_bind1 := 'RebellionRider'; --execute it

SET SERVEROUTPUT ON;

BEGIN

dbms_output.put_line(v_bind1);

END;

It prints the below output when m trying to declare the variable:

Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

and when tried to print it, it shows below error:

SP2-0552: Bind variable "V_BIND1" not declared.

I think you need to remove the colon character. I think the line should be: exec v_bind1 := 'RebellionRider'; Also the value that you are assigning to the variable is more than ten characters. — Abra
– Abra, Commented Jul 11, 2022 at 3:19
after posting question i kinda debeugged it. thanks for your comment though. yes thats the error and working code is as below VARIABLE v_bind1 VARCHAR2(30); SET SERVEROUTPUT ON; BEGIN :v_bind1:='testing'; dbms_output.put_line(:v_bind1); END; / print :v_bind1; — S_P
– S_P, Commented Jul 11, 2022 at 3:42

Gnqz · Accepted Answer · 2022-07-12 07:05:07Z

1

You need to have the : right next to the bind name:

VARIABLE v_bind1 VARCHAR2(10); --declare bind variable

exec :v_bind1 := 'RebellionRider'; --execute it

and then reference it as :v_bind1.

For reference:

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example

answered Jul 12, 2022 at 7:05

Gnqz

3,3823 gold badges28 silver badges38 bronze badges

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

Comments

William Robertson · Accepted Answer · 2022-07-31 15:41:38Z

Assuming this is SQL*Plus and not PL/SQL Developer as the error you posted is a SQL*Plus error, it's because of the trailing comment:

Fails:

SQL> VARIABLE v_bind1 VARCHAR2(10); --declare bind variable

Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

Succeeds:

SQL> VARIABLE v_bind1 VARCHAR2(10);
SQL>

The SQL terminator character (by default it's ; but don't forget it's configurable) must appear at the end of the line, otherwise SQL*Plus won't recognise it. But SQL*Plus commands like variable don't need a SQL terminator and don't have any comment syntax anyway, so the whole line is rejected as invalid.

The next issue you had was that v_bind1 and :v_bind1 are two different things. This fails because v_bind1 is an undeclared PL/SQL local variable (it would have needed to be declared within the block):

begin
    dbms_output.put_line(v_bind1);
end;
/

What you needed was the bind variable you just declared named :v_bind1:

begin
    dbms_output.put_line(:v_bind1);
end;
/

As v_ and : both indicate a variable, I'd suggest you don't need the first one.

Collectives™ on Stack Overflow

Having error while declaring binding variable in pl/sql

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related