execute immediate with to_date
As part of a function which is passed 3 variables as VARCHAR2 am using:
execute immediate 'alter table tablename add partition '||part_name||' values less than (to_date('||pdate||',''DD-MON-YYYY''))'||'TABLESPACE '||ptsname ;
which gives error: ORA-14019 partition bound element must be one of: string,datetime or number
when using:
execute immediate 'alter table tablename add partition '||part_name||' values less than (to_date(''||pdate||'',''DD-MON-YYYY''))'||'TABLESPACE '||ptsname ;
this gives error: ORA-01858 non-numeric character was found where a numeric was expected
The execute immediate works with hard-coded '01-DEC-2002' instead of pdate, but can't seem to pass in a value.
Any suggestions would be appreciated. Thanks.