|
|
Sponsored Content
Top Forums
Programming
Hi Folks please see the code and respond the questions!
Post 302303758 by Haque123 on Friday 3rd of April 2009 11:30:59 AM
04-03-2009
Hi Folks please see the code and respond the questions!
Hi All.
I have a package discreibed down.
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 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';
Selectyear,monthinto v_myear,v_month fromcontrol_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;
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_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);
ENDLOOP;
CLOSE cur_pci;
--INSERT INTO TEMP_IRIS_BACKFEED 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;
/
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.
I have a package discreibed down.
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 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';
Selectyear,monthinto v_myear,v_month fromcontrol_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;
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_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);
ENDLOOP;
CLOSE cur_pci;
--INSERT INTO TEMP_IRIS_BACKFEED 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;
/
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.
|
|
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