JDBC Adapters problem with oracle DB

,
#1

hi community,We use the webMethods Integration Suite with version 9.7.
We encounter difficulties when we use JDBC adapters with Oracle 11G. We use the odjbc6 driver.

We have trouble using the adapters in the Designer, but the problem seems to be related to the connection between the IS and the database.

Please note that when using JDBC Adapters with other databases vendor, everything works fine.

The database we target contains a LOT of tables.

When we create an adapter (of any kind), not only is the adapter creation long to execute, but also when we try to save we encounter designer UI errors. For a given adapter, we can’t add any expression for a select statement, and the expressions we add are always empty - detection of the fields doesn’t work. Also, when we want to select the table, it takes a long time to load all the available tables.

We encounter errors on the IS log such as this one :
[ART.114.243] Adapter Runtime (Metadata): Failed to run resourceDomainLookupValues service. Details provided in error log.
[ADA.1.319] Cannot get the list of table columns. "
(22025/1424) ORA-01424: missing or illegal character following the escape character
"
ORA-01424: missing or illegal character following the escape character
Caused by: com.wm.adk.error.AdapterException: [ADA.1.319] Cannot get the list of table columns. "
(22025/1424) ORA-01424: missing or illegal character following the escape character
"
ORA-01424: missing or illegal character following the escape character

We don’t know what kind of errors it is; we don’t know if the problem comes from the JDBC Adapter toolkit or the compatibility with our Oracle DB…

Did you experienced any kind of similar errors ? Any help would be appreciated.

Kind regards,

Nezha

#2

Check on the below notes

1> Make sure you have the latest JDBC adapter fix levels installed. Moreover check if the current oracle version that you are using is supported by the JDBC adapter version (you can find it in system requirements guide).

2> Restart the IS to check if issue resolves

3> Still issue, try giving the setting in other properties of JDBC adapter connection: includeSynonyms=true

Need below details for further investigation:

JDBC adapter version with fix levels installed.

#3

Hi,

Oracle 11g should be a valid database fur the current JDBC Adapters.

You can also try the Oracle 12c driver (ojdbc7.jar available from that version onwards).

Regards,
Holger

#4

Hello and thanks for your answers.

We first installed the latest available fix for the JDBC Adapter 6.5, Fix 53. Unfortunately, the fix didn’t resolve this peculiar issue.

We then tried to install JDBC Adapter 9.0, without fix, and the issue is still there.

We also switched our driver to ojdb7, without any luck. Our configuration already used the jdbc property includeSynonyms=true.

Maybe we should install JDBC Adapter 9.0 fixes ?

Not sure what to do up to that point.

#5

Hi Abrabri,

applying the latest Fix (JDBC_9.0_Fix8 as of now) is worth a try.

Additionally you should try to track the SQL Statements issued by the Adapter against the DB when this issue occurs.

Regards,
Holger

#6

Nezha,

Yes it’s worth a try with JDBC Adapter9.0. but did you open a ticket with SAG support regarding this issue?

Also I believe JDBC Adapter6.5 fixes and having SCG fixes along with the ojdbc6.jar should work fine with 11G R2. on wM 9.7.

HTH,
RMG

#7

hi community,

We tried all propositions but it dosn’t work, so now we upgrade the version of jdbc adapter from 6.5 to 9.0, we use the ojdbc7 and we installed the fixe 9.0.8 but we still have the same problems, the version of our ORACLE 11g database is 9.0.8.0,

@rmg : what is SCG fixes ??

Any idea, help would be appreciated,

Regards

#8

Hi Abrabri,

I doubt that Oracle Database 11g has a version of 9.0.8.0, more likely it should be either 11.2.0.3 or 11.2.0.4 or even newer.

When moving from JDBC Adapter 6.5 to JDBC Adapter 9.0 you will have to run a migration script to migrate the adapter services.

Additionally checking for ART-Fixes might be neccessary.

Can you provide the error message (with stacktrace) from the error log?
This might ease the analysis.

Regards,
Holger

#9

Are you still having issues after having lates fixes ? Can you share steps you followed ?

Thanks

#10

hi,
@MR as173d : i still have the same problems, even after installing lates fixes, i was looking for the script that i should run to migrate the adapter services?

@Holger von Thomsen : the DB admin confirm that the version of the database is 9.0.8, can you tell me please where i can found the script that i should run to migrate version,?
Regards,

#11

Nezha,
As you have tried many suggestions, want to check few other things not related to adapter…

Can you check which data centre, your integration server is running and Oracle server is running? Ideally it should be same.

Login to the Unix box of integration server and do a ping to Oracle server to verify the latency. See how much time IS takes to communicate with Oracle.

If both servers are in same data centre and you noticed ping taking less than 10 ms, with your DBA help, run some SQL queries connecting to the Oracle server (server), and run the same query using SQL developer (client). If you notice quicker response from them, then you would have to create ticket with SAG.

HTH
Senthil

#12

Please run wm.adapter.wmjdbc.admin:migrate_65_To_90 service. Let me know if you see any issues

Thanks,

#13

hi,

i execute the wm.adapter.wmjdbc.admin:migrate_65_To_90 service and it execute successfully, but we still have the same problems, the ping also work fine and i execute the same requests from both ans i notice that its quicker than designer, so we have to create ticket with SAG,

Regards,

#14

Something went wrong during your process. Please update once you hear back from SAG.

Thanks,

#15

Hi Abrabri,

please check your database version once again.

An Oracle 11g should never have a version 9.x, but more likely a 11.2.0.x.

A version 9.x usually indicates an Oracle 9i.

Regards,
Holger

#16

HI HOLGER,

the ERP version is 11i Release2 but the database is 9i(9.0.8)?

Regards,

#17

Hi Nezha,

what do you mean by ERP (resp. version)?

Most likely you should consider an Database Upgrade then.

Regards,
Holger

#18

Guys,

I am also getting same error while creating JDBC Order Notifications.But error was popping up only very first time creating the new adapter notification once error was occurred am able to create the notification.

Error deatils:
[ART.114.243] Adapter Runtime (Metadata): Failed to run notificationLookupValues service. Details provided in error log.
[ADA.1.319] Cannot get the list of table columns. "
(22025/1424) ORA-01424: missing or illegal character following the escape character
"
ORA-01424: missing or illegal character following the escape character
Caused by: com.wm.adk.error.AdapterException: [ADA.1.319] Cannot get the list of table columns. "
(22025/1424) ORA-01424: missing or illegal character following the escape character
"

ORA-01424: missing or illegal character following the escape character

Can any one please help me on this.

#19

Share more details about your environment, go through the same thread, follow the suggestions given, if still issue persists, please write back with complete details.

Thanks,

#20

Dear Nezha,

Did U try this query is working in any of sqlDeveloper or Toad
Properly,Because it seems that it’s query issue.

Please proceed with this and let me know for any error.