| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Procedure with RefCursor return through Oracle Apps Adapter |
« View previous topic :: View next topic » |
| Author |
Message
|
| needhelp |
 Posted: Wed Mar 21, 2007 1:28 am Post subject: Procedure with RefCursor return through Oracle Apps Adapter |
 |
|
Newbie
Joined: 20 Mar 2007
Posts: 1
|
Hi Guys,
I need your help regarding invoking Stored procedures in Oracle database which returns a record set (a REF CORSOR in Oracle procedure) as an OUT parameter.
I need to invoke the procedure in a specific package through the Oracle Apps adapter. The returned Record set is to be used in other applications. I am unable to catch the record set through the adapter.
Anyone having some specific clue, please let me know. That will be very much helpful.
-------------------------------------------------------------------------------------
The stored procedure is as below:
PROCEDURE GET_TEST_TABLE (
pid IN NUMBER,
precordset1 OUT SYS_REFCURSOR) AS
BEGIN
IF precordset1%ISOPEN THEN
CLOSE precordset1;
END IF;
OPEN precordset1 FOR
SELECT *
FROM TEST_TABLE
WHERE ID >= pid;
END GET_TEST_TABLE; |
|
| Back to top |
|
 |
| mqseries0209 |
| Posted: Wed Mar 21, 2007 9:44 am Post subject: Hey |
|
|
Voyager
Joined: 30 Mar 2006
Posts: 90
|
Hey, I did not worked with Oracle Apps adapter, but worked with JDBC adapter instead ,in invoking SP, which returns ResultSet (RS) in SP.
For that you have to make sure you have a wrapper for BO , and ASI WRAPPER=true for the top level BO.
The child BO should have the RetrieveSP and necesary ASI for the fields corresponding to the columns of RS.
I have found good information in JDBC adapter usage guide in infocenter.
Search for Stored Procedure.
Hope this helps. |
|
| Back to top |
|
|
| mqseries0209 |
| Posted: Fri Mar 23, 2007 5:33 am Post subject: |
|
|
Voyager
Joined: 30 Mar 2006
Posts: 90
|
From: needhelp
To: mqseries0209
Posted: Thu Mar 22, 2007 10:30 pm
Subject: Re: Regarding procedure with RefCursor
Hi MQSeries0209,
Thank you once again for your reply.
The SP Definition is as follows:
PROCEDURE GET_TEST_TABLE (
pid IN NUMBER,
precordset1 OUT SYS_REFCURSOR) AS
BEGIN
IF precordset1%ISOPEN THEN
CLOSE precordset1;
END IF;
OPEN precordset1 FOR
SELECT * FROM TEST_TABLE
WHERE ID >= pid;
END GET_TEST_TABLE;
-----------------------------------------------------------------------
I was doing R&D with the Child and Wrapper BOs. but couldn't succeed in invoking the procedure.
I have used retrieveSP, and made table name (TN) as 'dummy'. For SP related properties, I have made RS=true and OP=RS.
The Wrapper BO contains Child BO with N cardinality.
Can you please explain exactly what are the attributes that should be there in the child and wrapper BOs. And what other changes shall I make.
Thank you very much for your time and valuable suggestions. |
|
| Back to top |
|
|
| mqseries0209 |
| Posted: Fri Mar 23, 2007 5:54 am Post subject: |
|
|
Voyager
Joined: 30 Mar 2006
Posts: 90
|
hi Needhelp,
As I told you that I will post our PM messages here for two purposes
1) Some one would correct me if I am worng or add more information .
2) When the issue is solved will be helpful to others.
Here on continue to post ur commecnts in this thread and update us.
Comint to the point.
Your SP is :
PROCEDURE GET_TEST_TABLE (
pid IN NUMBER,
precordset1 OUT SYS_REFCURSOR) AS
BEGIN
IF precordset1%ISOPEN THEN
CLOSE precordset1;
END IF;
OPEN precordset1 FOR
SELECT * FROM TEST_TABLE
WHERE ID >= pid;
END GET_TEST_TABLE;
which is accepting onr input param and returning a ref cursor.
Lets assume that the table Test_Table has three columns (simple example):
Name
SSN
Address
then Wrapper BO is 'W' and Chile BO is called 'C'
The W BO should contain two elements:
pid (Input for SP)
C (child BO , will define in a litle bit) ----------- n cardinality
This BO level ASI should have:
WRAPPER=true;
Lets define the BO C (child BO)
It should have following elements
RetrieveSP ------------- SPN=GET_TEST_TABLE,
RS=true,IP=pid,OP=RS
pid -------------- FK=W.pid
Name .................. CN=Name
SSN ..................... CN=SSN
Address ..................... CN=Address
thats it:
I cant explain more detailed than this, this is more of a spoon feeding.
If you still have problems, test ur SP using a JAVA program and see it works (for debugging SP)
Check with the version of adapter u r using for any bugs on IBM site.
-Hope this helps
Time to follow up game India Vs Srilanka................  |
|
| Back to top |
|
|
| keenlearner |
| Posted: Mon Apr 09, 2007 8:43 pm Post subject: |
|
|
Acolyte
Joined: 24 Aug 2006
Posts: 62
|
hi mqseries0209
Can u post the sample BO for this. It will be really helpful |
|
| Back to top |
|
|
| 4integration |
| Posted: Wed Sep 12, 2007 7:46 am Post subject: |
|
|
Disciple
Joined: 04 Sep 2006
Posts: 197
Location: Gothenburg, Sweden
|
I having a similar problem with an Oracle Stored Procedure.
I would appreciate any help on this.
The SP looks like:
| Code: |
CREATE OR REPLACE PROCEDURE VRH.PROC_VOSP_QRY_GDS_REPAIR (
p_TCHASSIS IN varchar2,
p_REPAIRDATE IN varchar2,
ResultSet1 OUT SYS_REFCURSOR) AS
BEGIN
IF ResultSet1%ISOPEN THEN
CLOSE ResultSet1;
END IF;
OPEN ResultSet1 FOR
SELECT
TCHASSIS AS CHASSIS_ID,
MILEAGE,
REPAIRDATE AS SERVICE_DATE,
CODE AS OPERATION_TYPE
FROM
vw_VOSP_qry_GDS_repair
WHERE
tchassis = p_tchassis
and REPAIRDATE >= to_date(p_repairdate, 'YYYYMMDD')
order by repairdate asc;
END PROC_VOSP_QRY_GDS_REPAIR;
/ |
Using WBIA for JDBC v2.6.6 and have created the business object as:
| Code: |
[ReposCopy]
Version = 3.0.0
[End]
[BusinessObjectDefinition]
Name = PROC_VOSP_QRY_GDS_REPAIR
Version = 3.0.0
AppSpecificInfo = TN=PROC_VOSP_QRY_GDS_REPAIR;SCN=
[Attribute]
Name = RetrieveSP
Type = String
Cardinality = 1
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
AppSpecificInfo = SPN=PROC_VOSP_QRY_GDS_REPAIR;RS=true;IP=P_TCHASSIS:P_REPAIRDATE;OP=RS
IsRequiredServerBound = false
[End]
[Attribute]
Name = P_TCHASSIS
Type = String
Cardinality = 1
MaxLength = 255
IsKey = true
IsForeignKey = false
IsRequired = false
AppSpecificInfo = FK=PROC_VOSP_QRY_GDS_REPAIR_WRAPPER.P_TCHASSIS
IsRequiredServerBound = false
[End]
[Attribute]
Name = P_REPAIRDATE
Type = String
Cardinality = 1
MaxLength = 255
IsKey = true
IsForeignKey = false
IsRequired = false
AppSpecificInfo = FK=PROC_VOSP_QRY_GDS_REPAIR_WRAPPER.P_REPAIRDATE
IsRequiredServerBound = false
[End]
[Attribute]
Name = CHASSIS_ID
Type = String
Cardinality = 1
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
AppSpecificInfo = CN=CHASSIS_ID
IsRequiredServerBound = false
[End]
[Attribute]
Name = MILEAGE
Type = String
Cardinality = 1
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
AppSpecificInfo = CN=MILEAGE
IsRequiredServerBound = false
[End]
[Attribute]
Name = SERVICE_DATE
Type = String
Cardinality = 1
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
AppSpecificInfo = CN=SERVICE_DATE
IsRequiredServerBound = false
[End]
[Attribute]
Name = OPERATION_TYPE
Type = String
Cardinality = 1
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
AppSpecificInfo = CN=OPERATION_TYPE
IsRequiredServerBound = false
[End]
[Attribute]
Name = ObjectEventId
Type = String
Cardinality = 1
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
IsRequiredServerBound = false
[End]
[Verb]
Name = Retrieve
[End]
[End]
[BusinessObjectDefinition]
Name = PROC_VOSP_QRY_GDS_REPAIR_WRAPPER
Version = 3.0.0
AppSpecificInfo = WRAPPER=TRUE
[Attribute]
Name = P_TCHASSIS
Type = String
Cardinality = 1
MaxLength = 11
IsKey = true
IsForeignKey = false
IsRequired = false
IsRequiredServerBound = false
[End]
[Attribute]
Name = P_REPAIRDATE
Type = String
Cardinality = 1
MaxLength = 255
IsKey = true
IsForeignKey = false
IsRequired = false
IsRequiredServerBound = false
[End]
[Attribute]
Name = boList
Type = PROC_VOSP_QRY_GDS_REPAIR
ContainedObjectVersion = 3.0.0
Relationship = Containment
Cardinality = N
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
IsRequiredServerBound = false
[End]
[Attribute]
Name = ObjectEventId
Type = String
Cardinality = 1
MaxLength = 255
IsKey = false
IsForeignKey = false
IsRequired = false
IsRequiredServerBound = false
[End]
[Verb]
Name = Create
[End]
[Verb]
Name = Delete
[End]
[Verb]
Name = Retrieve
[End]
[Verb]
Name = Update
[End]
[End]
|
_________________
Best regards
4 Integration |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
