Catching ORAerrors

rkotha · March 12, 2003, 11:10pm

Hi all,

Can anybody tell me how I could catch ORA errors?

The situation is:

I have a “stored procedure” adapter service. This is inside
the TRY sequence. I use pub.flow:getLastError service in the
CATCH sequence.
For testing purpose, I have deleted the stored procedure
from the database(Oracle). ‘lastError’ record doesn’t give
the detailed error info as appears in the error log. How to
get these error messages in the CATCH sequence?

The errorDump for this exception is

[
Mesg: Exception at gbsgl:callProcGL , Error:
com.wm.pkg.art.error.DetailedServiceException:
[ART.117.4002] Adapter Runtime (Adapter Service): Unable to
invoke adapter service gbsgl:callProcGL.
at
com.wm.util.LocalizedException.(LocalizedException.java:114)
at
com.wm.app.b2b.server.ServerException.(ServerException.java:24)
at
com.wm.app.b2b.server.ServiceException.(ServiceException.java:15)

blah…blah…blah…

]

The “error log” messages (from admin screen) are:

ORA-06550: line 1, column 7: PLS-00201: identifier
‘WEBM.LOAD_GLCODE’ must be declared ORA-06550: line 1,
column 7: PL/SQL: Statement ignored

[ADA.001.316] Cannot execute the SQL statement “{call
WEBM.LOAD_GLCODE (?) }”. " (65000/6550) ORA-06550: line 1,
column 7: PLS-00201: identifier ‘WEBM.LOAD_GLCODE’ must be
declared ORA-06550: line 1, column 7: PL/SQL: Statement
ignored "

[ART.117.4002] Adapter Runtime (Adapter Service): Unable to
invoke adapter service gbsgl:callProcGL.

com.wm.pkg.art.error.DetailedServiceException:
[ART.117.4002] Adapter Runtime (Adapter Service): Unable to
invoke adapter service gbsgl:callProcGL.

The above error log messages look meaningful than the one
obtained from getLastError service. How to catch these
error messages?

Appreciate any help…

RK

hcruz · March 12, 2003, 11:10pm

Hi!

Here we have the same problem, and we could’t yet find a
solution.
I think that what is written in the server error log, is a
debug message from the JDBC adapter, so it is not possible
to catch it.

Guest · March 12, 2003, 11:10pm

Maybe the deprecated pub.db:call throws the SQLException’s error message? If not, I believe you will have to use a custom java service to call the procedure, if you need to respond differently to various Oracle errors.

Guest · January 23, 2004, 2:34pm

Hi,

I’m in a similar situation. I’d like to determine is one INSERT sentence has violated an unique constraint of my table. And then act in consequence, probably generating an UPDATE sentence.

How it’s supposed to catch or obtain the SQLError that it’s been generated after a JDBC adapter service is executed? I obtain no output, and as far as I’ve seen, it generates an ADA exception (with code 316) that is next encapsulated inside an wmART one.

Thanks in advance,

Ignasi

Mike_Nelson · January 23, 2004, 8:00pm

We do a pub.db.call to an Oracle stored procedure that returns a results string. The procedure has an exception process that puts an ‘OK’ in the results string or passes the ORA error message in it if the call didn’t work. We then branch on the results string and if it’s not OK - we send an error email that list the error. I’m sure you could customize it to strip out the ORA message and process accordingly.
I didn’t write the stored procedure but i’m sure I could get a copy from our developer if you want to see an example. Note - in our flow - if the stored procedure fails - it does not go to the Catch Block.

pankajkc · January 23, 2004, 8:26pm

We are using JDBC Stored Procedure Adapter service. The stored procedure returns the actual error as String and we capture it in the flow.

Thanks,
Pankaj

Guest · January 26, 2004, 1:54pm

Hi Michael,

Thanks for your explanation (also thanks to Pankaj). It would be great if you could send me a copy of this stored procedure.

I suppose that’s the best way to catch the ORA-errors. But before I obtained your response I tried a different solution. I call the “getLastError” service and then determine what’s the error by analyzing the “error” field in the “lasterror” structure (as by the moment I’m only worried about unique constraint violations, it’s easy to find the appropiate substring “ORA-001” in the text). It seems it works, but it’s quite slow. I’ll try with your solution and compare times.

Thanks in advance,

Ignasi

Mike_Nelson · January 26, 2004, 7:25pm

Ignasi,
i’m attaching a copy of the stored procedure. One thing to note is this stored procedure is using an XML SQL Utility (XSU) and the PL/SQL API. This allows us to send all our data in an xml string and the stored procedure is smart enough to parse the tags and update the appropriate database fields. It may look a little different from a standard update.

Oracle Strored procedure with ORA catch
EZO_OUTBOUND.sql (15.6 k)

Guest · August 3, 2005, 12:38pm

Hi,
Can any one please help me out in the following error.
When i open the [url]http://localhost:5555/WmMonitor/[/url] page, in that under the heading process if i select recent process tab
the following error is listed out

Failed getting instance list (java.sql.SQLException: [wm-cjdbc33-0069][Oracle JDBC Driver][Oracle]ORA-00904: “T1”.“AUDITTIMESTAMP”: invalid identifier )

please find the solution or a way to that…

chakradhar1 · August 4, 2005, 11:56am

Hi,
Can any one please help me out in the following error.
When i open the [url]http://localhost:5555/WmMonitor/[/url] page, in that under the heading process if i select recent process tab
the following error is listed out

Failed getting instance list (java.sql.SQLException:
[wm-cjdbc33-0009][Oracle JDBC Driver][Oracle]ORA-00904:
“T1”.“AUDITTIMESTAMP”: invalid identifier )

Failed getting instance list (java.sql.SQLException:
[wm-cjdbc33-0009][Oracle JDBC Driver][Oracle]ORA-00904:
“T1”.“AUDITTIMESTAMP”: invalid identifier )
please find the solution or a way to that…

Guest · August 4, 2005, 11:39pm

Hi Chakradhar,

Looks like you have executed the incorrect IS Core Audit scripts for Audit Logs. Check the version of the IS Core Audit Scripts you have executed and make sure it is in sync with your IS version (6.1/6.1.5). If not in sync, rollback the existing IS Core Audit database tables (scripts are provided for rollback as well). And execute the correct scripts.

Jyothi

avinasku · September 6, 2005, 12:29pm

hi,
I am calling a stored procedure from database. in the Stored Procedure there is a parameter whose datatype is ROWID.
when i am running this stored procedure in webMethods it gives me the following error.

[ADA.1.316] Cannot execute the SQL statement “{call DWA_DWSO6_PKG.DWSO_STAGE( ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?)}”. "
(17004) Invalid column type"
Invalid column type

can webMethods recognize ROWID datatype?
thanks and regards
Avinash