ORA-00900: invalid SQL statement

[NSIT-DVP-01]

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 !

[prasson_ibm]

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.

[chulett]

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.

[prasannakumarkk]

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.