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.
43 Comments:
> 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
...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
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" ?
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;
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.
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.
> ... "what I learned this week" sort of thread
Yes, a good idea in my opinion.
BR,
Martin
And, as Tom has as a thread on asktom (as I've just verified), globals as CONSTANTS are absolutely fine. No danger there.
>> 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
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.
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)
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.
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
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.
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.
Jeff said....
Why you list the code twice over ?
>> 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
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.
I can't see one good reason to pass a global package variable as argument to the same package procedure.
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.
>> 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
When will there be an update to Expert One On One Oracle for 10g?
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.
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!
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
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?
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!
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'?
they would be globals, just use formal parameters - you'll never be sorry you did, you would only be sorry you didn't.
It has been written. So says the man himself. Thanks for everything you provide to the community.
its nice
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?
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.
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.
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 /
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.
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
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.
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.
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.
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
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...
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.
POST A COMMENT
<< Home