|
|
Sponsored Content
Top Forums
Programming
Hi Folks please see the code and respond the questions!
Post 302303965 by Haque123 on Saturday 4th of April 2009 05:12:52 AM
04-04-2009
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.
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.
|
|
5 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
Hi,
I am currently in college and thought that I would go for an OCP ceritification....I know Windows fairly well, so I thought that I would learn it (oracle) Linux while I'm at it...
My suSe 7.3 Pro came in the mail yesterday....
so I was curious if you folks could recommend a book that... (1 Reply)
Discussion started by: F4i_Rider
1 Replies
2. Shell Programming and Scripting
Hi ..please tell me how to modinfy an existing file in a directory, i need commands like to open edit and save n execute the file in BASH. please respond immediately (3 Replies)
Discussion started by: deepti.1104
3 Replies
3. Solaris
Hi all,
i am phasing one problem yesterday.when i was login into sun server with my user id, i am trying to execute unix commands but the server responce is very slow. the command result will give after 5-10 min. i checked /var,/tmp and /export/home file system also.every thing is normal... (4 Replies)
Discussion started by: krishna176
4 Replies
4. Programming
Hi,
In a file called itemADT.c I have specified the itemType data type to contain a listADT (having already written this library) and an integer, which will eventually represent the no. of times the word that is stored in the listADT has occurred (when I do this, and write code to insert... (2 Replies)
Discussion started by: eva
2 Replies
5. Programming
Hi Folks UTL_FILE Issue.
I want to do detail study about the UTL FILE package could any one please provide me some of the best detail utl package links to go through and implement in my project will be very help full thanks a ton!!!
Rgds
Ann. (1 Reply)
Discussion started by: Haque123
1 Replies