Problem with executing Queries using Database Link...

Hello Techies,We are trying to execute a query through code (Oscript) using CAPI.Exec(prgCtx.fDBConnect.fConnecton, stmt) but it throws errors in connect logs as below -Oracle error: (12705) --> ORA-12705: invalid or unknown NLS parameter value specifiedORA-02063: preceding line from WISDOMWe are able to execute the same query successfully using sqlplus on same application server...I am not sure but seems livelink sets some Oracle Env. variable before executing queries (or establishing db connection)...Here is the query with database link name WISDOM pointing to a different livelink instance schema - Insert into WISDOM_UGMAPPING(LL91_ID, LL97_ID, NEWLY_CREATED, TYPE) (SELECT a.ID LL91_ID, b.ID LL97_ID, 'N' NEWLY_CREATED, 0 TYPE from KUAF@WISDOM a, KUAF b where a.NAME=b.NAME and a.TYPE=0 and a.DELETED=0 and a.DELETED=b.DELETED and a.TYPE=b.TYPE and b.ID not in (select LL97_ID from WISDOM_UGMAPPING where TYPE=0))Please advise...Thanks,Pravin.

Find more posts tagged with

Comments

Appu_Nair

Here's a suggestion,I would not know how to debug the database link scenario.However CAPI can execute a stored procedure,so if you could wrap the logic of your call in a stored proc and if the stored proc executes on the livelink schema,wouldn't you be better off.I have never tried these database links and what not but it looks like you are trying to find differences in kuaf in two livelink instancesif you use dbwizapi to call your "wisdom" are you any better off.

Pravs4

Thanks Appu! But wouldnt both executing query and stored proc be same as I would have to still fetch the data over database link in stored proc logic...Could you please elaborate on how we can use dbwizapi to fetch data from other database?Thanks!Pravin.

Appu_Nair

you are right.I searched the web and found this about the ora errorhttp://www.orafaq.com/forum/?t=msg&th=9602/0/This is probably a posting by Louis at the communities that help using the dbwizapi subsystem.if you run oscript search on this term in builder DBWizApiSubsystem you should see lot of examples where ot uses itIt probably is not that different from CAPI but just a thoughthttp://communities.opentext.com/communities/llisapi.dll/wiki/7967685/Connect to Other Databases

Pravs4

Thanks for the links Appu!Not sure though but it seems CAPI.AllocConnect() which is called while establishing prgCtx.fDBConnect.fConnection when user logs into Livelink, some kind of Alter session or something is done internally by Livelink which is causing ORA-12705. If not, I should have faced the same error when I execute the query in Sqlplus but its not the case...There are some functions e.g. 'SubclassConnect()' called through CAPI.AllocConnect as indicated by connect logs but those functions are not exposed to us. So there is no way to detect what happens internally

Pravin.

Greg_Griffiths_(ggriffiths_-_(deleted))

If you are having issues then you may be able to do it this way :1. Connect to the WISDOM db seperately (see http://www.greggriffiths.org/livelink/development/oscript/snippets/connect2otherdb.html) and grab what you need from there for the value)2. Run a standard CAPI.EXEC to insert the data into the table.

Pravs4

Thanks, Greg!That would be a good option if we dont find solution on this. We have contacted OT Support on this. Hopefully OT will find reason behind it...Thanks,Pravin.