Oracle Magazine PL/SQL Columns

Pub Date	Title	Description
September/October 2012	Bulk Processing with BULK COLLECT and FORALL	Part 9 in a series of articles on understanding and using PL/SQL
July/August 2012	Working with Collections	Part 8 in a series on understanding and using PL/SQL
May/June 2012	Working with Records	Part 7 in a series of articles on understanding and using PL/SQL
March/April 2012	Error Management	Part 6 in a series of articles on understanding and using PL/SQL
January/February 2012	Working with Dates in PL/SQL	Part 5 in a series of articles on understanding and using PL/SQL
November/December 2011	Working with Numbers in PL/SQL	Part 4 in a series of articles on understanding and using PL/SQL.
September/October 2011	Working with Strings	Part 3 in a series of articles on understanding and using PL/SQL.
July/August 2011	Controlling the Flow of Execution	Part 2 in a series of articles on understanding and using PL/SQL.
March/April 2011	Building with Blocks	Part 1 in a series of articles on understanding and using PL/SQL.
September/October 2010	Zoom In on Your Code	Use PL/Scope to analyze your PL/SQL.
May/June 2010	On Working in Parallel	Finish faster with DBMS_PARALLEL_EXECUTE.
January/February 2010	On Privacy and Function	Use Oracle Virtual Private Database and Function Result Cache—securely.
September/October 2009	On Looping, First, and Last	Choose the best approach to prevent a VALUE_ERROR exception.
July/August 2009	First Things First	Ask the right questions before starting construction of your new PL/SQL-based application.
May/June 2009	The Magic Kingdom	Choose the best way to manage literal values.
March/April 2009	On Avoiding Termination	Continue PL/SQL execution beyond exceptions.
January/February 2009	On Emulating FINALLY	Code PL/SQL to function like the Java FINALLY section.
November/December 2008	On Cursor FOR Loops	It’s important to know when not to use cursor FOR loops.
September/October 2008	On Signatures and Changing WHERE	Best practices for changing headers and handling different WHERE clauses.
July/August 2008	On Exceptions and Rules	Best practices for where, when, and how to handle exceptions
May/June 2008	On the PGA and Indexing Collections	Best practices for knowing your PGA impact and indexing collections
March/April 2008	On BULK COLLECT	Best practices for knowing your LIMIT and kicking %NOTFOUND.
November/December 2007	On Continuing and Executing	Best practices for PL/SQL in Oracle Database 11g and multilevel, string-indexed collections.
September/October 2007	On the PL/SQL Function Result Cache	Best practices—and preparation—for PL/SQL in Oracle Database 11g.
July/August 2007	On Invokers and Outs	Best practices for invoker rights and functions.
May/June 2007	On the Old, the New, and ORA-22160	Best practices for managing old and new information and preventing FORALL errors.
January/February 2007	The Right Place for PL/SQL	Best practices for PL/SQL location, location, location
November/December 2006	On Object Types in Collections	Best practices for retrieving objects and object attributes from objects
September/October 2006	On Generics and Traces	Best practices for string procedures and tracing
July/August 2006	On Conditional Compilation	Apply best practices to PL/SQL conditional compilation.
May/June 2006	On Confusion and Recompilation	Apply best practices to program naming, recompiling invalid programs, and collecting.
March/April 2006	On Variables, Moving, and Meaning	Apply best practices to cursor variables, bidirectional cursor access, and error messages.
January/February 2006	Answering PL/SQL	Apply best practices to defining arrays and stripping strings.
May/June 2005	Picking Your Packages	Know when—and when not—to package your PL/SQL.
March/April 2005	Tracing Lines	Find and report your errors—by line number—in Oracle Database 10g.
January/February 2005	Refactoring for PL/SQL Developers	Go beyond identifying best PL/SQL practices to create better code.
November/December 2004	Better to Best NDS	Best-practice tips for using Native Dynamic SQL in Oracle Database 10g.
September/October 2004	Controlling Mythological Code	Explode and decode the myths in your code.
July/August 2004	The Beauty Is in the Details	Pay attention to the details in conditions, loops, and declarations, and create better code.
May/June 2004	Self-Managing PL/SQL	Follow self-managing databases with self-managing PL/SQL .
March/April 2004	Cleaning Up PL/SQL Practices	Prioritize and apply PL/SQL best practices to polish applications both new and old
January/February 2004	Oracle 10g Adds More to FORALL	FORALL begins, BULK COLLECT builds, and VALUES OF excels.
November/December 2003	Disciplined PL/SQL	Four simple guidelines for improving the quantity and quality of PL/SQL code you write
September/October 2003	Taking Up Collections	Part 1 in a series that looks at enrichments to PL/SQL in Oracle Database 10g.
July/August 2003	Handling Exceptional Behavior, Part II	Handle PL/SQL exceptions with best practices.
May/June 2003	Managing Exceptional Behavior, Part 1	Handle PL/SQL exceptions with best practices.
January/February 2003	Strings Attached	Now you can index by strings and improve performance.

Procedure Overloading Restrictions

PL/SQL lets you overload subprogram names. That is, you can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family. 

 

 Restrictions

Only local or packaged subprograms can be overloaded. Therefore, you cannot overload standalone subprograms. Also, you cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:

PROCEDURE reconcile (acctno IN INTEGER) IS

BEGIN

...

END;

PROCEDURE reconcile (acctno OUT INTEGER) IS

BEGIN

...

END;

Furthermore, you cannot overload two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family. For instance, you cannot overload the following procedures because the datatypes INTEGER and REAL are in the same family:

PROCEDURE charge_back (amount INTEGER) IS

BEGIN

...

END;

PROCEDURE charge_back (amount REAL) IS

BEGIN

...

END;

Likewise, you cannot overload two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family. For example, you cannot overload the following procedures because the base types CHAR and LONG are in the same family:

DECLARE

SUBTYPE Delimiter IS CHAR;

SUBTYPE Text IS LONG;

...

PROCEDURE scan (x Delimiter) IS

BEGIN ... END;

PROCEDURE scan (x Text) IS

BEGIN ... END;

Finally, you cannot overload two functions that differ only in return type (the datatype of the result value) even if the types are in different families. For example, you cannot overload the following functions:

FUNCTION acct_ok (acct_id INTEGER) RETURN BOOLEAN IS

BEGIN ... END;

FUNCTION acct_ok (acct_id INTEGER) RETURN INTEGER IS

BEGIN ... END;

Procedures Parameter Modes

Parameter Modes