Hi All.
I have a package discreibed down.
CREATE OR REPLACE PACKAGE BODY IRISCOS_REFRESH_pkg AS
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 p.ssn=cstc.ssn
and p.ssn=pci.ssn
and pci.primary_plan=pl.plan_id
and ptc.ssn=p.ssn
and ptc.end_date > sysdate
and pci.end_date > sysdate
and pl.plan_name like c_myear;
C_CID char(11) ;
C_ELID char(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 varchar2(10);
v_filename varchar2(40);
file_handle utl_file.file_type;
V_year number(4);
V_month number(10);
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 );
--Execute immediate 'Truncate table temp_iris_refresh_back';
Select year ,month into v_myear,v_month from control_register;
V_year:=v_myear;
v_myear:=v_myear;--||'%';
OPEN cur_pci(v_myear);
LOOP
File_handle :=utl_file.fopen('/cddata/feeds_in', v_filename,'w');
Fetch cur_pci INTO C_CID ,C_ELID, C_first_name, C_Last_name, C_email_id, C_Plan_name, C_Chair_id;
EXIT when cur_pci%NOTFOUND;
Select org_level into C_org_level
from chair c,org_struct os
where c.chair_id=C_Chair_id and
c.org_struct_id=os.org_struct_id and
c.end_date > sysdate and os.end_date > sysdate;
If C_org_level > 3 then
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=3 then
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 > sysdate and
c2.chair_id=ctm.div_or_area_chair_id and
c2.end_date > sysdate;
Elsif c_org_level =2 then
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 > sysdate and
c2.chair_id=ctm.div_or_area_chair_id and
c2.end_date > sysdate and
c3.chair_id=ctm.region_chair_id and
c3.end_date > sysdate;
Elsif c_org_level =1 or c_org_level=0 then
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 > sysdate and
c2.chair_id=ctm.div_or_area_chair_id and
c2.end_date > sysdate and
c3.chair_id=ctm.region_chair_id and
c3.end_date > sysdate and
c4.chair_id=ctm.DIST_OR_BRANCH_CHAIR_ID and
c4.end_date > sysdate;
else
Raise Invalid_orglevel;
End if;
INSERT INTO temp_iris_refresh_back values(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_month,V_year);
Utl_file.put_line (file_handle, 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 );
row_c:=row_c+1;
Utl_file.fclose(file_handle);
END LOOP;
CLOSE cur_pci;
--INSERT INTO TEMP_IRIS_BACKFEED VALUES (TRL,row_c);
EXCEPTION
WHEN no_data_found then
Dbms_output.put_line ('There are no data found to pull');
WHEN invalid_orglevel then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Level');
END;
END IRISCOS_REFRESH_pkg;
/
After the execution of the packge from Unix I get a message as:
>./test_refresh.ksh
1 row created.
Session altered.
PL/SQL procedure successfully completed.
1.After when I check the results in the table the table has no rows in it
expected: huge data
2.Also this package write into a file through UTL file
when I see into that directory I could see only the file name with zero data.
I need to fix this ASAP
Suggest your answers.
