Try to put a 'COMMIT' after the insert (before open the second cursor).
"j" <perseus_medusa_at_hotmail.com> wrote in message
news:3e5a2739$1_at_newsgate.hknet.com...
> Hi all ,
>
> I have a stored proc and when I try to create it, it hangs and then it
> shows error message of time out waiting for a table lock of
> T_PROJECT_HIST_DEAL_RPT. Can someone help me with what's wrong with my
> script and why I didn't execute the stored proc yet but it has to acquire
> the lock ?
>
> Thanks.
>
> Here is the script
>
> CREATE OR REPLACE PROCEDURE Sp_Project_Hist_Deal_Rpt (
> -- some parameters
> ) AS
>
> CURSOR CUR_T_PROJECT_KEY IS
> SELECT DISTINCT
> -- some fields.
> FROM
> T_PROJECT_HDR_HIST HDR, T_PROJECT_DTL_HIST DTL,
> T_PRODUCT PRD, V_PROD_USER_GP USR, T_SUPPLY_CHAIN_TYPE SC
> WHERE
> HDR.WEEK_NO = DTL.WEEK_NO AND
> HDR.SFCST_PLAN_CD = DTL.SFCST_PLAN_CD AND
> HDR.PROJECT_CD = DTL.PROJECT_CD AND
> PRD.PRODUCT_CD = DTL.PRODUCT_CD AND
> USR.PRODUCT_CD = DTL.PRODUCT_CD AND
> PRD.SUPPLY_CHAIN_TYPE = SC.SUPPLY_CHAIN_TYPE AND
> (DTL.WEEK_NO = pWeekNum_1 OR DTL.WEEK_NO = pWeekNum_2) AND
> -- some other criteria
> CURSOR CUR_T_PROJECT_DTL (-- some param --) IS
> SELECT
> RQST_DLVY_DATE, PROB_CD, QTY, AMT, MARKED_DELETE_A, STATUS
> FROM
> T_PROJECT_DTL_HIST
> WHERE
> SFCST_PLAN_CD = mySFCST_PLAN_CD AND
> PROJECT_CD = myPROJECT_CD AND
> ITEM_NO = myITEM_NO AND
> WEEK_NO = myWEEK_NO;
>
> T_PROJECT_DTL_REC CUR_T_PROJECT_DTL%ROWTYPE;
> BEGIN
>
> FOR T_PROJECT_KEY_REC IN CUR_T_PROJECT_KEY LOOP
> INSERT INTO T_PROJECT_HIST_DEAL_RPT (...) VALUES (...);
> OPEN CUR_T_PROJECT_DTL( T_PROJECT_KEY_REC.SFCST_PLAN_CD,
> T_PROJECT_KEY_REC.PROJECT_CD,
> T_PROJECT_KEY_REC.ITEM_NO,
> pWeekNum_1);
> FETCH CUR_T_PROJECT_DTL INTO T_PROJECT_DTL_REC;
> IF CUR_T_PROJECT_DTL%FOUND then
> UPDATE T_PROJECT_HIST_DEAL_RPT set somefield = something WHERE
> SFCST_PLAN_CD = T_PROJECT_KEY_REC.SFCST_PLAN_CD AND
> PROJECT_CD = T_PROJECT_KEY_REC.PROJECT_CD AND
> ITEM_NO = T_PROJECT_KEY_REC.ITEM_NO;
> END IF ;
> CLOSE CUR_T_PROJECT_DTL;
> -- end of inserting version 1
> END LOOP;
> COMMIT;
> END;
>
>
>
>
Received on Mon Feb 24 2003 - 09:00:18 CST
