Is there a range of VARCHAR2 which can be automatically convert to NUMBER in SQL?

Question

There is a plsql Cursor like below.

CURSOR get_customer_balance(cust_id_ IN VARCHAR2) IS
      SELECT t.cust_balance
      FROM customer_tab t
      WHERE t.customer_id =cust_id_; 

Procedure Body

OPEN  get_customer_balance(123456789);
FETCH get_customer_balance INTO balance_;
CLOSE get_customer_balance;

In customer_tab, customer_id is a number type colomn. even through cursor parameter is varchar2 this works fine. for some values this give an error. also any error will be occurred in runtime only.

Is there a range of varchar2 that can automatically converted to number?

Answer 1

In customer_tab, customer_id is a number type colomn

vs.

CURSOR get_customer_balance(cust_id_ IN VARCHAR2) IS

means that you intentionally do things wrong.

Not only that you should have passed a number - as a matter of fact, you should have passed customer_tab.customer_id%type so cursor declaration would have been

CURSOR get_customer_balance(cust_id_ IN customer_tab.customer_id%type) IS

---

As of your question: Oracle will try to implicitly convert any datatype you pass to a valid number. Sometimes it'll succeed (if you pass '10', for example), sometimes not ('ABC' or '15XF4'). There's no "range"; it just depends on actual value.

---

If you want, have a look at Tom Kyte's <a=href="https://blogs.oracle.com/connect/post/on-implicit-conversions-and-more">On implicit conversions and more, saying - among other things:

The problem is in your code. Your developers have programmed a very nasty bug, and the bug is called “implicit conversions.”

Answer 2

Summary: NEVER rely on implicit casts between data types.

---

Is there a range of VARCHAR2 which can be automatically convert to NUMBER in SQL?

Any value that can be implicitly cast from VARCHAR2 to NUMBER is a something that can be successfully explicitly converted using TO_NUMBER(value) (with an implicit format model).

In general, this would match the regular expression:

[+-]?([0-9]+(D[0-9]*)?|D[0-9]+)([eE][+-][0-9]+)?

Where D is the decimal separator character specified by the NLS_NUMERIC_CHARACTERS session parameter.

There will be further limits as a NUMBER is only able to store (about) 38 digits but you may be able to convert larger strings and it will truncate the number beyond the precision of the number.

For example, given the sample data:

CREATE TABLE table_name (value) AS
  SELECT '0' FROM DUAL UNION ALL
  SELECT '+00001234' FROM DUAL UNION ALL
  SELECT '-00001234' FROM DUAL UNION ALL
  SELECT '-00001234.' FROM DUAL UNION ALL
  SELECT '-00001234.5678' FROM DUAL UNION ALL
  SELECT '.1234' FROM DUAL UNION ALL
  SELECT '.1234e4' FROM DUAL UNION ALL
  SELECT '.1234E-004' FROM DUAL UNION ALL
  SELECT '1234E-004' FROM DUAL

Then you can explicitly perform a cast between data-types using the CAST function:

SELECT value, CAST(value AS NUMBER) FROM table_name;

Which (when the NLS_NUMERIC_CHARACTERS is .,) outputs:

VALUE	CAST(VALUEASNUMBER)
0	0
+00001234	1234
-00001234	-1234
-00001234.	-1234
-00001234.5678	-1234.5678
.1234	.1234
.1234e4	1234
.1234E-004	.00001234
1234E-004	.1234

However, you should NEVER rely on implicitly casting between data types.

If we change the session parameters:

ALTER SESSION SET NLS_TERRITORY = 'France';

Then the NLS_NUMERIC_CHARACTERS changes from ., to , and exactly the same query and same data:

SELECT value, CAST(value AS NUMBER) FROM table_name;

Now fails with ORA-01722: invalid number because it is now expecting decimal values to use , as the decimal point (as per the normal convention in France, and many other countries) rather than . and now all the strings with . are invalid.

Since any user can change their session parameters at any time then you cannot (and should not) rely on implicit conversion to always work.

If you want numbers that that will be valid for any session parameters then you cannot use a decimal separator and would be restricted to an integer significand and an optional exponent:

[+-]?[0-9]+([eE][+-][0-9]+)?

fiddle