Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from Oracle in a Nutshell

Keywords

mode

Specifies whether the parameter can be read from or written to, with the following acceptable values:

IN

Read-only variable. The value of the actual parameter can be referenced inside the program, but the parameter cannot be changed. This is the default.

OUT

Read/write variable.

IN OUT

Read/write variable.

If an exception is raised during execution of a procedure or function, assignments made to OUT or IN OUT parameters get rolled back, unless NOCOPY is specified.

NOCOPY

Compiler hint for parameters makes the parameter a call-by-reference instead of a call-by-value parameter. Normally, PL/SQL passes IN OUT parameters by value—a copy of the parameter is created for the subprogram. When parameter items get large (as collections and objects do), the copy can eat memory and slow down processing. NOCOPY directs PL/SQL to pass the parameter by reference, using a pointer to the single copy of the parameter. The disadvantage of NOCOPY is that when an exception is raised during execution of a program that has modified an OUT or IN OUT parameter, the changes to the actual parameters are not rolled back because the parameters were passed by reference instead of being copied.

datatype

Can be any PL/SQL or programmer-defined datatype but cannot be constrained by a size. The actual size of the parameter is determined from the calling program or via a %TYPE constraint, which is explained later in this chapter.

CREATE OR REPLACE PROCEDURE empid_to_name (in_id emp.emp_id%TYPE -- Compiles OK. ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page