How to achieve TRUNCATE and DROP STORAGE functioanlity in HANA procedures

[Former Member]

Hello All,

What is the equivalent statement for the below statement in HANA while writing procedure...

   EXECUTE IMMEDIATE 'TRUNCATE TABLE <TABLE NAME>';

I am getting the below error:

SAP DBTech JDBC: [7]: feature not supported: GenericFailure exception: feature not supported: cannot perform truncate statement when ddl autocommit is off

Thanks

[justin_molenaur2]

Truncate is not an allowable operation in SQLScript. I have hit this issue before and have had to resort to DELETE and insert, which is quite a bit slower.

Another option is to actually delete the table, create the table and then insert, but you have to be pretty careful if it's used in other views as the concatenated columns need to be present or the models become invalidated.

Yet another option depending on how you call the scripts, is to issue a straight SQL command for TRUNCATE just prior to calling the stored procedure.

Regards,

Justin

[former_member182302]

Hai there,

Why do you wanted to use Truncate? If truncate is used you cannot rollback the data if required. Please find the below help link:

https://help.sap.com/hana/html/sql_truncate_table.html

You can rather use DELETE statement  like this:

CREATE PROCEDURE SP_TRUNCATE()
LANGUAGE SQLSCRIPT  AS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM  LOG';
END;
CALL SP_TRUNCATE();

Even am getting the same error while trying to use TRUNCATE. Even i called the commit statement explicitly in the procedure still it is failing though.

Should see if any body else gives a proper answer to that error.

Regards,

Krishna Tangudu