Re: Riddle me this, Batman. Why get an ora-922 when creating a partition?

  • From: Harel Safra <harel.safra@xxxxxxxxx>
  • To: bbel5@xxxxxxxxxxxx
  • Date: Wed, 05 May 2010 20:15:38 +0300

You should have opening paranthesis "(" before the partitions and closing parenthesis ")" after. 

From the docs:

CREATE TABLE sales_range
(salesman_id  NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount  NUMBER(10),
sales_date    DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')), PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')), PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')), 
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);

Harel Safra

On 05/05/2010 20:04, Bellows, Bambi (Comsys) wrote:
Let's create a partitioned table, and partition on date. Let's make it as easy as can be. 

SQL>  CREATE TABLE JUNK1

  2  (

  3  column_info_int       number          NOT NULL,

  4  column_info_date    date               NOT NULL

  5  )

  6  PARTITION BY RANGE (column_info_date)
7* PARTITION cid20thcentury VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')) 

Now, let's run it.

SQL> /
PARTITION cid20thcentury VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')) 

*

ERROR at line 7:

ORA-00922: missing or invalid option
Now, let's test that predicate separately... just to make sure there are no hidden characters or a bad number of parenthesis... 

SQL> select (TO_DATE('2000-01-01', 'YYYY-MM-DD')) from dual;

(TO_DATE(

---------

01-JAN-00

OK... version info...
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options
I see that 922 has been kind of a bugaboo, and there's the range/hash thing, but, I don't see how that applies here. 

What say the OraWizards?  Does the Riddler win the day?

Bambi.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2010