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

Attempt to assign a single white space to a number.

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>

Get value from different parts of a date using TO_NUMBER function

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>

Use TO_NUMBER to assign value to NATURAL type variable

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>