CODEWITHSUNDEEP
sqloracle-sqldeveloper
BlueRose
BlueRose

Reputation: 41

How to format currency in Oracle SQL Developer 19.4.0?

I'm using SQL developer in one of my database classes and I have homework where certain formatting is needed. When I use the commands given by the professor on my SQL Developer I get an error

Here are the commands: 

col price format $99,990.99
col quoted_price format $99,990.99
col balance format $99,990.99
col credit_limit format 99,990.99

The errors I get are:

SP2-0246: Illegal FORMAT string "$99,"
SP2-0246: Illegal FORMAT string "$99,"
SP2-0246: Illegal FORMAT string "$99,"
SP2-0246: Illegal FORMAT string "99,"

When I run these same commands on the lab computer it works but the lab uses an outdated version of SQL developer. It's version 4.1.1

Would appreciate any help

The homework is asks us to input a command that would display query results for example a question would be "x" situation

My command will be:

SELECT item_num, price, description, balance
from item;

The query output below will have $ if needed and will have the commas and periods.

Upvotes: 2

Views: 2218

Answers (1)

Katha patel
Katha patel

Reputation: 131

If you are storing salary as Number, then you can use to_char(column,'$9,999.99');

SQL> CREATE TABLE EMP
  2  (EMPNO NUMBER(4) NOT NULL,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2),
  9   DEPTNO NUMBER(2)
 10  );

SQL> INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);

SQL> select ename, to_char( sal, '$9,999.99' ) "Salary" from emp;

ENAME      Salary
---------- ----------
SMITH         $800.00

Upvotes: 2

Related Questions