| Author |
Message
|
| Robert |
 Posted: Tue Dec 16, 2003 2:23 pm Post subject: Error accessing Oracle Stored Procedure from database node |
 |
|
Apprentice
Joined: 22 Aug 2002
Posts: 46
Location: Quebec, Canada
|
Hi,
I have installed WBI MB5 with CSD02 on a Win2K box.
The Config Mgr and Message Broker primary database are on DB/2.
I developed a simple message flow to execute a stored procedure in Oracle 9.2 and I get the following message in the NT Event Log.
'[Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve service name'.
I am attemptimg to access a stored proc. in the DDO schema (Not the default schema for this database instance) under the TEST package.
I can locate the Stored Procedure when browsing with a database utility such as DBVisualizer or TOAD so I know that it is there!
The database node ESQL source goes as follow:
------------------
CREATE DATABASE MODULE mfInsert_Database
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
declare inputParm CHARACTER;
declare extTestVarChar CHARACTER;
set inputParm = 'CHARACTERS TO TEST';
set extTestVarChar = 'DDO.TEST.F_VARCHAR';
CALL TestVarChar (inputParm);
RETURN TRUE;
END;
CREATE PROCEDURE TestVarChar (
IN parm1 CHARACTER ) EXTERNAL NAME extTestVarChar;
END MODULE;
------------------
The database node Data Source is set to the ODBC data source.
I successfully connected to my Oracle SID with this ODBC conenction.
I issued a mqsisetdbparms to set the ODBC parms to the broker.
Can anybody provide me with clues on how to solve this specific problem?
And yes, I checked all the other similar posts...
Thanks
Robert |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Dec 17, 2003 1:34 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
I think the key to your problem may be in the following line of your post:
| Quote: |
| I am attemptimg to access a stored proc. in the DDO schema (Not the default schema for this database instance) under the TEST package. |
When calling an Oracle SP that is in a Package you have to explicitly qualify the EXTERNAL NAME with the SCHEMA and PACKAGE names, otherwise the SP will not be found. In your case this would be:
| Code: |
CREATE PROCEDURE TestVarChar (
IN parm1 CHARACTER ) EXTERNAL NAME [b]'DDO.TEST[/b].extTestVarChar'; |
Cheers,
MGK[/quote]
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Robert |
| Posted: Wed Dec 17, 2003 11:27 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2002
Posts: 46
Location: Quebec, Canada
|
Hi,
Sorry for my ignorance, but what does the and syntax means?
Thanks.
Robert |
|
| Back to top |
|
|
| Robert |
| Posted: Wed Dec 17, 2003 11:29 am Post subject: |
|
|
Apprentice
Joined: 22 Aug 2002
Posts: 46
Location: Quebec, Canada
|
The previous post wasn't sent correctly.
I was referring to the brackets and the "b" and "/b" within these brackets. Do they have a special meaning or is it just a display interpretation?
Thanks.
Robert |
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Dec 17, 2003 1:32 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
I think MGK was trying to BOLD part of the reply.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| Robert |
| Posted: Wed Dec 17, 2003 7:55 pm Post subject: |
|
|
Apprentice
Joined: 22 Aug 2002
Posts: 46
Location: Quebec, Canada
|
I tried to qualify the stored procedure with the fully qualified Schema and Package name and now, the message flow won't deploy even if it shows OK on the broker toolkit.
The delimiter character (the period) is rejected by the broker as an external name.
Anybody has experienced something similar with Version 5?
Thanks.
Robert
----------------------
The following NT Event Viewer message is logged:
Computer: PELLET99
Description:
( BRK01.default ) (RPELLET, 33.45) : Syntax error : '.'.
The token caused a syntax error. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Dec 18, 2003 9:10 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
The previous poster is correct. I did try to BOLD part of the text for clarity, but ended up causing confusion  .
Never mind what I ment to post was:
| Code: |
CREATE PROCEDURE TestVarChar (
IN parm1 CHARACTER ) EXTERNAL NAME 'DDO.TEST.extTestVarChar'; |
Note the apostrophe's around the EXTERNAL NAME's oprand, these are required to parse the whole oprand as a single string.
Cheers,
MGK
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
