1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
|
SET serveroutput ON
DECLARE
type myTableType IS TABLE OF LO_VESSEL.FLEET_UID%TYPE;
vLst VARCHAR2(2000);
vYEAR VARCHAR2(2);
FUNCTION in_list(
p_string IN VARCHAR2 )
RETURN myTableType
AS
l_string LONG DEFAULT p_string || ',';
l_data myTableType := myTableType();
n NUMBER;
BEGIN
LOOP
EXIT
WHEN l_string IS NULL;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) := ltrim( rtrim( SUBSTR( l_string, 1, n-1 ) ) );
l_string := SUBSTR( l_string, n +1 );
END LOOP;
RETURN l_data;
END;
PROCEDURE P_EXPORT_CONSO_VESSEL(
pLst IN VARCHAR2 ,
pYear in VARCHAR2)
AS
v_SQLYEAR VARCHAR2(400);
WBK RXLS.XLS_WorkBook; -- structure de données du classeur
TYPE integerVarray IS VARRAY(12) OF INTEGER;
counter INTEGER := 0;
TYPE CURSOR_EXPORT
IS
REF
CURSOR;
cValuationReport CURSOR_EXPORT;
TYPE t_recYear
IS
RECORD
(
FLEET_UID LO_FLEET.FLEET_uid%TYPE
);
rValuationReport t_recYear;
BEGIN
-- Création de 12 feuilles
---- construction de la requete
v_SQLYEAR :='SELECT ve.FLEET_UID FROM LO_VESSEL ve where ve.FLEET_UID in (cast( in_list(: pLst) as mytableType ))';
OPEN cValuationReport FOR v_SQLYEAR USING pLst;
LOOP
FETCH cValuationReport INTO rValuationReport;
EXIT
WHEN cValuationReport%NOTFOUND;
FOR counter IN 1..12
LOOP
RXLS.SetCellNumber (WBK, 1, 0,8, rValuationReport.FLEET_UID,NULL); -- Prix anuelle
END LOOP;
END LOOP;
END P_EXPORT_CONSO_VESSEL;
BEGIN
vYEAR:='11';
vLst :='2,6,8,10,12';
P_EXPORT_CONSO_VESSEL(vLst,vYEAR );
END; |
Partager