OraFAQ Forum: SQL & PL/SQL » Capturing OLD and NEW values from the Parameter of a sored procedure

Home » SQL & PL/SQL » SQL & PL/SQL » Capturing OLD and NEW values from the Parameter of a sored procedure (11g)

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

Capturing OLD and NEW values from the Parameter of a sored procedure [message #610315]

Wed, 19 March 2014 06:52

mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore

Junior Member

Hi All,

I have a procedure where a pass couple of in parameters such quote header id ,... and based on the value of quote header id it gives output pare metes values such response time , part replacement time etc .

My question is that when i rerun the same procedure for the same quote header id and then out put parameter values are different then i want to capture the values of of both old and new and set a flag e.g:- Y or N .

Here is an example :-

SET SERVEROUTPUT ON
DECLARE
v_msg VARCHAR2 (2000);
v_cde VARCHAR2 (2000);
v_sl_response_time VARCHAR2 (2000);
v_sl_part_replace VARCHAR2 (2000);

BEGIN
XX_SERVICE_TEST (p_quote_header_id =>18591601,
x_sl_response_time =>v_sl_response_time,
x_sl_part_replace =>v_sl_part_replace,);
DBMS_OUTPUT.put_line ('Response Time-'||v_sl_response_time);
DBMS_OUTPUT.put_line ('Part Replace-'||v_sl_part_replace);

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;

Here is my output when run it quote header if 9765:-

anonymous block completed
Response Time-2
Part Replace-2

Here is my Out put value when i run it for the second time for the quote header id 9765 -

anonymous block completed
Response Time-1
Part Replace-1

.

Now as the out put parameters values different for the same quote header id when i rerun the procedure
i want set falg as 'Y' otherwise 'N' and return that value .

Regard,
Mike

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610342 is a reply to message #610315]

Wed, 19 March 2014 08:29

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The very first thing to do is to remobe this:

Quote:

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);

Quote:

set a flag e.g:- Y or N .

Set where? What is flag? A variable? A column in a table? A parameter of a procedure?

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610351 is a reply to message #610342]

Wed, 19 March 2014 08:51

mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore

Junior Member

I have to set variable in the procedure .

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610352 is a reply to message #610351]

Wed, 19 March 2014 08:53

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

Senior Member

Which procedure?
The code in your first post is not a procedure, just an anonymous pl/sql block.

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610353 is a reply to message #610352]

Wed, 19 March 2014 09:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>i want set falg as 'Y' otherwise 'N' and return that value .
how does dumb code know, recognize or determine if it had been previously run?
>and return that value .
which value do you mean by "that value"?

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610358 is a reply to message #610352]

Wed, 19 March 2014 09:38

mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore

Junior Member

Here is an example of a procedure where i if pass the loc i get the respective dname and when i pass the same loc and if i get dname and both old and new
dname is different i want to set a falg :--

E.g:-
CREATE or REPLACE PROCEDURE test_n (p_loc varchar2,p_loc2 varchar2,
x_dname OUT VARCHAR2,x_dname2 OUT VARCHAR2)
AS
v_dname VARCHAR2(10);,
v_old_loc varchar2(10);
v_new_loc varchar2(10);
BEGIN
SELECT dname
INTO x_dname
FROM
scott.dept
WHERE loc=p_loc;

v_old_loc:=p_loc;
v_new_loc:=p_loc;
dbms_output.put_line('OLD LOC-'||v_old_loc);
dbms_output.put_line('NEW LOC-'||v_new_loc);

END;

napp_qhdr_rec_obj_typ

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610360 is a reply to message #610358]

Wed, 19 March 2014 09:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>if i get dname and both old and new dname is different i want to set a falg :--
FLAG?
what or where is flag?
is flag striped?
how many different colors does your flag have?

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610361 is a reply to message #610360]

Wed, 19 March 2014 09:57

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In addition, is the flag only for the current session or also for the other sessions?
In the later, does this flag must be seen immediately by the other sessions or after some event like a commit?
In the end, what is or will be the use of this flag?
What do OLD and NEW refer to?

Quote:

v_old_loc:=p_loc;
v_new_loc:=p_loc;

How old can be different to new if set to the same thing?

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610392 is a reply to message #610361]

Wed, 19 March 2014 13:33

mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore

Junior Member

This flag is for other sessions also .
This flag would would be seen immediately by the other sessions .
The use of the flag is that if there is any change in 'dname' value for the same 'loc' parameter then value of flag(variable) should be 'Y' else 'N' and
if it is 'Y' then some other validation has to be performed .

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610395 is a reply to message #610392]

Wed, 19 March 2014 13:51

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I only see one way to do it, global context variables.

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610558 is a reply to message #610395]

Fri, 21 March 2014 04:10

mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore

Junior Member

Could you please give me an example for this in the same procedure which i have given form dept table .

Report message to a moderator

Re: Capturing OLD and NEW values from the Parameter of a sored procedure [message #610560 is a reply to message #610558]

Fri, 21 March 2014 04:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please try to do it and then show us what you did and where you are stuck.
Did you try to create a (global) context? Did you try to set/get some variables into/from it?

Report message to a moderator

Previous Topic:	Date and Last7Days
Next Topic:	assign variable a calculated value in pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:43:21 CDT 2024