| Author |
Message
|
| dziku007 |
 Posted: Fri Aug 25, 2017 6:11 am Post subject: Trouble with handling oracle package procedure in IB9 |
 |
|
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 |
|
 |
| mqjeff |
| Posted: Fri Aug 25, 2017 6:27 am Post subject: |
|
|
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 |
|
|
| rekarm01 |
| Posted: Fri Aug 25, 2017 9:45 am Post subject: Re: Trouble with handling oracle package procedure in IB9 |
|
|
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 |
|
|
| dziku007 |
| Posted: Sun Aug 27, 2017 11:57 pm Post subject: |
|
|
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 |
|
|
| dziku007 |
| Posted: Mon Aug 28, 2017 12:09 am Post subject: |
|
|
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 |
|
|
| dziku007 |
| Posted: Mon Aug 28, 2017 1:51 am Post subject: |
|
|
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 |
|
|
| rekarm01 |
| Posted: Mon Aug 28, 2017 11:23 am Post subject: |
|
|
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 |
|
|
| dziku007 |
| Posted: Tue Aug 29, 2017 12:59 am Post subject: |
|
|
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 |
|
|
| rekarm01 |
| Posted: Wed Aug 30, 2017 3:58 am Post subject: Re: Trouble with handling oracle package procedure in IB9 |
|
|
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 |
|
|
|
|
