OLE2 Loop Problem

[krindler]

Hello...

I have the following code, and for some reason, the Loop will not end. Excel is made visible, but my 'Message 6' that I have marked (which I only receive one time), is the last message I never receive 'Message 7'. I have condensed my code as best I could....Can anyone help me to figure out why the loop does not seem to end?

DECLARE

/****************************
*SQL STATEMENT FOR CURRENCY
*****************************/
CURSOR c_currency IS
SELECT DISTINCT gis.currency_code currency_code
FROM global_invoice_class_sum gis,
tmp_vendors vn
WHERE vn.vendor_id = gis.vendor_id
AND gis.chk_month BETWEEN '200201' AND '200212'
ORDER BY gis.currency_code
;

/************************
*SQL STATEMENT FOR PLANT
*************************/
CURSOR c_plant ( p_currency_code VARCHAR2 ) IS
SELECT DISTINCT gis.plant_code plant_code,
gis.plant_code || ': ' || p.plant_name plant
FROM global_invoice_class_sum gis,
plants p,
tmp_vendors vn
WHERE vn.vendor_id = gis.vendor_id
and p.plant_code = gis.plant_code
AND gis.chk_month BETWEEN '200201' AND '200212'
and gis.currency_code = p_currency_code
ORDER BY gis.plant_code,
gis.plant_code || ': ' || p.plant_name
;


/********************************
*SQL STATEMENT FOR VENDOR DETAIL
********************************/
CURSOR c_vendor ( p_currency_code VARCHAR2,
p_plant_code VARCHAR2 ) IS
SELECT gis.vendor_id vendor_id
FROM global_invoice_class_sum gis
WHERE gis.chk_month BETWEEN '200201' AND '200212'
and gis.plant_code = p_plant_code
and gis.currency_code = p_currency_code
ORDER BY vn.vendor_id
;

/******************************
*Declare the PL/SQL Variables
*******************************/
v_currency_code VARCHAR2(8);
v_plant_code VARCHAR2(4);
v_currency_count NUMBER(3);

BEGIN

SELECT COUNT ( currency_code )
INTO v_currency_count
FROM ( select distinct currency_code
FROM global_invoice_class_sum
WHERE substr ( chk_month, 1, 4 ) = '2002'
and ltrim(vendor_id, '0') in
( select vendor_id from tmp_vendors ) );

/***************************
*Open Currency Cursor
****************************/
FOR r_currency IN c_currency LOOP

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG ( args, v_currency_count );
OLE2.ADD_ARG ( args, 1 );
cell := OLE2.GET_OBJ_PROPERTY ( worksheet, 'Cells', args );
OLE2.DESTROY_ARGLIST ( args );

IF r_currency.currency_code = '0000' THEN
OLE2.SET_PROPERTY ( cell, 'Value', 'USD' );
ELSIF r_currency.currency_code = '2999' THEN
OLE2.SET_PROPERTY ( cell, 'Value', 'CANADIAN' );
END IF;

font := OLE2.GET_OBJ_PROPERTY ( cell, 'font' );
OLE2.SET_PROPERTY ( font, 'Size', 5 );
OLE2.SET_PROPERTY ( font, 'bold', true );

OLE2.RELEASE_OBJ ( cell );

/******************
*Open Plant Cursor
*******************/
FOR r_plant IN c_plant ( r_currency.currency_code ) LOOP

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG ( args, v_currency_count + 1 );
OLE2.ADD_ARG ( args, 2 );
cell := OLE2.GET_OBJ_PROPERTY ( worksheet, 'Cells', args );
OLE2.DESTROY_ARGLIST ( args );

OLE2.SET_PROPERTY ( cell, 'Value', r_plant.plant );
font := OLE2.GET_OBJ_PROPERTY ( cell, 'font' );
OLE2.SET_PROPERTY ( font, 'Size', 5 );
OLE2.SET_PROPERTY ( font, 'bold', true );

OLE2.RELEASE_OBJ ( cell );

v_currency_count := v_currency_count + 1;

/***************************
*Open Vendor Detail Cursor
***************************/
FOR r_vendor IN c_vendor (
r_currency.currency_code,
r_plant.plant_code ) LOOP

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG ( args, v_currency_count + 1 );
OLE2.ADD_ARG ( args, 3 );
cell := OLE2.GET_OBJ_PROPERTY ( worksheet, 'Cells', args );
OLE2.DESTROY_ARGLIST ( args );

OLE2.SET_PROPERTY ( cell, 'Value', r_vendor.vendor_id );
font := OLE2.GET_OBJ_PROPERTY ( cell, 'font' );
OLE2.SET_PROPERTY ( font, 'Size', 5 );

OLE2.RELEASE_OBJ ( cell );

v_currency_count := v_currency_count + 1;

END LOOP;

v_currency_count := v_currency_count + 2;

END LOOP;

*****MESSAGE ( '6' );

v_currency_count := v_currency_count + 3;
END LOOP;

MESSAGE ( '7' );

OLE2.SET_PROPERTY ( application, 'Visible', true );

--Release all object handles
OLE2.RELEASE_OBJ ( cell );
OLE2.RELEASE_OBJ ( worksheet );
OLE2.RELEASE_OBJ ( worksheets );
OLE2.RELEASE_OBJ ( workbook );
OLE2.RELEASE_OBJ ( workbooks );
OLE2.RELEASE_OBJ ( application );

END;

 

[krindler]

Never mind...I figured out what was causing my problem. I needed to take out the last OLE2.RELEASE_OBJ ( cell ); line and everything now works fine.

Thanks Anyway!!