Need modification in Stored Procedure. [message #640736] |
Tue, 04 August 2015 11:42 |
|
vasudevaviswa
Messages: 53 Registered: August 2015 Location: US
|
Member |
|
|
Hi All,
I have written a Stored Procedure to update a columns called " CLEAN_RECORD".. But this is not updating CLEAN_RECORD column.
create or replace
procedure clean_record_proc(
p_gpnb varchar2
)
is
begin
update r_sheet
set clean_record = 'Y'
where clean_record is null
and batch = p_gpnb
and l_flag is null
and form_code_flag is null
and r1_flag is null
and r2_flag is null
and r3_flag is null
and r4_flag is null
and s1_flag is null
and s2_flag is null
and s3_flag is null
and s4_flag is null
and c_code_flag1 is null
and c_code_flag2 is null
and c_code_flag3 is null
and c_code_flag4 is null;
update r_sheet
set clean_record = ' '
where batch= p_gpnb
and (
l_flag = 'X'
or form_code_flag = 'X'
or r1_flag = 'X'
or r2_flag = 'X'
or r3_flag = 'X'
or r4_flag = 'X'
or s1_flag = 'X'
or s2_flag = 'X'
or s3_flag = 'X'
or s4_flag = 'X'
or c_code_flag1 = 'X'
or c_code_flag2 = 'X'
or c_code_flag3 = 'X'
or c_code_flag4 = 'X'
);
end;
/
exec clean_record_proc('123456');
select *
from rater_sheet
where pnb = '123456'
/
the procedure got compiled successfully. But it is not updating the required column. Can any one please have a look and do the required modifications.
Thanks,
Vasudev
*BlackSwan corrected {code} tags
[Updated on: Tue, 04 August 2015 11:52] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Need modification in Stored Procedure. [message #640743 is a reply to message #640742] |
Tue, 04 August 2015 12:45 |
|
vasudevaviswa
Messages: 53 Registered: August 2015 Location: US
|
Member |
|
|
before execution of Procedure
Case1 :
select * from r_sheet where batch = '123456'
batch l_flag form_code_flag r1_flag r2_flag r3_flag r4_flag s1_flag s2_flag s3_flag s4_flag c_code_flag1 c_code_flag2 c_code_flag3 c_code_flag4 S1 S2 S3 S4 R1 R2 R3 R4 Clean_record
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123456 2 2 3 1 99 98 101 108
AFter Execution of the procedure the table CLEAN_RECORD columns should update as
batch l_flag form_code_flag r1_flag r2_flag r3_flag r4_flag s1_flag s2_flag s3_flag s4_flag c_code_flag1 c_code_flag2 c_code_flag3 c_code_flag4 S1 S2 S3 S4 R1 R2 R3 R4 Clean_record
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123456 2 2 3 1 99 98 101 108 Y
Case 2 :
Before execution : ( IF CLEAN_RECORD column is Marked / Y/null )
select * from R_SHEET where batch = '079006';
batch l_flag form_code_flag r1_flag r2_flag r3_flag r4_flag s1_flag s2_flag s3_flag s4_flag c_code_flag1 c_code_flag2 c_code_flag3 c_code_flag4 S1 S2 S3 S4 R1 R2 R3 R4 Clean_record
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
079006 X X 2 2 3 1 99 98 101 108 Y/X/Null
AFter execution of the procedure : ( If any flag column is marked then CLEAN_RECORD column should be NULL)
batch l_flag form_code_flag r1_flag r2_flag r3_flag r4_flag s1_flag s2_flag s3_flag s4_flag c_code_flag1 c_code_flag2 c_code_flag3 c_code_flag4 S1 S2 S3 S4 R1 R2 R3 R4 Clean_record
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
079006 X X 2 2 3 1 99 98 101 108 NULL
There are around 50K batches. SO all should update as per above.
[Updated on: Tue, 04 August 2015 12:48] Report message to a moderator
|
|
|
|
|
|
Re: Need modification in Stored Procedure. [message #640785 is a reply to message #640760] |
Wed, 05 August 2015 02:53 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Considering how simple the procedure is there are the following possibilities:
1) The data doesn't match the where clause of the update statements
2) You're getting an error you're not telling us about
3) There's a trigger on r_sheet that's modifying clean_record
4) The update is working just fine and you're checking the wrong things afterwards
5) Some other session is modifying the data in between you updating it and you checking it.
We can't tell which of those it is with the information provided. If you give us a full test case then we should be able to work out the culprit.
|
|
|
Re: Need modification in Stored Procedure. [message #640827 is a reply to message #640736] |
Wed, 05 August 2015 09:15 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
update r_sheet
set clean_record = 'Y'
where clean_record is null
and batch = p_gpnb
select *
from rater_sheet
where pnb = '123456'
Even accepting that you typoed the table name, your where clause is restricting on a column that will not necessarily return the row(s) that you updated. If that's another typo, then post, along with the test case, the actual procedure (rather than one that contains typos.)
|
|
|
|
|
|
|
|
|
|
Re: Need modification in Stored Procedure. [message #640837 is a reply to message #640834] |
Wed, 05 August 2015 09:55 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vasudevaviswa wrote on Wed, 05 August 2015 15:49This procedure is working fine. But the problem is , each time we need to execute the procedure for every batch individually. But i am expecting this procedure should run for all batches without giving any specific batch number.
Thanks
Is that as simple as removing batch from the where clause of the updates?
|
|
|
Re: Need modification in Stored Procedure. [message #640838 is a reply to message #640834] |
Wed, 05 August 2015 10:26 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Tue, 04 August 2015 23:00
Michel Cadot wrote on Tue, 04 August 2015 20:07
Use SQL*Plus and copy and paste your session, the WHOLE session.
Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.
pablolee wrote on Wed, 05 August 2015 16:40And where is your test case
pablolee wrote on Wed, 05 August 2015 16:50Where's your test case?
[Updated on: Wed, 05 August 2015 10:27] Report message to a moderator
|
|
|