DSXchange

Posted: **Thu Mar 14, 2013 4:49 am**

Hello,

I have the exact same problem as this with DataStage Server 8.1

I have tried to execute the following code from the "Before" tab of the SQL statement in the Oracle Stage:

Code: Select all

exec dbms_stats.gather_table_stats(ownname => 'MA_ODS', tabname => 'ODS_ITEM', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', degree => 8, granularity => 'ALL', cascade => TRUE);

I have also tried with:

Code: Select all

begin
dbms_stats.gather_table_stats[...];
end;

and

Code: Select all

CALL dbms_stats.gather_table_stats[...];

(I have tried with and without simicolon, with simple/double quotes etc.)

But I get the same error: ORA-00900: invalid SQL statement

Please note that I have no problem running this with SQL Developer on the same database (Oracle 10g) and with the same user.

Looks like DataStage does not support this ? But isn't DataStage using SQL plus to execute what you enter in the Before/After SQL statement ?

An other project in the company had the same problem, and they used an insert statement in the "before SQL" to execute a trigger instead. But we can't do the same, because we had a no go from our Database admins to use this method.

Thanks for your help !

Posted: **Thu Mar 14, 2013 5:50 am**

Hi,
Just few days before,i have implemented stored proc in oracle connector stage in my before sql statements,you can go throgh the link below:-

viewtopic.php?t=149119

And its working fine for me.

Posted: **Thu Mar 14, 2013 7:05 am**

Please always start a new topic when you have a problem of your own, even when it's the "exact same problem". If you really feel the need, link back to the other post as I've done in your post.

Thanks.

Posted: **Thu Mar 14, 2013 9:25 am**

Did you try executing this in SQL Plus.
One thing we can try
Use positional parameter instead of =>
Remove the package constant and hard code it.