Accessing Bind Variable in PL/SQL

Question

I am trying to run a program in the Oracle express edition editor. When I execute the program, I get an error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Can anyone help me understand why I am getting an error and how to fix the code?

VARIABLE gvn_total_salary NUMBER;
DECLARE
   vn_base_salary NUMBER := 3000;
   vn_bonus NUMBER := 1000;
BEGIN
   :gvn_total_salary := vn_base_salary + vn_bonus;
END;

The output I'm getting

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 


Run By SYSTEM 
Parsing Schema SYSTEM 
Script Started Thursday, April 26, 2012 
 3 seconds ago 
Elapsed time 0.01 seconds 
Statements Processed 1 
Successful 0 
With Errors 1

ohh sorry.. it has got missed. it is defined as bind variable --- DECLARE vn_base_salary NUMBER := 3000; vn_bonus NUMBER := 1000; BEGIN :gvn_total_salary := vn_base_salary + vn_bonus; END; — S.P, Apr 26 '12 at 16:43
while i am trying to execute it i'm getting before execution itself as--- Script Name sample bind Created on 04/26/2012 10:20:22 PM by SYSTEM Updated on 04/26/2012 10:20:59 PM by SYSTEM Number of Statements 1 Script Size in Bytes 165 Line Number Unknown Statement 1 VARIABLE gvn_total_salary NUMBER — S.P, Apr 26 '12 at 16:52

score 1 · Answer 1 · answered Apr 26 '12 at 16:53

1

With the declaration of the bind variable, that code works fine for me in SQL*Plus

SQL> VARIABLE gvn_total_salary NUMBER;
SQL> DECLARE
  2     vn_base_salary NUMBER := 3000;
  3     vn_bonus NUMBER := 1000;
  4  BEGIN
  5     :gvn_total_salary := vn_base_salary + vn_bonus;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> print gvn_total_salary

GVN_TOTAL_SALARY
----------------
            4000

Can you connect to the database using SQL*Plus and run the same thing?

answered Apr 26 '12 at 16:53

Justin Cave

227,342
24
367
384

yes i can in sql* plus command line. But i'm not able to get it done in Oracle express edition editor (in browser which opens in database home page after login) – S.P Apr 26 '12 at 16:59

score 0 · Accepted Answer · answered Apr 26 '12 at 16:37

What are you actually trying to accomplish? This script won't execute in sqlplus or Oracle Developer or any PL/SQL execution environment I can think of. In fact, I don't understand how you are passing the bind variable :gvn_total_salary and how you can get the error you are describing. You should get something like "bind variable gvn_total_salary" not declared.

Accessing Bind Variable in PL/SQL

2 Answers2