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.

How do I CAST a NUMBER to VARCHAR2 in Oracle?

3 Answers