OracleBlog

What is a "package"?

According to the PL/SQL User's Guide and Reference, "A package is a schema object that groups logically related PL/SQL types, items and subprograms."

But I believe a package is far more than just a way of logically grouping objects together.

Before I digress, let's very briefly understand what a package is. It's probably easiest if you take a look at Oracle's documentation, which has a good description of packages and some examples:

PL/SQL User's Guide and Reference, Chapter 9: PL/SQL Packages:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624.pdf

Put simply, packages have two parts, the specification and the body.

The specification is the interface into the package. It describes all the types, variables, procedures (etc) within the package. Ideally the specification does not change.

The body has all the implementation. Inside the body you would actually write the procedures, assign the values, and have all the details and logic behind the package. Often (unless you are the developer), you can treat the body as a "black box."

As always, I'll refer you to Dan Morgan to understand the syntax of Oracle PL/SQL Packages:
http://www.psoug.org/reference/packages.html

Back to the story.

According to one of my favourite PL/SQL Authors Connor McDonald, "The main reason packages are not more widely adopted is that users are unaware of the benefits they offer."

Beyond the known, obvious advantage of grouping together related items, which is great for organising your code and "modularity", what are these benefits of which we are speaking?

1. Objects don't get invalidated when you makes changes to the body. That saves a lot of recompilation and makes changing the implementation much more painless. You will still have to recompile if you change the specification, but that's not something you should be doing very often.

2. You can "overload" subprograms (procedures/functions). You can have several subprograms with the same name, but with a different number of parameters, or different types. That is another thing that makes implementation changes more painless because you can keep legacy code if you like. You can also see the extra flexibility that offers developers.

3. You can have persistent variables throughout a session without storing anything in a database table. Packages can have variables and constants that are initialised when the packages is first used within a session, and then they are available for the remainder of the session for all future references to anything within that package. That comes in very handy.

4. Speaking of initialisation, being able to call a procedure automatically the first time a package is used within a session can also come in very handy.

5. You can take advantage of "encapsulation." In essence, you can hide the implementation details from users but still give them all the information they need to use the package. Since they aren't aware of the details, that means you can change them with minimal impact or risk. Packages also support private subprograms and variables which are available only to other subprograms within the package, and remain completely hidden and inaccessible to anything outside the package.

6. You may notice some performance improvement when using packages. When you first use a package, the entire package may be loaded into memory, meaning fewer disk I/Os as you use the related items within.

I'm sure there are other advantages, but those are the ones I've noticed.

Let me close with links to my favourite two articles about Packages, from two of my favourite PL/SQL experts:
Connor McDonald's Chapter 2 from Mastering Oracle PL/SQL: Practical Solutions
http://www.oracle.com/technology/books/pdfs/sample2174ch2.pdf

Steven Feuerstein's article "Picking Your Packages" from May/June 2005 Oracle Magazine
http://www.oracle.com/technology/oramag/oracle/05-may/o35plsql.html

---
Addition:
By the way, to use packages, just prefix the package name using dot notation

Check out PL/SQL Supplied Packages and Types Reference
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612.pdf

It includes UTL_HTTP and DBMS_OUTPUT
http://thinkoracle.blogspot.com/2005/08/utlhttp.html
http://thinkoracle.blogspot.com/2005/10/dbmsoutputputline.html

But also check out UTL_FILE, DBMS_PIPE, DBMS_ALERT and the DBMS_XML stuff.

DBMS_OUTPUT.PUT_LINE allows you to write information to a buffer throughout the execution of a trigger/procedure. That information is available to be read by a trigger/procedure (using GET_LINE(S)), or dumped to SQL*Plus upon completion of execution.

One of the most common misconceptions is that PUT_LINE writes data immediately to SQL*Plus. That is not true. PUT_LINE only puts it in the buffer. You will not see it before the block has executed. I can prove that with this example (note: you must load the user_lock package for this):

scott@Robert> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Going to sleep for 10 seconds...');
3 USER_LOCK.SLEEP(1000);
4 DBMS_OUTPUT.PUT_LINE('Woke up after 10 seconds.');
5 END;
6 /
Going to sleep for 10 seconds...
Woke up after 10 seconds.

You will have seen both messages come out after 10 seconds as opposed to one before and one after.

Despite the fact that it doesn't write messages throughout its progress, PUT_LINE can still make a useful debugging tool. I like the way that the messages can be kept but easily disabled by using DBMS_OUTPUT.DISABLE. Any PUT_LINE messages are silently ignored if you have DISABLEd DBMS_OUTPUT (or failed to ENABLE).

To see the messages, you need to call DBMS_OUTPUT.ENABLE. The only parameter is buffer_size, which, if NULL, will default to 20000. The buffer size can be anywhere from 2000 to 1000000.

scott@Robert> BEGIN
2 DBMS_OUTPUT.DISABLE;
3 DBMS_OUTPUT.PUT_LINE('Disabled');
4 DBMS_OUTPUT.ENABLE;
5 DBMS_OUTPUT.PUT_LINE('Enabled');
6 END;
7 /
Enabled

PL/SQL procedure successfully completed.

Incidentally, SQL*Plus's SET SERVEROUTPUT ON will call DBMS_OUTPUT.ENABLE. You can even use SIZE with that command. SET SERVEROUTPUT also includes formatting options, such as FORMAT WRAPPED, WORD_WRAPPED and TRUNCATE (along with a SET LINESIZE) to get the output the way you want it. [EDIT: Fixed Typo]

There are two common errors related to DBMS_OUTPUT.PUT_LINE. The first one is trying to put more than 255 characters per line.

scott@Robert> DECLARE
2 l_string VARCHAR2(300);
3 BEGIN
4 l_string := '1234567890';
5 l_string := l_string || l_string || l_string || l_string || l_string;
6 l_string := l_string || l_string || l_string || l_string || l_string || l_string;
7 DBMS_OUTPUT.PUT_LINE(l_string);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 7

The solution here is to use DBMS_OUTPUT.NEW_LINE to split it up into lines. 255 is a hard limit, if you really want to print a line with more than that, you can write your own package that does the same thing as DBMS_OUTPUT. That is actually a very common thing to do. Tom Kyte's has a handy one in Appendix A of "Expert One-on-One Oracle."

The second common error is overfilling your buffer.

scott@Robert> BEGIN
2 DBMS_OUTPUT.ENABLE(2000);
3 FOR i IN 1..1000 LOOP
4 DBMS_OUTPUT.PUT_LINE('This is line ' || i);
5 END LOOP;
6 END;
7 /
This is line 1

This is line 105
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 4

The solution here is increase the size of your buffer, using ENABLE. The maximum size is 1000000 and that is a hard limit. Once again, you can write your own package as a workaround.

This example also illustrated that even if you have an exception, the contents of the buffer until that point is still available.

The alternative to writing your own package is to write your messages to a table. Then you can query the table at any time to see your debug messages. DBMS_PIPE is another option to consider.

I will close with two more interesting "gotchas" for DBMS_OUTPUT.PUT_LINE.

scott@Robert> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('     What happened to my leading spaces?');
  3  END;
  4  /
What happened to my leading spaces?

This is an SQL*Plus Gotcha. Just be sure to use FORMAT WRAPPED, like so:

scott@Robert> SET SERVEROUTPUT ON FORMAT WRAPPED
scott@Robert> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('     There they are!');
  3  END;
  4  /
     There they are!

Here is the second gotcha.

scott@Robert> DECLARE
2 l_bool BOOLEAN;
3 BEGIN
4 l_bool := TRUE;
5 DBMS_OUTPUT.PUT_LINE(l_bool);
6 END;
7 /
DBMS_OUTPUT.PUT_LINE(l_bool);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

DBMS_OUTPUT.PUT_LINE is not overloaded for Booleans. The solution is to either to write your own package (as mentioned above), or convert from Boolean type to something else for the PUT_LINE call.

For more information, this package is described in (among other places) Oracle's Supplied PL/SQL Packages Reference Guide, Chapter 43:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612.pdf

And here is Dan Morgan's Quick Reference:
http://www.psoug.org/reference/dbms_output.html

// posted by Robert Vollman @ Wednesday, October 12, 2005 11 comments

Questions:
How do I limit the number of rows returned by a query?
How do I write a query to get the Top-N salaries from the employee table?
How can I add unique, sequential numbers to an existing table?
How can I differentiate between two completely identical rows?
How can I find a faster way to retrieve a queried row?
How can I find the last row processed in a big batch?

There is one thing all these questions have in common: the answer involves either ROWNUM or ROWID.

So what is ROWNUM and ROWID?

First of all, both are covered in the SQL Reference, Basic Elements of Oracle SQL, Chapter 2:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf

They are also both referred to as pseudo-columns. That is, they are not "real" columns that will show up when you DESC a table. They don't actually exist anywhere in the database. But they're available for you to use.

In fact, ROWNUM only exists for a row once it is retrieved from a query. It represents the sequential order in which Oracle has retrieved the row. Therefore it will always exist, be at least 1, and be unique (among the rows returned by the query). Obviously it will change from query-to-query. Let's look at a quick example:

scott@Robert> SELECT ROWNUM, ENAME, SAL
  2  FROM EMP;

    ROWNUM ENAME             SAL
---------- ---------- ----------
         1 SMITH             800
         2 ALLEN            1600
         3 WARD             1250
         4 JONES            2975
         5 MARTIN           1250
         6 BLAKE            2850
         7 CLARK            2450
         8 SCOTT            3000
         9 VOLLMAN          5000
        10 TURNER           1500
        11 ADAMS            1100
        12 JAMES             950
        13 FORD             3000
        14 MILLER           1300

Ok so let's say we want the 5 highest paid employees. Should be easy:

scott@Robert> SELECT ROWNUM, ENAME, SAL
  2  FROM EMP
  3  WHERE ROWNUM < 6
  4  ORDER BY SAL DESC;

    ROWNUM ENAME             SAL
---------- ---------- ----------
         4 JONES            2975
         2 ALLEN            1600
         3 WARD             1250
         5 MARTIN           1250
         1 SMITH             800

Whoops! Turns out ROWNUM is assigned before results are ordered, not after. Knowing that, we can write it like this:

scott@Robert> SELECT ENAME, SAL
  2  FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) E
  3  WHERE ROWNUM < 6;

ENAME             SAL
---------- ----------
VOLLMAN          5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

What about ROWID? ROWID actually represents the physical location of the record/row in the database. That being the case, it is (according to Oracle documentation) the fastest way to retrieve a particular row. Faster than an index, even.

Can you use ROWID to differentiate between duplicate rows?
Yes, you can. Since it actually represents the physical location of a row, no two rows within the same table will have the same ROWID. Notice the caveat I added: within the same table. If you're using clustering, two records from different tables could theoretically share the same ROWID.

Do ROWIDs change?
Yes, especially with index organized or partitioned tables. Because ROWIDs represent the physical location of a record/row, the ROWID will change every time the record is physically moved.

Can you use ROWID as a primary key?
No, that's not advisable. While the ROWID will be unique, you would ideally want to use a primary key that doesn't change.

How do you use ROWID to figure out what was the last record that was processed?
Using DBMS_SQL.LAST_ROW_ID to get the ROWID of the last row processed.

You'll see ROWNUM and ROWID pop up occasionally within solutions to problems on AskTom and various Discussion Forums, so I recommend adding it to your own toolbelt as well.

Not long ago, I mentioned that if you do a COUNT on a column (as opposed to on * or a constant), the result will not include rows that have a NULL value for that column.

http://thinkoracle.blogspot.com/2005/09/nulls-in-count.html

Apparently you can exploit this situation, by using DECODE, and improve the efficiency of your queries.

http://www.akadia.com/services/ora_decode.html

I found that very interesting. However, I had to check it for myself. Why? Because this assertion may be:
1. Applicable only to a different version than mine (9.2.0.6).
2. Misunderstood by me.
3. Just plain wrong.

Note: My first attempt at this was a case of #2, therefore this article was updated after my error was kindly pointed out by Gary Myers

Here is the test I ran which confirmed the results.

CREATE TABLE ReallyBigTable AS SELECT * FROM all_objects;

**Note: I did this about 15-20 times to get a really big table.

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT rbt1.the_count, rbt1.the_sum,
rbt2.the_count, rbt2.the_sum FROM
(SELECT COUNT(*) the_count, SUM(object_id) the_sum
FROM ReallyBigTable WHERE object_type='TABLE') rbt1,
(SELECT COUNT(*) the_count, SUM(object_id) the_sum
FROM ReallyBigTable WHERE object_type='INDEX') rbt2;

ALTER SESSION SET SQL_TRACE = FALSE;

TKPROF robert_ora_2236.trc robert_ora_2236.prf explain='sys/******** as sysdba'

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(DECODE(object_type,'TABLE','*',NULL)) the_count,
SUM(DECODE(object_type,'TABLE',object_id,NULL)) the_sum,
COUNT(DECODE(object_type,'INDEX','*',NULL)) the_count2,
SUM(DECODE(object_type,'INDEX',object_id,NULL)) the_sum2
FROM ReallyBigTable;

ALTER SESSION SET SQL_TRACE = FALSE;

TKPROF robert_ora_2416.trc robert_ora_2416.prf explain='sys/******** as sysdba'

The DECODE version completed twice as quickly, because it only need to make one pass through the data instead of 2.

Excerpt of the results:

SELECT rbt1.the_count, rbt1.the_sum, 
rbt2.the_count, rbt2.the_sum FROM
(SELECT COUNT(*) the_count, SUM(object_id) 
the_sum FROM ReallyBigTable
WHERE object_type='TABLE') rbt1,
(SELECT COUNT(*) the_count, SUM(object_id) 
the_sum FROM ReallyBigTable
WHERE object_type='INDEX') rbt2

call     count       cpu    elapsed       disk      query 
------- ------  -------- ---------- ---------- ---------- 
Parse        1      0.00       0.00          0          0 
Execute      1      0.00       0.00          0          0 
Fetch        2      0.64       2.97      16994      17542 
------- ------  -------- ---------- ---------- ---------- 
total        4      0.64       2.97      16994      17542

SELECT COUNT(DECODE(object_type,'TABLE','*',NULL)) the_count,
SUM(DECODE(object_type,'TABLE',object_id,NULL)) the_sum,
COUNT(DECODE(object_type,'INDEX','*',NULL)) the_count2,
SUM(DECODE(object_type,'INDEX',object_id,NULL)) the_sum2
FROM ReallyBigTable

call     count       cpu    elapsed       disk      query 
------- ------  -------- ---------- ---------- ---------- 
Parse        1      0.00       0.00          0          0 
Execute      1      0.00       0.00          0          0 
Fetch        2      0.60       1.76       8498       8771 
------- ------  -------- ---------- ---------- ---------- 
total        4      0.60       1.76       8498       8771

Recently Dr. Tim Hall posted a brief blog on SQL Tuning. He may have based it on a number of questions on the topic that have popped up recently on some of the Oracle forums.

http://oracle-base.blogspot.com/2005/10/no-shortcuts-for-sql-tuning.html

You might also have seen Steve Callan's 2-part article on Oracle Performance Tuning on DBASupport.com:

http://www.dbasupport.com/oracle/ora10g/perfTuning01.shtml

So I was searching for a good article I had once read on this topic, and I found it. Here it is, courtesy of Ian Jones and Roger Schrag of Database Specialists:

http://www.dbspecialists.com/presentations.html#perf_bottleneck

The articles on their site are sometimes dated, but I still find them useful and interesting. Here is a particularly fun article on "Database Mysteries" courtesy of Chris Lawson:

http://www.dbspecialists.com/presentations.html#dba_mysteries