How do I CAST a NUMBER to VARCHAR2 in Oracle?

Question

I have a problem with some P-SQL syntax. I have reduced the code sample to its minimum below.

The following works:

CREATE OR REPLACE FUNCTION MyFunction(LINE_ID SMALLINT)
RETURN VARCHAR2 IS
    tmp VARCHAR2(4000);
BEGIN
    tmp := CAST(LINE_ID AS VARCHAR2);
    RETURN(tmp);
END MyFunction;
/

However, I need to change the LINE_ID parameter to NUMBER(5, 0), after which the following does not work:

CREATE OR REPLACE FUNCTION MyFunction2(LINE_ID NUMBER(5, 0))
RETURN VARCHAR2 IS
    tmp VARCHAR2(4000);
BEGIN
    tmp := CAST(LINE_ID AS VARCHAR2);
    RETURN(tmp);
END MyFunction2;
/

The error message in Oracle SQL Developer 3.2.10.09 is

Error(1,36): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

How should I write the CAST statement in order to make it work with NUMBER(5, 0) instead of SMALLINT?

Again, this is not the original code but I am looking for a solution that does not deviate too much from the second version and preferably not another function call either. The VARCHAR2 return type is important as well.

Solution 1

You can't specify NUMBER precision and scale for a function's parameter. Just declare it like this:

CREATE OR REPLACE FUNCTION MyFunction2(LINE_ID NUMBER)

Solution 2

The function you're looking for is TO_CHAR:

tmp := TO_CHAR(LINE_ID);

Solution 3

The issue is not in your CAST, but rather in your parameter definition. From the documentation:

You can declare a formal parameter of a constrained subtype, like this:

DECLARE
  SUBTYPE n1 IS NUMBER(1);
  SUBTYPE v1 IS VARCHAR2(1);

  PROCEDURE p (n n1, v v1) IS ...

But you cannot include a constraint in a formal parameter declaration, like this:

DECLARE
  PROCEDURE p (n NUMBER(1), v VARCHAR2(1)) IS ...