Home » Developer & Programmer » Forms » block based procedure update error (form 10g Database 10gR2)
block based procedure update error [message #613444] |
Tue, 06 May 2014 23:21 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: block based procedure update error [message #613448 is a reply to message #613444] |
Wed, 07 May 2014 01:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43055.jpg) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: block based procedure update error [message #613454 is a reply to message #613453] |
Wed, 07 May 2014 02:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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.
|
|
|
Re: block based procedure update error [message #613459 is a reply to message #613454] |
Wed, 07 May 2014 05:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ?
|
|
|
|
Re: block based procedure update error [message #613461 is a reply to message #613460] |
Wed, 07 May 2014 06:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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:34please 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
zeeshan_mhd wrote on Wed, 07 May 2014 11:25This 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?
|
|
|
Re: block based procedure update error [message #613584 is a reply to message #613467] |
Thu, 08 May 2014 05:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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......
|
|
|
Re: block based procedure update error [message #613616 is a reply to message #613584] |
Thu, 08 May 2014 09:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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:08Thank 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:24Dear 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.
|
|
|
Goto Forum:
Current Time: Mon Sep 09 08:51:47 CDT 2024
|