title: How do I CAST a NUMBER to VARCHAR2 in Oracle?
body: <p>I have a problem with some P-SQL syntax. I have reduced the code sample to its minimum below.</p>
  
  <p>The following works:</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>However, I need to change the LINE_ID parameter to NUMBER(5, 0), after which the following does not work:</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>The error message in Oracle SQL Developer 3.2.10.09 is</p>
  
  <blockquote>
    <p>Error(1,36): PLS-00103: Encountered the symbol "(" when expecting one
    of the following:     := . ) , @ % default character The symbol ":="
    was substituted for "(" to continue.</p>
  </blockquote>
  
  <p>How should I write the <code>CAST</code> statement in order to make it work with <code>NUMBER(5, 0)</code> instead of <code>SMALLINT</code>?</p>
  
  <p>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 <code>VARCHAR2</code> return type is important as well.</p>