Re: hang while creating stored proc

Peter wrote:

> 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;
> >
> >
> >
> >

Not a good idea. The single commit is correct though a bit of error trapping might be appropriate. Commits inside of loops are good for producing ORA-01555 and killing performance, but little else.

The SQL, itself, has nothing to do with hanging during compilation even were it to be a problem when the proc was run. Jeff Smith's response is likely the correct one.

Daniel Morgan Received on Mon Feb 24 2003 - 13:54:20 CST