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