Hi folks I have corrected some of the things in the package and its writing into the table as well as file thanks for you help.
I need one favour more my package is writing only one row into the file.
see the modified query below:
CREATEORREPLACEPACKAGEBODYIRISCOS_REFRESH_pkgAS
PROCEDURE IRISCOS IS
CURSOR cur_pci(c_myear number)
IS
Select cstc.cid, cstc.elid, p.first_name,p.last_name,p.email_id ,pl.plan_name, ptc.chair_id
from
person p,person_to_chair ptc,conv_ssn_to_corpid cstc,person_comp_info pci,plan pl
Where
pci.status_code='A'
and ptc.end_date >sysdate
and pci.end_date >sysdate
and pci.primary_plan=pl.plan_id
and ptc.ssn=p.ssn
and p.ssn=cstc.ssn
and p.ssn=pci.ssn;
--and pl.plan_name like c_myear;
C_CID char(11);
C_ELID varchar2(9);
C_first_name char(30);
C_Last_name char(30);
C_email_id char(70);
C_Plan_name char(50);
C_Chair_id varchar2(10);
C_MBU_Chair_Name char(20);
C_MBU_Chair_Id number(10);
C_Area_Chair_Name char(20):='';
C_Area_Chair_Id number(10):='';
C_Region_Chair_Name char(20):='';
C_Region_Chair_Id number(10):='';
C_Branch_Chair_Name char(20):='';
C_Branch_Chair_Id number(10):='';
C_Org_Level number(2);
row_c number:=0;
v_myear number(4);
v_filename varchar2(40);
file_handle utl_file.file_type;
V_year number(4);
V_month number(2);
Invalid_orglevel EXCEPTION;
BEGIN
SELECT TO_CHAR(sysdate,'yyyymmddhh24miss')into v_filename from dual;
V_Filename:='c2_iris_refresh_'||v_filename||'.dat';
File_handle :=utl_file.fopen('/cddata/feeds_in', v_filename,'W');
Dbms_output.put_line('Refresh file name is :' || v_filename );
Executeimmediate'Truncate table temp_iris_refresh_back';
Selectyear,monthinto v_myear,v_month fromcontrol_register;
V_year:=v_myear;
v_myear:=v_myear;--||'%';
OPEN cur_pci(v_myear);
LOOP
Fetch cur_pci INTO C_CID ,C_ELID, C_first_name, C_Last_name, C_email_id, C_Plan_name, C_Chair_id;
EXITwhen cur_pci%NOTFOUND;
Select org_level into C_org_level
fromchair c,org_struct os
where c.chair_id=C_Chair_id and
c.org_struct_id=os.org_struct_id and
c.end_date >sysdateand os.end_date >sysdate;
If C_org_level >3then
select
c1.chair_name MBU_Chair_name,ctm.mbu_chair_id
into C_mbu_chair_name, c_mbu_chair_id
from
Chair c1,Chair_to_mbu ctm
where
ctm.chair_id=C_chair_id and
c1.chair_id=ctm.mbu_chair_id and
c1.end_date >sysdate;
Elsif C_org_level=3then
select
c1.chair_name MBU_Chair_name,ctm.mbu_chair_id, C2.Chair_name
area_Chair_name,ctm.div_or_area_chair_id
into c_mbu_chair_name,
c_mbu_chair_id, c_area_chair_name, c_area_chair_id
from
Chair c1,Chair c2,Chair_to_mbu ctm
where
ctm.chair_id=C_chair_id and
c1.chair_id=ctm.mbu_chair_id and
c1.end_date >sysdateand
c2.chair_id=ctm.div_or_area_chair_id and
c2.end_date >sysdate;
Elsif c_org_level =2then
select
c1.chair_name ,ctm.mbu_chair_id, C2.Chair_name,
ctm.div_or_area_chair_id, c3.chair_name , ctm.region_chair_id
into c_mbu_chair_name, c_mbu_chair_id,
c_area_chair_name, c_area_chair_id, c_region_chair_name,
c_region_chair_id
from
Chair c1,Chair c2,Chair c3,Chair_to_mbu ctm
where
ctm.chair_id=C_chair_id and
c1.chair_id=ctm.mbu_chair_id and
c1.end_date >sysdateand
c2.chair_id=ctm.div_or_area_chair_id and
c2.end_date >sysdateand
c3.chair_id=ctm.region_chair_id and
c3.end_date >sysdate;
Elsif c_org_level =1or c_org_level=0then
select
c1.chair_name ,ctm.mbu_chair_id, C2.Chair_name,
ctm.div_or_area_chair_id, c3.chair_name , ctm.region_chair_id,
c4.chair_name , ctm.DIST_OR_BRANCH_CHAIR_ID
into c_mbu_chair_name, c_mbu_chair_id, c_area_chair_name,
c_area_chair_id, c_region_chair_name, c_region_chair_id,
c_branch_chair_name, c_branch_chair_id
from
Chair c1,Chair c2,Chair c3,Chair c4,Chair_to_mbu ctm
where
ctm.chair_id=C_chair_id and
c1.chair_id=ctm.mbu_chair_id and
c1.end_date >sysdateand
c2.chair_id=ctm.div_or_area_chair_id and
c2.end_date >sysdateand
c3.chair_id=ctm.region_chair_id and
c3.end_date >sysdateand
c4.chair_id=ctm.DIST_OR_BRANCH_CHAIR_ID and
c4.end_date >sysdate;
else
Raise Invalid_orglevel;
Endif;
INSERTINTOtemp_iris_refresh_backvalues(C_CID,C_ELID, C_FIRST_NAME, C_LAST_NAME, C_EMAIL_ID, C_PLAN_NAME, C_MBU_CHAIR_NAME, C_MBU_CHAIR_ID, C_AREA_CHAIR_NAME, C_AREA_CHAIR_ID, C_REGION_CHAIR_NAME, C_REGION_CHAIR_ID, C_BRANCH_CHAIR_NAME, C_BRANCH_CHAIR_ID, C_ORG_LEVEL,V_year ,V_month);
Utl_file.put_line (file_handle, TRIM(C_CID)||'|'||
TRIM(C_ELID)||'|'||
TRIM(C_FIRST_NAME)||'|'||
TRIM(C_LAST_NAME)||'|'||
TRIM(C_EMAIL_ID)||'|'||
TRIM(C_PLAN_NAME)||'|'||
TRIM(C_MBU_CHAIR_NAME)||'|'||
TRIM(C_MBU_CHAIR_ID)||'|'||
TRIM(C_AREA_CHAIR_NAME)||'|'||
TRIM(C_AREA_CHAIR_ID)||'|'||
TRIM(C_REGION_CHAIR_NAME)||'|'||
TRIM(C_REGION_CHAIR_ID)|| '|'||
TRIM(C_BRANCH_CHAIR_NAME)|| '|'||
TRIM(C_BRANCH_CHAIR_ID)|| '|'||
TRIM(C_ORG_LEVEL));
row_c:=row_c+1;
Utl_file.put_line (file_handle,'TRL'||'|'||row_c);
ENDLOOP;
CLOSE cur_pci;
--File_handle :=utl_file.fopen('/cddata/feeds_in', v_filename,'W');
Utl_file.fclose(file_handle);
--INSERT INTO temp_iris_refresh_back VALUES ('TRL',row_c,'','','','','','','','','','','','','','','');
EXCEPTION
WHENno_data_foundthen
Dbms_output.put_line ('There are no data found to pull');
WHEN invalid_orglevel then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Level');
END;
ENDIRISCOS_REFRESH_pkg;
/
Please respond why its writing only once into the package rather the huge data why the cursor is getting closed after only one row fetch any suggestions.
Rgds
Ann.
04-03-2009
