Oracle PL/SQL Tutorial/PL SQL Data Types/Convert to Number

Attempt to assign a single white space to a number.

   <source lang="sql">

SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> -- Build an anonymous block that will trigger an error. SQL> DECLARE

 2    -- Define local variable.
 3    my_string         VARCHAR2(1) := " ";
 4    my_number         NUMBER;
 5
 6  BEGIN
 7    my_number := TO_NUMBER(my_string);
 8
 9  END;
10  /

DECLARE

ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 7

SQL></source>

Get value from different parts of a date using TO_NUMBER function

   <source lang="sql">

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    age   POSITIVE;
 3
 4    current_year  NATURAL;    --a year of 00 is valid.
 5    current_month POSITIVE;
 6    current_day   POSITIVE;
 7
 8    birth_year    NATURAL;    --a year of 00 is valid.
 9    birth_month   POSITIVE;
10    birth_day     POSITIVE;
11
12    birth_date    DATE := TO_DATE("11-15-1961","mm-dd-yyyy");
13    current_date  DATE;
14  BEGIN
15    current_date := TO_DATE ("12-1-2000","mm-dd-yyyy");
16
17    current_year := TO_NUMBER(TO_CHAR(current_date,"yy"));
18    current_month := TO_NUMBER(TO_CHAR(current_date,"mm"));
19    current_day := TO_NUMBER(TO_CHAR(current_date,"dd"));
20
21    DBMS_OUTPUT.PUT_LINE(current_year);
22    DBMS_OUTPUT.PUT_LINE(current_month);
23    DBMS_OUTPUT.PUT_LINE(current_day);
24  END;
25  /

0 12 1 PL/SQL procedure successfully completed. SQL> SQL></source>

Use TO_NUMBER to assign value to NATURAL type variable

   <source lang="sql">

SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    age   DATE;
 3    current_year NATURAL;
 4  BEGIN
 5    age := sysdate;
 6    current_year := TO_NUMBER(TO_CHAR(age,"yy"));
 7    DBMS_OUTPUT.PUT_LINE("current_year:");
 8    DBMS_OUTPUT.PUT_LINE(current_year);
 9  END;
10  /

current_year: 7 PL/SQL procedure successfully completed. SQL></source>