Unable to execute Before SQL statement

kiran kumar · Post by **kiran kumar** » Mon Apr 09, 2012 6:02 pm

Hi,

I tried to execute a Before SQL statement in one of my DS Job (v8.5). during execution it is throwing the error.

"OC_TD_MeterGroup_STG: The OCI function executeDirect returned status -1. Error code: 24,374, Error message: ORA-24374: define not done before fetch or execute and fetch. (CC_OraUtils::handleBeforeAfterSQL, file CC_OraUtils.cpp, line 4,633)"

I tried to search the forum, but no joy.

Can you please help me with this.

Thank You!

SURA · Post by **SURA** » Mon Apr 09, 2012 8:22 pm

Because it s an oracle error might be the reason. Are you trying to execute procedure / sql query?

DS User

qt_ky · Post by **qt_ky** » Mon Apr 09, 2012 8:57 pm

What statement is in the Before SQL?

kiran kumar · Post by **kiran kumar** » Mon Apr 09, 2012 10:08 pm

Its a general SQL query.

SELECT BATCH_NUMBER FROM CON.BT_GROUP

chulett · Post by **chulett** » Mon Apr 09, 2012 10:17 pm

You can't do things like "SELECT" before sql, where would the results go? You need to do "before things" like alter the session or truncate a table, to name a couple of options off the top of my head.

Forget the before SQL error and tell us what you are trying to accomplish instead, why you need this batch number, how it will be used in / by the job.

kiran kumar · Post by **kiran kumar** » Mon Apr 09, 2012 11:25 pm

Thank you, Craig.

I'm trying to extract the max(Batch_Number) from a table. So, that I can use this value and increment by 1 every time i run the job.

I tried using a routine (using basic transformer) that did not work.
used, jobruntime as well...no Joy.

Can you please share any thoughts on this.

Thanks.

austin_316 · Post by **austin_316** » Tue Apr 10, 2012 3:34 am

Can you please specify the exact meaning of

Code: Select all

I tried using a routine (using basic transformer) that did not work. 
used, jobruntime as well...no Joy.

What actually did not work?
This can be done using before job sub routine. You can fetch the value and pass it as a paramter to the job.

qt_ky · Post by **qt_ky** » Tue Apr 10, 2012 4:49 am

Try moving that select statement into the select statement for the stage (not a generated SQL statement), or moving it into a select statement in a separate Oracle stage.

chulett · Post by **chulett** » Tue Apr 10, 2012 9:15 am

austin_316 wrote:This can be done using before job sub routine. You can fetch the value and pass it as a paramter to the job.

No, it can't.

jwiles · Post by **jwiles** » Tue Apr 10, 2012 9:39 am

You could use a Job Sequence Execute Command Activity to call a shell script which runs the query against the database, retrieve the value and pass it as a parameter to your job.

Regards,

chulett · Post by **chulett** » Tue Apr 10, 2012 9:46 am

Yup, it really depends on what you need to do with this batch number other than simply increment it.

kiran kumar · Post by **kiran kumar** » Thu Apr 12, 2012 6:36 pm

I am trying to capture the records which gets rejected for every job/batch run.

qt_ky · Post by **qt_ky** » Thu Apr 12, 2012 7:08 pm

That sounds like a new topic to me. Did you try any of the above suggestions for this topic?

DSXchange