1) a ref cursor is a cursor -- its a pointer to a cursor but a cursor none the less.

Their primary uses are:

o returning result sets from stored procedures to any client application
o performing dynamic SQL in plsql

Varrays and Nested Tables are close cousins of eachother. When used in PLSQL they behave much like an array would. A varray is bounded (has a max number of items) whereas a nested table is not (bounded more or less by RAM in PLSQL when used as an array).

When used in CREATE TABLE statements for permanently stored objects, they allow you to keep either an ordered array of data (varray's) or an un-ordered collection of data (nested tables). A varray is physically stored in a LOB as packed data whereas a nested table is stored in a real physical child database table.


2) NOCOPY says to pass that parameter by REFERENCE not by value. IN only parameters are ALWAYS passed by reference (a pointer to the parameter is passed, not the parameter value). This is because IN parameters cannot be written to, hence passing the reference is "safe".

Normally IN OUT and OUT parameters are passed using "copy" semanatics. An IN OUT parameter is passed by copying the IN value into a temporary -- the temporary is used in the procedure/function and then (upon SUCCESSFUL completion of the routine) its value is copied back to the original parameter that was passed (two copies). An OUT parameter is not copied on the way into the procedure -- however a temporary variable is still used in the routine and on the way out (up SUCCESS) the temporary variable's value is copied back into the parameter that was passed initially (one copy).

If you pass a varible with NOCOPY -- a pointer is passed. For an IN OUT parameter, this means that when you modify the parameter -- its value is IMMEDIATELY modified, we do not copy to a temporary variable. What this means is that any modification you make to it in the routine is made to the parameter right then -- not when the procedure succesfully completes. This means side effects from a failed procedure can be visible to the caller (and this is not normally so). If you have just a small routine such as:

procedure p( x in out NOCOPY number ) is
begin
x := 55;
raise program_error;
end;

Then you call it like this:

declare
y number := 0;
begin
p(y);
exception when others then
dbms_output.put_line(y);
end;

that will print 55 -- not 5 (it will print 5 if you do not use NOCOPY since the values are ONLY copied upon SUCCESS).

It can be even more tricky with OUT parameters. OUT parameters are ALWAYS modified -- even if the procedure you called never touches them. Simply change P to be:

procedure p( x out NOCOPY number ) is
begin
raise program_error;
end;

that doesn't modify x at all, now if you execute:

declare
y number := 0;
begin
p(y);
exception when others then
dbms_output.put_line('y='||y);
end;

that'll print "y=" -- y will be set to NULL.

So, you cannot just go in and put NOCOPY everywhere as this side effect can change the existing behaviour of your code. NOCOPY is ONLY useful on truly large objects like tables and collections. It doesn't make nearly as much sense to use NOCOPY on a simple NUMBER/DATE type or small strings as it does on these large variables (doesn't make sense on a LOB either as the lob locator itself is small).