Oracle PL/SQL/PL SQL/Number to Char

Converting a character formatted as currency to an integer value

SQL>
SQL>
SQL> -- Converting a character formatted as currency to an integer value.
SQL> DECLARE
  2        myString VARCHAR2(20) := "$123,456.78";
  3        myNumber NUMBER ;
  4  BEGIN
  5        myNumber := TO_Number(myString,"$999,999,999.99");
  6        DBMS_OUTPUT.PUT_LINE("The converted number is: " || myString);
  7        DBMS_OUTPUT.PUT_LINE("Your commission at 6% is: " || (myNumber*.06));
  8  END;
  9  /
The converted number is: $123,456.78
Your commission at 6% is: 7407.4068
PL/SQL procedure successfully completed.
SQL>
SQL>

Converting Numbers to Characters Formatted as a Numeric String

 
SQL>
SQL> DECLARE
  2       v_Convert_Number NUMBER := 90210;
  3       v_Hold_Char VARCHAR2(21) ;
  4  BEGIN
  5       v_Hold_Char := TO_CHAR(v_Convert_Number,"0000000000");
  6       DBMS_OUTPUT.PUT_LINE("The employee ID is: " || v_Hold_Char);
  7  END;
  8  /
The employee ID is:  0000090210
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

Expressing Your Work in Scientific Notation

 
SQL>
SQL> DECLARE
  2       v_Convert_Number NUMBER := 90210;
  3       v_Hold_Char VARCHAR2(21) ;
  4  BEGIN
  5       v_Hold_Char := TO_CHAR(v_Convert_Number,"9.99EEEE");
  6       DBMS_OUTPUT.PUT_LINE("The Scientific Notation is: " || v_Hold_Char);
  7  END;
  8  /
The Scientific Notation is:   9.02E+04
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

Illegal conversion between character and number

 
SQL>
SQL> DECLARE
  2    v_TempVar NUMBER;
  3  BEGIN
  4    v_TempVar := "xyz";
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

SQL>
SQL>