Good reference site

This is good reference site especially for Datawarehouse:

http://it.toolbox.com/

A short PL/SQL Course

Application Structure

The basic structure of a PL/SQL program consist of three parts:

Declare
...
Begin
...
End;

Using PL/SQL, we can develop several different kinds of codes:

1. PL/SQL script. Codes that execute using external files.
2. Database stored codes - once executed the code is stored in the database. This code consist several types:

• Stored function (returns one value)
• Stored procedure (does not return value - except through the parameter).
• Triggers (linked to a specific action to a table.

Stored procedures and functions can be grouped together into a package.

For the code below, we will create a sample table called EmployeeTab as follows:

CREATE TABLE EMPLOYEETAB
(
  ENO VARCHAR2(20),
  ENAME VARCHAR2(40),
  EDEPT VARCHAR2(20),
  SUPNO VARCHAR2(20),
  PHONE VARCHAR2(20),
  JTITLE VARCHAR2(20)
);

CREATE TABLE EMPLOYEETABHIST
( ENO VARCHAR2(20 BYTE),
COLNAME VARCHAR2(20 BYTE),
OLDVAL VARCHAR2(40 BYTE),
NEWVAL VARCHAR2(40 BYTE),
CHANGEDATE DATE
);

Stored Procedure/Function

To create a stored procedure, the syntax is as follows:

Create or replace procedure storeData
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2)
IS
Begin -- start of code
-- Insertion code here
Insert into EmployeeTab (eNo, eName) Values (pEno, pEName);

End storeData;

Note the code above:

1. The code above takes in two parameters and both are input parameters.
2. PL/SQL codes are not case sensitive so pEno and peno are the same.
3. Comments can be added any where either surrounded by /* ... */ or -- (codes after -- is comment until the end of the line. while everything between the * are all comments irrespective of the number of lines.

Here is an example of a function which sums two parameters entered to it.

Create or Replace
Function sumFunc
  (pVar1 IN Number, pVar2 IN Number)
   Return Number IS
   tmpNum   Number;
Begin
  tmpNum := pVar1 + pVar2;
  return tmpNum;
End sumFunc;

Note the code above:

1. Both parameter above are input parameters and in this example both are of type Number.
2. This function will return a value of type number.
3. After the keyword IS, we can declare our variables (this represent the Declare portion of the PL/SQL block.
4. I used a parameter to return the value just to show how the local variables can be used. The code could be changed as follows:

Create or Replace
Function sumFunc
(pVar1 IN Number, pVar2 IN Number)
Return Number IS
Begin
 return pvar1 + pvar2;
End sumFunc;

One nice thing about functions, is you can call the function as another column in your SQL statement. For example:

Select eNo, eName, sumFunc(5, 3) Age from EmployeeTab;


Package

Package allows the developer to group codes that are related together. So assuming that the above codes are common than we can group them together in a package. Package consist of two parts:

1. Declaration
2. Body

The declaration is as follows:

create or replace PACKAGE TestPack
AS
procedure storeData
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2);
-------------
Function sumFunc
  (pVar1 IN Number, pVar2 IN Number)
   Return Number;
-------------
End TestPack;

The body contains the actual codes and the header needs to match the declaration as follows:

create or replace
PACKAGE BODY TestPack
AS
procedure storeData
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2)
IS
Begin -- start of code
  -- Insertion code here
  Insert into EmployeeTab (eNo, eName) Values (pEno, pEName);

End storeData;
------------------
Function sumFunc
  (pVar1 IN Number, pVar2 IN Number)
   Return Number IS
   tmpNum   Number; 
Begin
  tmpNum := pVar1 + pVar2;
  return tmpNum;
End sumFunc;

End TestPack;

To use the codes in the package, we need to prefix the procedure or function with the package name, for example:

Select eNo, eName, TestPack.sumFunc(5, 3) Age from EmployeeTab;

Triggers

Triggers are special type of stored procedures that will be executed based on an event. For example, in the case of the employeeTab, I would like to keep track of changes to the columns in the table. For this example, I make an assumption that eNo is the primary key and will not change. All changes will be stored in the EmployeeTabHist.

create or replace
Trigger trEmp_AttribUpd
Before Update Of eName, eDept, SupNo, Phone, jTitle on EmployeeTab
For Each Row
Declare
/*
 Create a history of the changes in the employee attributes
 Author Strovek
 Date Jul 18, 2009
 Revised
*/
 Procedure ins_empattribhist(pParam IN Varchar2, pOldVal IN Varchar2, pNewVal IN Varchar2)
  IS
 Begin
  insert into employeetabhist
   (eNo, colName, oldVal, newVal, changedate) values
   (:new.eNo, pParam, pOldVal, pnewVal, sysdate);

 End ins_empattribhist;

Begin
       If :old.eName <> :new.eName Then
          ins_empattribhist('Name', :old.eName, :new.eName);
       End If;
       If :old.eDept <> :new.eDept Then
          ins_empattribhist('Dept', :old.eDept, :new.eDept);
       End If;
       If :old.SupNo <> :new.SupNo Then
          ins_empattribhist('SuperVisor', :old.SupNo, :new.SupNo);
       End If;
       If :old.Phone <> :new.Phone Then
          ins_empattribhist('Phone', :old.Phone, :new.Phone);
       End If;
       If :old.jTitle <> :new.jTitle Then
          ins_empattribhist('Name', :old.jTitle, :new.jTitle);
       End If;

End;

Note the code above:

1. Since some of the codes are common, we can reduce the amount of codes written by create a procedure within the trigger. This procedure is only accessible by the trigger since it is within the declaration section of the code.
2. Some common columns can be obtained directly and does not need to passed to the code via parameters (e.g. eNo, which is the same irrespective of the attribute changed and the changedate which is the sysdate).

Error Handling

Similar to Java and Vb.Net, you can also catch and throw exceptions in PL/SQL. Before we go into that note that it is possible to create your own error code, Oracle has reserved the numbers -20000 to -20999 for this purpose.

Going back to the first section, we have the code block:

Declare
 ...
Begin
 ...
End;

There is an optional section between Begin and End called Exception. So the code can as follows:

Declare
...
Begin
...
Exception
...
End;

Similar to the try catch, you can specify multiple whens to handle different error codes. Some examples found here.

Using an earlier example of store data, we can further enhance it to prevent insertion of existing ENo as follows:

Create or replace procedure storeData2
/*
Sample stored procedure to store Employee Data
Author  Strovek
Date    Jul 17, 2009
*/
(pEno IN Varchar2,
pEName In Varchar2)
IS
Cursor chkRec IS
Select eName from employeeTab where eNo = pENo;
vChkRec   chkRec%ROWTYPE;
vRecExist Boolean;

Begin -- start of code
vRecExist := False;
Open chkRec;
Fetch chkRec INTO vChkRec;
If chkRec%NOTFOUND Then
Insert into EmployeeTab (eNo, eName) Values (pEno, pEName);
Else
vRecExist := True;
End If;
Close chkRec;

If vRecExist Then
Raise_application_error(-20101, 'Eno ' || pENo || ' already exist, cannot insert record');
End If;

End storeData2;

Cursors

In order to retrieve records from a database via the sql statement, a cursor is used. The cursor can either be used implicitly or explicitly.

Implicit means that Oracle will open, fetch the records and close the cursor for you automatically.

An example of implicit cursor is as follows:

Set serveroutput on size 100000
Declare
  cursor cGetEmp IS
    Select eName, Phone from EmployeeTab;

Begin
  For vRec in cGetEmp Loop
    dbms_output.put_line(vRec.eName || ' phone number is ' || vRec.Phone);
  End Loop;
End;

Note on the code above:

1. I did not have to declare vRec. It is implied.
2. I did not have to open the cursor. The records are fetched into vRec and once all the records are retrieve, the cursor is closed.

By comparison of an explicit cursor is as follows:

Set serveroutput on size 100000
Declare
  cursor cGetEmp IS
    Select eName, Phone from EmployeeTab;
  vGetEmp   cGetEmp%ROWTYPE;

Begin
  Open cGetEmp;
  Fetch cGetEmp INTO vGetEmp;
  While cGetEmp%FOUND
  Loop
    dbms_output.put_line(vGetEmp.eName || ' phone number is ' || vGetEmp.Phone);
    Fetch cGetEmp INTO vGetEmp;
  End Loop;
  Close cGetEmp;
End;

Note on the code above:

1. vGetEmp had to be declared and to ease things, we use a special type called %ROWTYPE so the record type is changed depending on the cursor declaration.
2. The cursor has to be opened and closed using the Open and Close statement.
3. Each record has be fetched and we have to check for the end of the records using %FOUND condition.
   

Closing

This short cause is meant to only introduce you to PL/SQL programming and not meant to be comprehensive. Note that when writing to code to avoid using reserved words for variables. You can learn more about the Loops structure, logic, predefined function from the Oracle Manual.

Here is the one for Oracle 9i from Oracle.

When writing code, you can use Oracle SQL Developer (a freeware from Oracle) to write the codes. That way during compilation (especially for stored procedure/function/packages), you can easily find the cause why your code has error.

Any comments are welcome.

Source for Sudo

To download sudo for Solaris, this is the source:


http://www.courtesan.com/sudo/download.html

Excerpt from the following site, on what is sudo:


sudo stands for "su do", and means "do something as the supervisor". `sudo` is an enhanced alternative to the Unix `su` command.

The `su` command allows any user to obtain superuser privileges, if they know the root password:
bash-2.05a$ su -
Password:
#

The Impact of sudo on Unix System Security
`sudo` improves on `su` in several ways:
`sudo` allows you to give privileged access to only some commands, instead of all commands.
`sudo` allows you to log all commands (and their arguments) executed as the privileged user.
`sudo` does not require the administrator to share the root password.
`sudo` allows you to limit the users who can use it by editing the sudoers file.
`sudo` times out after 5 minutes (by default).

Adding user in Sun Solaris

If you want to create a new group, use the groupadd command, eg, if you want to create a group call user, then just type

groupadd user

The above command will automatically create a group called "user" and assign a group id (gid) to it.

To create a user, use the command useradd, so for example you want to create a user call strovek, then just type:

useradd strovek

This will automatically create a user, assign and user id (uid) to it. You can alternatively assign the userid and the group id to it by use -g to assign a gid to the assign a uid by use -u parameter and -d to assign a home folder to it.

Next create a folder (or can be created earlier), using mkdir:

mkdir /export/home/strovek

Then change the owner of the folder using chown:

chown strovek:user /export/home/strovek

If you had not set the home folder earlier, you can do that using usermod, as in

usermod -d /export/home/strovek strovek

Set the password for the user using passwd, as in

passwd strovek

When you type the above, it will prompt you for the new password and then a confirmation.

To remove the account, just type userdel, as in

userdel strovek

The Solaris Administrator guide can be found here.

Downloading Firefox 3.5 for Sun Solaris

The following is a good source for download firefox for both Open Solaris and Sun Solaris:

http://ftp.spnet.net/mozilla/firefox/releases/3.5/contrib/solaris_pkgadd/

The instruction of how to install is available from the sunfreeware site but I had difficulty downloading the actual software:

http://sunfreeware.com/mozilla.html

Note:

Additional instruction found here:

The following is quoted from the entry

first, dont use the tar.gz build, use this instead:
http://releases.mozilla.org/pub/mozilla.org/firefox/releases/3.5/contrib/solaris_pkgadd/firefox-3.5.en-US.opensolaris-i386-pkg.bz2

then bunzip2 it, pkgadd it, and then,

pkgadd command is as follows:

pkgadd -d firefox-3.5.en-US.solaris-10-fcs-i386-pkg

to make it kindof replace the default firefox, do this:

pfexec mv /usr/bin/firefox /usr/bin/firefox-old
pfexec ln -s /opt/sfw/bin/firefox /usr/bin/firefox

i just did this a couple minutes ago and it worked almost perfectly! i say 'almost' because i lost the java plugin in the process. not hard to fix that though, just make a new symlink. not that ive ever used the java plugin lol.

hope this helps
--joe

Moving from Lotus NOTES to Gmail

Found a good article on the above subject here.

The main highlight is the following note:

Note that there are some quirks with this process:

• In Notes, all messages now have a forwarded status icon, but unlike normal forwards in Notes, the header doesn’t say where it’s been forwarded to.
• Most formatting (font colors, bullets, numbers, etc.) of Notes email is lost (they appear as plain text). This seems to be an artifact of the Notes IMAP implementation. Strangely, email that is sent from the Notes client to non-Notes users appear to preserve the formatting, but mail forwarded via a rule does not preserve formatting.
• Message counts between Notes and Gmail probably won’t match exactly. I initially thought that something was breaking in the copy process, but when I had a closer look, I realized Gmail automatically collapses duplicate messages into a single message. (We’ve got a couple team mailing lists and when I send to one, I get a copy sent back to me, so I had quite a few duplicate messages.) Also, because Labels are applied to an entire conversation, not just individual messages, some of my messages would appear in Gmail folders other than the one I had copied the message to.
  

A good place to learn Korn Shell programming

http://www.bolthole.com/solaris/ksh.html

Another useful link is found here, not as complete but equally useful:

http://user.it.uu.se/~matkin/documents/shell/