| Author |
Message
|
| moogoo |
 Posted: Wed Aug 23, 2006 2:07 pm Post subject: Trouble calling External Oracle Stored Procedures in WMB 6.0 |
 |
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Hi,
I'm trying to call an Oracle stored procedure using the "call procedure" functionality in WMB 6.0. Just a bit of background: we're currently able to call Oracle functions that return a single value using PASSTHRU, so database connectivity and access should not be an issue. Hoping you guys can help.
Here's my procedure definition:
CREATE PROCEDURE pssci_test(OUT p_error_code INTEGER)
LANGUAGE DATABASE
EXTERNAL NAME "CREATE_BATCH_PKG.pssci_test";
and my call to it:
call pscci_test(Environment.Variable.ErrorCd);
I've also tried having the DBA creating this outside of a package, changing the EXTERNAL NAME definition to not include the package but that too does not work and I am getting the following exception:
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbDataFlowNode.cpp'
(0x03000000):Line = 616
(0x03000000):Function = 'ImbDataFlowNode::createExceptionList'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'STTP_Routing_V01R01#FCMComposite_1_4.STTP_Director_Routing_SUBFLOW_V01R01#FCMComposite_1_3'
(0x03000000):Label = 'STTP_Routing_V01R01.STTP_Director_Routing_SUBFLOW_V01R01.Extract_Details'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x03000000):Text = 'Node throwing exception'
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 589
(0x03000000):Function = 'SqlStatementGroup::execute'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'STTP_Routing_V01R01#FCMComposite_1_4.STTP_Director_Routing_SUBFLOW_V01R01#FCMComposite_1_3'
(0x03000000):Label = 'STTP_Routing_V01R01.STTP_Director_Routing_SUBFLOW_V01R01.Extract_Details'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x03000000):Text = 'Error detected, rethrowing'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.STTP_Director_Routing_SUBFLOW_V01R01_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '86.5'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'pssci_test(Environment.Variable.ErrorCd);'
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp'
(0x03000000):Line = 685
(0x03000000):Function = 'SqlRoutine::invoke'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'STTP_Routing_V01R01#FCMComposite_1_4.STTP_Director_Routing_SUBFLOW_V01R01#FCMComposite_1_3'
(0x03000000):Label = 'STTP_Routing_V01R01.STTP_Director_Routing_SUBFLOW_V01R01.Extract_Details'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2934
(0x03000000):Text = 'Error occured in procedure'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'pssci_test'
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbDatabaseManager.cpp'
(0x03000000):Line = 1960
(0x03000000):Function = 'ImbDatabaseManager::getDBProcedureParameterInformation'
(0x03000000):Type = 'ComIbmDatabaseConnectionManager'
(0x03000000):Name = 'ComIbmDatabaseConnectionManager'
(0x03000000):Label = 'ComIbmDatabaseConnectionManager'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2920
(0x03000000):Text = 'The procedure is unknown to the database and no definition could be found.'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'GPBODS1.CREATE_BATCH_PKG.PSSCI_TEST'
)
)
)
)
)
)
Just as an FYI, "GPBODS1" is the data source.
Any idea what is going on here? Again, when using PASSTHRU, our functions are working fine but we'd like to start exploiting the value of using IN, OUT, and INOUT parameters and would like to get this working.
Any help would be greatly appreciated.
MG |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Aug 23, 2006 2:22 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
There have been a number of posts here before about this error.
I don't remember the resolution.
First guesses are that you're using the wrong ODBC driver - you need to be using the Merant driver that comes with Broker and not anything else - or that you have not turned on EnableSQLDescribeParam.
I think if you search for the 'The procedure is unknown to the database and no definition could be found.' error, you might find some good stuff.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| moogoo |
| Posted: Thu Aug 24, 2006 6:22 am Post subject: |
|
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Thanks Jeff. Yup, we are using the Merant (DataDirect) driver and I'm currently checking with the DBA to make sure that the "EnableDescribeParam" you mentioned is enabled. But PASSTHRU is working perfectly and we have numerous DB function calls that make use of it. Could a configuration problem really be the source of why PASSTHRU would work but CALL <EXTERNAL procedure> wouldn't?
I did do a search on the error beforehand but the posts that came didnt' directly relate...permission problems or moving the stored proc from 1 environment to another. In my case, DB connectivity does work with PASSTHRU and I'm still trying to do the call on the same box, albeit with a different syntax.
I must be missing something here, though everything I've read in the .pdf files and on the message board seems relatively straightforward on how to do this.
Any other thoughts would be greatly appreciated.
MG |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Aug 24, 2006 7:19 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
EnableDescribeSQLParam is set on the ODBC DSN. It's either a checkbox in Windows or it's a line in the .odbc.ini entry for the dsn that says "EnableDescribeSQLParam=ON".
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| moogoo |
| Posted: Thu Aug 24, 2006 7:29 am Post subject: |
|
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Did I say DBA? I meant our SA for WMB. I'll have him doublecheck that setting (its a Windows box which I don't have access to).
What exactly does that option do? If it wasn't checked, would PASSTHRU still work?
MG |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Aug 24, 2006 7:42 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
It has to do with how datatypes are passed back and forth between ODBC and broker and oracle - I don't know the details exactly, but the name is very suggestive.
I suspect that PASSTHRU would still work if it was not checked.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| moogoo |
| Posted: Thu Aug 24, 2006 8:35 am Post subject: |
|
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Just heard back from the SA....looks like that option is checked. I'm stumped...does anyone have any other suggestions?
The one thing I'm finding out about implementing features I haven't used in MB is that regardless how easy it sounds in the manuals, it always takes a bit of time to get it really working properly. Guess this is no different. 
MG |
|
| Back to top |
|
|
| moogoo |
| Posted: Thu Aug 24, 2006 1:39 pm Post subject: |
|
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Ahh bugger...found out what the problem was. I was specifying the wrong schema all along. Duh. Thanks Jeff for all your help.
MG |
|
| Back to top |
|
|
|
|
