How to Convert Number to String in Oracle?

In Oracle, you can convert a number to string using the TO_CHAR() function; however, you can simply assign a numeric value to a string variable without using any function in Oracle. Below are the examples.

1. Convert Number to String using TO_CHAR() Function

The following PL/SQL program converts the number 9876543210 to a string using TO_CHAR, and prints it.

declare
   s_phone varchar2(100);
begin
   s_phone := TO_CHAR(9876543210);
   dbms_output.put_line(s_phone);
end;

Output:

9876543210

Convert in currency format:

declare
   s_dollar varchar2(100);
begin
   s_dollar := TO_CHAR(1234, '$999999.99');
   dbms_output.put_line(s_dollar);
end;

Output:

$1234.00

2. Directly assign a Number to a String (Varchar2) Variable

If we don't need any specific format conversion, then in Oracle, you can simply convert a number value by assigning it to a VARCHAR2 variable. Here's an example:

declare
   s_pincode varchar2(100);
begin
   s_pincode := 123456789;
   dbms_output.put_line(s_pincode);
end;

Output:

123456789

3. Get Number into String (Varchar2) Variable using SQL Query

declare
   s_amount varchar2(100);
begin
   select 98765.99 into s_amount from dual;
   dbms_output.put_line(s_amount);
end;

Output:

98765.99

4. Convert Number to String using TO_CHAR() Function in SQL

This PL/SQL block selects a numeric value, converts it to a dollar amount string implicitly, stores it in a variable, and prints it.

declare
   s_amount varchar2(100);
begin
   select to_char(98765.99, '$99999999.99') into s_amount from dual;
   dbms_output.put_line(s_amount);
end;

Output:

$98765.99