Job server aborting with error ORA-24374

MaxRevan · Post by **MaxRevan** » Tue Apr 07, 2009 7:19 am

I'm writing this topic cause i didn't find any already.
I have a job server in which i use one Oracle OCI to query data to be looked up with an hash file. The custom query in this OCI works fine.
The hash file is created and loaded using another OCI having a custom query to a different DB than the other one. Both DB are on the same server.
The second query, running the job or viewing data from Designer, returns the same error : ORA-24374: define not done before fetch or execute and fetch.
Oracle DB version on the ETL server, same as DB server, is 9.x
I tried to build a new job but the error persists.
I tried to substitute the OCI that givess the error, but it still persists.
I do not know what to do...
Thanks in advance...

chulett · Post by **chulett** » Tue Apr 07, 2009 7:37 am

Confusing. This second OCI stage that generates your error, what exactly is it doing - selecting from a source or writing to a target? My assumption is that your error is caused by your use of a 'custom query' without understanding the rules that the OCI stage enforces. Always best to let the stage generate the SQL if at all possible, especially until you learn all the nuances of the Oracle Call Interface rules. So...

Describe what the stage is doing. Post the sql you are using. Confirm for us the number and order of the columns defined in the stage. Let's start with that. The more details the better!

fabtort · Post by **fabtort** » Tue Apr 07, 2009 9:09 am

I'm MaxRevan. I'm using my friend's account cause i'm having problems with mine

The job has several parameters. Those used within the OCI are correctly used and set.
Custom query of the OCI is

SELECT /*+ PARALLEL(a,4) */
COD_PORTFOLIO,
COD_IMSI,
FLG_PROSPECT,
FLG_USIM_SCIOLTA
FROM
#DDSOWNERE#.TF_NDM_PORTFOLIO a
where FLG_PROSPECT='Y' and FLG_USIM_SCIOLTA='Y'
#DDSOWNERE# is a job parameter. The value corresponds to the owner of the table and it is correctly set.
Columns defined are, obviously, four : COD_PORTFOLIO, COD_IMSI, FLG_PROSPECT and FLG_USIM_SCIOLTA. SQL types corresponds to oracle ones. If i set "Generate SELECT..." letting the stage generate the statement to execute and i press the "View Data" button, the problem still persists. This OCI READS data and WRITES to an hash file (used for lookup later in the same job).
What else ? I'd post a screenshot of the job if possible but i guess i have to upload it somewhere...

Just to let you know, i'm using Datastage since version 4 so i sense to know it a bit

Thanks again in advance.

chulett · Post by **chulett** » Tue Apr 07, 2009 9:29 am

Ok. Nothing about that needs to be 'custom' but let's see... first suggestion would be to add the alias you defined to all columns and see if that helps:

a.COD_PORTFOLIO,
a.COD_IMSI,

Etc. All field names in the query.

ps. Nothing is obvious from the other side of the glass.

fabtort · Post by **fabtort** » Tue Apr 07, 2009 9:49 am

Test done. Nothing changes

Query changed to
SELECT /*+ PARALLEL(a,4) */
a.COD_PORTFOLIO,
a.COD_IMSI,
a.FLG_PROSPECT,
a.FLG_USIM_SCIOLTA
FROM
#DDSOWNERE#.TF_NDM_PORTFOLIO a
where a.FLG_PROSPECT='Y' and a.FLG_USIM_SCIOLTA='Y'

Challenging problem, isn't it ?
I solved many other worst problems within DS but this one is really awesome !!!

chulett · Post by **chulett** » Tue Apr 07, 2009 9:58 am

Bummer, that would have been too easy I guess. For grins, what are the Oracle data types for the four columns you are selecting? Also, what version is the Oracle database and what client version are you using to connect to it? All four digits, please, like 9.2.0.5 for example.

mfavero · Post by **mfavero** » Tue Apr 07, 2009 9:59 am

try getting rid of the comments /*+ PARALLEL(a,4) */

fabtort · Post by **fabtort** » Tue Apr 07, 2009 10:16 am

*********************
Oracle DB server version

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

*********************
Oracle client version on ETL server : 9.2.0.8
*********************
Datastage Server Version : 7.5.1.A
*********************
COD_PORTFOLIO VARCHAR2(100)
COD_IMSI VARCHAR2(100)
FLG_PROSPECT VARCHAR2(3)
FLG_USIM_SCIOLTA VARCHAR2(3)

Yes, it would have been so easy.
Hint PARALLEL removed. Same error.

fabtort · Post by **fabtort** » Tue Apr 07, 2009 10:24 am

I solved the problem.
Unbelievable how i found the solution...just choosing a NLS set in the OCI.
I changed it from Project Default (NONE) to ISO8859-1 and i read data

Why does it has to be like this on this OCI and not the same on the other ?
In truth i should check database NLS configuration, but...

Now the new question is : what if i change the project default (by Administrator) ? Should i expect other kind of errors on other jobs ?

In any case thanks to all of you for your suggestions...

chulett · Post by **chulett** » Tue Apr 07, 2009 12:07 pm

fabtort wrote:In truth i should check database NLS configuration

Yes, you should. Good to know when any characterset conversions are involved.

fabtort also wrote:Now the new question is : what if i change the project default (by Administrator) ? Should i expect other kind of errors on other jobs ?

Hard to say, but I could see that becoming an issue. Override it only in the jobs that need it by setting $NLS_LANG (and perhaps $LC_CTYPE) properly there.