Sunday, October 08, 2006

Something new I learned this week...

I like to say from time to time "I learn something new about Oracle every day".  That is what is known as "hyperbole", but it really isn't that far from the truth.

Last week, I learned something about PL/SQL I was not aware of.  I was not aware of it because

  • I avoid global variables as much as possible
  • I would not be likely to have a construct such as the following example in my code

But, I am glad I know it now.  I can use this to develop "safer code" in the future having this knowledge.

Watch what happens to my parameter value in "private" below!

ops$tkyte%ORA9IR2> create or replace package my_pkg
2 as
3 procedure p;
4 end;
5 /
Package created.

ops$tkyte%ORA9IR2> create or replace package body my_pkg
2 as
3 g_global number;
4
5 procedure private( l_parameter in number )
6 is
7 begin
8 dbms_output.put_line( 'My Parameter value was ' || l_parameter );
9 g_global := 42;
10 dbms_output.put_line( 'My Parameter value is now ' || l_parameter );
11 end;
12
13 procedure p
14 is
15 begin
16 g_global := 55;
17 private(g_global);
18 end;
19
20 end;
21 /

Package body created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec my_pkg.p
My Parameter value was 55
My Parameter value is now 42

PL/SQL procedure successfully completed.

Neat eh?  It comes from the fact that IN parameters are passed by reference (as a pointer).  So, how can I defend from this?  Well, one approach is to never use globals (yah!  go for it, do it, you will never be sorry!!! truly, honestly, I assure you this is true).


The other approach, when FORCED to use globals, assign them to another variable OR cause a temporary to be created (|| '' for strings, +0 for dates, numbers...)

 13  procedure p
14 is
15 begin
16 g_global := 55;
17 private(g_global+0);
18 end;
19
20 end;
21 /

Package body created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec my_pkg.p
My Parameter value was 55
My Parameter value is now 55

PL/SQL procedure successfully completed.

But, if you just never use globals in any sense of the word you shall be immune from this.


I've been thinking of starting a "what I learned this week" sort of thread, maybe this will be the genesis of it all.

POST A COMMENT

43 Comments:

Anonymous Mark A. Williams said....

> That is what is known as "hyperbole", but it really isn't that far from the truth.

Hmm... that could be fodder for the Phantom Nitpicker, eh?

Another possible alternative might be declaring the parameter to be "in out" rather than simply "in" as the default for "in out" parameters is pass by value (if my memory serves me right). Any immediate drawbacks jump out about that?

- Mark

Sun Oct 08, 09:12:00 PM EDT  

Anonymous Mark A. Williams said....

...and when I asked "Any immediate drawbacks jump out about that?", I meant other than the obvious "you can modify the value of the passed parameter in the procedure body" in case anyone brings up that.

- Mark

Sun Oct 08, 09:22:00 PM EDT  

Blogger Robert said....

hmmm after all these years, didn't know you were "against" using GV.

First, isn't it a rather illogical, nonsensical programming pratice to be passing GV as parameter in the first place ?

Also - not using GV as a rule - doesn't it kind of put a dent to one of the benefits of Packages - i.e. Having a "state" ?

Sun Oct 08, 09:24:00 PM EDT  

Blogger Gary Myers said....

Doesn't have to be a package level global variable, but anyone that is within scope.

DECLARE
v_num number;
PROCEDURE p (p_in in number) IS
BEGIN
dbms_output.put_line(p_in);
v_num := v_num + 1;
dbms_output.put_line(p_in);
END;
BEGIN
v_num := 1;
p(v_num);
END;

Sun Oct 08, 09:57:00 PM EDT  

Anonymous Anthony Wilson said....

Another point to note is that when an exception is raised, the behaviour is different. PL/SQL will roll back any alterations to in/out parameters or globals:

SQL> set serveroutput on
SQL> ed
Wrote file afiedt.sql

1 declare
2 n number := 5;
3 procedure p(p_var in out number) as
4 begin
5 p_var := 3;
6 raise no_data_found;
7 end;
8 begin
9 dbms_output.put_line('n before p: '||n);
10 p(n);
11 exception
12 when no_data_found then
13 dbms_output.put_line('n after p: '||n);
14* end;
SQL> /
n before p: 5
n after p: 5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>

I was unaware of this until recently, as it's different to other pass-by-reference languages such as java.

Sun Oct 08, 11:23:00 PM EDT  

Anonymous Anthony Wilson said....

Sorry, what I said above is slightly incorrect.

When an exception is raised by a procedure, changes to OUT or IN/OUT parameters are rolled back. changes to globals remain.

Mon Oct 09, 12:49:00 AM EDT  

Anonymous Anonymous said....

> ... "what I learned this week" sort of thread
Yes, a good idea in my opinion.
BR,
Martin

Mon Oct 09, 04:55:00 AM EDT  

Blogger SeanMacGC said....

And, as Tom has as a thread on asktom (as I've just verified), globals as CONSTANTS are absolutely fine. No danger there.

Mon Oct 09, 04:56:00 AM EDT  

Blogger APC said....

>> First, isn't it a rather illogical,
>> nonsensical programming pratice to
>> be passing GV as parameter in the
>> first place ?

I must admit that occured to me too. Surely the point of using a global variable is to avoid the need to pass it as a parameter within a package.

Cheers, APC

Mon Oct 09, 06:10:00 AM EDT  

Blogger SeanMacGC said....

I've been thinking of starting a "what I learned this week" sort of thread...
Absolutely! If it's worth your acknowledgement, it's worth our knowing it.

Mon Oct 09, 07:02:00 AM EDT  

Blogger Thomas Kyte said....

Any immediate drawbacks jump out about that?

the overhead of the copy on in and the copy back on the out path of the code.

didn't know you were "against" using GV

Really - been saying it for a long time. Globals are evil, bugs just waiting to happen.

nonsensical programming pratice to be passing GV

Not really - why?

create package body my_pkg
as
g_file_hdl utl_file.file;
....

Now, I'll obviously be passing g_file_hdl to utl_file a lot won't I...

It is rather common I believe.


Maintaining a state is not all that it is cracked up to be in many cases, I limit the use of them to the bare minimum.

And for me, the main package benefit are:

a) breaks dependency chain, stops cascading invalidations
b) modularity, related code goes together
c) LESS GLOBAL NAMESPACE OBJECTS
d) hiding procedure and functions that are not relevant from the outside world.

Doesn't have to be a package level global variable

correct, just needs to be a variable that is currently global in scope. I figured that most people don't even necessarily know that you can have an anonymous block with a procedure in it - and that the package situation more readily mimicks "real life"

ill roll back any alterations to in/out parameters or globals

it is not that is ROLLS BACK, but rather that plsql in out variables are

a) copied into tempories on the way in
b) copied back out into the in out parameter upon successful completion

NOCOPY hint may alter that behavior (MAY, not WILL).

also, this has NO effect on globals at all as noted in a subsequent comment...

5 procedure private
6 ( l_p1 in out number,
7 l_p2 in out NOCOPY number )
8 is
9 begin
10 g_global := 42;
11 l_p1 := 42;
12 l_p2 := 42;
13 raise program_error;
14 end;
15
16 procedure p
17 is
18 l_param1 number := 55;
19 l_param2 number := 55;
20 begin
21 g_global := 55;
22 private(l_param1,l_param2);
23 exception
24 when program_error
25 then
26 dbms_output.put_line
27 ( g_global || ',' ||
28 l_param1 || ',' ||
29 l_param2 );
30 end;
31
32 end;
33 /

Package body created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec my_pkg.p
42,55,42

PL/SQL procedure successfully completed.

See how the global is "modified" as well as l_param2 because of the NOCOPY HINT (that might be ignored - don't rely on this)

Mon Oct 09, 07:47:00 AM EDT  

Anonymous Mark A. Williams said....

Any immediate drawbacks jump out about that?

the overhead of the copy on in and the copy back on the out path of the code.


True. I wonder if the overhead of that is greater than the overhead of creating and destroying a temp variable or maintaining an additional "permanent" variable. Yes, I'm being lazy by not tracing it and finding out myself.

Plus, this is somewhat academic in that it takes place in the context of being "forced" to use globals.

Mon Oct 09, 09:54:00 AM EDT  

Anonymous Anonymous said....

Background/comment disclaimer: I'm originally a C coder, been learning SQL for the past decade or so, and Oracle for the past 3 months. Really really enjoy learning stuff from your asktom site and this blog.

The original code snippet is simply a textbook example of variable aliasing ), and as such, something to be avoided unless you dislike both the optimizer and any future readers of your code. But it is a perfectly normal antipattern for procedural-thinking function-oriented C-heads like me. Set oriented languages like SQL are what I find hard. :(

-Jim

Mon Oct 09, 10:16:00 AM EDT  

Blogger Thomas Kyte said....

I'm originally a C coder

I'm a C coder from way back myself...

However, in C, you would pass explicitly by reference:


f( &global );

as opposed to a normal "in" parameter:

f( global );


Here, plsql is doing the f(&global) for all in variables and exposing that to the programmer - whom is likely unaware (as I was :) of the side effect that was possible. In C it would be done explicitly (probably not on PURPOSE, but explicitly :)


I agree it is an anti-pattern.

Mon Oct 09, 10:24:00 AM EDT  

Anonymous Jeff said....

You can also bump into a similar problem simply by recompiling the program unit that recreates and populates the global variable.

CREATE OR REPLACE PACKAGE jce_initialize
AS
--
v_global_counter NUMBER;
PROCEDURE increment;
--
END;
/

CREATE OR REPLACE PACKAGE BODY jce_initialize
AS
--
PROCEDURE increment
IS
BEGIN
FOR i IN 1..5 LOOP
v_global_counter := v_global_counter + 1;
END LOOP;
END increment;
--
BEGIN
v_global_counter := 1;
END jce_initialize;
/

cle scr;
BEGIN
jce_initialize.increment;
dbms_output.put_line('v_global_counter = '||jce_initialize.v_global_counter);
END;
/

ALTER PACKAGE jce_initialize COMPILE;

BEGIN
dbms_output.put_line('v_global_counter = '||jce_initialize.v_global_counter);
END;
/

--DROP PACKAGE jce_initialize;

jce>BEGIN
2 jce_initialize.increment;
3 dbms_output.put_line('v_global_counter = '||jce_initialize.v_global_counter);
4 END;
5 /
v_global_counter = 6

PL/SQL procedure successfully completed.

jce>
jce>ALTER PACKAGE jce_initialize COMPILE;

Package altered.

jce>
jce>BEGIN
2 dbms_output.put_line('v_global_counter = '||jce_initialize.v_global_counter);
3 END;
4 /
v_global_counter = 1

PL/SQL procedure successfully completed.

jce>


Everyone "knows" that program units aren't recompiled on the fly in production systems, but we all know that in reality it happens. Add in implicit recompilations due to different timestamps between calling and called program units and your globals are just a bunch of junk. Just one more reason to avoid them.

Mon Oct 09, 06:35:00 PM EDT  

Anonymous mlot said....


Jeff said....


Why you list the code twice over ?

Mon Oct 09, 10:43:00 PM EDT  

Blogger APC said....

>> Now, I'll obviously be passing g_file_hdl to utl_file a lot won't I...

yes, obviously. But that is a different case to your original example, where you passed the global variable as a parameter to a private procedure in the same package.

I don't disagree with your overall point: the behaviour is unsafe and globals should be used with caution, if at all.

Cheers, APC

Tue Oct 10, 04:55:00 AM EDT  

Anonymous John Flack said....

I use globals, but I have very strict rules about it.
1. Globals are never public. If they must be accessed, it will be through setters and getters.
2. Globals are only for data that must be retained between calls to the package.
For instance, I have validation routines that keep an error stack for listing all of the errors after all validations are done.
One of my most common uses for globals is to turn instrumentation on and off. With conditional compilation now available, many of my globals will go away.
3. Globals are written in one and only one place in the code - so Tom's example would not happen in my code.
4. Globals are never passed as parameters - if tempted to do so, I re-evaluate why this is a global, and consider making it local.

Tue Oct 10, 09:51:00 AM EDT  

Anonymous Francois said....

I can't see one good reason to pass a global package variable as argument to the same package procedure.

Wed Oct 11, 04:52:00 AM EDT  

Blogger Thomas Kyte said....

I can't see one good reason to pass a global package variable as argument to the same package procedure.

This was just an example, if you have the global in a package specification.... Even worse then having a global in a body - it could easily happen.

Wed Oct 11, 07:41:00 AM EDT  

Blogger APC said....

>> I can't see one good reason to pass a global package variable as
>> argument to the same package procedure.

I think global variables are like triggers and WHEN OTHERS THEN exceptions. Helpful in certain specific situations but open to misuse and abuse. It's a matter of knowing the correct usage, which is narrower than the permitted usage. Which is why we're supposed to have code reviews :)

Cheers, APC

Thu Oct 12, 07:04:00 AM EDT  

Anonymous Mia said....

When will there be an update to Expert One On One Oracle for 10g?

Mon Oct 16, 08:32:00 AM EDT  

Blogger Thomas Kyte said....

Expert Oracle Database Architecture is the first half of Expert One on One Oracle redone.

second half is still in my head, waiting to come out.

Mon Oct 16, 08:35:00 AM EDT  

Anonymous neil said....

I have a great interest in this thread-I am 'translating' a massive NATURAL batch job to PL/SQL. Some of these call stacks are very deep with subroutines calling subroutines calling subroutines many levels deep. These subroutines all have the same input and output. Some of these subroutines will modify a 'row' and then pass that modified row to other routines to actually 'commit' to the db. Not sure I'm using the right words in mainframe-speak.

I've been using a few ROWTYPE objects as IN OUT parameters. I'm wondering what the overhead is in passing all of these rows IN and OUT of my procs, versus keeping a ROWTYPE defined in the package body and just 'pointing' to that.

Now that I've read this thread, I'm still not sure what the harm is. Is this an 'acceptible use' of a package-level variable? Or should I just keep passing the same row objects further and further into my subroutines?

Hope this made sense, will gladly clarify!

Mon Oct 16, 11:13:00 AM EDT  

Blogger Thomas Kyte said....

Neil --

look into NOCOPY. Usually in out parameters use:

copy on the way IN into a temporary
procedure works on temporary
copy on the way OUT back into variable


If the procedure fails, the copy on the way out does not happen - UNLESS the NOCOPY HINT is used and we decide to pass a pointer, then any modifications the procedure makes are in fact visible in the record.

(as they would be with a global, but without using a global...)

sort of like passing a pointer to a C routine, instead of passing the thing being pointed to...

ops$tkyte%ORA9IR2> create or replace package my_pkg
2 as
3 procedure p1( p_rec in out all_objects%rowtype);
4 procedure p2( p_rec in out NOCOPY all_objects%rowtype );
5 end;
6 /

Package created.

Elapsed: 00:00:00.05
ops$tkyte%ORA9IR2> show err
No errors.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package body my_pkg
2 as
3 procedure p1( p_rec in out all_objects%rowtype )
4 is
5 begin
6 p_rec.object_name := 'X';
7 end;
8
9 procedure p2( p_rec in out NOCOPY all_objects%rowtype )
10 is
11 begin
12 p_rec.object_name := 'X';
13 end;
14 end;
15 /

Package body created.

Elapsed: 00:00:00.04
ops$tkyte%ORA9IR2> set timing on
ops$tkyte%ORA9IR2> declare
2 l_rec all_objects%rowtype;
3 begin
4 select * into l_rec from all_objects where rownum=1;
5 for i in 1 .. 300000
6 loop
7 my_pkg.p1( l_rec );
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.93
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
2 l_rec all_objects%rowtype;
3 begin
4 select * into l_rec from all_objects where rownum=1;
5 for i in 1 .. 300000
6 loop
7 my_pkg.p2( l_rec );
8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24

Mon Oct 16, 11:23:00 AM EDT  

Anonymous neil said....

Thanks for the super-quick followup. Often I'm passing in record five or six subroutines deep just to get at a few fields. I thought I could 'get lazy' (yeah, I know) by declaring the record once in the package body.

From what I read, NOCOPY may or may not always work is that true? Additionally, should I be concerned about memory overhead by sending the same few records into a bunch of sub routines?

Mon Oct 16, 11:39:00 AM EDT  

Blogger Thomas Kyte said....

as you can see, the overhead was "modest", so even if the hint isn't obeyed - you would have to have

a) HUGE records
b) millions of calls

before you would likely notice.


avoid globals, the temporaries will be fine.

memory is cheaper than our time spent debugging!

Mon Oct 16, 11:44:00 AM EDT  

Anonymous neil said....

Tom, one last variation-I now learn that these subroutines are not shared across other NATURAL programs. Is it 'cheating' if I create sub-procedures within my main procedure and have the sub-procedures modify/reference the records defined in the main proc? Do those count as 'globals'?

Mon Oct 16, 12:39:00 PM EDT  

Blogger Thomas Kyte said....

they would be globals, just use formal parameters - you'll never be sorry you did, you would only be sorry you didn't.

Mon Oct 16, 12:46:00 PM EDT  

Anonymous neil said....

It has been written. So says the man himself. Thanks for everything you provide to the community.

Mon Oct 16, 12:50:00 PM EDT  

Anonymous Anonymous said....

its nice

Mon Nov 27, 04:22:00 AM EST  

Blogger Andrey Zaytsev said....

Be careful, when you use GV as default values.

CREATE OR REPLACE PACKAGE my_pkg AS
g_variable NUMBER;
END;
/

begin
my_pkg.g_variable := 0;
end;
/

create or replace procedure my_proc(p_var IN NUMBER DEFAULT my_pkg.g_variable)
as
begin
dbms_output.put_line('in parameter before set p_var=' || p_var);
my_pkg.g_variable := 99;
dbms_output.put_line('in parameter after set p_var=' || p_var);
end;
/

EXEC my_proc();

...
in parameter before set p_var=0
in parameter after set p_var=99

Can I find something about this in documentation?

Tue Jul 17, 10:59:00 AM EDT  

Blogger Thomas Kyte said....

that is not any different than

p( x in number default function() )

it is 'obvious' - you defaulted to a variable, the variable is referenced as needed.

Tue Jul 17, 11:09:00 AM EDT  

Blogger Andrey Zaytsev said....

But I want to defaulted to variable value, so I have to use the functions:
p(x in number default number_value())
varchar2_value()
date_value()
...
user_defined_type_value()
...


It isn't very comfortable.

Tue Jul 17, 12:23:00 PM EDT  

Blogger Andrew said....

There is a legitimate purpose for passing a global/local variable to a procedure. How about procedure reuse so I can do the same functionality on like typed variables?

Ex:
This is just a test I was running but I use similar concepts within my packages.

(assume amatyp2 is a UDT and amatyp2_coll is a NT of amatyp2)


declare
2 ar1 amatyp2_coll := amatyp2_coll(amatyp2(1,2), amatyp2(1,5), amatyp2(4,2));
3 ar2 amatyp2_coll := amatyp2_coll();
4 -------------------------------------
5 procedure prn(p_arr in amatyp2_coll)
6 is
7 i integer := p_arr.FIRST;
8 begin
9 while (i is not null)
10 loop
11 dbms_output.put_line('idx=' || i || chr(9) ||
12 'fld1=' || p_arr(i).fld1 || chr(9) ||
13 'fld2=' || p_arr(i).fld2
14 );
15
16 i := p_arr.NEXT(i);
17 end loop;
18 end;
19 ---------------------------------------
20 procedure changeValue(p_arr in out nocopy amatyp2_coll)
21 is
22 begin
23 dbms_output.put_line(ulib.cpad('Start change', 40, '*'));
24 prn(p_arr);
25 for rec in (select v.idx
26 from (select rownum idx
27 , t.*
28 from table(p_arr) t
29 ) v
30 where v.fld1 = 1
31 )
32 loop
33 p_arr(rec.idx).fld2 := 42;
34 end loop;
35
36 dbms_output.put_line(rpad('-', 40,'-'));
37 prn(p_arr);
38 dbms_output.put_line(ulib.cpad('End Change', 40, '*'));
39 end;
40 -------------------------------------
41 begin
42 changeValue(ar1);
43
44 ar2.EXTEND(20);
45 ar2(3) := amatyp2(1,200);
46 ar2(8) := amatyp2(1,578);
47 ar2(19) := amatyp2(4,278);
48
49 dbms_output.put_line(chr(13));
50 changeValue(ar2);
51 end;
52 /

Thu Jan 10, 11:26:00 AM EST  

Blogger Thomas Kyte said....

Andrew,

I don't see why a global is necessary or desirable there.

Instead of a bunch of code, why not spell it out - tell us why you *need* a global and why a global is *superior* to formal parameters.

Thu Jan 10, 12:40:00 PM EST  

Blogger Andrew said....

Tom,
That particular case was just a test case I was building for another purpose but the concept is still the same.

Change that code to a stand alone stored procedure. Now if I want to do the same thing to multiple like typed variables (amatyp2_coll), why wouldn't I create another locally scoped procedure to generically perform that task and pass in the variables of the larger scope as formal parameters?

I guess I would turn it around and ask the question, what else would I do instead of the "prn" and "changeValue" procedures (which do accept formal parameters)?

Would I duplicate the code n times in the main block to print out n object instances?

Globals are not preferable to formal parameters, ever.
I did not intend to insinuate I needed them or advocate their use. But there were posts that claimed there is not one good reason to pass in a global variable to a procedure. My example, similar to the one you provided at the start of the blog, shows a very good reason to pass in a variable with a larger scope (relatively global if you will).
Code reuse. Even if it is only local to that one procedure.

Andrew

Thu Jan 10, 02:21:00 PM EST  

Blogger Thomas Kyte said....

Andrew,

I'm sorry but I don't follow you at all, you keep saying "like typed" - but I have no idea what that really means.

Give a concrete, really tiny example.

And tell us why a LOCAL variable would not work just as well or better than a GLOBAL would.


I have no idea why you use a global here, none.

I don't see why you would be duplicating any code, ever.

I'm not getting your point at all.

Thu Jan 10, 02:32:00 PM EST  

Blogger Andrew said....

Tom,
I hope the code is readable. Tried to make it tiny.
By "like typed" I am referring to variables of the same data type.

In the following example there are two package procedures (run1, run2) which do the same thing on package (body) globals.

My point is that run1 duplicates what I want to do on each of the variables (rec[1..6]) while run2 creates a local procedure to modularize the functionality.
It has formal parameters and in this case the values passed in are package globals.
I find run2 the better code.

Why do I need rec[1..6]?
Perhaps I want/need them to persist for the session.

I hope this helps explain the point I am trying to make.

Andrew

->create or replace package aptest
2 is
3 type rec is record(
4 fld1 integer
5 , fld2 varchar2(10)
6 );
7
8 procedure run1;
9 procedure run2;
10 end;
11 /

Package created.

Elapsed: 00:00:00.01
->show err
No errors.
->
->create or replace package body aptest
2 is
3 rec1 rec;
4 rec2 rec;
5 rec3 rec;
6 rec4 rec;
7 rec5 rec;
8 rec6 rec;
9
10 procedure run1
11 is
12 begin
13 rec1.fld1 := 42;
14 rec1.fld2 := 'dsdsds';
15 dbms_output.put_line('fld1=' || rec1.fld1 || chr(9) || 'fld2=' || rec1.fld2);
16
17 rec2.fld1 := 12;
18 rec2.fld2 := 'qwerty';
19 dbms_output.put_line('fld1=' || rec2.fld1 || chr(9) || 'fld2=' || rec2.fld2);
20
21 rec3.fld1 := 99;
22 rec3.fld2 := 'endeds';
23 dbms_output.put_line('fld1=' || rec3.fld1 || chr(9) || 'fld2=' || rec3.fld2);
24
25 rec4.fld1 := 11;
26 rec4.fld2 := 'reewrw';
27 dbms_output.put_line('fld1=' || rec4.fld1 || chr(9) || 'fld2=' || rec4.fld2);
28
29 rec5.fld1 := 67;
30 rec5.fld2 := 'wwwwww';
31 dbms_output.put_line('fld1=' || rec5.fld1 || chr(9) || 'fld2=' || rec5.fld2);
32
33 rec6.fld1 := 87;
34 rec6.fld2 := 'oooooo';
35 dbms_output.put_line('fld1=' || rec6.fld1 || chr(9) || 'fld2=' || rec6.fld2);
36
37 end;
38
39 procedure run2
40 is
41 procedure setValues(
42 p_rec in out nocopy aptest.rec
43 , val1 in integer
44 , val2 in varchar2
45 )
46 is
47 begin
48 p_rec.fld1 := val1;
49 p_rec.fld2 := val2;
50 dbms_output.put_line('fld1=' || p_rec.fld1 || chr(9) || 'fld2=' || p_rec.fld2);
51 end;
52
53 begin
54 setValues(rec1, 42, 'dsdsds');
55 setValues(rec2, 12, 'qwerty');
56 setValues(rec3, 99, 'endeds');
57 setValues(rec4, 11, 'reewrw');
58 setValues(rec5, 67, 'wwwwww');
59 setValues(rec6, 87, 'oooooo');
60 end;
61
62 end;
63 /

Package body created.

Elapsed: 00:00:00.18
->show err
No errors.
->
->
->exec aptest.run1
fld1=42 fld2=dsdsds
fld1=12 fld2=qwerty
fld1=99 fld2=endeds
fld1=11 fld2=reewrw
fld1=67 fld2=wwwwww
fld1=87 fld2=oooooo

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
->exec aptest.run2
fld1=42 fld2=dsdsds
fld1=12 fld2=qwerty
fld1=99 fld2=endeds
fld1=11 fld2=reewrw
fld1=67 fld2=wwwwww
fld1=87 fld2=oooooo

PL/SQL procedure successfully completed.

Thu Jan 10, 04:26:00 PM EST  

Blogger Thomas Kyte said....

Andrew,

sorry - not trying to be dense here, but I do not get why you NEED GLOBALS.

run2 is the right way to approach this (obviously)

but run2 *does not MANDATE THE USE OF GLOBALS*

Not in the least
Not at all


If you need the variables to persist for the life of a session, that is something ENTIRELY DIFFERENT - but it has nothing at all to do with the code you posted.


I see no reason to use globals in your example UNLESS rec1..6 need to persist across calls to the database

Which, in 2008, with mostly stateless web based applications using connection pools - it something that *rarely* happens.

Thu Jan 10, 04:32:00 PM EST  

Blogger Andrew said....

Tom,
I would never accuse you of being dense. We are just failing to communicate....

I think we are saying the same thing. I was mainly responding to comments like
"I can't see one good reason to pass a global package variable as argument to the same package procedure."

Given: there are globals in your package OR you even have local variables in a procedure

Observation: Modularity within that package or procedure can be obtained by passing the global/local values.

That is my only point. And it is ONE good reason to pass a global to a procedure. (if you have a global)


As far as globals,
I agree I don't NEED them, but there are many legacy cases and even new code that uses them. After all, Feurstein has advocated this approach in plsql for the past 15 years. And why not, it was a good approach to have package variables hidden in the package body which remained cached. No need to incur db hits. That is not necessarily true any more but old habits die hard.

Now with the web and stateless environments, this approach has problems. In our shop we have had some discussions but the attitude is "we'll cross that bridge when we get to it". Unfortunately without the proper planning I don't think there will be a bridge.

Andrew

Thu Jan 10, 06:05:00 PM EST  

Blogger Thomas Kyte said....

Ah, got it - the comment above...

yes, if you had a bunch of globals that were the same type and needed to perform the same operation on them, you would tend to

a) pass them
b) put them into an array in the first place and iterate over them :)

There are a couple of things Steve F. advocates that I don't necessarily agree with - like "don't do complex SQL" - that is just so wrong where I come from. My goal is to erase procedural code - his approach is somewhat the opposite. His use of globals might derive from the fact that he doesn't utilize the full power of SQL and needed to "cache" in order to "tune" the procedural code.

Don't get me wrong - he is a smart guy, we just don't agree on the approach to a database application :)

Not as much as I don't agree with most middle tier approaches though...

Thu Jan 10, 06:15:00 PM EST  

Blogger Andrew said....

I do not agree with all of Steve F. practices either. I write as much as possible in SQL first as well. I find it so powerful that I can tell the computer what I want and it will (usually) create the best program it can under the current conditions to get that data. No recoding of looping or caching data. Likely fewer context switches since the entire join can take place in the db instead of a manual NL join of cursor for-loops. How can you not like that?
It seems many procedural coders just can not or refuse to think in terms of sets. As if SQL is seen as a step to just get data in the program to be operated on instead of the SQL itself as a dynamic program to retrieve, match, filter and translate sets of data.
But I'm getting off topic.

As for the practice of caching using package globals, that practice is VERY much alive and in some cases I've seen, caching at the spec level drives the (poor) design of code.

Fri Jan 11, 09:41:00 AM EST  

POST A COMMENT

<< Home