Hi,

I am newbie to webMethods and have a scenerio where the data should be retrieved from DB based on Group_ID or Group_Name; process the retrieved data and delete all the processed data based on Group_ID ro Group_Name.

Could we perform the above mechanism by implementing a Store Procedure call? If so, could someone explain how that can be performed using a SP Call?

Usually, the above scenerio can be done using REF Cursor but was unaware of how webMethods handles REF Cursors and where could I find the document/s that explain REF Cursor usage?

Thanks in advance.

Hello!

No takers on this one? I really need some help on this and appreciate all your suggestions.

Thanks,
John

John,

Is there a reason that the cursor loop cannot be run within the stored procedure? That will be much more efficient.

What DB version are you on?

Regards

John,

I was suggesting that you forget the ref cursor, set up a cursor loop in the stored procedure do any grouping required there, and pass in any values needed in your PL/SQL WHERE or HAVING clauses as arguments to the stored procedure.

I asked about your Oracle version because depending on how many rows there are to fetch, you can get some really good performance optimization using tricks like array fetches (BULK COLLECT). 10g is supposed to automate this, but I haven’t checked into it yet. If you had any significant computation, native compilation would speed things up as well.

This will be MUCH faster than sucking rows 1-at-a-time into webMethods.

Mark,

Thanks for suggestions. I will try to work that way and will let know if have any questions.

Sounds good.

If you decide you really must use a ref cursor, the PL/SQL would look like this example hitting the emp table of the SCOTT schema:

CREATE OR REPLACE PROCEDURE SHOW_EMPLOYEES_CURSOR(a_results OUT SYS_REFCURSOR) IS
v_empno number;
v_ename varchar2(10);
v_job varchar2(9);
v_mgr number;
v_hiredate date;
v_sal number;
v_comm number;
v_deptno number;

BEGIN
OPEN a_results FOR SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
FROM emp;

RETURN;

END SHOW_EMPLOYEES_CURSOR;

On the webMethods side, you set up a storedProcedureWithSignature adapter service. On the call tab, enter the stored procedure name. The args should be populated for you. On the resultset tab, in the lower table, add a row for every column in the result set. Select the name of your ref cursor out arg in the result set name dropdown for each row. I set the type for all rows to VARCHAR.

The retch results will be pulled into an IS document which will contain a document list containing the rows fetched from the cursor.

Regards

Mark,

Thanks for taking pain to provide me with a nice solution. I didnt get the chance to explore that today but will definetly try the way you mentioned.