OraFAQ Forum: Forms » block based procedure update error

Home » Developer & Programmer » Forms » block based procedure update error (form 10g Database 10gR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

block based procedure update error [message #613444]

Tue, 06 May 2014 23:21

zeeshan_mhd
Messages: 9
Registered: September 2008
Location: india

Junior Member

Dear All,
I am getting error during update .
ORA-01422 exact fetch returns more than requested number of rows ORA-06512 at WEB_HR.SHIFT_PKG2
my package and it body are as below
please help me out
I wish to update of a particular selected employee 7 days shift
eg
Fetching record like this sucessfully
i wish to update any recored from this.

empid shiftdate shift
100 07-May-2014 A
100 08-May-2014 A
100 09-May-2014 A
100 10-May-2014 A
100 11-May-2014 A

CREATE OR REPLACE PACKAGE "WEB_HR"."SHIFT_PKG2" as

TYPE shift_rec is RECORD(
SHIFT_ID WEB_HR.HR_SHIFT.SHIFT_ID%TYPE,
EMP_ID WEB_HR.HR_SHIFT.EMP_ID%TYPE,
SHIFT_DATE WEB_HR.HR_SHIFT.SHIFT_DATE%TYPE,
DELFLAG WEB_HR.HR_SHIFT.DELFLAG%TYPE,
COMPANYCODE WEB_HR.HR_SHIFT.COMPANYCODE%TYPE,
ENTRYDATE WEB_HR.HR_SHIFT.ENTRYDATE%TYPE,
SHIFT WEB_HR.HR_SHIFT.SHIFT%TYPE,
OT_ID WEB_HR.HR_SHIFT.OT_ID%TYPE ,
YEAR_ID WEB_HR.HR_SHIFT.YEAR_ID%TYPE
);

TYPE shifttab_type IS TABLE OF shift_rec INDEX BY BINARY_INTEGER;
--TYPE shifttab_type IS TABLE OF shift_rec INDEX BY varchar2;

PROCEDURE shift_query(sft_tab IN OUT shifttab_type,p_empid IN NUMBER);
PROCEDURE Shift_update(sft_tab IN OUT shifttab_type,empid IN number);
PROCEDURE shift_lock(sft_tab IN OUT shifttab_type);
end shift_pkg2;
CREATE OR REPLACE PACKAGE BODY "WEB_HR"."SHIFT_PKG2" IS
PROCEDURE shift_query(sft_tab IN OUT shifttab_type,p_empid IN NUMBER)
is
CURSOR emp_cur (vempid web_hr.hr_shift.emp_id%type) is
select SHIFT_ID, EMP_ID,SHIFT_DATE,DELFLAG, COMPANYCODE, ENTRYDATE, SHIFT,OT_ID,year_id
from web_hr.hr_shift where trunc(shift_date)>=trunc(sysdate)and emp_id=vempid;
idx number :=1;
BEGIN
sft_tab.delete;

for rec in emp_cur(p_empid) loop
sft_tab(idx):=rec;
idx := idx + 1;
end loop;
end;

PROCEDURE Shift_update(sft_tab IN OUT shifttab_type, empid IN number)
is
CURSOR emp_cur (vempid web_hr.hr_shift.emp_id%type) is
select SHIFT_ID, EMP_ID,SHIFT_DATE,DELFLAG, COMPANYCODE, ENTRYDATE, SHIFT,OT_ID,year_id
from web_hr.hr_shift where trunc(shift_date)>=trunc(sysdate)and emp_id=vempid;
idx number :=1;
BEGIN
sft_tab.delete;

for rec in emp_cur(empid) loop
sft_tab(idx):=rec;

UPDATE WEB_HR.HR_SHIFT
SET
SHIFT_ID=sft_tab(idx).SHIFT_ID,
EMP_ID=sft_tab(idx).EMP_ID,
SHIFT_DATE=sft_tab(idx).SHIFT_DATE,
DELFLAG=sft_tab(idx).DELFLAG,
COMPANYCODE=sft_tab(idx).COMPANYCODE,
ENTRYDATE=sft_tab(idx).ENTRYDATE,
SHIFT=sft_tab(idx).SHIFT,
OT_ID=sft_tab(idx).OT_ID,
year_id=sft_tab(idx).year_id
where emp_id=sft_tab(idx).emp_id ;
idx := idx + 1;
end loop;
/*
--CURSOR shift_details(vempid number) IS
CURSOR shift_details is
select *
from web_hr.hr_shift where trunc(shift_date)>=trunc(sysdate);--and emp_id=vempid;

TYPE l_emp_id is TABLE OF shift_details%ROWTYPE INDEX BY PLS_INTEGER;
TYPE l_shift is TABLE OF shift_details%ROWTYPE INDEX BY PLS_INTEGER;

v_empid l_emp_id ;
v_shift l_shift ;

BEGIN
OPEN shift_details ;
LOOP
FETCH shift_details BULK COLLECT INTO v_empid ,v_shift ;

FORALL i IN 1 .. V_empid.COUNT
UPDATE WEB_HR.HR_SHIFT
SET SHIFT=v_shift (i)
where emp_id=v_shiftid(i);

EXIT WHEN shift_details%NOTFOUND;
END LOOP;
CLOSE shift_details ;
*/
END Shift_update;

PROCEDURE shift_lock(sft_tab IN OUT shifttab_type)
is
v_empid web_hr.hr_shift.emp_id%TYPE;
v_shiftid web_hr.hr_shift.SHIFT_ID%TYPE;
BEGIN
FOR v_ct IN 1 .. sft_tab.count
LOOP
SELECT emp_id ,shift_id
INTO v_empid ,v_shiftid
FROM web_hr.hr_shift
WHERE emp_id = sft_tab(v_ct).emp_id and shift_id=sft_tab(v_ct).shift_id
FOR UPDATE;
END LOOP;
END shift_lock;

END shift_pkg2;

form is attached

Any help is highly appreciate.
zeeshan

Attachment: SHIFT_CHANGE_UPDATE.fmb
(Size: 84.00KB, Downloaded 1219 times)

Report message to a moderator

Re: block based procedure update error [message #613448 is a reply to message #613444]

Wed, 07 May 2014 01:10

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Could you format your code, next time?

[code]formatted code here[/code]

Now, at first glance I believe that the procedure shift_lock is causing the error.

   PROCEDURE shift_lock (sft_tab IN OUT shifttab_type)
   IS
      v_empid     web_hr.hr_shift.emp_id%TYPE;
      v_shiftid   web_hr.hr_shift.SHIFT_ID%TYPE;
   BEGIN
      FOR v_ct IN 1 .. sft_tab.COUNT
      LOOP
         SELECT emp_id, shift_id
           INTO v_empid, v_shiftid
           FROM web_hr.hr_shift
          WHERE     emp_id = sft_tab (v_ct).emp_id
                AND shift_id = sft_tab (v_ct).shift_id
         FOR UPDATE;
      END LOOP;
   END shift_lock;

What is the key of table web_hr.hr_shift? Is the combination emp_id, shift_id unique in the table?

Since I don't have Forms around I can't look at your FMB.

MHE

[Updated on: Wed, 07 May 2014 07:06]

Report message to a moderator

Re: block based procedure update error [message #613449 is a reply to message #613448]

Wed, 07 May 2014 01:54

zeeshan_mhd
Messages: 9
Registered: September 2008
Location: india

Junior Member

web_hr.hr_shift table
SHIFT_ID NUMBER(20)UNIQUE
EMP_ID NUMBER(4)
SHIFT_DATE DATE
DELFLAG CHAR(1)
COMPANYCODE NUMBER(4)
ENTRYDATE DATE
SHIFT CHAR(1)
OT_ID NUMBER(20)
YEAR_ID NUMBER(2)

Dear Sir,
SHIFT_ID is unique field

Report message to a moderator

Re: block based procedure update error [message #613453 is a reply to message #613449]

Wed, 07 May 2014 02:47

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Please read and follow How to use [code] tags and make your code easier to read?

That select is the only thing in the code you've posted that can throw that error, which implies that shift_id isn't really unique.
Does this return any rows:

SELECT emp_id, shift_id
FROM web_hr.hr_shift
GROUP BY emp_id, shift_id
HAVING COUNT(*) > 1;

Report message to a moderator

Re: block based procedure update error [message #613454 is a reply to message #613453]

Wed, 07 May 2014 02:50

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Side note, this:

where trunc(shift_date)>=trunc(sysdate)

is equivalent to:

where shift_date >= trunc(sysdate)

But the 2nd one will allow oracle to use an index on shift_date if there is one. You should avoid applying functions to columns in where clauses where possible.

Report message to a moderator

Re: block based procedure update error [message #613459 is a reply to message #613454]

Wed, 07 May 2014 05:25

zeeshan_mhd
Messages: 9
Registered: September 2008
Location: india

Junior Member

PROCEDURE shift_lock(sft_tab IN OUT shifttab_type)
is
v_empid web_hr.hr_shift.emp_id%TYPE;
v_shiftid web_hr.hr_shift.SHIFT_ID%TYPE;
BEGIN
FOR v_ct IN 1 .. sft_tab.count
LOOP
SELECT emp_id ,shift_id
INTO v_empid ,v_shiftid
FROM web_hr.hr_shift
WHERE emp_id = sft_tab(v_ct).emp_id and shift_id=sft_tab(v_ct).shift_id
FOR UPDATE;
END LOOP;
END shift_lock;

This procedure return more than one rows ...this is the problem is there any alternative to achieve this ?

Report message to a moderator

Re: block based procedure update error [message #613460 is a reply to message #613459]

Wed, 07 May 2014 06:04

zeeshan_mhd
Messages: 9
Registered: September 2008
Location: india

Junior Member

please HV FINE THE ERROR SNAP SHOOT

Report message to a moderator

Re: block based procedure update error [message #613461 is a reply to message #613460]

Wed, 07 May 2014 06:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

zeeshan_mhd wrote on Wed, 07 May 2014 16:34

please HV FINE THE ERROR SNAP SHOOT

?

1. And why are you not using code tags even after being told couple of times? Unformatted code looks ugly and unreadable.
2. Please post Cookiemonster's query results to know whether shift_id is really unique or not.

[Updated on: Wed, 07 May 2014 06:16]

Report message to a moderator

Re: block based procedure update error [message #613467 is a reply to message #613459]

Wed, 07 May 2014 06:42

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

zeeshan_mhd wrote on Wed, 07 May 2014 11:25

This procedure return more than one rows ...this is the problem is there any alternative to achieve this ?

First off - use code tags, as Maaher and I already asked you to. I've posted the link to the instructions on how to do so.
Secondly - that procedure doesn't return any rows other than what's passed in in the first place, the problem is the select. Again - what results does the query I supplied give?

Report message to a moderator

Re: block based procedure update error [message #613584 is a reply to message #613467]

Thu, 08 May 2014 05:38

zeeshan_mhd
Messages: 9
Registered: September 2008
Location: india

Junior Member

Thank i my problem got resolved ..
by using bulk collect

CREATE OR REPLACE PACKAGE BODY "WEB_HR"."SHIFT_PKG2" IS
PROCEDURE shift_query(sft_tab IN OUT shifttab_type,p_empid IN NUMBER)
is
CURSOR emp_cur (vempid web_hr.hr_shift.emp_id%type) is
select SHIFT_ID, EMP_ID,SHIFT_DATE,DELFLAG, COMPANYCODE, ENTRYDATE, SHIFT,OT_ID,year_id
from web_hr.hr_shift where trunc(shift_date)>trunc(sysdate)and emp_id=vempid;
idx number :=1;
BEGIN
sft_tab.delete;

for rec in emp_cur(p_empid) loop
sft_tab(idx):=rec;
idx := idx + 1;
end loop;
end;

PROCEDURE Shift_update(sft_tab IN OUT shifttab_type, empid IN number)
IS
BEGIN
FOR i IN 1..sft_tab.COUNT
LOOP
UPDATE WEB_HR.HR_SHIFT
SET SHIFT=sft_tab(i).SHIFT
WHERE SHIFT_ID=sft_tab(i).SHIFT_ID ;
END LOOP ;
END Shift_update ;

PROCEDURE shift_lock(sft_tab IN OUT shifttab_type,empid IN number)
IS
type mycollectiontype is table of number index by binary_integer;
mycollection mycollectiontype;
v_empno number;
vshift_id number;
begin
select shift_id bulk collect into mycollection from web_hr.hr_shift where shift_id =vshift_id and emp_id=v_empno
for update ;
FOR indx IN 1 .. mycollection.COUNT
LOOP
vshift_id := mycollection(indx);
END LOOP;
END shift_lock ;
END shift_pkg2 ;

Thanks......

Report message to a moderator

Re: block based procedure update error [message #613616 is a reply to message #613584]

Thu, 08 May 2014 09:03

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

zeeshan_mhd wrote on Thu, 08 May 2014 16:08

Thank i my problem got resolved ..
by using bulk collect

So that proves tha fact that shift_id never return unique record and that scalar assignment failed all the time. And it was asked to you several times to check the same, however you just said the opposite :

zeeshan_mhd wrote on Wed, 07 May 2014 12:24

Dear Sir,
SHIFT_ID is unique field

If you don't provide correct information, then how could you expect a solution? The solution was right there in the error message itself, and everybody pointed to you initially.

Report message to a moderator

Previous Topic:	Moving a Form column position
Next Topic:	VALIDATATION

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 01 23:22:29 CDT 2024