OracleBlog

What is NOCOPY?

'NOCOPY' is an optional 'hint' to tell the PL/SQL 'compiler' not to go through the overhead of making a copy of the variable, instead just send a reference. This is generally because we don't plan on modifying it within the procedure.

My first surprise was that you couldn't use "IN NOCOPY." Isn't NOCOPY your way of telling Oracle you don't plan on messing around with the parameter? Yes, but you CAN'T mess with IN parameters, try it!

CREATE OR REPLACE PROCEDURE MyProc (in_value IN number)
AS
BEGIN
in_value := 3;
END;

PLS-00363: expression 'IN_VALUE' cannot be used as an assignment target

Therefore, it is always safe to send IN parameters by reference, making NOCOPY redundant.

My second surprise was that you had to specify NOCOPY for an OUT parameter. Because by definition isn't an OUT parameter stating that you plan on modifying the variable? Why would it be an OUT variable if you weren't touching it? So why would you NOT want NOCOPY? The answer (like so many) comes from Ask Tom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2047154868085

Tom explains one situation where you want a copy rather than a reference for an OUT or IN OUT parameter. When you change a NOCOPY parameter, it changes right away, instead of upon successful completion of the stored procedure.

Imagine you modified the parameter, but threw an exception before successful completion. But that parameter has been changed and the calling procedure could be stuck with a bogus value.

Despite how much I trust Tom, everybody knows that I don't believe things until I see for myself. And neither should you! Besides, things change. Here's my example.

CREATE OR REPLACE PROCEDURE NoCopyProc (in_value IN OUT NOCOPY number)
AS
x number;
BEGIN
DBMS_OUTPUT.PUT_LINE(in_value || ' NoCopyProc');
in_value := 2;
x := 1/0;
END;

CREATE OR REPLACE PROCEDURE CopyProc (in_value IN OUT number)
AS
x number;
BEGIN
DBMS_OUTPUT.PUT_LINE(in_value || ' CopyProc');
in_value := 4;
x := 1/0;
END;

CREATE OR REPLACE PROCEDURE InterProc (in_value IN OUT NOCOPY number)
AS
BEGIN
IF (in_value = 1) THEN NoCopyProc(in_value);
ELSE CopyProc(in_value);
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;

CREATE OR REPLACE PROCEDURE MyProc
AS
the_value NUMBER(1);
BEGIN
the_value := 1;
InterProc(the_value);
DBMS_OUTPUT.PUT_LINE(the_value);

the_value := 3;
InterProc(the_value);
DBMS_OUTPUT.PUT_LINE(the_value);
END;

BEGIN MyProc; END;

1 NoCopyProc
2
3 CopyProc
3

For an excellent and more detailed overview of NOCOPY, complete with examples, restrictions and performance analysis, I once again refer you to Steven Feuerstein's writings. Although I encourage you to add his books to your collection, this chapter happens to be on-line for free:

Oracle PL/SQL Programming Guide to Oracle8i Features
http://www.unix.org.ua/orelly/oracle/guide8i/ch10_01.htm

So what is a guy to do?

Well, first of all, it was suggested to me that I should find a more gender-neutral way of summing up an article. Allow me to rephrase.

So what should we do?

1. Understand what NOCOPY means and its uses and restrictions (by following those links)
2. Take advantage of NOCOPY when you want the performance advantage of avoiding the cost of the temporary storage for OUT or IN OUT parameters.
3. Avoid NOCOPY when you don't want the side effects if the procedure fails early.

Remember, in the end, that NOCOPY is just a "hint" and Oracle will do whatever it wants anyway. Like all hints, you have to ask yourself what makes it necessary, and what makes you think Oracle is going to choose incorrectly.

What is ENUM?

ENUM is short for enumeration. Its a useful programming type that contains an ordered list of string values. The programmer can define the valid values depending on their application.

Some good examples of ENUMs would be days and months, or something like directions ('North', 'South', 'East', 'West').

Is there an Oracle 'ENUM' type?

No, not really. But there are other ways of accomplishing the same thing.

For tables, just set it to a string and add a constraint that it is within a certain set.

CREATE TABLE atable (
col1 varchar2(10),
CONSTRAINT cons_atable_col1 CHECK (col1 IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday',
'Friday', 'Saturday', 'Sunday'))
);

SQL> INSERT INTO atable (col1) VALUES ('Monday');

1 row created.

SQL> INSERT INTO atable (col1) VALUES ('Blingday');
insert into atable (col1) values ('Blingday')
*
ERROR at line 1:
ORA-02290: check constraint (ROBERT.CONS_ATABLE_COL1) violated

What happens if you use this type in a procedure? Will the constraint be checked? No.

CREATE OR REPLACE PROCEDURE MyProc (in_col IN atable.col1%TYPE)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(in_col);
END;

SET SERVEROUTPUT ON;

EXEC MyProc('Monday');
EXEC MyProc('Blingday');

So can you create a package subtype for this? That would be more elegant anyway.

But according to Oracle PL/SQL Programming by Steven Feuerstein Chapter 4, I don't think you can (check comments for any refutations to this).

http://www.amazon.com/exec/obidos/ASIN/0596003811/
qid=1117039808/sr=2-1/ref=pd_bbs_b_2_1/102-9543590-3979349

I think the best thing to do in this case is to create a procedure to validate your input.

CREATE OR REPLACE PROCEDURE MyCheck (in_col IN atable.col1%TYPE)
AS
BEGIN
IF (in_col NOT IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')) THEN
-- Throw Exception here, be sure to catch it in MyProc!!
NULL;
END IF;
END;

This approach is consistent with Steven Feuerstein's approach to programming. He suggests separating these things into separate procedures. Then when a future release of Oracle supports a concept, or when you figure out how to do it, you can make the change in a single place.

So what is a guy to do?
1. If you want to use enum in a table, use a check constraint.
2. If you want to use enum in a stored procedure, write a separate procedure to validate the input.

Let me get your advice on this one.

Here's the situation, you need an evenly distributed sequence of random integers from 1 to 20. You decided to use the Oracle random number package 'dbmsrand'.

Incidentally, I decided to do it this way after searching Ask Tom.

http://asktom.oracle.com/~tkyte/Misc/Random.html
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:831827028200

There is a link to his web site to the right. Bookmark it.

While you're at it, bookmark Dan Morgan's page (link on the right).

http://www.psoug.org/reference/dbms_random.html

Ok, back to the story.

After you read the instructions on using it, you probably write something like this in your PL/SQL stored procedure:


AS
random_value number(2,0);
BEGIN
random_value := dbms_random.value(0,20);

Now you notice whoops, you're getting some 0s. You don't want 0. Plus you're not getting very many 20s. So you do this instead

random_value := dbms_random.value(1,21);

Much better! But whoops - you're getting the occasional 21! So you scratch your brain and do this:

random_value := dbms_random.value(1,21);
random_value := MOD(random_value, 20) + 1;

That should pour the 21s into the 1 pile, which you noticed is very, very slightly lower than the others.

Now you think you're getting the values you want. So here are my questions:

1. Will this truly generate an even sample of numbers from 1 to 20? Will there be the right number of 1s and 20s?
2. Is there a better way?
3. Say you're generating a very large sequence, thinking about performance, do you see any problems with this approach I should consider?

Here is the complete solution:

--------------------
@utlraw
@prvtrawb.plb
@dbmsoctk
@prvtoctk.plb
@dbmsrand

create table value_holder as (f_value NUMBER(2,0));

create or replace procedure ValuePlacer (number_to_generate IN number)
AS
random_value number(2,0);
BEGIN
for x in 1..number_to_generate
LOOP
random_value := dbms_random.value(1,21);
random_value := MOD(random_value, 20) + 1;
insert into value_holder values (random_value);
END LOOP;
END;

exec ValuePlacer(1000);

select f_value, count (f_value) from value_holder group by f_value;
-----------------

Thanks!

// posted by Robert Vollman @ Tuesday, May 24, 2005 2 comments

You can't set the same foreign key for two different columns with the same constraint:

create table atable (
id varchar2(65) primary key);

create table btable (
a_id1 varchar2(65),
a_id2 varchar2(65));

ALTER TABLE btable
ADD CONSTRAINT btable_fkey_both
FOREIGN KEY (a_id1, a_id2)
REFERENCES atable(id, id)
ON DELETE SET NULL;

ERROR at line 1:
ORA-00957: duplicate column name

ALTER TABLE btable
ADD CONSTRAINT btable_fkey_both
FOREIGN KEY (a_id1, a_id2)
REFERENCES atable(id)
ON DELETE SET NULL;

ERROR at line 1:
ORA-02256: number of referencing columns must match referenced columns

But you can do it if you break it up into separate constraints:

ALTER TABLE btable
ADD CONSTRAINT btable_fkey1
FOREIGN KEY (a_id1)
REFERENCES atable (id)
ON DELETE SET NULL;

Table altered.

ALTER TABLE btable
ADD CONSTRAINT btable_fkey2
FOREIGN KEY (a_id2)
REFERENCES atable (id)
ON DELETE SET NULL;

Table altered.

Note: Monday is a holiday in Canada, no update until Tuesday.

I thought I would declare my variable-lengthed variables with a pre-defined constant. Why?

1. I'd be sure they matched up everywhere.
2. I could change them all in a single place if required.

But I don't think you can do that in PL/SQL.

CREATE OR REPLACE PACKAGE test_constants IS
MAX_LEN CONSTANT NUMBER := 32;
END test_constants;

Package created.

SET SERVEROUTPUT ON

-- Anonymous block
DECLARE
x VARCHAR2(test_constants.MAX_LEN); -- Can't do this
BEGIN
x := 'Test ' || test_constants.MAX_LEN;
dbms_output.put_line(x);
END;

ERROR at line 2:
ORA-06550: line 2, column 31:
PLS-00491: numeric literal required

But what about other types?

Substitute NVARCHAR2, VARCHAR, CHAR, NCHAR, or STRING if you like, same result. Incidentally the "N" just means you are specifying the length in bytes instead of characters.

So what is a guy to do?

1. Just put a comment before every number, and make it a practise to make sure they all match.

DECLARE
x VARCHAR2(32); -- test_constants.MAX_LEN = 32

But here is another option.

2. Create a table with the types you want, and then use those types.

CREATE TABLE TEST_CONSTANTS (
MAX_LEN VARCHAR2(32)
);

Table created.

DECLARE
x TEST_CONSTANTS.MAX_LEN%TYPE;
BEGIN
x := 'Test';
dbms_output.put_line(x);
END;

Refactoring for PL/SQL Developers
By Steven Feuerstein

From Oracle Magazine January/February 2005

http://www.oracle.com/technology/oramag/oracle/05-jan/o15plsql.html

Steven Feuerstein is one of my favourite PL/SQL authors, especially when he lays off the socio-political tangents.

I especially enjoyed his article on refactoring PL/SQL code. I had a few thoughts to add to it.

My additional thoughts on Refactoring:

1. One of the advantages that also bears note is that re-factoring your code can encourage code re-use. Clean, modular, well-written code is easy to re-use, reducing future programming efforts.

2. However, one of the disadvantages of code re-factoring is that you can introduce unknown factors to a (presumably) working, trusted procedure. There is an expense to re-testing and re-validating the modified code. Even if you FIXED errors, some other procedures might have been written in such a way that they depend on them, and they will now fail.

My additional imperfections in the unfactored code sample:

1. Inline documentation

I think Steven failed to point out the most egregious transgression: lack of inline documentation! I believe almost every procedure needs it, at LEAST a 'header' but preferrably also some comments that explain in plain language what was intended by the code. Steven did go ahead and add some in-line documentation, but didn't draw any attention to that.

* Note: Perhaps due to his friendly nature, I am referring to him in this post in the familiar. Certainly no disrespect is intended.

2. Bad variable names

If this article was to be re-written, I would change the initial code sample to use very bad variable names and then re-name them as part of the "refactored" version. I think that's a very common problem.

My comments on the refactored version:

1. utl_file_constants

Rather than define my own file utl_file_constants, I would use something that was already defined and possibly tied in. I mean, there's a REASON that 32767 was used. You are bound by a particular (probably OS-level, or DB-level) limitation that is probably defined in some package or some configuration value, or right in UTL_FILE.

If you define your own and then all of a sudden the 32767 limitation is changed at the OS or DB level, you STILL have to go change all your private constant packages. I mean that's a bit better than having to change magic numbers in your code, but we can do a little better, I think.

get_next_line_from_file isn't even aware of that limitation. How would this program fail if you set the value to, say, '4' instead of '32767' in utl_file_constants?

It appears like Steven didn't even use his utl_file_constant for 32767 in the final version (Code Listing 7). Why not? The answer is probably that you can not dynamically define the length of a string. That is, of course, a PL/SQL restriction. I suppose we should be happy with whatever magic numbers we can remove.

2. compare_latest_read

I'm really not sure I would have created "compare_latest_read". That seems like we are overdoing it. I mean this is not a big procedure and its entire point is to do what is in that procedure anyway. Why introduce the overhead of another procedure (assuming there is any overhead)?

Think of it this way, you are passing in the lines. Instead of that, you could pass in the files and compare_latest_read can call get_next_line_from_file to get the lines. That would be reasonable. But now, all of a sudden, the main procedure is doing nothing but initialising and calling a procedure. So basically "compare_latest_read" is one reasonable change away from already doing all the work.

So why is it even necessary? It is only done once ... How is the purpose of this internal procedure significantly different from the purpose of the overall procedure? I suppose this is just an opinion, and a matter of taste, but I really think the extra procedure is unnecessary.

Other comments and questions:

1. VARCHAR2 length

In the procedure Steven uses VARCHAR2 as a variable-lengthed string even though we know the maximum size (32767). Is there any value in specifying VARCHAR2 (32767) instead of VARCHAR2 in the final version (as the IN or OUT parameters)? Is there any advantage to that? Is it even possible?

2. Procedures vs Functions

Any reason why Steven uses procedures instead of functions?

3. Cleanup: closing an unopened file

In "cleanup", will there be a problem if you UTL_FILE.fclose a file that hasn't been opened? Because that will happen if there is an exception while opening the first file when cleanup closes both.

4. Handling "WHEN OTHERS"

WHEN OTHERS - cleanup .... without any error message written to screen or log?? For shame! Steven said earlier in the article you would re-raise the exception, but in the end he didn't.

Plus the problem with re-raising an exception is you are actually getting a NEW exception. If it is checked later, the error messages will say the error was created at this new line number instead of the original spot. Might as well fully handle the exception right here when we catch it.

Conclusion:

A sure sign of a good article is that it gets you thinking. Steven Feuerstein rarely fails to achieve that. I have shared these thoughts with him, and I'll follow-up with any answers or clarification he provides.

I noticed Harm Vershuren had some thoughts in his blog as well:
http://technology.amis.nl/blog/index.php?p=317

In Oracle, there is a difference between 'null' and nothing at all. Here is my story.

I discovered this when playing with default values. First, I created a table that had a default value for one column. Then I tried to insert a row that had nothing at all, and it wouldn't use the default value. So I tried inserting a row with null, and it didn't assign the default value. Observe.

SQL> create table atable (
2 last_name varchar2(32) not null,
3 first_name varchar2(32) not null,
4 rating smallint default 0);

Table created.

SQL> insert into atable values ('Smith', 'John', null);

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John

Notice it inserted NULL, and not the default value.

SQL> insert into atable values ('Smith', 'John');
insert into atable values ('Smith', 'John')
*
ERROR at line 1:
ORA-00947: not enough values

Why doesn't this work? You may know this already, but in the words of Dan Morgan: "By not specifying column names you are signifying that you are providing values for ALL columns. This is why it is a very bad practice as doing an ALTER TABLE ADD immediately invalidates all SQL statements."

So let's do it the proper way and see if there is a difference.

SQL> insert into atable (last_name, first_name) values ('Smith', 'John');

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John
Smith John 0

And there is a difference. Excellent. Out of curiousity between the two ways of inserting rows, I tried this:

SQL> insert into atable (last_name, first_name, rating) values ('Smith', 'John', null);

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John
Smith John 0
Smith John

So I was thinking, why isn't this null being replaced with the default value? Isn't the default value there for instances to replace null? If you're as skeptical as I am, it will make sense after this second test, where I add the "NOT NULL" constraint.

drop table atable;

create table atable (
last_name varchar2(32) not null,
first_name varchar2(32) not null,
rating smallint default 0 NOT NULL);

SQL> insert into atable (last_name, first_name, rating) values ('Smith', 'John', null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ROBERT"."ATABLE"."RATING")

That's when I figured out where I was confused. There are 3 things you can assign to a column when doing an insert:
1. a value (eg: 0)
2. null
3. nothing at all

See, there is a difference between #2 (null) and #3 (nothing at all). To wit:
1. Using null (#2) is 'ok' if you are inserting without specifying columns. Nothing at all (#3) is not.
2. Null (#2) will not be replaced by a default value when inserting, whereas nothing at all (#3) will.

When I started looking at stored procedures, I found even more difference between NULL and nothing. My second story starts off as an investigation of passing NULL to stored procedures.

There is no such thing as "NOT NULL" for stored procedure parameters.

I noticed that the "NOT NULL" keyword can't be used in procedure parameters (nor can NULL). Apparently that is for creating tables only. You can not force input parameters to a procedure to be non-null. All you can do is start your procedure by verifying the input parameters.

SQL> create or replace procedure MyProc1 (some_value IN number NOT NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6 /

Warning: Procedure created with compilation errors.

Nor can you allow it to be NULL.

SQL> create or replace procedure MyProc2 (some_value IN number NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6 /

Warning: Procedure created with compilation errors.

You can, however, assign default values. However, note the difference between assigning "NULL" and assigning nothing at all.

SQL> create or replace procedure MyProc3 (some_value IN number := 0)
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(some_value);
5 NULL;
6 END;
7 /

Procedure created.

SQL> exec MyProc3();
0

PL/SQL procedure successfully completed.

SQL> exec MyProc3(NULL);

PL/SQL procedure successfully completed.

SQL> exec MyProc3(1);
1

PL/SQL procedure successfully completed.

Bottom line, there is a difference NULL and nothing at all for both tables and stored procedures. So what is a guy to do?
1. Understand there is a difference between NULL and nothing
2. For tables, use "NOT NULL" and for stored procedures, verify the input manually
3. Use default values for both tables and stored procedures when passing nothing at all.

There are certain "camps" in the worldwide Oracle community. For example, there is the "Oak Table Network" of "Oracle scientists" who seek thorough understandings of issues backed up by details, tests and proofs. Contrasting is the "Silver Bullet" family of field-tested generals who prefer rules of thumb and quick fixes even it means some false understandings and occasionally being wrong. Cary Millsap (of the Oak Table Network) stands as someone respected by both sides.

Cary Millsap worked at Oracle for 10 years on system performance before co-founding Hotsos in 1999 (http://www.hotsos.com - register for free). He is one of the most trusted sources on matters of Oracle system performance, and "Optimizing Oracle Performance" is considered his finest work (4.5 out of 5 stars on Amazon). The best way to learn more about him is to see for yourself. Here are some of his most popular articles:

"Diagnosing Performance Problems" from Oracle Magazine. A brief summary of what is covered in this book:http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html

"Introduction", the first chapter from "Optimizing Oracle Performance."Chapter 1: http://www.oreilly.com/catalog/optoraclep/chapter/ch01.pdf

"Case Study", the 12th chapter from "Optimizing Oracle Performance."Chapter 12 (Case Study): http://www.oreillynet.com/pub/a/network/excerpt/optimizing_oracle_chap12/index.html

"Performance Management: Myths and Facts." One of his most popular articles.https://secure.hotsos.com/downloads/visitor/00000024.pdf

"Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok." Another of his more popular articles.http://www.oradream.com/pdf/Why%20a%2099%20Cahe%20Hit%20Ratio%20is%20Not%20OK.pdf

While everyone will have their own favourite parts of the book, I think most readers would agree that getting a good taste of the author's performance tuning philosophy is one of the highlights. "Method R", not to be confused with "System R" (ie. SQL), is not about looking at STATSPACK, cache hit ratios, or V$ tables and guessing. The author wanted to devise a system to identify and resolve the top performance concerns of an organisation with reliable, predictable results. The first few chapters put this method in writing in perhaps the best way since the introduction of "YAPP" (Anjo Kolk).

"The performance enhancement possible with a given improvement is limited by the fraction of the execution time that the improved feature is used." - Amdahl's Law

After several years of research, the author discovered that Extended SQL Trace Data was at the centre of "Method R". Some of the articles should give you a good taste of what Extended SQL Trace data is, if you didn't know already. By the time you finish reading this book you will know exactly how to collect and interpret all the little "ela=17101 p1=10 p2=2213 p3=1 ..." within into something meaningful. For some, that justifies the price tag right there.

So in essence I would have re-named this book "Method R: Optimizing Oracle Performance Using Extended SQL Trace Data," because that is basically what this book is about. There are some reasonably "stand-alone" chapters on other topics, for instance on the Oracle Fixed View tables (Chapter 8) and on Queueing Theory (Chapter 9), but that is not the primary focus of the book.

Those that are expecting a more broad treatment of the subject of performance tuning may be justifiably disappointed that it basically covers only this narrow aspect. However, it is covered very well, and it isn't really covered anywhere else. The author makes no apologies for this, claiming that extended SQL trace data is the only resource you will ever need for diagnosing and solving performance problems.

"You cannot extrapolate detail from an aggregate." - Cary Millsap's preference of SQL extended trace data over fixed views (system-wide average performance characteristics)

Indeed, some people might contend that the author spends a little too much time stating his beliefs, defending them, and patting himself on the back. But I think it adds a certain flavour to the book, and I respect an author who backs up his statements.

"Proving that V$ data are superior to extended SQL trace data because of the 'missing time' issue is analagous to proving that its safer to be in a room with a hungry bear if you'll just close your eyes." - Cary Millsap

The book can be a tough read in the sense that the author goes very deep into the material, and generally each subject is treated thoroughly. Chapter 9 on Queueing Theory can be a particularly overwhelming chapter. But the material is served in bite-size pieces, and broken up with tips, tricks, stories, diagrams and code (sometimes 3+ pages worth at a time, embedded directly in the middle of a chapter). There are even worthwhile exercises at the end of each chapter.

In the end, I enjoyed this book and I'm glad I got it. I don't consider it a "must have" for your Oracle collection, but I definitely feel it is quite worthwhile. I recommend it especially to those who read his articles and were very comfortable with his writing style and philosophy, and also to those that need a book on extended SQL trace data (because this is basically the only one). But even those in the "Silver Bullet" camp will be glad to add another tool to their belt.

Thumbs up.

http://www.coug.ab.ca/Resources/BookReviews/MillsapsOOPByRVollman.htm