Home » SQL & PL/SQL » SQL & PL/SQL » Need modification in Stored Procedure. (11.2.0.3 )
Need modification in Stored Procedure. [message #640736] Tue, 04 August 2015 11:42 Go to next message
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 #640738 is a reply to message #640736] Tue, 04 August 2015 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

https://community.oracle.com/thread/3775251?tstart=0
Re: Need modification in Stored Procedure. [message #640739 is a reply to message #640736] Tue, 04 August 2015 11:59 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I do not understand. You are updating r_sheet and then querying rater_sheet. What are you trying to show?
Re: Need modification in Stored Procedure. [message #640740 is a reply to message #640739] Tue, 04 August 2015 12:13 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Sorry for teh confusion.

Typo mistake.. it's not Rater_sheet .. it's R_SHEET.



CLEAN_RECORD is the column.

1. Need to UPDATE "CLEAN_RECORD ='Y' if all FLAG columns are NULL

2. Need to update CLEAN_RECORD='' if any one flag marked as 'X'

Let me know if you need more information.
Re: Need modification in Stored Procedure. [message #640742 is a reply to message #640740] Tue, 04 August 2015 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Let me know if you need more information.

we need Test Case
Re: Need modification in Stored Procedure. [message #640743 is a reply to message #640742] Tue, 04 August 2015 12:45 Go to previous messageGo to next message
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 #640744 is a reply to message #640743] Tue, 04 August 2015 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

Re: Need modification in Stored Procedure. [message #640758 is a reply to message #640744] Tue, 04 August 2015 15:43 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
batch	L_CODE	L_FLAG	CLEAN_RECORD	F_CODE	F_CODE_FLAG	R1	R1_FLAG	S1	S1_FLAG	R2	R2_FLAG	S2	S2_FLAG	R3	R3_FLAG	SCAN_SEQ	SPEC_CODES	SPEC_CODES_FLAG	E_SCORE	TEST_DATE	TEST_YEAR	C_CODE	C_CODE_FLAG	E_SCORE1	E_SCORE2	E_SCORE3	E_SCORE4
079010	461847			23B		9999		3		9998		2				2			24	0615	14			5	7	7	5



if you observe this result set all flag values are NULL so need to update CLEAN_RECORD as 'Y'

if any one flag is marked as 'X' then need to update CLEAN_RECORD as ' '

this is the actual requirement, can any one you please advice..


Re: Need modification in Stored Procedure. [message #640760 is a reply to message #640758] Tue, 04 August 2015 16:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.



Re: Need modification in Stored Procedure. [message #640785 is a reply to message #640760] Wed, 05 August 2015 02:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #640828 is a reply to message #640827] Wed, 05 August 2015 09:37 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
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;
dbms_output.put_line('Updated ' || SQL%ROWCOUNT || ' rows.');
        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'
              );
dbms_output.put_line('Updated ' || SQL%ROWCOUNT || ' rows.');
end;
/
set serveroutput on
exec  clean_record_proc('123456');
select  *
  from  r_sheet
  where batch = '123456'
/



This is the procedure which I have written to update Clena_record column which is existed in R_SHEET table.

Have a look and advice.

Thanks,
Vasudev
Re: Need modification in Stored Procedure. [message #640829 is a reply to message #640828] Wed, 05 August 2015 09:40 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Doesn't change any of my points above.
All the options remain and you haven't given us enough info to determine the culprit.

Does the dbms_output say that rows have been updated?
Re: Need modification in Stored Procedure. [message #640830 is a reply to message #640828] Wed, 05 August 2015 09:40 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
And where is your test case
Re: Need modification in Stored Procedure. [message #640831 is a reply to message #640828] Wed, 05 August 2015 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Have a look and advice.
Please post complete Test Case
http://www.orafaq.com/wiki/Test_case
Re: Need modification in Stored Procedure. [message #640834 is a reply to message #640828] Wed, 05 August 2015 09:49 Go to previous messageGo to next message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
This 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
Re: Need modification in Stored Procedure. [message #640835 is a reply to message #640834] Wed, 05 August 2015 09:50 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Where's your test case?
Re: Need modification in Stored Procedure. [message #640836 is a reply to message #640834] Wed, 05 August 2015 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vasudevaviswa wrote on Wed, 05 August 2015 07:49
This 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


so write a new procedure to do that.
Re: Need modification in Stored Procedure. [message #640837 is a reply to message #640834] Wed, 05 August 2015 09:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
vasudevaviswa wrote on Wed, 05 August 2015 15:49
This 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 Go to previous message
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:40
And where is your test case


pablolee wrote on Wed, 05 August 2015 16:50
Where's your test case?


[Updated on: Wed, 05 August 2015 10:27]

Report message to a moderator

Previous Topic: update century in timestamp column in table (topics merged)
Next Topic: Merge on tables
Goto Forum:
  


Current Time: Thu Mar 28 04:13:33 CDT 2024