A Good Reference for Oracle Pl/Sql

A good reference for Oracle PL/SQL

As Klaus says the online documentation is pretty good. Start with the 2-Day Application Developer's Guide. If you're using 11gR2, you'll want to read the most recent version of the PL/SQL Language Reference. Newer Oracle releases have updated versions of the documents.

But if you want to buy a book, then Steven Feuerstein's Oracle PL/SQL Programming is the one.

A good reference for Oracle PL/SQL

Oracle's own website has extensive documentation, both online and PDF. See here for 11g's online SQL reference.

Need to understand everything in pl/sql

This is Oracle 10g documentation homepage. Yes, I know, you said you're on 11g, but never mind that. Page I suggested is nicely divided into several sections - have a look at Most Popular.

• as you're new to Oracle, read Concepts first
• as you know a little bit of SQL, read SQL Reference next; you must know SQL before you move on to
• PL/SQL User's Guide and Reference book, which will teach you what you asked for
• don't miss Application Developer's Guide - Fundamentals as the last book I'm going to suggest in this answer

Feel free to find similar books related to your Oracle database version; there will be some differences, but - as fundamentals - previously mentioned literature will be just fine.

Info Needed: What are some of the best PL/SQL Blogs to follow?

Every PL/SQL developer should read Oracle PL/SQL Programming by Steven Feuerstein as an excellent introduction to the structure of the language and how it differs from most languages by its database-specific nature.

In addition to a good working knowledge of the Oracle Database, some key concepts to understand in PL/SQL include (but are not limited to):

• The execution environment, being "inside" the database
• The BEGIN/END block structure
• Packages, procedures, functions, triggers, as well as anonymous blocks
• Cursors & Iterative row processing vs Bulk row processing
• Exception handling
• Dynamic SQL & Execute Immediate
• Collections & Records
• Database Transaction Management

For blogs, take a look at Steven Feuerstein's PL/SQL Blog.

http://toadworld.com/Community/ExpertsBlog/tabid/67/BlogID/13/Default.aspx

He's got lots more resources on his site as well. Take a look at the PL/SQL challenge!

http://www.stevenfeuerstein.com

What is pass by reference in oracle?

To better understand, let's review how PL/SQL handles parameters in two ways:

By Reference
In this case, a pointer to the actual parameter is passed to the corresponding formal parameter. Both actual and formal parameters point to the same location in memory that holds the value of the parameter.

By Value
In this case, the value of the actual parameter is copied to the corresponding formal parameter. If the program then terminates without an exception, the formal parameter value is copied back to the actual parameter. If an error occurs, the changed values are not copied back to the actual parameter.

By default OUT and IN OUT parameters are passed by value and IN parameters are passed by reference. When an OUT or IN OUT parameter is modified inside the procedure the procedure actually only modifies a copy of the parameter value. Only when the procedure has finished without exception is the result value copied back to the formal parameter.

procedure add_numbers(
first_val in number,
second_val in out number
)
BEGIN
second_val := first_val + second_val;
--some other statements

END;
/

TEST:

BEGIN
par_in = 124;
par_inout = 76;
add_numbers(par_in, par_inout);
DBMS_OUTPUT.PUT_LINE(par_inout);
END;
/

The example above, will print 200 on the screen. There are two parameters passed to add_numbers procedure:

1. The first one is par_in which is of the mode IN. This parameter is passed by reference. Hence, in add_numbers procedure, no other copy of this parameter will be created. Rather, the same copy will be used. If you are allowed to modify the value first_val inside add_numbers procedure (but you are not), the value will reflect directly to par_in. Because simply both first_val and par_in are two names for the same memory location.
2. The second one is par_inout which is of the mode IN OUT. This parameter is passed by value by default. Hence, in add_numbers procedure, new (memory location) will be allocated to store the another copy of this parameter. Hence, par_inout is a name to different location in the memory than second_val. They two names to two different locations.

The implication of the second parameter is that, after the line second_val := first_val + second_val; is executed, the value of second_val is different to the value of par_inout until the end of the procedure. Only at the end of the procedure, the value of second_val will be passed to par_inout.

Now, if you pass a large collection as an OUT or an IN OUT parameter then it will be passed by value, in other words the entire collection will be copied to the formal parameter when entering the procedure and back again when exiting the procedure. If the collection is large this can lead to unnecessary CPU and memory consumption.

The NOCOPY Parameter Mode Hint alleviates this problem because you can use it to instruct the runtime engine to try to pass OUT or IN OUT parameters by reference instead of by value

The hint requests PL/SQL runtime engine to pass an IN OUT argument by reference rather than by value. This can speed up the performance of your programs, because by-reference arguments are not copied within the program unit. When you pass large, complex structures like collections, records, or objects, this copy step can be expensive.

---

Related Topics