Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Creation : Hanging in Parse phase (merged)
icon4.gif  Procedure Creation : Hanging in Parse phase (merged) [message #395913] Fri, 03 April 2009 01:32 Go to next message
nandusb
Messages: 4
Registered: March 2009
Location: INDIA
Junior Member
HI ALL,


I am tryin to create a procedure,but It is hanging....
after wating foe 1hr I have forcefully stoped the procedure,
Then I have enabled sql_trace for the user and tried to recreate the procedure....
I observed the trace file generated in udump,
but I am not gating the root of the problem.

Can any one Help me(Its Urgent).......


Thanks,


Here Is The Trace File:-


/backup/oracle/bidbtier/admin/bidb/udump/bidb_ora_23771.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /backup/oracle/bidbtier
System name: Linux
Node name: punasapps2.india.ziic.net
Release: 2.6.9-55.0.0.0.2.ELsmp
Version: #1 SMP Wed May 2 14:59:56 PDT 2007
Machine: i686
Instance name: bidb
Redo thread mounted by this instance: 1
Oracle process number: 46
Unix process pid: 23771, image: oracle@punasapps2.india.ziic.net (TNS V1-V3)

*** 2009-04-02 12:15:54.431
*** ACTION NAME:() 2009-04-02 12:15:54.430
*** MODULE NAME:(SQL*Plus) 2009-04-02 12:15:54.430
*** SERVICE NAME:(SYS$USERS) 2009-04-02 12:15:54.430
*** SESSION ID:(619.7470) 2009-04-02 12:15:54.430
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #2 len=17241 dep=0 uid=60 oct=24 lid=60 tim=1209623783624599 hv=0 ad='b728b770'
CREATE OR REPLACE PROCEDURE fill_f_fin_gl_balance (p_SBUCode VARCHAR2, p_Year NUMBER, p_Month NUMBER)
AS
--# *************************************************************************************************
--# NAME : FILL_F_FIN_GL_BALANCE
--# PURPOSE : To populate the F_FIN_GL_BALANCE Fact, using the dimensions D_SBU, D_FIN_CURRENCY,
--# D_FIN_ACCOUNT_TYPE, D_FIN_SEGMENT1, D_FIN_SEGMENT2, D_FIN_SEGMENT3, D_FIN_SEGMENT4,
--# D_FIN_SEGMENT5, D_FIN_SEGMENT6 and the ERP's GL tables GL_BALANCES, GL_CODE_COMBINATIONS
--#
--# PARAMETER1 : p_SBUCode = Name of the Strategic Business Unit e.g. 'ZSI'
--# PARAMETER2 : p_Year = Four Digit YEAR, e.g. 2008
--#
--# CREATION DATE : 26-FEB-2009
--# CREATED BY : SHAKIR ZAFAR RIZVI
--# *************************************************************************************************
--# MODIFICATION HISTORY
--# Date Author Description
--# ----------- ------------------ ------------------
--#
--#
--# *************************************************************************************************
TYPE refCursor IS REF CURSOR;
v_refCursor refCursor;
v_Limit PLS_INTEGER := 10000;
TYPE tab_ID IS TABLE OF F_FIN_GL_BALANCE.ID%TYPE;
TYPE tab_SBU_ID IS TABLE OF F_FIN_GL_BALANCE.SBU_ID%TYPE;
TYPE tab_SEGMENT1_ID IS TABLE OF F_FIN_GL_BALANCE.SEGMENT1_ID%TYPE;
TYPE tab_SEGMENT2_ID IS TABLE OF F_FIN_GL_BALANCE.SEGMENT2_ID%TYPE;
TYPE tab_SEGMENT3_ID IS TABLE OF F_FIN_GL_BALANCE.SEGMENT3_ID%TYPE;
TYPE tab_SEGMENT4_ID IS TABLE OF F_FIN_GL_BALANCE.SEGMENT4_ID%TYPE;
TYPE tab_SEGMENT5_ID IS TABLE OF F_FIN_GL_BALANCE.SEGMENT5_ID%TYPE;
TYPE tab_SEGMENT6_ID IS TABLE OF F_FIN_GL_BALANCE.SEGMENT6_ID%TYPE;
TYPE tab_TIME_ID IS TABLE OF F_FIN_GL_BALANCE.TIME_ID%TYPE;
TYPE tab_CURRENCY_ID IS TABLE OF F_FIN_GL_BALANCE.CURRENCY_ID%TYPE;
TYPE tab_ACCOUNT_TYPE_ID IS TABLE OF F_FIN_GL_BALANCE.ACCOUNT_TYPE_ID%TYPE;
TYPE tab_ACTUAL_PTD IS TABLE OF F_FIN_GL_BALANCE.ACTUAL_PTD%TYPE;
TYPE tab_BUDGETED_PTD IS TABLE OF F_FIN_GL_BALANCE.BUDGETED_PTD%TYPE;
TYPE tab_ENCUMBURANCE_PTD IS TABLE OF F_FIN_GL_BALANCE.ENCUMBURANCE_PTD%TYPE;
TYPE tab_ACTUAL_YTD IS TABLE OF F_FIN_GL_BALANCE.ACTUAL_YTD%TYPE;
TYPE tab_BUDGETED_YTD IS TABLE OF F_FIN_GL_BALANCE.BUDGETED_YTD%TYPE;
TYPE tab_ENCUMBURANCE_YTD IS TABLE OF F_FIN_GL_BALANCE.ENCUMBURANCE_YTD%TYPE;
TYPE tab_INSERT_DATE IS TABLE OF F_FIN_GL_BALANCE.INSERT_DATE%TYPE;
v_Tab_ID tab_ID;
v_Tab_SBU_ID tab_SBU_ID;
v_Tab_SEGMENT1_ID tab_SEGMENT1_ID;
v_Tab_SEGMENT2_ID tab_SEGMENT2_ID;
v_Tab_SEGMENT3_ID tab_SEGMENT3_ID;
v_Tab_SEGMENT4_ID tab_SEGMENT4_ID;
v_Tab_SEGMENT5_ID tab_SEGMENT5_ID;
v_Tab_SEGMENT6_ID tab_SEGMENT6_ID;
v_Tab_TIME_ID tab_TIME_ID;
v_Tab_CURRENCY_ID tab_CURRENCY_ID;
v_Tab_ACCOUNT_TYPE_ID tab_ACCOUNT_TYPE_ID;
v_Tab_ACTUAL_PTD tab_ACTUAL_PTD;
v_Tab_BUDGETED_PTD tab_BUDGETED_PTD;
v_Tab_ENCUMBURANCE_PTD tab_ENCUMBURANCE_PTD;
v_Tab_ACTUAL_YTD tab_ACTUAL_YTD;
v_Tab_BUDGETED_YTD tab_BUDGETED_YTD;
v_Tab_ENCUMBURANCE_YTD tab_ENCUMBURANCE_YTD;
v_Tab_INSERT_DATE tab_INSERT_DATE;
-- Creating an exception handler for ORA-24381
v_ErrCount NUMBER;
EXP_DML_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(EXP_DML_ERRORS, -24381);
BEGIN
IF (Length(p_Year) != 4) THEN
Dbms_Output.PUT_LINE('MSG::: Invalid Year Parameter. Year should be 4 digits long !!! ');
RETURN;
END IF;
IF (p_Month <1) OR (p_Month >12) THEN
Dbms_Output.PUT_LINE('MSG::: Invalid Month Parameter. Month should be between 1 and 12 !!! ');
RETURN;
END IF;
--############-- Deleting records from F_FIN_GL_BALANCE --############--
DELETE FROM F_FIN_GL_BALANCE
WHERE SBU_ID =(SELECT ID FROM D_SBU WHERE CODE = p_SBUCode)
AND TIME_ID = Last_Day(To_Date(LPad(p_Month,2,0)||p_Year,'MMYYYY'));
COMMIT;
--############-- Opening the cursor for ZSI using the DBLINK "ZSI_LINK.INDIA.ZIIC.NET" --############--
CASE WHEN P_SBUCODE = 'ZSI' THEN
OPEN v_refCursor FOR
SELECT SEQ_F_FIN_GL_BALANCE.NEXTVAL ID, C.ID SBU_ID, F.ID SEGMENT1_ID,
G.ID SEGMENT2_ID, H.ID SEGMENT3_ID, I.ID SEGMENT4_ID, J.ID SEGMENT5_ID, K.ID SEGMENT6_ID,
LAST_DAY(TO_DATE(B.PERIOD_YEAR||LPad(B.PERIOD_NUM,2,'0'),'YYYYMM')) TIME_ID,
D.ID CURRENCY_ID, E.ID ACCOUNT_TYPE_ID,
Nvl(Decode(B.ACTUAL_FLAG,'A',1,'B',0,'E',0)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ACTUAL_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',1,'E',0)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) BUDGETED_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',0,'E',1)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ENCUMBURANCE_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',1,'B',0,'E',0)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ACTUAL_YTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',1,'E',0)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) BUDGETED_YTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',0,'E',1)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ENCUMBURANCE_YTD,
SYSDATE INSERT_DATE
FROM APPS.GL_BALANCES@ZSI_LINK.INDIA.ZIIC.NET B, APPS.GL_CODE_COMBINATIONS@ZSI_LINK.INDIA.ZIIC.NET A,
D_FIN_SEGMENT1 F, D_FIN_SEGMENT2 G, D_FIN_SEGMENT3 H, D_FIN_SEGMENT4 I, D_FIN_SEGMENT5 J, D_FIN_SEGMENT6 K,
D_FIN_CURRENCY D, D_FIN_ACCOUNT_TYPE E, D_SBU C
WHERE A.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
AND C.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
AND C.CHART_OF_ACCOUNTS_ID = A.CHART_OF_ACCOUNTS_ID
AND D.CODE = B.CURRENCY_CODE
AND E.CODE = A.ACCOUNT_TYPE
AND F.LN_CODE = A.SEGMENT1
AND G.LN_CODE = A.SEGMENT2
AND H.LN_CODE = A.SEGMENT3
AND I.LN_CODE = A.SEGMENT4
AND J.LN_CODE = A.SEGMENT5
AND K.LN_CODE = A.SEGMENT6
AND C.ENABLED_FLAG='Y'
AND C.ENABLED_FLAG = D.ENABLED_FLAG
AND C.ENABLED_FLAG = E.ENABLED_FLAG
AND C.ENABLED_FLAG = F.ENABLED_FLAG
AND C.ENABLED_FLAG = G.ENABLED_FLAG
AND C.ENABLED_FLAG = H.ENABLED_FLAG
AND C.ENABLED_FLAG = I.ENABLED_FLAG
AND C.ENABLED_FLAG = J.ENABLED_FLAG
AND C.ENABLED_FLAG = K.ENABLED_FLAG
AND C.CODE = p_SBUCode
AND C.ID = D.SBU_ID
AND C.ID = F.SBU_ID
AND C.ID = G.SBU_ID
AND C.ID = H.SBU_ID
AND C.ID = I.SBU_ID
AND C.ID = J.SBU_ID
AND C.ID = K.SBU_ID
AND B.PERIOD_YEAR = p_Year
AND B.PERIOD_NUM = p_Month
AND B.TEMPLATE_ID IS NULL
AND B.ACTUAL_FLAG IN ('A','B','E')
AND ((B.PERIOD_NET_DR - B.PERIOD_NET_CR) <> 0
OR (B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR) <> 0);
--############-- Opening the cursor for ZAC, ZIIC, AFICO using the DBLINK "ZAC_LINK.INDIA.ZIIC.NET" --############--
WHEN P_SBUCODE IN ('ZAC', 'ZIIC', 'AFICO') THEN
OPEN v_refCursor FOR
SELECT SEQ_F_FIN_GL_BALANCE.NEXTVAL ID, C.ID SBU_ID, F.ID SEGMENT1_ID,
G.ID SEGMENT2_ID, H.ID SEGMENT3_ID, I.ID SEGMENT4_ID, J.ID SEGMENT5_ID, K.ID SEGMENT6_ID,
LAST_DAY(TO_DATE(B.PERIOD_YEAR||LPad(B.PERIOD_NUM,2,'0'),'YYYYMM')) TIME_ID,
D.ID CURRENCY_ID, E.ID ACCOUNT_TYPE_ID,
Nvl(Decode(B.ACTUAL_FLAG,'A',1,'B',0,'E',0)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ACTUAL_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',1,'E',0)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) BUDGETED_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',0,'E',1)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ENCUMBURANCE_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',1,'B',0,'E',0)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ACTUAL_YTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',1,'E',0)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) BUDGETED_YTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',0,'E',1)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ENCUMBURANCE_YTD,
SYSDATE INSERT_DATE
FROM APPS.GL_BALANCES@ZAC_LINK.INDIA.ZIIC.NET B, APPS.GL_CODE_COMBINATIONS@ZAC_LINK.INDIA.ZIIC.NET A,
D_FIN_SEGMENT1 F, D_FIN_SEGMENT2 G, D_FIN_SEGMENT3 H, D_FIN_SEGMENT4 I, D_FIN_SEGMENT5 J, D_FIN_SEGMENT6 K,
D_FIN_CURRENCY D, D_FIN_ACCOUNT_TYPE E, D_SBU C
WHERE A.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
AND C.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
AND C.CHART_OF_ACCOUNTS_ID = A.CHART_OF_ACCOUNTS_ID
AND D.CODE = B.CURRENCY_CODE
AND E.CODE = A.ACCOUNT_TYPE
AND F.LN_CODE = A.SEGMENT1
AND G.LN_CODE = A.SEGMENT2
AND H.LN_CODE = A.SEGMENT3
AND I.LN_CODE = A.SEGMENT4
AND J.LN_CODE = A.SEGMENT5
AND K.LN_CODE = A.SEGMENT6
AND C.ENABLED_FLAG='Y'
AND C.ENABLED_FLAG = D.ENABLED_FLAG
AND C.ENABLED_FLAG = E.ENABLED_FLAG
AND C.ENABLED_FLAG = F.ENABLED_FLAG
AND C.ENABLED_FLAG = G.ENABLED_FLAG
AND C.ENABLED_FLAG = H.ENABLED_FLAG
AND C.ENABLED_FLAG = I.ENABLED_FLAG
AND C.ENABLED_FLAG = J.ENABLED_FLAG
AND C.ENABLED_FLAG = K.ENABLED_FLAG
AND C.CODE = p_SBUCode
AND C.ID = D.SBU_ID
AND C.ID = F.SBU_ID
AND C.ID = G.SBU_ID
AND C.ID = H.SBU_ID
AND C.ID = I.SBU_ID
AND C.ID = J.SBU_ID
AND C.ID = K.SBU_ID
AND B.PERIOD_YEAR = p_Year
AND B.PERIOD_NUM = p_Month
AND B.TEMPLATE_ID IS NULL
AND B.ACTUAL_FLAG IN ('A','B','E')
AND ((B.PERIOD_NET_DR - B.PERIOD_NET_CR) <> 0
OR (B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR) <> 0);
--############-- Opening the cursor for ZGI using the DBLINK "ZGI_LINK.INDIA.ZIIC.NET" --############--
WHEN P_SBUCODE = 'ZGI' THEN
OPEN v_refCursor FOR
SELECT SEQ_F_FIN_GL_BALANCE.NEXTVAL ID, C.ID SBU_ID, F.ID SEGMENT1_ID,
G.ID SEGMENT2_ID, H.ID SEGMENT3_ID, I.ID SEGMENT4_ID, J.ID SEGMENT5_ID, K.ID SEGMENT6_ID,
LAST_DAY(TO_DATE(B.PERIOD_YEAR||LPad(B.PERIOD_NUM,2,'0'),'YYYYMM')) TIME_ID,
D.ID CURRENCY_ID, E.ID ACCOUNT_TYPE_ID,
Nvl(Decode(B.ACTUAL_FLAG,'A',1,'B',0,'E',0)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ACTUAL_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',1,'E',0)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) BUDGETED_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',0,'E',1)*(B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ENCUMBURANCE_PTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',1,'B',0,'E',0)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ACTUAL_YTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',1,'E',0)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) BUDGETED_YTD,
Nvl(Decode(B.ACTUAL_FLAG,'A',0,'B',0,'E',1)*(B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR),0) ENCUMBURANCE_YTD,
SYSDATE INSERT_DATE
FROM APPS.GL_BALANCES@ZGI_LINK.INDIA.ZIIC.NET B, APPS.GL_CODE_COMBINATIONS@ZGI_LINK.INDIA.ZIIC.NET A,
D_FIN_SEGMENT1 F, D_FIN_SEGMENT2 G, D_FIN_SEGMENT3 H, D_FIN_SEGMENT4 I, D_FIN_SEGMENT5 J, D_FIN_SEGMENT6 K,
D_FIN_CURRENCY D, D_FIN_ACCOUNT_TYPE E, D_SBU C
WHERE A.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
AND C.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
AND C.CHART_OF_ACCOUNTS_ID = A.CHART_OF_ACCOUNTS_ID
AND D.CODE = B.CURRENCY_CODE
AND E.CODE = A.ACCOUNT_TYPE
AND F.LN_CODE = A.SEGMENT1
AND G.LN_CODE = A.SEGMENT2
AND H.LN_CODE = A.SEGMENT3
AND I.LN_CODE = A.SEGMENT4
AND J.LN_CODE = A.SEGMENT5
AND K.LN_CODE = A.SEGMENT6
AND C.ENABLED_FLAG='Y'
AND C.ENABLED_FLAG = D.ENABLED_FLAG
AND C.ENABLED_FLAG = E.ENABLED_FLAG
AND C.ENABLED_FLAG = F.ENABLED_FLAG
AND C.ENABLED_FLAG = G.ENABLED_FLAG
AND C.ENABLED_FLAG = H.ENABLED_FLAG
AND C.ENABLED_FLAG = I.ENABLED_FLAG
AND C.ENABLED_FLAG = J.ENABLED_FLAG
AND C.ENABLED_FLAG = K.ENABLED_FLAG
AND C.CODE = p_SBUCode
AND C.ID = D.SBU_ID
AND C.ID = F.SBU_ID
AND C.ID = G.SBU_ID
AND C.ID = H.SBU_ID
AND C.ID = I.SBU_ID
AND C.ID = J.SBU_ID
AND C.ID = K.SBU_ID
AND B.PERIOD_YEAR = p_Year
AND B.PERIOD_NUM = p_Month
AND B.TEMPLATE_ID IS NULL
AND B.ACTUAL_FLAG IN ('A','B','E')
AND ((B.PERIOD_NET_DR - B.PERIOD_NET_CR) <> 0
OR (B.BEGIN_BALANCE_DR - B.BEGIN_BALANCE_CR + B.PERIOD_NET_DR - B.PERIOD_NET_CR) <> 0);
END CASE;
--############-- Inserting records into F_FIN_GL_BALANCE --############--
LOOP
FETCH v_refCursor BULK COLLECT INTO v_Tab_ID, v_Tab_SBU_ID, v_Tab_SEGMENT1_ID, v_Tab_SEGMENT2_ID, v_Tab_SEGMENT3_ID,
v_Tab_SEGMENT4_ID, v_Tab_SEGMENT5_ID, v_Tab_SEGMENT6_ID, v_Tab_TIME_ID, v_Tab_CURRENCY_ID,
v_Tab_ACCOUNT_TYPE_ID, v_Tab_ACTUAL_PTD, v_Tab_BUDGETED_PTD, v_Tab_ENCUMBURANCE_PTD,
v_Tab_ACTUAL_YTD, v_Tab_BUDGETED_YTD, v_Tab_ENCUMBURANCE_YTD, v_Tab_INSERT_DATE
LIMIT v_Limit;
EXIT WHEN v_Tab_ID.Count = 0;
FORALL idx IN v_Tab_ID.FIRST..v_Tab_ID.LAST SAVE EXCEPTIONS
INSERT INTO F_FIN_GL_BALANCE (ID, SBU_ID, SEGMENT1_ID, SEGMENT2_ID, SEGMENT3_ID,
SEGMENT4_ID, SEGMENT5_ID, SEGMENT6_ID, TIME_ID, CURRENCY_ID,
ACCOUNT_TYPE_ID, ACTUAL_PTD, BUDGETED_PTD, ENCUMBURANCE_PTD,
ACTUAL_YTD, BUDGETED_YTD, ENCUMBURANCE_YTD, INSERT_DATE)
VALUES (v_Tab_ID(idx), v_Tab_SBU_ID(idx), v_Tab_SEGMENT1_ID(idx), v_Tab_SEGMENT2_ID(idx), v_Tab_SEGMENT3_ID(idx),
v_Tab_SEGMENT4_ID(idx), v_Tab_SEGMENT5_ID(idx), v_Tab_SEGMENT6_ID(idx), v_Tab_TIME_ID(idx), v_Tab_CURRENCY_ID(idx),
v_Tab_ACCOUNT_TYPE_ID(idx), v_Tab_ACTUAL_PTD(idx), v_Tab_BUDGETED_PTD(idx), v_Tab_ENCUMBURANCE_PTD(idx),
v_Tab_ACTUAL_YTD(idx), v_Tab_BUDGETED_YTD(idx), v_Tab_ENCUMBURANCE_YTD(idx), v_Tab_INSERT_DATE(idx));
COMMIT;
END LOOP;
CLOSE v_refCursor;
COMMIT;
EXCEPTION
WHEN EXP_DML_ERRORS THEN
v_ErrCount := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of statements that failed ::: ' || v_ErrCount);
FOR i IN 1..v_ErrCount LOOP
DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || ':::');
DBMS_OUTPUT.PUT_LINE('Error message is ::: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
WHEN OTHERS THEN Dbms_Output.PUT_LINE('OTHERS::: '||SQLERRM||' !!!');
END;
END OF STMT
PARSE #2:c=1999,e=1196,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1209623783624593
=====================
PARSING IN CURSOR #1 len=54 dep=1 uid=0 oct=3 lid=0 tim=1209623783627129 hv=696375357 ad='7e57be98'
select source from source$ where obj#=:1 order by line
END OF STMT
PARSE #1:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783627125
EXEC #1:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783627274
FETCH #1:c=1000,e=537,p=0,cr=8,cu=0,mis=0,r=281,dep=1,og=4,tim=1209623783627834
STAT #1 id=1 cnt=281 pid=0 pos=1 obj=72 op='TABLE ACCESS BY INDEX ROWID SOURCE$ (cr=8 pr=0 pw=0 time=1797 us)'
STAT #1 id=2 cnt=281 pid=1 pos=1 obj=113 op='INDEX RANGE SCAN I_SOURCE1 (cr=4 pr=0 pw=0 time=647 us)'
=====================
PARSING IN CURSOR #1 len=50 dep=1 uid=0 oct=3 lid=0 tim=1209623783628074 hv=27287881 ad='7e57bb9c'
select param, value from settings$ where obj# = :1
END OF STMT
PARSE #1:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783628070
EXEC #1:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783628172
FETCH #1:c=0,e=144,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783628338
FETCH #1:c=0,e=14,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783628389
FETCH #1:c=0,e=9,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783628430
FETCH #1:c=0,e=7,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783628465
FETCH #1:c=0,e=7,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783628503
=====================
PARSING IN CURSOR #3 len=97 dep=1 uid=0 oct=3 lid=0 tim=1209623783628597 hv=752984429 ad='7e57ac3c'
select warning_num, global_mod, property from warning_settings$ where obj# = :1 order by property
END OF STMT
PARSE #3:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783628593
EXEC #3:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783628704
FETCH #3:c=0,e=147,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783628872
FETCH #3:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783628912
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 time=156 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=677 op='TABLE ACCESS BY INDEX ROWID WARNING_SETTINGS$ (cr=3 pr=0 pw=0 time=102 us)'
STAT #3 id=3 cnt=1 pid=2 pos=1 obj=733 op='INDEX RANGE SCAN I_WARNING_SETTINGS (cr=2 pr=0 pw=0 time=74 us)'
FETCH #1:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783629041
FETCH #1:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1209623783629083
FETCH #1:c=0,e=6,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1209623783629136
STAT #1 id=1 cnt=7 pid=0 pos=1 obj=80 op='TABLE ACCESS BY INDEX ROWID SETTINGS$ (cr=16 pr=0 pw=0 time=145 us)'
STAT #1 id=2 cnt=7 pid=1 pos=1 obj=121 op='INDEX RANGE SCAN I_SETTINGS1 (cr=9 pr=0 pw=0 time=166 us)'
*** 2009-04-02 13:13:47.948
=====================
PARSING IN CURSOR #3 len=302 dep=1 uid=0 oct=3 lid=0 tim=1209627175731282 hv=444865451 ad='7e56bfe0'
select o.owner#, u.name, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o, user$ u where d.p_obj#=:1 and (d.p_timestamp=:2 or d.property=2) and d.d_obj#=o.obj# and o.owner#=u.user# order by o.obj#
END OF STMT
PARSE #3:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175731276
EXEC #3:c=0,e=116,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175731564
FETCH #3:c=0,e=99,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175731702
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 time=127 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=3 pr=0 pw=0 time=101 us)'
STAT #3 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=3 pr=0 pw=0 time=92 us)'
STAT #3 id=4 cnt=0 pid=3 pos=1 obj=92 op='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=86 us)'
STAT #3 id=5 cnt=0 pid=4 pos=1 obj=123 op='INDEX RANGE SCAN I_DEPENDENCY2 (cr=3 pr=0 pw=0 time=73 us)'
STAT #3 id=6 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=7 cnt=0 pid=6 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=8 cnt=0 pid=2 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=9 cnt=0 pid=8 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)'
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #1 len=56 dep=1 uid=0 oct=6 lid=0 tim=1209627175732615 hv=2139656522 ad='7e5674c8'
update procedure$ set audit$=:2,options=:3 where obj#=:1
END OF STMT
PARSE #1:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175732610
EXEC #1:c=1000,e=598,p=0,cr=2,cu=3,mis=0,r=1,dep=1,og=4,tim=1209627175733294
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE PROCEDURE$ (cr=2 pr=0 pw=0 time=495 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=109 op='INDEX UNIQUE SCAN I_PROCEDURE1 (cr=2 pr=0 pw=0 time=47 us)'
=====================
PARSING IN CURSOR #3 len=33 dep=1 uid=0 oct=7 lid=0 tim=1209627175733453 hv=3383239196 ad='7ea61070'
delete from source$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175733449
EXEC #3:c=13998,e=14006,p=0,cr=4,cu=602,mis=0,r=281,dep=1,og=4,tim=1209627175747522
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SOURCE$ (cr=4 pr=0 pw=0 time=13957 us)'
STAT #3 id=2 cnt=281 pid=1 pos=1 obj=113 op='INDEX RANGE SCAN I_SOURCE1 (cr=4 pr=0 pw=0 time=580 us)'
=====================
PARSING IN CURSOR #3 len=55 dep=1 uid=0 oct=2 lid=0 tim=1209627175747687 hv=3546203855 ad='7e566b78'
insert into source$(obj#,line,source) values (:1,:2,:3)
END OF STMT
PARSE #3:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175747683
EXEC #3:c=37994,e=37677,p=0,cr=13,cu=1152,mis=0,r=281,dep=1,og=4,tim=1209627175785432
=====================
PARSING IN CURSOR #1 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175785542 hv=4265478526 ad='7ea60438'
delete from idl_ub1$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175785539
EXEC #1:c=0,e=117,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175785740
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB1$ (cr=2 pr=0 pw=0 time=85 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=114 op='INDEX RANGE SCAN I_IDL_UB11 (cr=2 pr=0 pw=0 time=75 us)'
=====================
PARSING IN CURSOR #1 len=47 dep=1 uid=0 oct=7 lid=0 tim=1209627175785871 hv=3133972415 ad='7ea5ff20'
delete from idl_char$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175785867
EXEC #1:c=0,e=92,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175786037
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_CHAR$ (cr=2 pr=0 pw=0 time=67 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=115 op='INDEX RANGE SCAN I_IDL_CHAR1 (cr=2 pr=0 pw=0 time=59 us)'
=====================
PARSING IN CURSOR #1 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175786202 hv=2838192429 ad='7ea5f634'
delete from idl_ub2$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175786198
EXEC #1:c=0,e=78,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175786354
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB2$ (cr=2 pr=0 pw=0 time=59 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=116 op='INDEX RANGE SCAN I_IDL_UB21 (cr=2 pr=0 pw=0 time=51 us)'
=====================
PARSING IN CURSOR #1 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175786487 hv=215168627 ad='7ea5f11c'
delete from idl_sb4$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175786482
EXEC #1:c=0,e=100,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175786660
=====================
PARSING IN CURSOR #3 len=62 dep=1 uid=0 oct=7 lid=0 tim=1209627175786746 hv=2219634335 ad='7ea5eb40'
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
END OF STMT
PARSE #3:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175786742
EXEC #3:c=0,e=70,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175786882
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE NCOMP_DLL$ (cr=1 pr=0 pw=0 time=28 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=730 op='INDEX RANGE SCAN I_NCOMP_DLL1 (cr=1 pr=0 pw=0 time=20 us)'
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_SB4$ (cr=2 pr=0 pw=0 time=76 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=117 op='INDEX RANGE SCAN I_IDL_SB41 (cr=2 pr=0 pw=0 time=69 us)'
=====================
PARSING IN CURSOR #3 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175787073 hv=4265478526 ad='7ea60438'
delete from idl_ub1$ where obj#=:1 and part=:2
END OF STMT
PARSE #3:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787069
EXEC #3:c=0,e=34,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787181
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB1$ (cr=2 pr=0 pw=0 time=21 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=114 op='INDEX RANGE SCAN I_IDL_UB11 (cr=2 pr=0 pw=0 time=13 us)'
=====================
PARSING IN CURSOR #3 len=47 dep=1 uid=0 oct=7 lid=0 tim=1209627175787276 hv=3133972415 ad='7ea5ff20'
delete from idl_char$ where obj#=:1 and part=:2
END OF STMT
PARSE #3:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787272
EXEC #3:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787380
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_CHAR$ (cr=2 pr=0 pw=0 time=18 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=115 op='INDEX RANGE SCAN I_IDL_CHAR1 (cr=2 pr=0 pw=0 time=12 us)'
=====================
PARSING IN CURSOR #3 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175787474 hv=2838192429 ad='7ea5f634'
delete from idl_ub2$ where obj#=:1 and part=:2
END OF STMT
PARSE #3:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787470
EXEC #3:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787578
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB2$ (cr=2 pr=0 pw=0 time=19 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=116 op='INDEX RANGE SCAN I_IDL_UB21 (cr=2 pr=0 pw=0 time=12 us)'
=====================
PARSING IN CURSOR #3 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175787674 hv=215168627 ad='7ea5f11c'
delete from idl_sb4$ where obj#=:1 and part=:2
END OF STMT
PARSE #3:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787670
EXEC #3:c=1000,e=36,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787783
=====================
PARSING IN CURSOR #1 len=62 dep=1 uid=0 oct=7 lid=0 tim=1209627175787830 hv=2219634335 ad='7ea5eb40'
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
END OF STMT
PARSE #1:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787826
EXEC #1:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175787936
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE NCOMP_DLL$ (cr=1 pr=0 pw=0 time=17 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=730 op='INDEX RANGE SCAN I_NCOMP_DLL1 (cr=1 pr=0 pw=0 time=11 us)'
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_SB4$ (cr=2 pr=0 pw=0 time=20 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=117 op='INDEX RANGE SCAN I_IDL_SB41 (cr=2 pr=0 pw=0 time=14 us)'
=====================
PARSING IN CURSOR #1 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175788113 hv=4265478526 ad='7ea60438'
delete from idl_ub1$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788108
EXEC #1:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788219
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB1$ (cr=2 pr=0 pw=0 time=19 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=114 op='INDEX RANGE SCAN I_IDL_UB11 (cr=2 pr=0 pw=0 time=12 us)'
=====================
PARSING IN CURSOR #1 len=47 dep=1 uid=0 oct=7 lid=0 tim=1209627175788313 hv=3133972415 ad='7ea5ff20'
delete from idl_char$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788309
EXEC #1:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788418
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_CHAR$ (cr=2 pr=0 pw=0 time=18 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=115 op='INDEX RANGE SCAN I_IDL_CHAR1 (cr=2 pr=0 pw=0 time=13 us)'
=====================
PARSING IN CURSOR #1 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175788511 hv=2838192429 ad='7ea5f634'
delete from idl_ub2$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788507
EXEC #1:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788619
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB2$ (cr=2 pr=0 pw=0 time=19 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=116 op='INDEX RANGE SCAN I_IDL_UB21 (cr=2 pr=0 pw=0 time=12 us)'
=====================
PARSING IN CURSOR #1 len=46 dep=1 uid=0 oct=7 lid=0 tim=1209627175788711 hv=215168627 ad='7ea5f11c'
delete from idl_sb4$ where obj#=:1 and part=:2
END OF STMT
PARSE #1:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788708
EXEC #1:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788821
=====================
PARSING IN CURSOR #3 len=62 dep=1 uid=0 oct=7 lid=0 tim=1209627175788870 hv=2219634335 ad='7ea5eb40'
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
END OF STMT
PARSE #3:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788866
EXEC #3:c=0,e=31,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175788964
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE NCOMP_DLL$ (cr=1 pr=0 pw=0 time=15 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=730 op='INDEX RANGE SCAN I_NCOMP_DLL1 (cr=1 pr=0 pw=0 time=9 us)'
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_SB4$ (cr=2 pr=0 pw=0 time=20 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=117 op='INDEX RANGE SCAN I_IDL_SB41 (cr=2 pr=0 pw=0 time=14 us)'
=====================
PARSING IN CURSOR #3 len=32 dep=1 uid=0 oct=7 lid=0 tim=1209627175789155 hv=3868380941 ad='7ea5ced8'
delete from error$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175789150
EXEC #3:c=0,e=302,p=0,cr=1,cu=12,mis=0,r=2,dep=1,og=4,tim=1209627175789532
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE ERROR$ (cr=1 pr=0 pw=0 time=274 us)'
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=120 op='INDEX RANGE SCAN I_ERROR1 (cr=1 pr=0 pw=0 time=31 us)'
=====================
PARSING IN CURSOR #3 len=118 dep=1 uid=0 oct=2 lid=0 tim=1209627175789683 hv=121930443 ad='73ae8350'
insert into error$(obj#,sequence#,line,position#,textlength,text, property, error#) values (:1,:2,:3,:4,:5,:6, :7, :8)
END OF STMT
PARSE #3:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175789679
EXEC #3:c=0,e=157,p=0,cr=1,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175789931
EXEC #3:c=0,e=69,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175790037
=====================
PARSING IN CURSOR #1 len=37 dep=1 uid=0 oct=7 lid=0 tim=1209627175790137 hv=2618992073 ad='7ea5c2a0'
delete from settings$ where obj# = :1
END OF STMT
PARSE #1:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175790132
EXEC #1:c=1999,e=1523,p=0,cr=2,cu=35,mis=0,r=7,dep=1,og=4,tim=1209627175791739
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SETTINGS$ (cr=2 pr=0 pw=0 time=1495 us)'
STAT #1 id=2 cnt=7 pid=1 pos=1 obj=121 op='INDEX RANGE SCAN I_SETTINGS1 (cr=2 pr=0 pw=0 time=51 us)'
=====================
PARSING IN CURSOR #1 len=61 dep=1 uid=0 oct=2 lid=0 tim=1209627175791896 hv=4075193923 ad='7e55eb2c'
insert into settings$(obj#, param, value) values (:1, :2, :3)
END OF STMT
PARSE #1:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175791891
EXEC #1:c=0,e=243,p=0,cr=1,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175792202
EXEC #1:c=0,e=209,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175792447
EXEC #1:c=1000,e=212,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175792694
EXEC #1:c=0,e=207,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175792935
=====================
PARSING IN CURSOR #3 len=45 dep=1 uid=0 oct=7 lid=0 tim=1209627175793019 hv=1496117919 ad='7e55e830'
delete from warning_settings$ where obj# = :1
END OF STMT
PARSE #3:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175793014
EXEC #3:c=0,e=500,p=0,cr=2,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175793583
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE WARNING_SETTINGS$ (cr=2 pr=0 pw=0 time=466 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=733 op='INDEX RANGE SCAN I_WARNING_SETTINGS (cr=2 pr=0 pw=0 time=30 us)'
=====================
PARSING IN CURSOR #3 len=94 dep=1 uid=0 oct=2 lid=0 tim=1209627175793728 hv=2385338619 ad='7e55e254'
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
END OF STMT
PARSE #3:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175793723
EXEC #3:c=0,e=293,p=0,cr=1,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175794084
EXEC #1:c=0,e=212,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175794353
EXEC #1:c=1000,e=215,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175794603
EXEC #1:c=0,e=215,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175794853
=====================
PARSING IN CURSOR #3 len=40 dep=1 uid=0 oct=7 lid=0 tim=1209627175794957 hv=3766675826 ad='7ea5b538'
delete from procedureinfo$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175794953
EXEC #3:c=0,e=84,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175795105
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE PROCEDUREINFO$ (cr=2 pr=0 pw=0 time=61 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=110 op='INDEX RANGE SCAN I_PROCEDUREINFO1 (cr=2 pr=0 pw=0 time=53 us)'
=====================
PARSING IN CURSOR #3 len=35 dep=1 uid=0 oct=7 lid=0 tim=1209627175795251 hv=449119246 ad='7ea5b020'
delete from argument$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175795247
EXEC #3:c=0,e=81,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175795395
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE ARGUMENT$ (cr=2 pr=0 pw=0 time=61 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=112 op='INDEX RANGE SCAN I_ARGUMENT2 (cr=2 pr=0 pw=0 time=53 us)'
=====================
PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=7 lid=0 tim=1209627175795541 hv=2420331960 ad='7ea59bdc'
delete from procedurec$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175795537
EXEC #3:c=0,e=67,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175795674
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE PROCEDUREC$ (cr=2 pr=0 pw=0 time=42 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=329 op='INDEX RANGE SCAN I_PROCEDUREC$ (cr=2 pr=0 pw=0 time=34 us)'
=====================
PARSING IN CURSOR #3 len=41 dep=1 uid=0 oct=7 lid=0 tim=1209627175795812 hv=1508374054 ad='7ea58e74'
delete from procedureplsql$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175795807
EXEC #3:c=0,e=81,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175795958
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE PROCEDUREPLSQL$ (cr=2 pr=0 pw=0 time=55 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=331 op='INDEX RANGE SCAN I_PROCEDUREPLSQL$ (cr=2 pr=0 pw=0 time=48 us)'
=====================
PARSING IN CURSOR #3 len=40 dep=1 uid=0 oct=7 lid=0 tim=1209627175796104 hv=1677281641 ad='7ea5823c'
delete from procedurejava$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175796100
EXEC #3:c=0,e=81,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175796248
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE PROCEDUREJAVA$ (cr=2 pr=0 pw=0 time=51 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=327 op='INDEX RANGE SCAN I_PROCEDUREJAVA$ (cr=2 pr=0 pw=0 time=44 us)'
=====================
PARSING IN CURSOR #3 len=33 dep=1 uid=0 oct=7 lid=0 tim=1209627175796390 hv=332180896 ad='7ea56d70'
delete from vtable$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175796386
EXEC #3:c=1000,e=525,p=0,cr=23,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175796979
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE VTABLE$ (cr=23 pr=0 pw=0 time=504 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=366 op='TABLE ACCESS FULL VTABLE$ (cr=23 pr=0 pw=0 time=498 us)'
=====================
PARSING IN CURSOR #1 len=39 dep=1 uid=0 oct=7 lid=0 tim=1209627175797128 hv=3406431822 ad='7ea53dec'
delete from dependency$ where d_obj#=:1
END OF STMT
PARSE #1:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175797124
EXEC #1:c=6999,e=6668,p=0,cr=3,cu=252,mis=0,r=25,dep=1,og=4,tim=1209627175803869
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE DEPENDENCY$ (cr=3 pr=0 pw=0 time=6638 us)'
STAT #1 id=2 cnt=25 pid=1 pos=1 obj=122 op='INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=0 pw=0 time=144 us)'
=====================
PARSING IN CURSOR #1 len=35 dep=1 uid=0 oct=7 lid=0 tim=1209627175804016 hv=1428113398 ad='7ea53af0'
delete from access$ where d_obj#=:1
END OF STMT
PARSE #1:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175804012
EXEC #1:c=2000,e=2074,p=0,cr=2,cu=80,mis=0,r=16,dep=1,og=4,tim=1209627175806164
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE ACCESS$ (cr=2 pr=0 pw=0 time=2043 us)'
STAT #1 id=2 cnt=16 pid=1 pos=1 obj=124 op='INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=0 pw=0 time=118 us)'
=====================
PARSING IN CURSOR #1 len=118 dep=1 uid=0 oct=2 lid=0 tim=1209627175806315 hv=3983271524 ad='7e55de94'
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7)
END OF STMT
PARSE #1:c=1000,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175806311
EXEC #1:c=0,e=294,p=0,cr=1,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175806697
EXEC #1:c=0,e=279,p=0,cr=0,cu=12,mis=0,r=1,dep=1,og=4,tim=1209627175807021
EXEC #1:c=999,e=247,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175807312
EXEC #1:c=0,e=257,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175807613
EXEC #1:c=0,e=244,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175807900
EXEC #1:c=0,e=184,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175808127
EXEC #1:c=1000,e=249,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175808419
EXEC #1:c=0,e=246,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175808708
EXEC #1:c=0,e=249,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175808999
EXEC #1:c=1000,e=253,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175809294
EXEC #1:c=0,e=249,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175809586
EXEC #1:c=0,e=256,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175809885
EXEC #1:c=0,e=253,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175810181
EXEC #1:c=0,e=255,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175810482
EXEC #1:c=0,e=254,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175810779
EXEC #1:c=0,e=255,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175811077
EXEC #1:c=1000,e=264,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175811384
EXEC #1:c=0,e=235,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175811663
EXEC #1:c=0,e=257,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175811963
EXEC #1:c=1000,e=262,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175812271
EXEC #1:c=0,e=260,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175812574
EXEC #1:c=0,e=266,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175812883
EXEC #1:c=1000,e=267,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175813194
EXEC #1:c=0,e=262,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175813514
EXEC #1:c=0,e=259,p=0,cr=0,cu=10,mis=0,r=1,dep=1,og=4,tim=1209627175813817
=====================
PARSING IN CURSOR #1 len=69 dep=1 uid=0 oct=2 lid=0 tim=1209627175813913 hv=544200346 ad='7e55dad4'
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
END OF STMT
PARSE #1:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175813909
EXEC #1:c=999,e=159,p=0,cr=1,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175814156
EXEC #1:c=0,e=109,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175814301
EXEC #1:c=0,e=115,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175814454
EXEC #1:c=0,e=109,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175814601
EXEC #1:c=0,e=112,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175814750
EXEC #1:c=0,e=109,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175814896
EXEC #1:c=0,e=109,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175815042
EXEC #1:c=1000,e=113,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175815194
EXEC #1:c=0,e=113,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175815344
EXEC #1:c=0,e=112,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175815493
EXEC #1:c=0,e=118,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175815649
EXEC #1:c=0,e=115,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175815801
EXEC #1:c=0,e=117,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175815955
EXEC #1:c=1000,e=151,p=0,cr=0,cu=7,mis=0,r=1,dep=1,og=4,tim=1209627175816143
EXEC #1:c=0,e=116,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175816297
EXEC #1:c=0,e=119,p=0,cr=0,cu=5,mis=0,r=1,dep=1,og=4,tim=1209627175816454
=====================
PARSING IN CURSOR #3 len=331 dep=1 uid=0 oct=6 lid=0 tim=1209627175816579 hv=2997034431 ad='7e55d4d0'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #3:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1209627175816575
EXEC #3:c=0,e=236,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1209627175816924
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJ$ (cr=2 pr=0 pw=0 time=182 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=46 us)'
EXEC #2:c=119982,e=3393121258,p=0,cr=126,cu=2537,mis=0,r=0,dep=0,og=1,tim=1209627176748027
*** 2009-04-02 13:23:03.143
XCTEND rlbk=0, rd_only=1

Report message to a moderator

Re: Procedure Creation : Hanging in Parse phase (merged) [message #395925 is a reply to message #395913] Fri, 03 April 2009 02:03 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Creating a procedure should not take long. Possible reason could be that the current one is in use by another session.

About your code:
- why do you use CASE instead of the far more intuitive IF-THEN-ELSE construction?
- get rid of your WHEN OTHERS clause. It will give you LOTS of troubles if you leave it in.

Report message to a moderator

Re: Procedure Creation : Hanging in Parse phase (merged) [message #395926 is a reply to message #395913] Fri, 03 April 2009 02:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would guess that the procedure already existed, and that it was in use. Your session was probably hanging, waiting for other sessions to finish accessing it.

Worse is that you may well have been preventing other users from accessing the procedure while you were waiting.

Report message to a moderator

Re: Procedure Creation : Hanging in Parse phase (merged) [message #396052 is a reply to message #395926] Fri, 03 April 2009 11:46 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
(so try creating the proc with a different name to test if it's a locking issue)

Report message to a moderator

Re: Procedure Creation : Hanging in Parse phase (merged) [message #396053 is a reply to message #395913] Fri, 03 April 2009 11:55 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The reality is that say you have a procedure which does DML; but does contain any COMMIT (or ROLLBACK).
The procedure compiles without error & then gets invoked ( & no COMMIT).

If the same or other session tries to recompile procedure, it will "hang"; wait patiently for COMMIT or ROLLBACK to complete transaction.

Oracle does not allow a procedure to be changed while pending DML exists for this procedure.

Hope This Helps (HTH)!

Report message to a moderator

Previous Topic: SUBSTR Program
Next Topic: Use SQLPLUS Variable in DDL Command
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 01 21:21:37 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.