SAP HANA - How to run alter table statement in HANA procedure?

[Former Member]

I am trying to run alter table statement in a procedure. HANA gives error saying

SAP DBTech JDBC: [257] (at 1338): sql syntax error: ALTER TABLE is not allowed in SQLScript: line 36 col 8 (at pos 1338)

How to run alter table statements in procedure?

Thanks,

Suren.

[RichHeilman · Accepted solution]

Although I can not recommend doing this, you can achieve your requirement by using dynamic SQL to call the ALTER statement.

Cheers,

Rich Heilman

[Former Member · Accepted solution]

Hi Rich Heilman,

Thanks for your response.  I have tried with dynamic SQL. I am trying to add partitions to a non portioned table.

EXECUTE IMMEDIATE 'ALTER TABLE ' || :SCHEMA_NAME || '.TARGET_TABLE PARTITION BY RANGE (TARGET_TYPE_ID) (PARTITION VALUE = 1, PARTITION VALUE = 2, PARTITION VALUE = 3, PARTITION VALUE = 4, PARTITION OTHERS)';

Execution fails with error

Could not execute 'CALL PARTITION_TARGET_TABLE('SUREN_TEST')' in 1.160 seconds .

[129]: transaction rolled back by an internal error:  [129] "SUREN_TEST"."PARTITION_TARGET_TABLE": line 53 col 3 (at pos 2173): [129] (range 3)

Any reasons for this error?

Thanks,

Suren.

[Former Member]

TRY -

Approach 1

temp_var := 'ALTER TABLE tablename ADD ("COLUMN_NAME" INTEGER)';

EXECUTE IMMEDIATE (:temp_var );

Approach 2

EXECUTE IMMEDIATE 'ALTER TABLE tablename ADD ("COLUMN_NAME" INTEGER)';