0

I'm trying to do a character to number conversion but the system doesn't let me and threw me the ORA-06502 ERROR .

This is the code that I'm trying to compile :

VPARAMETROS_MENSAJE VARCHAR2(2000);
ERROR_NUMBER NUMBER;

BEGIN

ERROR_NUMBER := 0;
ERROR_NUMBER := SQLCODE;

VPARAMETROS_MENSAJE :='{' +'"ERROR_NUMBER":"' + CAST('ERROR_NUMBER' AS 
VARCHAR2) + '",' +'}' ;

THANKS YOU ALL!

I found the mistake traducing the code that I was trying to compile from Microsoft SQL Server to Oracle and it's finally works.

The error was in this line.

 VPARAMETROS_MENSAJE :='{' ||'"ERROR_NUMBER":"' || CAST('ERROR_NUMBER' AS VARCHAR2) || '",' ||'}' ;
Syscall
  • 19,327
  • 10
  • 37
  • 52
A THOUSAND HOURS
  • 25
  • 1
  • 8
  • 1
    You aren't trying to do a character to number conversion; you're trying to go the other way. But you are accidentally doing an implicit conversion, because of the incorrect concatenation operator `+`. See [What is the string concatenation operator in Oracle?](https://stackoverflow.com/q/278189/266304) – Alex Poole Feb 25 '21 at 17:34
  • It's great that you're accepting the answers that have helped you, including your own, on this and other questions; but please [don't add 'solved' or the solution to the question](https://meta.stackoverflow.com/q/311829/266304) - that's what the answers are for. – Alex Poole Feb 25 '21 at 19:52
  • Also the version to added to the question still has `CAST('ERROR_NUMBER' AS VARCHAR2)`, which will leave that literal string in the result, *not* the value of the `ERROR_NUMBER` variable. Ankit's answer does use that variable. – Alex Poole Feb 25 '21 at 19:54

1 Answers1

1

You are using Oracle but this is SQL Server Syntax. You need to use the below code -

VPARAMETROS_MENSAJE VARCHAR2(2000);
ERROR_NUMBER        NUMBER;
BEGIN
     ERROR_NUMBER := 0;
     ERROR_NUMBER := SQLCODE;

     VPARAMETROS_MENSAJE := '{"ERROR_NUMBER":"' || ERROR_NUMBER || '",}';
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40