| Author |
Message
|
| shashivarungupta |
 Posted: Mon Feb 16, 2015 6:20 pm Post subject: Call Package (Oracle) from Mapping Node? |
 |
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
Hi,
Has anyone faced a scenario where 'Packages' of Oracle Database (v11) is called from Mapping Node [ of WebSphere Message Broker v8 ] to update one or more rows in a table, based on input message?
Where, 'Packages' has got lots many functions in it, and out of those Mapping node needs to call one function to get the Table updated.
Its like..
Package.functname(parameter1,parameter2) and that function return the integer value after successful update to the Table.
I don't see an option to select the 'package' as we do select the database 'Table' using graphical mapping.
(At present its a kind of requirement to use Mapping node and call that Package Function, instead of directly updating the Table or options.)
Any suggestion?
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Feb 16, 2015 10:28 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Aren't packages just Oracle's way of describing Stored Procedures?
(been a while since I used Oracle)
If this is correct then defining them in the normal way may (as an external function) well work if you are calling them from ESQL. If you are using JAVA then I really haven't a clue how to define them (Java is a black hole as far as I'm concerned)
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Simbu |
| Posted: Mon Feb 16, 2015 10:47 pm Post subject: |
|
|
Master
Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India
|
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Tue Feb 17, 2015 1:34 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
| smdavies99 wrote: |
Aren't packages just Oracle's way of describing Stored Procedures?
|
Yup, AFAIK.
| Code: |
Oracle says:
A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (called the spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside of the package. The body defines the queries for the cursors and the code for the subprograms. |
Also in DB2, here they talk about Packages, but I think that totally different from what they have in Oracle. (Here I am not the Database Professional and I am not supposed to make comparisons between Packages of different products).
I'll try using ESQL .. so, PASSTHRU would do in that case?
Or CALL Statement to invoke the external database packaged function that returns a value.
_________________
*Life will beat you down, you need to decide to fight back or leave it.
Last edited by shashivarungupta on Tue Feb 17, 2015 2:07 pm; edited 1 time in total |
|
| Back to top |
|
|
| nelson |
| Posted: Tue Feb 17, 2015 2:07 pm Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
|
| Back to top |
|
|
| phanish |
| Posted: Mon Feb 23, 2015 2:13 am Post subject: |
|
|
Apprentice
Joined: 04 May 2013
Posts: 30
|
Hi,
The link is to call an oracle SP from esql.Is it possible to invoke a Stored Procedure from a MAP,in IIB9,by inputting a value from the request,which has to be looked up in the Database table and return a column value? Can this be done using the DSN configured. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Feb 23, 2015 6:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| phanish wrote: |
Hi,
The link is to call an oracle SP from esql.Is it possible to invoke a Stored Procedure from a MAP,in IIB9,by inputting a value from the request,which has to be looked up in the Database table and return a column value? Can this be done using the DSN configured. |
You can use whatever database functions exist in the mapping node.
This does not include calling databases from a custom ESQL procedure being called by a map.
Last I knew, the map only supported insert, update, and delete, but not call. But that's unreliable compared to the correct documentation. |
|
| Back to top |
|
|
| nelson |
| Posted: Mon Feb 23, 2015 7:33 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
| phanish wrote: |
Hi,
The link is to call an oracle SP from esql.Is it possible to invoke a Stored Procedure from a MAP,in IIB9,by inputting a value from the request,which has to be looked up in the Database table and return a column value? Can this be done using the DSN configured. |
As Simbu said before :
| Simbu wrote: |
| IIB 9 has this feature but it is only for DB2 stored procedure and not for Oracle. |
|
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Mon Feb 23, 2015 1:42 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
| phanish wrote: |
| The link is to call an oracle SP from esql.... |
And yes.. that works. You need to have ODBC connection to achieve that. (I used Compute Node)
| phanish wrote: |
| ... Can this be done using the DSN configured. |
(ODBC Connection)SYSTEM DSN configuration is required to CALL the Procedure/Function (may be of Oracle).
Before you CALL, you must have to Create Procedure or Function.
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Wed Feb 25, 2015 5:30 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
| Simbu wrote: |
As smdavies suggested, you may have to use ESQL or JAVA. |
And since I have applied the suggestion [suggestion that's within broker's limitations, in v8] .. I was wondering from the prespective of database configuration on different platforms (other than Windows, could be Linux/Solaris/AIX) and from solution artitechtural perspective.. what monitoring tools are supplied to check the odbc connections (over Oracle db) when Message Broker makes a call to it (via odbc)?
I know its bit inclined towards database, but wanted to hear your peace of thoughts, if you've came across or known or thought about it.
( Sorry for bringing this up again )
And Thanks once again.
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
|
| shashivarungupta |
| Posted: Tue Mar 10, 2015 2:55 pm Post subject: |
|
|
Grand Master
Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.
|
As a response to this not very old post.. refer following link shared by IBM to understand the Broker/IIB behaviour over ODBC connections (managed by broker itself)...
Here
Hope this will help other mqseries.net users as well. Thanks.
_________________
*Life will beat you down, you need to decide to fight back or leave it. |
|
| Back to top |
|
|
| martinb |
| Posted: Wed Mar 02, 2016 12:04 pm Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
|
| Back to top |
|
|
|
|
