Anonymouse SQL block in after SQL of Oracle stage

Ragunathan Gunasekaran · Mon Jan 05, 2009 1:01 pm

Hi ,
I am using an anonymouse PL/SQL code block . The block is executing in SQL * plus client but when i use the same in After SQL of the oracle stage of datastage its throwing Oracle compilation errors any clues.

Is it not possible to execute the Anonymous pl/sql code in the after SQL ... Please help

chulett · Post by **chulett** » Mon Jan 05, 2009 1:19 pm

No, not possible - PL/SQL is a different animal from straight SQL. You should be able to use the Store Procedure stage as long as you uncheck the 'generate procedure call' option, depending on exactly what your code is trying to accomplish.

You may be able to use the ODBC stage for that, don't recall.

Ragunathan Gunasekaran · Mon Jan 05, 2009 1:29 pm

Hi ,
The datastage passes them to the Pl/sql engine for parsing , i am getting the PLS-00103 error which is thrown by the PL/SQL Engine

I have to run a analyze statement for a partition suffixed by timestamp .. any w3ay could i do this in the after SQL . The partition name is something like partition_name_20080101( Partitionnameyyyymmdd)

Any clue on this please

chulett · Post by **chulett** » Mon Jan 05, 2009 1:45 pm

DBMS_STATS package? Have you tried just "call DBMS_STATS.gather_table_stats(...)" there? Still better to use the SP stage for that IMHO.