Fun with Time and Labor Dates

There's a useful little table in PeopleSoft Time and Labor called PS_TL_DATES_TBL. This holds a variety of date information, such as day number, julian date , day of week and month, that can save a bit of coding to derive these values yourself. The following example uses this table to get the start and end dates of the previous, current and next years, months and weeks.

select DAT.THE_DATE,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as START_OF_PREVIOUS_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),-12) as END_OF_PREVIOUS_YEAR,
       TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as START_OF_CURRENT_YEAR,
       TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY')))                 as END_OF_CURRENT_YEAR,
       ADD_MONTHS(TO_DATE('01-JAN-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as START_OF_NEXT_YEAR,
       ADD_MONTHS(TO_DATE('31-DEC-'|| TO_NUMBER(TO_CHAR(DAT.THE_DATE,'YYYY'))),12)  as END_OF_NEXT_YEAR,
       ADD_MONTHS((TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1),-1)                    as START_OF_PREVIOUS_MONTH,
       DAT.THE_DATE - DAT.DAYOFMONTH                                                as END_OF_PREVIOUS_MONTH,
       TRUNC(DAT.THE_DATE - DAT.DAYOFMONTH) + 1                                     as START_OF_CURRENT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),1)                                as END_OF_CURRENT_MONTH,
       ADD_MONTHS(TRUNC((DAT.THE_DATE - DAT.DAYOFMONTH) + 1),1)                     as START_OF_NEXT_MONTH,
       ADD_MONTHS((DAT.THE_DATE - DAT.DAYOFMONTH),2)                                as END_OF_NEXT_MONTH,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 13
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 8
                         when DAT.DAYOFWEEK = 4 then 9
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 11
                         when DAT.DAYOFWEEK = 7 then 12
                       end                                                          as START_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 1
                         when DAT.DAYOFWEEK = 3 then 2
                         when DAT.DAYOFWEEK = 4 then 3
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 5
                         when DAT.DAYOFWEEK = 7 then 6
                       end                                                          as END_OF_PREVIOUS_WEEK,
       DAT.THE_DATE  - case
                         when DAT.DAYOFWEEK = 1 then 6
                         when DAT.DAYOFWEEK = 2 then 0
                         when DAT.DAYOFWEEK = 3 then 1
                         when DAT.DAYOFWEEK = 4 then 2
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 4
                         when DAT.DAYOFWEEK = 7 then 5
                       end                                                          as START_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 0
                         when DAT.DAYOFWEEK = 2 then 6
                         when DAT.DAYOFWEEK = 3 then 5
                         when DAT.DAYOFWEEK = 4 then 4
                         when DAT.DAYOFWEEK = 5 then 3
                         when DAT.DAYOFWEEK = 6 then 2
                         when DAT.DAYOFWEEK = 7 then 1
                       end                                                          as END_OF_CURRENT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 1
                         when DAT.DAYOFWEEK = 2 then 7
                         when DAT.DAYOFWEEK = 3 then 6
                         when DAT.DAYOFWEEK = 4 then 5
                         when DAT.DAYOFWEEK = 5 then 4
                         when DAT.DAYOFWEEK = 6 then 3
                         when DAT.DAYOFWEEK = 7 then 2
                       end                                                          as START_OF_NEXT_WEEK,
       DAT.THE_DATE  + case
                         when DAT.DAYOFWEEK = 1 then 7
                         when DAT.DAYOFWEEK = 2 then 13
                         when DAT.DAYOFWEEK = 3 then 12
                         when DAT.DAYOFWEEK = 4 then 11
                         when DAT.DAYOFWEEK = 5 then 10
                         when DAT.DAYOFWEEK = 6 then 9
                         when DAT.DAYOFWEEK = 7 then 8
                       end                                                          as END_OF_NEXT_WEEK
  from PS_TL_DATES_TBL DAT
 where DAT.THE_DATE = TRUNC(SYSDATE)
 order by DAT.THE_DATE desc

What day is it son?

If you need to know what day of the week it is you could get the day number then go on the principle that 1 is a sunday, 2 is a Monday etc, to work it out. The problem with this however, is that this number can vary depending on how the Oracle environment is configured, which makes this unreliable. A better way of finding out the day is to use the 'DAY' parameter of TO_CHAR. This returns the day as MONDAY, TUESDAY etc. The only thing to watch here is that the returned value has trailing spaces, so if you're comparing it then remember to trim it first. For example: -

select DAT.THE_DATE, to_char( DAT.THE_DATE, 'DAY' )
  from PS_TL_DATES_TBL DAT
where trim(to_char( DAT.THE_DATE, 'DAY' )) in ('SATURDAY','SUNDAY')
  and DAT.THE_DATE between '01-DEC-2011' and '31-DEC-2011'
order by DAT.THE_DATE Asc

Enjoy.

Finding a table name

select *
from dba_tables
where table_name like '%<table_name>';

Creating a Database Link

I always forget the syntax for this one...

CREATE DATABASE LINK 
CONNECT TO 
IDENTIFIED BY 
USING '';

For example: -

CREATE DATABASE LINK HR89TST
CONNECT TO FBLOGGS
IDENTIFIED BY FR3DD13
USING 'HR89TST';

Note that if you have special characters in your password, you should enclose it in double quotes or you may get an error message.

To list existing database links, use: -

select * from dba_db_links;
or

Select * from ALL_DB_LINKS;

To drop a database link use: -

DROP DATABASE LINK linkname;

Pump Up The Volume

Recently I had a problem with the sound level on my EEE PC, which for no apparent reason went rather quiet. A trawl through the forumsfound the solution. What you need to do to remedy this grievous state of affairs is to take the following steps.

• Enter <ctrl><alt>T to open a command console window.
  

• Type alsamixer to bring up the sound card control panel.

• Use the up arrow to increase the PCM level, which is the first bar displayed.

• Celebrate by jacking in some power speakers and playing some Motorhead really loud.

An alternative solution would be to boost your hearing with the aid of some bionics, though you'll need to find Rudy Wells first. We have the technology.....

How to Install Wordpress and learn to stop Worrying and Love the Blog

There are two ways to setup a blog. The first is the easiest and involves registering with an on-line blogging service such as Blogger, selecting a template, configuring your account then off you go. The second route is to install a blogging application on a server, configure it, customise it then your up and running. The first method is the quickest and can have you blogging within minutes. The second is a tad more time consuming but gives you far greater flexibility in terms of what you can do.

Wordpress
Having already setup 4 blogs using the first method, I thought it was high time I got down to some real hardcore blogging and setup my own hosted blog. After a few nights of on-line research and numerous coffees, I concluded that probably the best tool for the job was Wordpress. This uses a MySQL database to hold your blogs content then a combination of PHP, XHTML and CSS style sheets to present it.

Hosting
First off you need to have a place for Wordplace to hang its hat. The server that it's hosted on needs to have MySQL and PHP installed on it. The Wordpress site lists a number of recommended Hosts. Again after more surf and coffee sessions I plumped for AN Hosting as my host of choice. A good price, well recommended and bags of features, AN were the host with the most. I also toyed with installing a WAMP server and trying things out locally, but that's a different story. AN also offer automatic Wordpress installation, but for the sake of this post I'll describe how to install it old school.

Installation
Creating the Wordpress Database
Through your hosts control panel create a new database to hold your Wordpress tables. Give it a suitable name such as wordpress or an unsuitable one such as ziggy. Add any users to this database who you want to give access to (e.g. yourself, yourwife, the cat...).

Download Wordpress
First off, download a zip file of the latest stable Wordpress release from the Wordpress website to your PC. Then un-zip it to a suitable place.

Create the wp-config.php file

If you look in the wordpress folder you will find a file called wp-config-sample.php. Save this as wp-config.php then edit it using a text editor such as notepad or the developers PFE.

Find the line that says define('DB_NAME', 'wordpress') and replace wordpress with the name of the MySQL database you created earlier. On the next two lines enter the name of a user who you gave admin rights to your database and their password.

e.g.

define('DB_USER', 'dbrock'); // Your MySQL username define('DB_PASSWORD', 'hawkwind'); // ...and password

On the line that says: -

define('DB_HOST', 'localhost'); // 99% chance you won't need to change this value

I also had to replace local host with remotemysqlhost, as instructed on the AN hosting control panel. I guess I must be the 1%.

One neat trick you can do is to have several blogs setup in the same database, if you want to do this drop down to the line that says:-

$table_prefix = 'wp_'; // Only numbers, letters, and underscores please! this is the prefix that will be given to the name of each wordpress table that is created. For more than one table use different prefixes. For example you could change it to: -


$table_prefix = 'wp_mh_'; // Only numbers, letters, and underscores please!
for your Motorhead blog.

Copy Wordpress Files to Your Server

Now you've created a configuration file, it's time to copy your files up to the server. To do this use an FTP application such as FileZilla or CuteFTP. I personally use WS-FTP, which isn't free but is reliable.

Use your FTP software to copy the extracted Worpress directory to a directory under the directory that holds your web documents. I set one up in this called \blogs\ into which I'll copy any future wordpress directories.
Run install.php

Through a web browser, such as firefox, run the worpress install.php file. To do this enter the URL of your websites root directory followed by the path to this file, which is found in the wp-admin folder.

e.g.

http://www.kungfool.co.uk/blogs/wordpress/wp-admin/install.php You should now be prompted for the name of your blog, which you can always change later, and your e-mail address. Enter both and click on the Install Wordpress link.

In the blink of an eye you'll get the message: -

WordPress has been installed. Were you expecting more steps? Sorry to disappoint.

And given a user ID and password to administer your blog with. Make a note of them for future reference and hit Log In baby.

Congratulations, you've installed Wordpress, woo hoo, go away and have a nice cup of tea and a biscuit to celebrate why don't you.

To view your fledgling blog enter your root URL followed by the wordpress directory: -

e.g.
http://www.kungfool.co.uk/blogs/wordpress
To bring up the admin login page run the following script from your browser: -
http://www.kungfool.com/blogs/wordpress/wp-login.php

substituting your root web URL and Wordpress directory as appropriate.


By the way you can visit the blog I created in this exercise at

http://www.seishan.com/blogs/test25/

I'll be building on it over the next couple of weeks, so expect it to morph as time goes by.

By the way, the 25 in test25 has nothing to do with Hawkwind's - 25 Years On, I'm just checking out Release Candidate 1 of Wordpress 2.5. Wordpress 2.5 isn't on general release yet, but hey I thought I'd give it a whirl.

Enjoy.

Oracle 11g Release on You Tube

Blogged with Flock