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.

Got to thinking that it would be interesting to create a Google Earth Tour of where I have been.. from birth to today. So I dragged out this old piece of paper with a list of places and dates. Having grown up in the military, I have been to a number of places. I figured I would start mostly with where I lived, then move to filling in the gaps with places visited while there. For the most part the tour file is in order, and then sporadically filled with some highlights.

If you want to run through the tour yourself, grab a copy of Google Earth and then grab this file here to walk through the tour. Its under 10 minutes right now. Who knows down the road. Guess I will see where it takes us...

Where Have I Been

Found these on YouTube regarding Agile Development. Found them very informative and possibly a introductory tool for business when introducing Agile methodology for the first time.

Agile Methodology is a tool like any other tool... right time and right place. Agreeably more often than not it could be the right method to engage.

You can download the videos here, both of which are under 10 minutes each. The downloads are not streaming. If you want streaming versions, I am certain you can find them you YouTube.

Agile vs Waterfall : A Tale of Two Teams
SCRUM in Under 10 minutes

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