Question Re: Data services: erroneous expression in Technology Q&A

Data services: erroneous expression

Former Member — Wed, 21 Mar 2018 15:32:22 GMT

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

Re: Data services: erroneous expression

former_member187605 — Thu, 22 Mar 2018 08:24:30 GMT

Why do you want to call a procedure for truncating a table?

sql ('<datastore name>','truncate table OWNER.TABLE') will do the trick.

Re: Data services: erroneous expression

Former Member — Thu, 22 Mar 2018 18:48:21 GMT

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.

Re: Data services: erroneous expression

former_member187605 — Fri, 23 Mar 2018 09:03:33 GMT

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

Re: Data services: erroneous expression

Former Member — Fri, 23 Mar 2018 11:10:33 GMT

The culprit was an empty parameter hand over into the data flow containing the script. All works as expected now.