Data services: erroneous expression

[Former Member]

We want to truncate tables with a user other than the table owner. Latter has a package providing a procedure to do so.

    procedure TRUNCATE_TABLE(
        I_TABLE_NAME user_objects.object_name%type,
        I_CASCADE boolean default false
    );

We imported the package into the datastore but it complains about datatype impossible to import. As it happens this is fancy boolean. We try to use it anyway as this has a default in a script running in a batch job before the dataflow.

We can select the procedure from the list in the select functions dialog (though not in the smart editor??) but get following parameter list.

We only entered the I_TABLE_NAME getting

DS_DWH_ETL.IL."PKG_OBJECT_MANAGEMENT.TRUNCATE_TABLE"(HE#PATIENT, AL_UNSPECIFIED_PARAM, AL_UNSPECIFIED_PARAM)

in the script editor. Executing this returns mentionned error. Also following adaptions do not alter the result.

DS_DWH_ETL.IL."PKG_OBJECT_MANAGEMENT.TRUNCATE_TABLE"(HE#PATIENT)

DS_DWH_ETL.IL."PKG_OBJECT_MANAGEMENT.TRUNCATE_TABLE"("HE#PATIENT")

DS_DWH_ETL.IL."PKG_OBJECT_MANAGEMENT.TRUNCATE_TABLE"('HE#PATIENT', false)

What are we missing?

Btw, we also tried the SQL function with the same result. And we also tried it with a non-fancy named inexisting table hoping for a table or view does not exist error to no avail.

Kind regards Thiemo

[Former Member]

It would if the data store user had the database rights to do so. Our concept is to seperate ownership of the database object from the manipulation of the data. That is, we use an ETL user to manipulate the data in the tables owned by IL. As Oracle's policy on truncate right implies the right to drop it, user ETL has not got it. (We feel this is badly designed but we cannot help it). We therefore created this workaround by package.

To get a better feeling about what we are doing wrong, we created a script containing only following.

sql('DS_DWH_ETL', 'truncate table LZ.HE#PAITENT');

However, the error basically persists:

5616    7496    VAL-030159    22/03/2018 11:45:21    Found erroneous expression <>. Check its syntax and fix this expression.

[former_member187605]

Can you try a table without a hash in its name?