SQL & PL/SQL

Hi all,

I have an issue I need help with. I have created a script for an automated partition create on a monthly basis. It creates a monthly partition containing all dates within the respective month. The script is essentially this:

-----
ALTER TABLE SCHEMA.TABLE
ADD PARTITION &&1
VALUES LESS THAN (to_number(to_char(to_date('&&2', 'DD-MON-YY'), 'YYYYMMDD')))
TABLESPACE LARGE_DATA94 COMPRESS;
-----

I continually get this error message "ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE"

The variable &&2 is passing in character data for the first of the month (E.G. '01-SEP-12'). &&1 passes character data for the month in MONYY ('AUG12') I can run this query:

-----
select
(to_number(to_char(to_date('&&2', 'DD-MON-YY'), 'YYYYMMDD')))
from dual;
-----

With the output of 20120901. I cannot understand why I am able to run this partition create statement by hardcoding 20120901 but passing it in as a variable I receive the error. Note that I am not having problems with the &&1 variable. If anyone has any ideas please let me know. Thanks!

Edited by: 962466 on Oct 1, 2012 8:42 AM
Grammar