Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_PROFILER Results
| DBMS_PROFILER Results [message #187071] |
Thu, 10 August 2006 14:52  |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Hi everyone!
Any body has any idea why line 689 is taking so much resouces?
Please let me know if you need any further info on the following.
Please suggests me How can i improve the performance?
SQL> prompt Most popular lines (more than 1%), summarize across all runs
SQL> SELECT p1.runid AS runid, p1.total_time/10000000 AS Hsecs,
2 p1.total_time/p4.grand_total*100 AS pct,
3 SUBSTR(p2.unit_owner, 1, 20) AS owner,
4 DECODE(p2.unit_name, '', '<anonymous>',
5 SUBSTR(p2.unit_name,1, 20)) AS unit_name, p1.line#, (
6 SELECT p3.text
7 FROM all_source p3
8 WHERE p3.owner = p2.unit_owner
9 AND p3.line = p1.line#
10 AND p3.name=p2.unit_name
11 AND p3.type NOT IN ('PACKAGE', 'TYPE')) text
12 FROM plsql_profiler_data p1, plsql_profiler_units p2,
13 plsql_profiler_grand_total p4
14 WHERE (p1.total_time >= p4.grand_total/100)
15 AND p1.runID = p2.runid
16 AND p2.unit_number=p1.unit_number
17 and p1.runid = 28
18* ORDER BY p1.total_time desc
SQL> /
RUNID HSECS PCT OWNER UNIT_NAME LINE# TEXT
---------- ---------- ---------- -------------------- -------------------- ---------- --------------
28 47010.8718 27.4094401 AKDADHAN PK_CDTABLE_PART_TEST 689 SELECT BeginDate, EndDate,.....--------> Why ? can't understand?
28 7096.52888 4.13759363 AKDADHAN PK_CDTABLE_PART_TEST 816 FETCH curcd -------->bearable result
28 5481.77693 3.19612104 AKDADHAN PK_CDTABLE_PART_TEST 975 FORALL i IN 1 .. claimspli -------->bearable result
Elapsed: 00:00:00.25
TEXT
--------------
SELECT BeginDate, EndDate,.....--------> Why takes so much HSECS AND PCT ? can't understand.
FETCH curcd -------->bearable result
FORALL i IN 1 .. claimspli -------->bearable result
HERE IS WHAT IN THE PACKAGE BODY contains where line 689# :
line
------
FUNCTION f_GetFiscalInfo (v_Date IN DATE)
RETURN tFiscalMonthRec
IS
v_FiscalInfo tFiscalMonthRec;
BEGIN
689 SELECT BeginDate, EndDate, FiscalMonth
INTO v_FiscalInfo
FROM tblFiscalMonth
WHERE BeginDate <= v_Date
AND EndDate >= v_Date;
RETURN (v_FiscalInfo);
EXCEPTION
WHEN OTHERS THEN
v_FiscalInfo.FiscalMonth := 'PEND';
RETURN (v_FiscalInfo);
END f_GetFiscalInfo;
----------------over loaded
FUNCTION f_GetFiscalInfo (v_FiscalMonth IN tblFiscalMonth.FiscalMonth%TYPE)
RETURN tFiscalMonthRec
IS
v_FiscalInfo tFiscalMonthRec;
BEGIN
SELECT BeginDate, EndDate, FiscalMonth
INTO v_FiscalInfo
FROM tblFiscalMonth
WHERE FiscalMonth = v_FiscalMonth;
RETURN (v_FiscalInfo);
EXCEPTION
WHEN OTHERS THEN
v_FiscalInfo.FiscalMonth := 'PEND';
RETURN (v_FiscalInfo);
END f_GetFiscalInfo;
|
|
|
|
| Re: DBMS_PROFILER Results [message #187074 is a reply to message #187071] |
Thu, 10 August 2006 15:22  |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
| Akshar wrote on Thu, 10 August 2006 15:52 |
SELECT BeginDate, EndDate, FiscalMonth
INTO v_FiscalInfo
FROM tblFiscalMonth
WHERE BeginDate <= v_Date
AND EndDate >= v_Date;
|
What does the explain plan say when you do:
explain plan
set statement_id = 'L689'
for
SELECT BeginDate, EndDate, FiscalMonth
FROM tblFiscalMonth
WHERE BeginDate <= :1
AND EndDate >= :1;
select *
from table (dbms_xplan.display ('PLAN_TABLE', 'L689'));
delete from plan_table
where statement_id = 'L689';
commit;
I would guess that it is doing a full scan on a rather large table, especially if your only expecting one row back.
|
|
|
|
| Re: DBMS_PROFILER Results [message #187078 is a reply to message #187074] |
Thu, 10 August 2006 15:51 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Thanks a lot for your reply and suggestion.
Your guess is correct, it is scanning full table,
so what is solution now?
here PLAN_TABLE results :
SQL> explain plan
2 set statement_id = 'L689'
3 for
4 SELECT BeginDate, EndDate, FiscalMonth
5 FROM tblFiscalMonth
6 WHERE BeginDate <= :1
7 AND EndDate >= :1;
Explained.
Elapsed: 00:00:00.12
SQL> select *
2 from table (dbms_xplan.display ('PLAN_TABLE', 'L689'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 2 |
| 1 | TABLE ACCESS FULL | TBLFISCALMONTH | 1 | 23 | 2 |
------------------------------------------------------------------------
|
|
|
|
| Re: DBMS_PROFILER Results [message #187082 is a reply to message #187071] |
Thu, 10 August 2006 16:30 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
How many rows are actually in the table TBLFISCALMONTH? Is this a temporary table of some sorts? How long does it actually take when you execute the query with parameters? Is there any indexes on the begindate/enddate column? Or are the statistics way skewed here?
If it is a temporary table, then collecting statistics would obviously be difficult -- so you may have to hint it or collect statistics when the table has data. It may not be unreasonable for the database to believe full scanning is the best plan.
47010 hsecs is 47010/100 seconds?, I am assuming. So we are talking over 5 minutes here?
Another thing you should probably pull back is the total_occur column, perhaps this is a function that is called inside a loop?
|
|
|
|
| Re: DBMS_PROFILER Results [message #187089 is a reply to message #187082] |
Thu, 10 August 2006 20:41 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
The table is realy small,it has only 132 ROWS, its kind of lookup table for week,month,year information only.
WHEN I QUERY
SQL> ed
Wrote file afiedt.buf
1 select fiscalmonth,begindate,enddate,numberofweeks from tblfiscalmonth
2* where fiscalmonth = 200508
3 /
FISCAL BEGINDATE ENDDATE NUMBEROFWEEKS
------ --------- --------- -------------
200508 01-AUG-05 28-AUG-05 4
Elapsed: 00:00:00.59
SQL>
When i use the same function :
SQL> ed
Wrote file afiedt.buf
1 declare
2 v_fiscalinfo pk_cdtable_part_test.tfiscalmonthrec;
3 begin
4 v_fiscalinfo := pk_cdtable_part_test.f_getfiscalinfo('28-AUG-05');
5 DBMS_OUTPUT.PUT_LINE (v_fiscalinfo.fiscalmonth);
6* end;
SQL> /
PEND
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.40
Yes we are talking about 5 minitues,
because if you see the 'FETCH CURCD ' statement
and 'FORALL ..' statement , these statement are
processing 2 million rows in 0.70 minitues and 0.54 respectively,why 'SELECT BEGINDATE..' statment 5 minitues?
entire 2 million rows process takes 13 minitues and out that
5 minitues is taken by 'SELECT BEGINDATE..' statement.
why i am concern about that minitues because this same
process will process 20 million rows and that time
what would be time for 'SELECT BEGINDATE...' statement?
SQL> desc tblfiscalmonth
Name Null? Type
----------------------------------------- -------- ----------------------------
FISCALMONTH NOT NULL VARCHAR2(6)
BEGINDATE DATE
ENDDATE DATE
NUMBEROFWEEKS NUMBER(10)
NUMBEROFDAYS NUMBER(10)
TABLENAME VARCHAR2(30)
CREATEDBY VARCHAR2(30)
CREATEDDATETIME DATE
UPDATEDBY VARCHAR2(30)
UPDATEDDATETIME DATE
AUTHORIZATIONID NUMBER(10)
there are two index : FISCALMONTH AND AUTORIZATIONID
|
|
|
|
|
|
| Re: DBMS_PROFILER Results [message #187102 is a reply to message #187090] |
Thu, 10 August 2006 21:31 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry if this isn't useful - I only skimmed the details:
So you are calling a SQL inside a loop that executes 2 million times? SQLs inside loops are a no-no. A table with this few rows would probably fit inside a single block, so I doubt I/O is the problem.
Can you join the table into the main cursor so that you don't need to do the lookup?
Ross Leishman
|
|
|
|
| Re: DBMS_PROFILER Results [message #187217 is a reply to message #187102] |
Fri, 11 August 2006 06:08 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
I agree with Ross here.
If you cannot join it into the query and it is always going to be a small table then perhaps consider preloading it into a collection.
Another alternative is to try it out as a subquery in the SELECT clause in the main query.
I recall reading with 10g that you can use "associative" (hash) collections, so you can reference the record based on a key -- rather than a integer index.
The only thing that seems to make sense to me is it is being called inside the loop.
Since you have gotten handy with the profiler, I am sure you can try out a few of these ideas and figure out the most optimal solution.
|
|
|
|
| Re: DBMS_PROFILER Results [message #187262 is a reply to message #187217] |
Fri, 11 August 2006 09:26 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Thanks you for putting so much effort for me
i have to show you new results where i can
bring you more clear VIEW.
Please suggest how can i bypass or improve use of that
function ( from line no : 682 to 699 ) being call from
my p_ClaimSplit procedure ,
This call is realy killing the performance.
Actual rows process is : 2,070,829
SQL> spool c:\results.txt
SQL> @c:\dbms_profiler\evaluate_profiler_results.sql
Enter value for runid: 29
Enter value for name: pk_cdtable_part_test
Enter value for owner: akdadhan
Line Occur Sec Text
---------- ---------- ---------- --------------------------------------------------------------------------------------------
671 v_FiscalMonth tblFiscalMonth.FiscalMonth%TYPE;
672 v_EndDate tblFiscalMonth.EndDate%TYPE;
673 BEGIN
674 0 0 SELECT EndDate INTO v_EndDate
675 FROM tblFiscalMonth
676 WHERE FiscalMonth = p_FiscalMonth; --v_FiscalMonth;
677 0 0 RETURN v_EndDate;
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN
680 0 0 DBMS_OUTPUT.PUT_LINE('FISCALMONTH [' || v_FiscalMonth || '] WAS NOT FOUND IN TABLE TBLFISCALMONTH.');
681 0 0 RAISE;
682 3663843 11.3198125 END f_GetLastPendDay;
683
684 FUNCTION f_GetFiscalInfo (v_Date IN DATE)
685 RETURN tFiscalMonthRec
686 IS
687 v_FiscalInfo tFiscalMonthRec;
688 BEGIN
689 3663843 468.861021 SELECT BeginDate, EndDate, FiscalMonth
690 INTO v_FiscalInfo
691 FROM tblFiscalMonth
692 WHERE BeginDate <= v_Date
693 AND EndDate >= v_Date;
694 3663745 5.68674985 RETURN (v_FiscalInfo);
695 EXCEPTION
696 WHEN OTHERS THEN
697 98 .000083135 v_FiscalInfo.FiscalMonth := 'PEND';
698 98 .010264864 RETURN (v_FiscalInfo);
699 3663843 5.07679844 END f_GetFiscalInfo;
700 FUNCTION f_GetFiscalInfo (v_FiscalMonth IN tblFiscalMonth.FiscalMonth%TYPE)
701 RETURN tFiscalMonthRec
702 IS
703 v_FiscalInfo tFiscalMonthRec;
704 BEGIN
705 0 0 SELECT BeginDate, EndDate, FiscalMonth
706 INTO v_FiscalInfo
707 FROM tblFiscalMonth
708 WHERE FiscalMonth = v_FiscalMonth;
709 0 0 RETURN (v_FiscalInfo);
710 EXCEPTION
711 WHEN OTHERS THEN
712 0 0 v_FiscalInfo.FiscalMonth := 'PEND';
713 0 0 RETURN (v_FiscalInfo);
714 1 .000143008 END f_GetFiscalInfo;
715
716
-----The procedure which i have run under DBMS_PROFILER START HERE-------*****************************************
717 PROCEDURE p_ClaimSplit (v_LastDay IN DATE) -----,v_Rc OUT NUMBER)
718 IS
719
720 /* VARIABLES */
721 v_ThisFiscalMonth tblclaimdetmonthly_s_part.FiscalMonth%TYPE; --'200402';
722 v_PendingLastDay tblclaimdetmonthly_s_part.FromDate%TYPE; --Current fiscal month.
723 v_SvcFiscalMonth tblclaimdetmonthly_s_part.FiscalMonth%TYPE;
724 v_ProvAmount tblclaimdetmonthly_s_part.ProvAmount%TYPE;
725 v_PlanAmount tblclaimdetmonthly_s_part.PlanAmount%TYPE; --LJK 06/27/2005 New field Added.
726 1 .000000352 v_Date tblclaimdetmonthly_s_part.Fromdate%TYPE := NULL;
727 1 .000000242 v_FiscalDate tblclaimdetmonthly_s_part.Fromdate%TYPE := NULL;
728 1 .000000488 v_TOFiscalMonth tblclaimdetmonthly_s_part.FiscalMonth%TYPE := NULL;
729 1 .000001715 v_AmtUsed tblclaimdetmonthly_s_part.ProvAmount%TYPE := 0;
730 1 .000000555 v_PlanAmtUsed tblclaimdetmonthly_s_part.ProvAmount%TYPE := 0; --LJK 06/27/2005 New field Added.
731 1 .000000294 v_ClaimID tblClaimDetail.ClaimID%TYPE := NULL;
732 1 .00000029 v_ClaimLineNumb tblClaimDetail.ClaimLineNumb%TYPE := NULL;
733 1 .0000003 v_Ct PLS_INTEGER :=0;
734 v_FMPart PLS_INTEGER;
735 1 .000000289 v_CTX PLS_INTEGER := 0;
736 error_number number;
737 error_message varchar2(200);
738 1 .000000367 v_SeqNumb NUMBER := 0;
739 --v_EmailTo EMAIL.AddressList_Tab;
740 ---v_EmailFrom VARCHAR2(50) := USER || '@GENTIVA.COM';
741 ---v_EmailSubject VARCHAR2(50) := 'MONTH-END CLAIM SPLIT';
742 --- v_EmailText VARCHAR2(2000) := NULL;
743 1 .000000294 x PLS_INTEGER := 0;
744 1 .000000468 y PLS_INTEGER := 100000;
745 1 .000000275 z PLS_INTEGER := 0;
746 v_FromFiscalInfo tFiscalMonthRec;
747 v_ToFiscalInfo tFiscalMonthRec;
748 v_CurrentInfo tFiscalMonthRec;
749
750
751 CURSOR curcd
752 IS
753 1 .034344102 SELECT o.claimid
754 ,o.claimlinenumb
755 ,o.regionalnetworkcenter
756 ,o.paymentfiscalmonth
757 ,DECODE (o.accountingdepartmentrptcd
758 ,'1', '1'
759 ,'2', '2'
760 ,'3', '3'
761 ,'4', '4'
762 ,'5', '3'
763 ,'6', '3'
764 ,'7', '3'
765 ,'9', '9'
766 ,'1'
767 ) feeforservice
768 ,o.servicecatid
769 ,o.fromdate
770 ,o.todate
771 ,o.fromdatefiscalmonth
772 ,o.provamount
773 ,o.planamount
774 ,NVL (o.claimdetailhistrectypecd, 'A') claimdetailhistrectypecd
775 ,NVL (o.claimdetailhistseqnumb, 0) claimdetailhistseqnumb
776 , (o.todate - o.fromdate) + 1 totalservicedays
777 ,carid
778 ,o.provpaymenttype provtype
779 FROM tblclaimdetmonthly_s_part o
780 WHERE FISCALDATE = v_fiscaldate
781 AND (O.ProvAmount <> 0 OR O.PlanAmount <> 0);
782
783 TYPE curcd_type IS TABLE OF curcd%ROWTYPE
784 INDEX BY PLS_INTEGER;
785
786 reccd curcd_type;
787
788 TYPE claimsplit_type IS TABLE OF w_claimsplit%ROWTYPE;
789
790 1 .000003294 claimsplit claimsplit_type := claimsplit_type ();
791 bulk_errors EXCEPTION;
792 PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
793
794 BEGIN
795
796 2 .000013644 v_CurrentInfo := f_GetFiscalInfo(v_LastDay);
797 1 .00000098 v_PendingLastDay := v_CurrentInfo.LastDay;
798 1 .000002019 v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth;
799 1 .000045313 v_FiscalDate := '01-AUG-05';
800
801 1 .354989302 EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';
802
803 1 .000001492 OPEN curcd;
804
805 LOOP ----loop 1st
806 16 78.2874728 FETCH curcd
807 BULK COLLECT INTO reccd LIMIT 100000; --> It depends
808
809 16 .000305357 EXIT WHEN reccd.COUNT = 0;
810
811 1593020 1.25357991 FOR i IN 1 .. reccd.LAST
812 LOOP
813
814 1593004 .625077101 v_SeqNumb := 0;
815 1593004 2.69749856 v_SvcFiscalMonth := recCD(i).FromDateFiscalMonth;
816 3186008 3.59208191 v_FromFiscalInfo := f_GetFiscalInfo(recCD(i).FromDate);
817 3186008 4.42603402 v_ToFiscalInfo := f_GetFiscalInfo(recCD(i).ToDate);
818
819 1593004 .492907669 IF v_ToFiscalInfo.LastDay IS NULL THEN
820 97 .000232765 v_ToFiscalInfo.LastDay := v_PendingLastDay + 360;
821 END IF; -----loop 2nd
822
823 1593004 .980360951 IF v_ToFiscalInfo.LastDay > v_PendingLastDay THEN
824 125 .000078004 v_ToFiscalInfo.FiscalMonth := 'PEND';
825 END IF;
826
827 1593004 4.21071099 IF v_FromFiscalInfo.LastDay > v_PendingLastDay OR
828 v_FromFiscalInfo.FiscalMonth = 'PEND' OR
829 recCD(i).TotalServiceDays <= 0 THEN
830
831 9 .000028617 v_ProvAmount := recCD(i).ProvAmount;
832 9 .000009165 v_PlanAmount := recCD(i).PlanAmount;
833 9 .000004076 v_SvcFiscalMonth := 'PEND'; --lk 10/04/2004
834 9 .000009444 v_SeqNumb := v_SeqNumb + 1;
835
836 9 .000012681 reccd(i).claimdetailhistseqnumb := v_seqnumb;
837
838 9 .000052094 claimsplit.EXTEND;
839
840 9 .00002588 claimsplit (claimsplit.LAST).claimid :=
841 reccd(i).claimid;
842 9 .000028974 claimsplit (claimsplit.LAST).claimlinenumb :=
843 reccd(i).claimlinenumb;
844 9 .000022748 claimsplit (claimsplit.LAST).claimdetailhistseqnumb :=
845 reccd(i).claimdetailhistseqnumb;
846 9 .000023617 claimsplit (claimsplit.LAST).claimdetailhistrectypecd :=
847 reccd(i).claimdetailhistrectypecd;
848 9 .00002752 claimsplit (claimsplit.LAST).operationcentercode :=
849 reccd (i).regionalnetworkcenter;
850 9 .0000217 claimsplit (claimsplit.LAST).feeforservice :=
851 reccd (i).feeforservice;
852 9 .000022241 claimsplit (claimsplit.LAST).servicecatid :=
853 reccd (i).servicecatid;
854 9 .000016163 claimsplit (claimsplit.LAST).svcfiscalmonth :=
855 v_svcfiscalmonth;
856 9 .000029945 claimsplit (claimsplit.LAST).paymentfiscalmonth :=
857 reccd (i).paymentfiscalmonth;
858 9 .000022222 claimsplit (claimsplit.LAST).provamount :=
859 reccd (i).provamount;
860 9 .000022452 claimsplit (claimsplit.LAST).carid :=
861 reccd (i).carid;
862 9 .000029258 claimsplit (claimsplit.LAST).provtypecd :=
863 reccd (i).provtype;
864 9 .000021614 claimsplit (claimsplit.LAST).planamount :=
865 reccd (i).planamount;
866
867 9 .000006077 v_FromFiscalInfo.FiscalMonth := 'PEND';
868 ELSE
869
870
871 1592995 1.43699647 v_AmtUsed := 0;
872 1592995 1.0019323 v_PlanAmtUsed := 0; --LJK 06/27/2005 Added to initialize new field.
873 1592995 1.42662269 v_Date := recCD(i).FromDate;
874 7327416 12.6085247 WHILE v_FromFiscalInfo.LastDay <=
875 NVL(v_ToFiscalInfo.LastDay,(v_CurrentInfo.LastDay + 1))
876 LOOP
877 2070829 1.33272801 v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
878
879 2070829 .951088351 IF v_FromFiscalInfo.LastDay > v_PendingLastDay THEN --LK 10/04/2004
880 116 .000177082 v_ProvAmount := recCD(i).ProvAmount - v_AmtUsed; -- Into the Pending month
881 116 .000159214 v_PlanAmount := recCD(i).PlanAmount - v_PlanAmtUsed; --LJK 08/23/2005. Set the PlanAmount not the Used Amount.
882 116 .000057051 v_FromFiscalInfo.FiscalMonth := 'PEND';
883 116 .000069536 v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
884 2070713 .972101712 ELSIF v_FromFiscalInfo.LastDay = v_ToFiscalInfo.LastDay THEN
885 1592879 2.31814951 v_ProvAmount := recCD(i).ProvAmount - v_AmtUsed;
886 1592879 2.0440113 v_PlanAmount := recCD(i).PlanAmount - v_PlanAmtUsed;
887 1592879 .714622595 v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; --v_ToFiscalMonth;
888 1592879 .758996615 v_FromFiscalInfo.FiscalMonth := 'PEND';
889 1592879 3.1165439 v_FromFiscalInfo.LastDay := v_ToFiscalInfo.LastDay + 1;
890 ELSE
891 477834 2.20228293 v_FMPart := ((v_FromFiscalInfo.LastDay - v_Date) + 1) / recCD(i).TotalServiceDays;
892 477834 1.29889431 v_ProvAmount := Round(recCD(i).ProvAmount * v_FMPart,2);
893 477834 1.0671404 v_PlanAmount := Round(recCD(i).PlanAmount * v_FMPart,2);
894 477834 .375650201 v_AmtUsed := v_AmtUsed + v_ProvAmount;
895 477834 .328612569 v_PlanAmtUsed := v_PlanAmtUsed + v_PlanAmount;
896 955668 1.60861288 v_FromFiscalInfo := f_GetFiscalInfo(v_FromFiscalInfo.LastDay + 1);
897 477834 .195676451 v_Date := v_FromFiscalInfo.FirstDay;
898 END IF;
899
900 2070829 1.73724967 v_SeqNumb := v_SeqNumb + 1;
901
902 2070829 11.9630477 ClaimSplit.EXTEND;
903 2070829 5.92202957 ClaimSplit(ClaimSplit.LAST).ClaimID := recCD(i).ClaimId;
904 2070829 4.89309189 ClaimSplit(ClaimSplit.LAST).ClaimLineNumb := recCD(i).ClaimLineNumb;
905 2070829 3.6978673 ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb := v_seqnumb;
906 2070829 5.20126715 ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
907 2070829 4.90498906 ClaimSplit(ClaimSplit.LAST).OperationCenterCode := recCD(i).RegionalNetworkCenter;
908 2070829 4.85973593 ClaimSplit(ClaimSplit.LAST).FeeForService := recCD(i).FeeForService;
909 2070829 4.84971236 ClaimSplit(ClaimSplit.LAST).ServiceCatId := recCD(i).ServiceCatId;
910 2070829 3.64029326 ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth := v_SvcFiscalMonth;
911 2070829 4.87229358 ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth := recCD(i).PaymentFiscalMonth;
912 2070829 3.77270551 ClaimSplit(ClaimSplit.LAST).ProvAmount := v_ProvAmount;
913 2070829 4.90257822 ClaimSplit(ClaimSplit.LAST).CarId := recCD(i).CarId;
914 2070829 6.3208152 ClaimSplit(ClaimSplit.LAST).ProvTypeCD := recCD(i).ProvType;
915 2070829 3.77312419 ClaimSplit(ClaimSplit.LAST).PlanAmount := v_PlanAmount;
916
917 2070829 1.22766754 IF v_svcfiscalmonth = 'PEND' THEN
918 2070829 .623008038 EXIT;
919 END IF;
920 END LOOP;
921 end if;
922 END LOOP;
923
924 BEGIN
925 16 58.068514 FORALL i IN 1 .. claimsplit.COUNT SAVE EXCEPTIONS
926 INSERT INTO w_claimsplit
927 VALUES claimsplit (i);
928 EXCEPTION
929 --> Use SQL%bulk_exceptions.COUNT to get the count of exceptions
930 WHEN bulk_errors
931 THEN
932 14659 .010037323 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
933 LOOP
934 29314 .061665272 DBMS_OUTPUT.put_line
935 ( 'Error from element #'
936 || TO_CHAR (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX)
937 || ': '
938 || SQLERRM (SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
939 );
940 END LOOP;
941
942 2 .008879581 COMMIT;
943 2 .000004366 END;
944
945 16 4.31921722 claimsplit.DELETE;
946
947 16 .000097023 EXIT WHEN curcd%NOTFOUND;
948
949 END LOOP;
950 1 .030182013 COMMIT;
951 1 .000002985 CLOSE curCD;
952 EXCEPTION
953 WHEN OTHERS THEN
954 0 0 error_number := SQLCODE;
955 0 0 error_message := substr(SQLERRM, 1, 200);
956 0 0 dbms_output.put_line('error: ' || error_number || ' ' || error_message);
957 0 0 DBMS_OUTPUT.PUT_LINE(v_ClaimID || ' ' || v_ClaimLineNumb);
958 2 .047362609 END p_claimsplit;
959
960 --------END OF PROCEDURE-------********************************************************----------------------------------------------
961
962
[Updated on: Fri, 11 August 2006 09:29] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 00:11:49 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
