Geek Answers Handbook

How to make CAST NUMBER for VARCHAR2 in Oracle?

I have a problem with some P-SQL syntax. I reduced the sample code 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;
/

Error message in Oracle SQL Developer 3.2.10.09

Error (1.36): PLS-00103: The symbol "(" was detected while waiting for one of the following :: =.), The symbol @% is default. The symbol ": =" has been replaced by "(" continue.

How to write an instruction CASTto work with NUMBER(5, 0)instead SMALLINT?

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

+4
3

NUMBER . :

CREATE OR REPLACE FUNCTION MyFunction2(LINE_ID NUMBER)
+3

, , TO_CHAR:

tmp := TO_CHAR(LINE_ID);
+10

The problem is not your CAST, but the parameter definition. From the doc:

You can declare a formal parameter of a restricted subtype, for example:

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, for example:

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

More articles:


All Articles