SQL data type to use when inserting money

Question

I am using an Oracle SQL database and I need to insert a monetary value (salary) as part of the string. For some strange reason, the money team is not working, are there any alternatives that will work with this?

Data Entry Format: £ 00,000,000

CREATE TABLE staff (staffno CHAR(6) NOT NULL , staffsurname VARCHAR(8) NOT NULL , staffforename VARCHAR(7) NOT NULL , salary MONEY NOT NULL , PRIMARY KEY (staffno) ); 

Answer 1

Look at this line

 salary MONEY NOT NULL 

There is no existing data type of money.

If you are looking for something similar to the SQL-Server small money type, you want to use Number(10,4) and then format the number.

You can format a number using the to_char function

 select to_char(yourColumn, '$99,999.99') from yourTable where someCondition 

Answer 2

The "strange" reason is simple: No MONEY data type .

The data type most suitable for monetary values ​​will be NUMBER (using the appropriate scale). Since it is a decimal floating-point type, it is better suited for monetary values ​​than the binary floating-point BINARY_FLOAT and BINARY_DOUBLE .

Note that you still need to £00,000.000 input string of £00,000.000 at your front end and send it as a numeric value to the back end.