Mycode2

CREATE OR REPLACE PROCEDURE F1
(
I_FUND_NO IN VARCHAR2,
I_FUND_NAME IN VARCHAR2,
O_FUND OUT OBJ_FUND_A,
O_FUND1 OUT OBJ_FUND
)

IS
V_FUND OBJ_FUND;

COUNT1 NUMBER;

CURSOR C1 IS

SELECT FUND_NO,FUND_NAME
FROM FUND
WHERE (FUND_NO= I_FUND_NO OR FUND_NAME=I_FUND_NAME) OR (I_FUND_NO IS NULL AND I_FUND_NA
ME IS NULL) ;

BEGIN

O_FUND:= OBJ_FUND_A();

COUNT1:= 0;
FOR FUNDINFO IN C1
LOOP

--CREATE AN OBJECT

V_FUND:= NEW OBJ_FUND(NULL,NULL);
COUNT1:=COUNT1+1;

--SET VALUES TO EMPTY OBJECT

V_FUND.FUND_NO:= FUNDINFO.FUND_NO;
V_FUND.FUND_NAME:=FUNDINFO.FUND_NAME;
O_FUND1:=V_FUND;

--SET OBJECT INTO COLLECTION

O_FUND.EXTEND;
O_FUND(COUNT1):=V_FUND;

END LOOP;

END;

/

Procedure created.

DECLARE
I_FUND_NO VARCHAR2(15);
I_FUND_NAME VARCHAR2(15);
O_FUND OBJ_FUND_A;
O_FUND1 OBJ_FUND;
BEGIN
I_FUND_NO:='&FUND_NO';
I_FUND_NAME:='&FUND_NAME';


SCOTT.F1(I_FUND_NO,I_FUND_NAME,O_FUND,O_FUND1);

IF O_FUND IS NOT NULL AND I_FUND_NO IS NOT NULL OR I_FUND_NAME IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('FUNDDETAILS::' ||O_FUND1.FUND_NO || '-'||O_FUND1.FUND_NAME);
END IF;
IF O_FUND IS NOT NULL AND I_FUND_NO IS NULL AND I_FUND_NAME IS NULL THEN
BEGIN
FOR I IN 1..O_FUND.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('FUNDDETAILS::' ||O_FUND(I).FUND_NO ||'-'||O_FUND(I).FU
ND_NAME);
END LOOP;
END;
END IF;
END;
/
Enter value for fund_no: FNO2
old 7: I_FUND_NO:='&FUND_NO';
new 7: I_FUND_NO:='FNO2';
Enter value for fund_name:
old 8: I_FUND_NAME:='&FUND_NAME';
new 8: I_FUND_NAME:='';
FUNDDETAILS::FNO2-FNAME2

PL/SQL procedure successfully completed.

SQL> /
Enter value for fund_no:
old 7: I_FUND_NO:='&FUND_NO';
new 7: I_FUND_NO:='';
Enter value for fund_name: FNAME3
old 8: I_FUND_NAME:='&FUND_NAME';
new 8: I_FUND_NAME:='FNAME3';
FUNDDETAILS::FNO3-FNAME3

PL/SQL procedure successfully completed.

SQL> /
Enter value for fund_no:
old 7: I_FUND_NO:='&FUND_NO';
new 7: I_FUND_NO:='';
Enter value for fund_name:
old 8: I_FUND_NAME:='&FUND_NAME';
new 8: I_FUND_NAME:='';
FUNDDETAILS::FNO1-FNAME1
FUNDDETAILS::FNO2-FNAME2
FUNDDETAILS::FNO4-FNAME4
FUNDDETAILS::FNO5-FNAME5
FUNDDETAILS::FNO6-FNAME6
FUNDDETAILS::FNO3-FNAME3

PL/SQL procedure successfully completed.