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>