I'm trying to insert it into a table called bill but I keep getting errors no matter how much I what I do.

Below is the code that I'm using, when I tried replacing the total with NULL (for the sake of testing), it went through and inserted into the table. When I try to use any number no matter how long I keep getting "ORA-01722: invalid number"

insert into Bill ( Bill_code, Total, Start_date, End_date, Customer_ID )
VALUES('1373','5200','02-03-2022','02-05-2021','6724');

The number datatype has a precision of 16 with a scale of 2 and it's nullable.

CodePudding user response：

ORA-01722: invalid number clearly states that this is an Oracle error; what is that postgres tag doing here?

In Oracle - as far as I managed to recreate your test case (as you didn't post table description), it works correctly.

You should really use proper datatypes:

• as long as bill_code or customer_id may be strings,
• total certainly isn't (you said it is a number), while
• dates should be exactly that - DATE datatype
  • in that case, use date literal (as my example shows) or to_date function with appropriate format model. Don't rely on implicit datatype conversion. What is 02-03-2022? Is it 2nd of March or 3rd of February? Could be both.

So:

SQL> CREATE TABLE bill
  2  (
  3     bill_code     VARCHAR2 (10),
  4     total         NUMBER (16, 2),
  5     start_date    DATE,
  6     end_date      DATE,
  7     customer_id   VARCHAR2 (10)
  8  );

Table created.

SQL> INSERT INTO Bill (Bill_code,
  2                    Total,
  3                    Start_date,
  4                    End_date,
  5                    Customer_ID)
  6       VALUES ('1373',
  7               5200,
  8               DATE '2022-03-02',
  9               DATE '2021-05-02',
 10               '6724');

1 row created.

SQL>

CodePudding user response：

If your column type is not varchar or any character based one, do not use quotes for inserting numbers.

insert into Bill ( Bill_code, Total, Start_date, End_date, Customer_ID ) VALUES('1373',5200,'02-03-2022','02-05-2021','6724');