0

I tried to insert into an Oracle table by the following instruction:

insert into orders values (&order_id, &order_date, '&customer_id', &amount);

where desc orders is as follows:

Name        Null Type      
----------- ---- --------- 
ORDER_ID         NUMBER(4) 
ORDER_DATE       DATE      
CUSTOMER_ID      NUMBER(4) 
AMOUNT           NUMBER(5)

I tried to insert: order_id=110, order_date=12-march-2013, customer_id=115 and amount=1100

This is the error message I am getting:

Error starting at line 26 in command:
insert into orders values (110, 12-march-2013, 115, 1100)
Error at Command Line:26 Column:35
Error report:
SQL Error: ORA-00984: column not allowed here
00984. 00000 -  "column not allowed here"
*Cause:    
*Action:
Mistu4u
  • 5,132
  • 15
  • 53
  • 91
  • Why do have single quotes here : '&customer_id' ? – Lokesh Sep 22 '13 at 05:18
  • @Lokesh, I removed the single quotes and tried to insert the above values, however the error still persists! – Mistu4u Sep 22 '13 at 05:22
  • On which sql client are you executing this sql? – Lokesh Sep 22 '13 at 05:25
  • @Lokesh, Oracle SQL developer. By the way, suddenly it occured to my mind that the reason might be because the `customer_id` field is a foreign key to another table. This might be the reaons. – Mistu4u Sep 22 '13 at 05:27
  • Nah, checked it. This is not the reason. What a shitty problem :/ – Mistu4u Sep 22 '13 at 05:30
  • Can you paste exact error with error code? – Lokesh Sep 22 '13 at 05:31
  • @Lokesh, Check the edit. Updated the error code. – Mistu4u Sep 22 '13 at 05:34
  • 1
    Try adding quotes on date field. The date format may be '12-Mar-2013'. – laksys Sep 22 '13 at 05:34
  • @laksys, It solved my issue. Thanks. By the way, is not there any format for inserting date by default? Or in any format I can insert? – Mistu4u Sep 22 '13 at 05:37
  • There is _no_ date format @Mistu4u. I'm willing to bet any money that you've already got some incorrect dates in your database. Use ANSI date literals `date 2013-09-22` or the `to_date` function to insert dates. See most of my answer here, the OP had the same problem as you do: http://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql/10178346#10178346 – Ben Sep 22 '13 at 09:12

1 Answers1

1

this code works

insert into orders values (200, '12-march-2013', 100, 1100)
Viji
  • 2,629
  • 1
  • 18
  • 30
  • This is incorrect; it will only work if Oracle's feeling clever enough to translate that string into a date... and then that'll be based on your NLS_DATE_FORMAT. – Ben Sep 22 '13 at 09:12