hi Community,

i encounter a problem when trying to enable an adapter notification using when condition, when i delet the when condition it works fine,
bellow the error i get :
[ART.116.3038] Adapter Runtime (Notification): Error in Notification Callback:enableCallback notification ocp.ind.app.jdbc.lims.outbound:analysesAddedNotification.
[ADA.1.349] Execution of DDL query of Notification failed during enable/disable. Please check the datatypes of columns selected in notification.
ORA-00905: missing keyword
Caused by: com.wm.pkg.art.error.DetailedException: [ART.116.3038] Adapter Runtime (Notification): Error in Notification Callback:enableCallback notification ocp.ind.app.jdbc.lims.outbound:analysesAddedNotification.
[ADA.1.349] Execution of DDL query of Notification failed during enable/disable. Please check the datatypes of columns selected in notification.
ORA-00905: missing keyword

we are using Webmethod 9,7, and IS_9.7_Core_Fix9 TNS_9.7_Fix1

please helep,

Regards,

Hi Nezha,

I guess it is about JDBC Adapter.

Which Version of the JDBC Adapter are you running?
6.5 or 9.0?

Which Fixes are applied to it?

Can you share a screenshot of your Notification definition?

Regards,
Holger

HI HOLGER,

we are using JDBC_9.0_Fix8 version,

please find attached the scrren shots of my configuration,

Regards,

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files3/e557fc45b40c05b52494d21d3091d02481c291e5_2_1035x580.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/e557fc45b40c05b52494d21d3091d02481c291e5.png 2x" data-dominant-color="E0E5E7">

notificationSchedule.png1366×768 179 KB

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files3/8b11e02f5560007ddff7cbb81fa58bff252df250_2_1035x580.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/8b11e02f5560007ddff7cbb81fa58bff252df250.png 2x" data-dominant-color="E7EBF0">

NotificationConfigure.png1366×768 185 KB

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files3/2a876ebdaf230121964f464504dc4a22b91dff3b_2_1035x580.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/2a876ebdaf230121964f464504dc4a22b91dff3b.png 2x" data-dominant-color="ECF0F5">

whencondition.png1366×768 152 KB

Hi Nezha,

Adapter version and Fix level sound good.

Please provide a screenshot of the select tab.

According to the error message your are probably trying to select a field with type LOB (BLOB or CLOB),
which are known to cause issues when used with Notifications.

Can you provide version of Database and JDBC driver you are using?

Regards,
Holger

hi Holger,

we are using ojdbc7 i will search for the database version and get back to you:

attached the the selected table,

Regards,

table.png1236×836 82.9 KB

hi,
the database version is oracle 11g,

Regards,

Hi Nezha,

please set the Java-Types for the Decimal-, Float- and Date-Fields to java.lang.String.

The adapter will do the appropriate conversions.

Deriving from the file name of your driver you are using an Oracle 12c driver in combination with an Oracle 11g database. This sounds good so far.

The complete version of the driver can be found in the META-INF/MANIFEST.MF in the jar.
You extract it with any zip-compliant tool.

Your table looks quite huge.
Is it required to retrieve all the fields directly in the notification itself?

A workaround might be to get only some few unique identifier fields and then load the rest of the data from the original table in a separate Select-Service by using the identifiers in the where-clause.

This will speed up the notifications as there will be less data to be copied inside the database and being published to messaging afterwards.

Regards,
Holger

Hi Holger,
thank you for yr reply,

even after setting all the Java-Types of my attributes to java.lang.String i still have the same problem,

in my when condition i tried to compare one of my attribute to a string new.STATUS = A, i tried also with new.STATUS Like A, but it dosn’t work, always the same error message when tring to enable notification,

about retrieving all the fields, yes i need them all because in my onupdate notification all my fields may be modified so i need to have a listner on all my attributs,

really i don’t now why it donsn’t work with when condition but it works fine without

Regards,

Please change when condition to some other field and see the status.

Thanks,

hi MR as173d,
Yes it works with another field TEST_NUMBER= 32448, the notification is enabled ?? i don’t understand why??

please could you explain, is it a limitation of adapter notification? it dosn’t work with string dataType?

Regards,

Hi Nezha,

eventually will have to specify the condition as STATUS = ‘A’.
Please note the single quotes around A as these indicate a String value.

If this doesnt help either you will have to check the “Create or Update Trigger”- as well as the “Create Table”-statement together with your DBA.

You might want to check in IS Admin UI for Logging (Settings → Logging → Server Logging), if there is a log factory for the JDBC Adapter which might help you to retrieve the effective statements wen enabling the notification.
If so increase it to Trace, save it, try to enable the notification and then revert the log level to its original value afterwards.

Regards,
Holger

I agree with Holger. Give a try and let us know.

Thanks,

hi,
it works like a charm , it was just a problem of the single quotes around A :oops: :oops: ,

thank you all so much for your answers and your help,

Regargds,

Hi Nezha,

Great, that this could be solved.

Sometimes complex things become easy when you try to think them the other way round to check what is really required.
Regarding this case: I was trying to think it from the SQL side instead from the Designer side remembering that when doing a select in the database there need to be quotes around the values for String types, which are added by the select template arbitrarily, but not in the notification template.

Regards,
Holger

Good day

Me and the developers we have been following and doing solutions mentioned on this thread with no solution.

We have the same problem, we selecting an ‘int’ value from table monitor this field but the notification cannot be enabled, it complains about this “Column name ‘WM_ROWID’ does not exist in the target table or view.”

For testing Purposes we selecting a single row and monitor that specific field of ‘int’ and output field ‘string’ and there is not where clause or join.

Please help!.

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files3/3af31cc64d0600b5137837f02370b580f40e8370_2_1035x564.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/3af31cc64d0600b5137837f02370b580f40e8370.png 2x" data-dominant-color="DBEBF1">

sageError1.png1366×745 79.8 KB

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/259527f80a45e61326ec9532138733726abda611.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/259527f80a45e61326ec9532138733726abda611.png 2x" data-dominant-color="F3F4F7">

sageError2.png775×331 18.7 KB

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/4ead20657452bf1aab31976aefb8fa5eb0a7bbeb.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/4ead20657452bf1aab31976aefb8fa5eb0a7bbeb.png 2x" data-dominant-color="EFF1F3">

sageError3.png828×382 34 KB