Oracle OCI Connect Error

cdrk823 · Post by **cdrk823** » Fri Apr 29, 2005 2:00 pm

Hi Everyone,

I'm having some problems connecting to our database server through my routine.

I get the following error:

************************************************************
Error Text: [DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Data source name not found and no default driver specified
Error Status: 0
************************************************************

This is with the assumption that the DSN for Oracle is the same as one of the entries in tnsnames.ora (which I have used in the code).

Was I correct in using that name?

Thanks!

Cedrick

ray.wurlod · Post by **ray.wurlod** » Fri Apr 29, 2005 2:54 pm

Welcome aboard! :D

You have a little more setting up to do. You are apparently using the ODBC stage, rather than the Oracle OCI stage; the latter would use the name in tnsnames.ora and not complain; certainly it wouldn't give an error with [ODBC] (the ODBC driver manager) in it!

In the DataStage engine directory, edit the .odbc.ini file. Use the appropriate Oracle example there as your guide to create a data source name with appropriate library name and other information.

In the DataStage project directory, edit the uvodbc.config file. At the very list you need the data source name and "DBMSTYPE = ODBC" (and there must be space characters either side of the "=".

cdrk823 · Post by **cdrk823** » Fri Apr 29, 2005 3:24 pm

Thanks for the help. I didn't realize that it would still need this.

I have modified uvodbc.config for the project, but not .odbc.ini. Would I need to add an entry for the data source name or would I just have to modify the <Oracle> entry there? (i suspect I would have to create one).

ray.wurlod · Post by **ray.wurlod** » Fri Apr 29, 2005 5:20 pm

You can do either, but I'd recommend creating a new entry so that the original remains intact. In any case, as with any "system" file, make sure you've got a backup copy before changing it, and document any changes you make (so that someone else doesn't reverse them later!).

Or you can use an Oracle OCI stage, and thereby employ the OCI software directly.

chulett · Post by **chulett** » Fri Apr 29, 2005 5:45 pm

ray.wurlod wrote:Or you can use an Oracle OCI stage, and thereby employ the OCI software directly.

What he said.

Don't use the ODBC stage unless you've really really got no other choice.

cdrk823 · Post by **cdrk823** » Sat Apr 30, 2005 9:51 am

chulett wrote:
ray.wurlod wrote:Or you can use an Oracle OCI stage, and thereby employ the OCI software directly.
What he said.

Don't use the ODBC stage unless you've really really got no other choice.

:D

I'm sorry if do not quite understand... but does this mean that if I use this routine in an OCI stage, I do not have to worry about this?

Oh man.. I'm confused..

chulett · Post by **chulett** » Sat Apr 30, 2005 2:01 pm

Sorry, didn't mean to confuse. During discussion of the problems of trying to get a routine to connect to Oracle, the use of an ODBC stage came up. And then an 'or' - or use an OCI stage. My only point was to encourage the use of a native stage, like an OCI stage, over the generic ODBC stage. Unless you've got no choice but to use an ODBC stage.

This has nothing to do with getting your routine to work... unless the work you are doing in the routine could be more 'properly' handled by a job instead. BTW, what exactly does your routine do? Or perhaps what do you mean by 'routine' - literally a custom DataStage routine or something else?

cdrk823 · Post by **cdrk823** » Mon May 02, 2005 7:53 am

I was hoping to get the field values for this SQL statement (or something like it). The thing is, the 'TEXT' within the SQL statement is part of an input file (seq file), and I do not know how I would pass this value to an OCI stage whose SQL is as shown below. So I thought of using a routine, and return a string that would be much easier to parse.

** SQL STATEMENT **

SELECT VNDR_NAME_SHRT_USR, MAX(VNDR_NAME_SEQ_NUM)
FROM PS_VENDOR
WHERE VNDR_NAME_SHRT_USR IN
(SELECT 'TEXT' FROM DUMMY_TBL
UNION
SELECT RPAD(SUBSTR('TEXT',1,10-RN),10,'x')
FROM (SELECT ROWNUM RN FROM PS_VENDOR WHERE ROWNUM <= 10)
GROUP BY VNDR_NAME_SHRT_USR
HAVING MAX(VNDR_NAME_SEQ_NUM) >0
AND MAX(VNDR_NAME_SEQ_NUM) < 990

** END **

Now I know that there's an easier way than to slap this SQL statement there, but I would have to (a) loop through a select statement a couple of times, or (b) use a much simpler SQL statement but a slightly more complex routine

For option (a), I don't know if it's possible without using a routine
For option (b), I could do this, but I couldn't connect to our database (see above).

Thanks guys, this little discussion of ours is teaching me a lot.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon May 02, 2005 12:36 pm

You do not need a routine to perform an SQL. You can do it in a DataStage job. Also pass the 'TEXT' values as parameter. Search for 'parameters' in this forum for more info.

cdrk823 · Post by **cdrk823** » Mon May 02, 2005 2:48 pm

Thanks for that!

Anyway, I'm still having problems connecting to Oracle through the routine I've made. Says it cannot resolve TNS name, I'm gonna have to dig in deeper to solve this.

Thanks for all the help and advise so far.

elavenil · Post by **elavenil** » Tue May 03, 2005 9:46 am

Hi,

Create the routine code as an Oracle Stored procedure and this procedure can be called from before/after SQL of the Oracle OCI stage. Then you do not need to create ODBC DSN and other entries. This will work as long as you are able to connect to Oracle DB using OCI stage.

Hope this would help.

Regards
Saravanan

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue May 03, 2005 10:51 am

You can try to print the sql statement in sqlplus (on DS Server) and run the same. Did you mention a specific Oracle SID or is there any default set for this?

cdrk823 · Post by **cdrk823** » Tue May 03, 2005 3:56 pm

elavenil wrote:Hi,

Create the routine code as an Oracle Stored procedure and this procedure can be called from before/after SQL of the Oracle OCI stage. Then you do not need to create ODBC DSN and other entries. This will work as long as you are able to connect to Oracle DB using OCI stage.

Hope this would help.

Regards
Saravanan

Good tip. Thanks for the advice!

cdrk823 · Post by **cdrk823** » Tue May 03, 2005 4:02 pm

Sainath.Srinivasan wrote:You can try to print the sql statement in sqlplus (on DS Server) and run the same. Did you mention a specific Oracle SID or is there any default set for this?

No I have not specify an SID nor is there a default. I'm wondering.. maybe the syntax I'm using is wrong. I had pulled my code from this forum and had just changed the values so that they would fit my routine. Apparently, I'm doing something wrong.

How would I connect to the database using BCI? Would the SQLDBConnect statement work the same way? What parameters do I need to pass, because I believe that you need to pass UID,PWD,DSN for ODBC using databases.

I mean, I maybe looking at it the wrong way.

cdrk823 · Post by **cdrk823** » Tue May 03, 2005 4:04 pm

BTW, I solved my issue with that mess of an SQL statement and do not have to worry about that anymore (thank goodness).

But I am still curious on how I would be able to connect to the database and run SQL statements within a Routine. Thanks for all the help!!