Re: ALTER TABLE some table ADD PARTITION partition_name VALUES less than (TO_DATE('variablex', 'DD/MM/YYYY')) ;

jagadishk wrote:
> I am writing a plsql code
> DECLARE
> sys_date character varying(500);
> variablex character varying(500);
> sys_date:='select to_char(now,''YYYYMMDD'')';
> execute immediate sys_date into variablex;
> dbms_output.put_line(variablex);
> begin
> ALTER TABLE some table ADD PARTITION partition_name VALUES less than
> (TO_DATE('variablex', 'DD/MM/YYYY')) ;
>
> I want to add a partition based on sysdate and each day a table would be
> created. So i am writing the code as above
>
> It throws an error
> ERROR: date format not recognized
> CONTEXT: SQL statement "ALTER TABLE some table ADD PARTITION partition_name
> VALUES less than (TO_DATE('variablex', 'DD/MM/YYYY'))"
> edb-spl function inline_code_block line 44 at SQL statement

Remove the single quotes around "variablex".

I believe that you need dynamic SQL to perform an ALTER TABLE.

Yours,
Laurenz Albe