| Author |
Message
|
| isaw |
 Posted: Wed Sep 02, 2009 7:33 am Post subject: Need Stored Proc help - cant match DB proc params |
 |
|
Apprentice
Joined: 09 May 2007
Posts: 45
|
Hey guys.
I acknowledge this might be done to death but after searching the site and using the 6.1 ref guide, I'm still unsure.
I have WMB 6.1 calling a basic stored proc in Oracle. Well, that's the goal.
The process was to :
1. Create the Stored Proc in Oracle. Done.
2. Create the procedure in WMB ESQL to reference the stored proc. Fail.
3. Call the WMB procedure to retrieve the cursor containing the info. Fail.
My proc in Oracle looks like this :
| Code: |
CREATE OR REPLACE PACKAGE BODY TEST.PKG_MARS AS
PROCEDURE getAttributeRefData(p_attribcur OUT ref_cur)
IS
BEGIN
OPEN p_attribcur FOR
SELECT * FROM ATTRIBUTE;
COMMIT WORK;
END getAttributeRefData;
END;
|
(I've already defined the package header)
Now in WMB, I need to create a procedure to reference this -- and this is where I am getting stuck. I don't know how to match the OUT param datatype of 'cursor'. Here's what I've tried :
| Code: |
CREATE PROCEDURE GetAttributeRefData(OUT outParam1 ???)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "TEST.PKG_MARS.getAttributeRefData";
|
And then I would have tried to invoke that with :
| Code: |
DECLARE cursor REFERENCE TO Environment.Variables.MyCursorTest;
DECLARE outParam1 ???;
CALL GetAttributeRefData(outParam1, cursor.ResultSet[]);
|
The error message i typically get is :
| Code: |
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.' (CHARACTER)
|
Where "(CHARACTER)" is whatever latest datatype I've tried in place of the expected CURSOR result.
I used the 6.1 reference guide under "Calling a stored procedure that returns two result sets" to help guide me but it doesn't seem to give an example that matches what I'm trying to do -- or, admitedly, I don't get it.
I've tried removing DYNAMIC RESULT SETS # but that doesn't seem to matter as I'm not matching the outParam1 datatype to the stored proc on the database.
Could someone tell me what the datatype should be? Or if I've done it completely @ss backwards, let me know how to correct it? |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Sep 02, 2009 9:43 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello.
The bit you are missing is that DYNAMIC RESULT sets do not appear in the procedure's signature as they do in Oracle. Your ESQL definition should look like this (not tested):
| Code: |
CREATE PROCEDURE GetAttributeRefData()
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "TEST.PKG_MARS.getAttributeRefData"; |
And the CALL should look like this (not tested):
| Code: |
DECLARE cursor REFERENCE TO Environment.Variables.MyCursorTest;
CALL GetAttributeRefData(cursor.ResultSet[]); |
Regards,
_________________
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 |
|
|
| isaw |
| Posted: Thu Sep 03, 2009 12:08 am Post subject: |
|
|
Apprentice
Joined: 09 May 2007
Posts: 45
|
Hi mgk,
Thanks for the suggestion - I changed my WMB procedure to remove the param and the calling of said procedure but oracle isn't too happy with that :
| Code: |
Text:CHARACTER:[IBM][ODBC Oracle driver][Oracle]ORA-06550: line 1, column 8: PLS-00306: wrong number or types of arguments in call to 'GETATTRIBUTEREFDATA' ORA-06550: line 1, column 8: PL/SQL: Statement ignored
|
Looks like it still wants a param as the original Oracle proc still has "p_attribcur OUT ref_cur" as the proc param.
I'll muck about with this and see what other examples I can find.
Thanks for the help! |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Sep 03, 2009 12:44 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi, There is also a flag you have to set in odbc.ini (or Datasources control panel on windows) to say "procedure returns results" which allows results-sets to be returned.
_________________
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 |
|
|
| isaw |
| Posted: Thu Sep 03, 2009 1:07 am Post subject: |
|
|
Apprentice
Joined: 09 May 2007
Posts: 45
|
Ah, that seems to have fixed it. It's no longer throwing an exception and continuing onwards.
I guess my eyes glossed over that checkbox in the ODBC config for the datasource. I'll have to remember to set that flag in the *.ini file on the server as well.
Thanks for your help, mgk. Greatly appreciated! |
|
| Back to top |
|
|
| isaw |
| Posted: Thu Sep 03, 2009 1:14 am Post subject: |
|
|
Apprentice
Joined: 09 May 2007
Posts: 45
|
Nope, I spoke to soon (maybe the break points were out of sync). Still throws an exception, saying :
| Code: |
'A non scalar parameter passed to Stored Procedure' (CHARACTER)
CALL TEST.PKG_MARS.GETATTRIBUTEREFDATA ( ? ) } )' (CHARACTER) |
It still doesn't like the empty param passed to the original stored proc in Oracle. (I have restarted the Broker since the flag change). |
|
| Back to top |
|
|
| isaw |
| Posted: Thu Sep 03, 2009 1:23 am Post subject: |
|
|
Apprentice
Joined: 09 May 2007
Posts: 45
|
Got it.
Changed the to a field ref for holding the result set...
Instead of :
| Code: |
DECLARE cursor REFERENCE TO Environment.Variables.MyCursorTest;
CALL GetAttributeRefData(cursor.ResultSet[]);
|
I used :
| Code: |
CALL GetAttributeRefData(Environment.ResultSet1[]);
|
And it's populated with the data I expect to see. Result!
Cheers! |
|
| Back to top |
|
|
|
|
