ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Trouble with handling oracle package procedure in IB9

Post new topic  Reply to topic
 Trouble with handling oracle package procedure in IB9 « View previous topic :: View next topic » 
Author Message
dziku007
PostPosted: Fri Aug 25, 2017 6:11 am    Post subject: Trouble with handling oracle package procedure in IB9 Reply with quote

Apprentice

Joined: 26 Jul 2012
Posts: 32

Guys,

I'm receiving "The procedure is unknown to the database and no definition could be found" when I am trying to connect to Oracle DB stored procedure in package.

I am using code in Compute node:

Code:

CREATE COMPUTE MODULE dbtest_Compute
   
   DECLARE connectionString EXTERNAL CHAR 'TESTER.TEST_PACKAGE';
   
   CREATE FUNCTION Main() RETURNS BOOLEAN
   BEGIN
(...)
      CALL test(in_var,out_var) IN Database.{connectionString};
(...)
      RETURN TRUE;
   END;
   
   CREATE PROCEDURE test(IN input INT, OUT output CHAR)
   LANGUAGE DATABASE EXTERNAL NAME "test_proc_package";
END MODULE;


I read about types of Oracle DB procedure calling here:
https://www.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04970_.htm
https://www.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04920_.htm

This has nothing to do with wrong list/types of parameters or wrong schema/package/procedure name or overload db procedure issue. Similar code works for procedure that is just not inside an Oracle package but as a individual oracle procedure:

Code:

CREATE COMPUTE MODULE dbtest_Compute
   
   DECLARE connectionString EXTERNAL CHAR 'TESTER';
   
   CREATE FUNCTION Main() RETURNS BOOLEAN
   BEGIN
(...)
      CALL test(in_var,out_var) IN Database.{connectionString};
(...)
      RETURN TRUE;
   END;
   
   CREATE PROCEDURE test(IN input INT, OUT output CHAR)
   LANGUAGE DATABASE EXTERNAL NAME "test_proc";
END MODULE;


Also calling procedure in package works but I need to change type of invokation:

Code:

CREATE COMPUTE MODULE dbtest_Compute
   
   CREATE FUNCTION Main() RETURNS BOOLEAN
   BEGIN
(...)
      CALL test(in_var,out_var);
(...)
      RETURN TRUE;
   END;
   
   CREATE PROCEDURE test(IN input INT, OUT output CHAR)
   LANGUAGE DATABASE EXTERNAL NAME "TESTER.TEST_PACKAGE.test_proc_package";
END MODULE;


It seems that IB (or it is the DB driver issue) does not recognize procedure in oracle package while using CALL test(in_var,out_var) IN Database.{connectionString}; semantics.

Is it IB lack of compatibility with Oracle?

What I want to achieve:
I need to enable using DB schema name as a parameter to the BAR, because it is different in each environments. Thought that "CALL...IN Database" is a way to achieve that. Are there any other ideas how to achieve that?

Im using IB 9.0.0.2 with Oracle DB Driver provided by IBM (UKORA26.dll).

Thanks in advance for any help.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Fri Aug 25, 2017 6:27 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

My memory is fuzzy.

But try this. CREATE Procedure with just the procedure name.

Call the procedure as "schema".procedure

You can then set "schema" to a variable that's read from somewhere.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
rekarm01
PostPosted: Fri Aug 25, 2017 9:45 am    Post subject: Re: Trouble with handling oracle package procedure in IB9 Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

dziku007 wrote:
I'm receiving "The procedure is unknown to the database and no definition could be found" when I am trying to connect to Oracle DB stored procedure in package.

Code:
DECLARE connectionString EXTERNAL CHAR 'TESTER.TEST_PACKAGE';
(...)
      CALL test(in_var,out_var) IN Database.{connectionString};
(...)

Look more closely at the syntax for the DatabaseSchemaReference in the CALL statement. The DatabaseSourceExpr and SchemaExpr need to be separate expressions:

Code:
DECLARE connectionDSN EXTERNAL CHAR 'TESTER';
DECLARE connectionSchema EXTERNAL CHAR 'TEST_PACKAGE';
(...)
      CALL test(in_var,out_var) IN Database.{connectionDSN}.{ConnectionSchema};
(...)

The ESQL { curly brace } operators won't parse a dot in the character string.
Back to top
View user's profile Send private message
dziku007
PostPosted: Sun Aug 27, 2017 11:57 pm    Post subject: Reply with quote

Apprentice

Joined: 26 Jul 2012
Posts: 32

Thanks mqjeff,

Unfortunately, there is no proper syntax to use CALL statement with database schema name as a procedure prefix (only broker schema name possible) according to https://www.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04920_.htm

Another option is that I misunderstood Your suggestion.

Anyway I do not have trouble to set database schema name as a param to the IB, the trouble is that I have no place to put database package name as a param to the IB.
Back to top
View user's profile Send private message
dziku007
PostPosted: Mon Aug 28, 2017 12:09 am    Post subject: Reply with quote

Apprentice

Joined: 26 Jul 2012
Posts: 32

Thanks rekarm01,

I did not know that ESQL do not parse dot in a {}. But still I have no place to put package name. According to Your suggestion:
Code:
CALL test(in_var,out_var) IN Database.{connectionDSN}.{ConnectionSchema};

Where should I place a package name? When I try this:
Code:
CALL test(in_var,out_var) IN Database.{connectionDSN}.{ConnectionSchema}.{ConnectionPackageName};
IB complains syntax error.

The strange thing is that I receive (at best case)
Code:
The procedure is unknown to the database and no definition could be found MYDNS.TESTER.TEST_PACKAGE.TEST_PACKAGE_PROC"
error. But the path is pefectly correct.... MYDNS is my DNS name, TESTER is my schema name, TEST_PACKAGE is my (Oracle) package name, and TEST_PACKAGE_PROC is my procedure name.
Back to top
View user's profile Send private message
dziku007
PostPosted: Mon Aug 28, 2017 1:51 am    Post subject: Reply with quote

Apprentice

Joined: 26 Jul 2012
Posts: 32

I finally manage to do that. Solution that works for me use both styles of Database procedure call:
Code:

CREATE COMPUTE MODULE dbtest_Compute
   
   DECLARE connectionString EXTERNAL CHAR 'TESTER';
   
   CREATE FUNCTION Main() RETURNS BOOLEAN
   BEGIN
(...)
      CALL test(in_var,out_var) IN Database.{connectionString};
(...)
      RETURN TRUE;
   END;
   
   CREATE PROCEDURE test(IN input INT, OUT output CHAR)
   LANGUAGE DATABASE EXTERNAL NAME "UNIMPORTANT.TEST_PACKAGE.test_proc_package";
END MODULE;


Documentation says :
Code:
packageName is optional and applies only to Oracle data sources. If you supply a packageName you must supply a schemaName.


So i provided both schemaName, package name and (of course) procedure name in procedure declaration: "UNIMPORTANT.TEST_PACKAGE.test_proc_package". I called schema name UNIMPORTANT because it seems it is not used when procedure call uses style:
Code:
CALL test(in_var,out_var) IN Database.{connectionString};

In connectionString i provided real schema name and it seems it has priority over "UNIMPORTANT" value. Unfortunately "UNIMPORTANT" prefix is needed in procedure declaration, because if not, IB complains error as I mentioned previously.
Back to top
View user's profile Send private message
rekarm01
PostPosted: Mon Aug 28, 2017 11:23 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

dziku007 wrote:
Where should I place a package name?

Look more closely at the syntax for the CREATE PROCEDURE ExternalRoutineName, and also the explanatory text:

Quote:
..., if the procedure belongs to an Oracle package, the package is treated as part of the procedures name. Therefore you must provide a schema name and the package name, in the form:

EXTERNAL NAME "mySchema.myPackage.myProc";

This form allows the schema, but not the package name, to be chosen dynamically in the CALL statement.

So, you must put the package name in the CREATE PROCEDURE statement, not in the CALL statement.

dziku007 wrote:
Code:
CREATE COMPUTE MODULE dbtest_Compute
   
    DECLARE connectionString EXTERNAL CHAR ...

The variable name "connectionString" is misleading. "connection string" does not mean "datasource", "schema", and/or "package". It means something else.

dziku007 wrote:
I finally manage to do that. Solution that works for me use both styles of Database procedure call

That's good. But what does "both styles" mean here?
Back to top
View user's profile Send private message
dziku007
PostPosted: Tue Aug 29, 2017 12:59 am    Post subject: Reply with quote

Apprentice

Joined: 26 Jul 2012
Posts: 32

rekarm01, You are rigth "connectionString" is misleading, it should be "schemaName" or something similar to be more descriptive.

Saying "both styles" I ment that I had to provide schema name twice, during CALL:
Code:
CALL test(in_var,out_var) IN Database.{schemaName};

and during CREATE PROCEDURE:
Code:
CREATE PROCEDURE test(IN input INT, OUT output CHAR)
   LANGUAGE DATABASE EXTERNAL NAME "UNIMPORTANT.TEST_PACKAGE.test_proc_package";


Normally I do not need to provide schema name in CREATE PROCEDURE statement if I set it in CALL statement, but to provide package name in CREATE PROCEDURE statement I need also to provide schema name (as it is written in cited documentation). It seems that schema name in CREATE PROCEDURE statement is just to be clear with the syntax, because it is not used (the schema name provided in CALL statement takes priority).
Back to top
View user's profile Send private message
rekarm01
PostPosted: Wed Aug 30, 2017 3:58 am    Post subject: Re: Trouble with handling oracle package procedure in IB9 Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

dziku007 wrote:
Normally I do not need to provide schema name in CREATE PROCEDURE statement if I set it in CALL statement, ...

The difference is that the schema name in the CREATE PROCEDURE statement is meant to be static, decided at deploy time, while the schema name in the CALL statement is meant to dynamically override the static value at execution time.

More importantly, the qualifying IN clause that contains the schema name in the CALL statement is meant to be optional. If the CREATE PROCEDURE statement provides an invalid schema name, then that forces the caller to override it in the CALL statement.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Trouble with handling oracle package procedure in IB9
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.