Well we finally upgraded to 11g at work. New playground for me to play in. Few things that most are aware of already :

- PLS_INTEGER was the recommended approach for coding when precision was not required. More efficient and faster. 11g brings to the table SIMPLE_INTEGER which is always NOT NULL. Since it is never NULL, then it never checks for that value (or lack of), thus making it even faster than PLS_INTEGER.

- Oracle 11g virtual table columns are columns that are actually functions (create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual), and similarly, virtual indexes that are based on functions REF partitioning, allowing you to partition a table based on the partition scheme of another. Allows you to partition an order_items table based off of the order_date column in an orders table.

- Oracle 11g PL/SQL will you to specify trigger firing order.

- The 11g SQL Access Advisor gives partitioning advice, including advice on the new interval partitioning. Interval partitioning is an automated version of range partitioning, where new equally-sized partitions are automatically created when needed. Both range and interval partitions can exist for a single table, and range partitioned tables can be converted to interval partitioned tables.

- New with 10r2 proxy identification in SQL*Plus, the connect command has been enhanced to allow for a proxy, to aid applications that always connect with the same user ID:

connect sapr3[scott]/tiger

- Fine Grained Dependancy Tracking (FGDT)
This means that when you add a column to a table, or a cursor to a package spec, you don't invalidate objects that are dependant on them.

Link: http://momendba.blogspot.com/2009/07/who-says-full-table-scans-are-evil.html

Really good article that demonstrates how full table scans can be better than indexed scans... but as you read on, the real truth emerges as to why. Lesson to be learned... if your statistics are up to date, then the optimizer will do its job in almost all cases... if they are out of wack, thats when you find yourself using optimization hints. So think twice before you apply the optimization hint and promote that to production. Work with your DBAs to identify the root cause.

In my previous life at Papa Johns Corporate, we had fiscal years that did not necessarily match calendar years. To keep track of what date cycles we were in we generated a calendar table. The calendar table had a variety of data stored within it, such as :

* Period
* Start and end of period
* Day of period and days remaining in period
* etc.

Additionally, we threw in a counter field that simply provided an incremental number that could be leveraged for joining and filling in empty rows when joining unlike tables, or for any number of other occassions where you needed a list of sequential numbers.

I brought that table with me when I joined my current employer, and to this date we still use it for variety of efforts as well.

Recently I stumbled on a simple query that provided a list of dates :

SELECT TO_DATE (:x, 'DD-MON-YYYY') + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= :n;

Where x is a string formatted date e.g. 03-JAN-2009 and n is number of days in range. Submitting '14-MAY-2009' and 5 would return 5 rows of data beginning with 14-May-2009 and ending with 18-May-2009.

I gave it some thought and wondered if I could convert our existing CAL_TABLE to a dynamic on-the-fly SQL query instead...

Pages: 1 · 2

Link: http://www.myoracleportal.net/blog1.php/2009/02/01/plsqlftp

Author Comments : Important issue identified regarding the structure of your remote FTP servers banners. Although with a true FTP client tool it may not be required, this FTP process follows best practice and expects that the server codes precede each line being returned by the remote FTP Server as it authenticates to the remote server. Failure to include the required prefix could cause you to be unable to access the remote server or failure to authenticate. See for more info => TCP Banners and then contact the respective server administrator.

Additional information on server codes can also be found here.

It might be possible to enhance the PLSQL solution to work past the issue, however I would not encourage that and instead stay with best practice.

Was reading through a listserver group that I am subscribed to, and a question prompted me to repost this information. It is in fact included in the Email Utility that I have here, but thought it worth posting on its own as well. I cannot remember where I found the original code of this file dump utility, however, originally it was accompanied by a dump_fixed routine as well. I later enhanced the overall into a single API call with additional features such as :

• User Defined Separator
• Optional Header Inclusion
• Optional Trailing Separator
• User Defined Maximum Line Size
• Write and Append Modes

It provides a very easy means of writing out a flat file from data source. Much easier than making all of those UTL_FILE.PUT statements... all reduced down to a single API call... simply by passing your SQL. The SQL itself can be generated dynamically. Take note also that we include the standard, and ever important, exceptions to address key errors that may potentially occur. Depending upon your usage, you might alter the exception handler so that it raises the error and propogates it out instead of just displaying the information.

Another interesting benefit is that it provides a good example of how you can dynamically identify columns that belong to a table, and then utilize that information sourced.

Pages: 1 · 2