RE: Riddle me this, Batman. Why get an ora-922 when creating a partition?
- From: "Bellows, Bambi (Comsys)" <bbel5@xxxxxxxxxxxx>
- To: "Harel Safra" <harel.safra@xxxxxxxxx>
- Date: Wed, 5 May 2010 12:26:25 -0500
Ahhhhh... there *were* the wrong number of parenthesis!
Gotham will sleep well tonight! Thank you, Harel!
________________________________
From: Harel Safra [mailto:harel.safra@xxxxxxxxx]
Sent: Wednesday, May 05, 2010 12:16 PM
To: Bellows, Bambi (Comsys)
Cc: oracle-l L
Subject: Re: Riddle me this, Batman. Why get an ora-922 when creating a
partition?
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:
