0

The following code runs good as expected in SQL Developer without any errors, but when I run the code in SQL*Plus it is throwing an error.

declare
      v_num1   integer := '&a';
      v_num2   integer := '&b';
      v_result number;
    begin
      v_result := v_num1 / v_num2;
      dbms_output.put_line ('v_result: '||v_result);
    end;

The error raised is

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

Please point out where the error I made.

Improve this question
6
  • What values are you giving for a and b in both cases; and why are you quoting them in the assignment if they are numbers? If the values have group or decimal separators (though decimal doesn't make sense if they are integers), are your NLS settings the same, particularly NLS_NUMERIC_CHARACTERS? Commented Jan 7, 2014 at 9:52
  • @AlexPoole Can you specify how to check the settings of NLS_NUMERIC_CHARACTERS. When I searched the net, they are providing commands for changing the character. What is the command to check for the current character. Commented Jan 7, 2014 at 10:25
  • 1
    select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';. It's only relevant if you're entering a value with a separator, e.g. 1,001 or 1.001 rather than just 1001. That NLS setting will determine whether it's interpreted as one thousand and one, or one and a little bit. Is that what you're doing? It might explain the error - if you're passing a group separator then you will get that error. Please show what values you are using. Commented Jan 7, 2014 at 10:30
  • @AlexPoole I'm getting the error during compilation itself. Its before asking the input from me. But, thank you, now I have come to know about NLS_NUMERIC_CHARACTERS. and when I execute the command, ., is the output in both places. Commented Jan 7, 2014 at 10:33
  • 1
    Oh, OK... in that case, what does show define have in SQL*Plus? Sounds like it might be off, and it's trying to convert the &a to a number with treating it as a substitution variable. Commented Jan 7, 2014 at 10:36

1 Answer 1

Reset to default
1

Your SQL*Plus session has substitution variables turned off, so the '&a' is being treated as a literal string, and it isn't prompting you for the value. In a simplified version:

set define off
declare
  i integer := to_number('&a');
begin
  null;
end;
/

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

To fix it, turn substitution variables back on:

set define &

Then it works:

declare
  i integer := to_number('&a');
begin
  null;
end;
/

 Enter value for a:

If I enter 1:

PL/SQL procedure successfully completed.

You don't need the quotes, or the to_number(), unless you're using group seperators (and then you'd need to supply a format model too). You can just do:

declare
  i integer := &a;
begin
  null;
end;
/

As you have it you're doing an unnecessary implicit string-to-number conversion.

If you haven't explicitly turned them off, then you probably have a site or user profile that is doing the set define off, among other configuration settings. it's probably doing that for a reason, but if you currently only have a site profile then you could create your own user profile to override that. Just be aware of the effect of anything you change; you may have other scripts that rely on it being off, e.g. if you have data that has ampersands and it's been disabled to prevent issues interpreting those.

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

Comments

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.