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?
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.
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.