ORA-00984: column not allowed here

This error message indicates that you are trying to insert or update any column of the table with which is not allowed,


e.g.

CREATE TABLE temp_student
  (
    stu_roll_no NUMBER(4),
    stu_name    VARCHAR2(10),
    stu_email   VARCHAR2(15),
    stu_dob DATE,
    stu_city VARCHAR2(20)
  );

INSERT INTO temp_student VALUES (3,Bhajan,'bh','04-081982','Gurgaon-India');
-- Above query will gives you this error., Because you are trying to add "Bhajan" as name of student without single quote (which is valid sequence of characters, But we have missed single quote arround this. Which is required every time you are tried to use VARCHAR, VARCHAR2, DATE etc.)

To make it work, I have changed above query as: added single quote arround "Bhajan".
INSERT INTO temp_student VALUES (3,'Bhajan','bh','04-081982','Gurgaon-India');

How to get data for a given range for TIMESTAMP in oracle 11g

Problem: To find data for a given two dates, whereas in database columns are TIMESTAMP.

Environment: Datbase 11g.

Use Case: In database, For a table datatype of a coulmn is TIMESTAMP instead of date. But, user need to find recodes based on given dates (Where time is not required).

Suppose for a Online Book Order system, we have a table to store order of books with following table struture.

CREATE TABLE book_order
  (
    order_num NUMBER,
    book_id   NUMBER,
    order_date TIMESTAMP(6)
  );

Insert few records:

INSERT INTO book_order VALUES (1,3001, '05/07/12 12:15:32');
INSERT INTO book_order VALUES (2,3001, '26/06/12 12:17:18');
INSERT INTO book_order VALUES (3,1021, '26/06/12 12:05:53');
INSERT INTO book_order VALUES (4,3051, '08/06/12 11:07:44');
INSERT INTO book_order VALUES (5,6703, '01/06/12 12:54:57');
INSERT INTO book_order VALUES (6,3401, '01/06/12 11:20:43');
INSERT INTO book_order VALUES (7,1606, '29/05/12 10:42:41');
INSERT INTO book_order VALUES (8,2986, '25/05/12 16:19:45');
INSERT INTO book_order VALUES (9,2986, '05/07/12 12:31:45');

COMMIT;

SELECT * FROM book_order;
-- 9 Rows Selected

SELECT * FROM book_order WHERE order_date BETWEEN '25/05/12' AND '05/07/12';
-- 7 rows selected (Expeted 9 Rows)

select * from book_order
WHERE order_date > '25/05/12'
  AND order_date   < '05/07/12';
-- 7 rows selected (Expeted 9 Rows)

SELECT *
FROM book_order
WHERE order_date BETWEEN to_date('25/05/12','dd/mm/yy') AND to_date('05/07/12','dd/mm/yy');
-- 7 rows selected (Expeted 9 Rows)

SELECT *
FROM book_order
WHERE to_date(order_date,'dd/mm/yy') BETWEEN to_date('25/05/12','dd/mm/yy') AND to_date('05/07/12','dd/mm/yy');
-- Error Message : ORA-01830: date format picture ends before converting entire input string

Here is our solution for which we are looking for:
SELECT *
FROM book_order
WHERE to_date(TO_CHAR(order_date,'dd/mm/yy')) BETWEEN to_date('25/05/12','dd/mm/yy') AND to_date('05/07/12','dd/mm/yy');
-- 9 rows selected

DROP TABLE book_order;