| Author |
Message
|
| Frik |
 Posted: Tue Feb 22, 2011 2:49 am Post subject: Can't get out variable from stored procedure |
 |
|
Acolyte
Joined: 25 Nov 2009
Posts: 69
|
hey,
i have an oracle stored procedure, that gets 5 variables:
in - number, number, varchar2, varchar2
out - varchar2.
DECLARE resultMsg CHARACTER;
I'm trying to invoke that procedure like this:
PASSTHRU('{call PUPIL.PUPILS_PKG.update_pupil_info(?,?,?,?,?)}',
123, 456, 'Ron', 'Class A', result);
I can see that I'm reaching the database and the right stored procedure is invoked, as well as there is a result that gets back from the method,
but in the broker - there is nothing on the resultMsg variable.
why is that???
is there any way to call stored procedure from esql with passthru and get the resultMsg? |
|
| Back to top |
|
 |
| mgk |
| Posted: Tue Feb 22, 2011 3:31 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Because PASSTHRU does not support OUT parameters.
Use the CREATE PROCEDURE statement to call Stored Proccedures with OUT parameters instead.
Kind 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 |
|
|
| Frik |
| Posted: Tue Feb 22, 2011 4:01 am Post subject: |
|
|
Acolyte
Joined: 25 Nov 2009
Posts: 69
|
| mgk wrote: |
Because PASSTHRU does not support OUT parameters.
Use the CREATE PROCEDURE statement to call Stored Proccedures with OUT parameters instead.
Kind Regards, |
how do you know that? i couldn't find it documented... |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Feb 22, 2011 4:17 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| how do you know that |
Hmmm, long story 
| Quote: |
| i couldn't find it documented... |
Actually it is documented (in the docs for the PASSTHRU statement) here:
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/topic/com.ibm.etools.mft.doc/ak05100_.htm
The doc explcitly says:
"Note: Do not use PASSTHRU to call stored procedures; instead, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example)."
Kind 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 |
|
|
| Frik |
| Posted: Tue Feb 22, 2011 4:19 am Post subject: a |
|
|
Acolyte
Joined: 25 Nov 2009
Posts: 69
|
| mgk wrote: |
Because PASSTHRU does not support OUT parameters.
Use the CREATE PROCEDURE statement to call Stored Proccedures with OUT parameters instead.
Kind Regards, |
BTW - it's kinda problematic to do it in the CREATE PROCEDURE way -
when i declare odbc, it gets the name "PupilDS" because of our standarts here, but the name of the schema is differenet, so every time I'm trying to call the procedure it search for it in a schema called "PupilDS" which doesn't exist at all. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Feb 22, 2011 4:43 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| so every time I'm trying to call the procedure it search for it in a schema called "PupilDS" which doesn't exist at all |
You can specify the actual schema name to use dynamically on the CALL statement.
Kind 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 |
|
|
| Vitor |
| Posted: Tue Feb 22, 2011 4:56 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mgk wrote: |
| Quote: |
| how do you know that |
Hmmm, long story |
The short version is the location my most worthy associate gives... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Frik |
| Posted: Tue Feb 22, 2011 6:11 am Post subject: |
|
|
Acolyte
Joined: 25 Nov 2009
Posts: 69
|
| mgk wrote: |
| Quote: |
| so every time I'm trying to call the procedure it search for it in a schema called "PupilDS" which doesn't exist at all |
You can specify the actual schema name to use dynamically on the CALL statement.
Kind regards |
How do you do that?
it's still search for the schema i wrote in the "Data source" field of the compute node. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Feb 22, 2011 6:56 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| How do you do that? |
Well, first I read the docs for CALL:
http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/topic/com.ibm.etools.mft.doc/ak04920_.htm
Then I deduced something like this (not tried) should do trick:
| Code: |
| CALL update_pupil_info(p1, p2, p3, p4, p5) IN Database.PUPIL; |
Of course you have to use the CREATE PROCEDURE statement as well to give the prototype for the procedure...
You should also take note of this guidence in the CREATE PROCEDURE docs:
| Quote: |
A fully qualified routine typically takes the form:
EXTERNAL NAME "mySchema.myProc";
However, if the procedure belongs to an Oracle package, the package is treated as part of the procedure's 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 |
Kind 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 |
|
|
|
|
