Friday, February 22, 2008

Example Oracle Procedure with Cursors to loop inside and Table Types

PROCEDURE UpdateLeader(argMemId IN ChallengeMember.MemberId%TYPE) IS

CURSOR userChallenges IS
SELECT DISTINCT(CHALLENGEMEMBER.CHALLENGEID)
FROM CHALLENGEMEMBER, CHALLENGE
WHERE CHALLENGEMEMBER.MEMBERID = argMemId AND CHALLENGEMEMBER.CHALLENGEID = CHALLENGE.CHALLENGEID AND (CHALLENGE.CHALLENGESTATUSID = 2 OR CHALLENGE.CHALLENGESTATUSID = 3);


CURSOR ChallengeMembers (varCHALLENGEID CHALLENGEMEMBER.CHALLENGEID%TYPE) ISSELECT CHALLENGEMEMBER.MEMBERID AS MEMBERID,CHALLENGE.STARTDATE AS STARTDATE, CHALLENGE.ENDDATE AS ENDDATE
FROM CHALLENGEMEMBER, CHALLENGE
WHERE CHALLENGE.CHALLENGEID = varCHALLENGEID AND
CHALLENGE.CHALLENGEID = CHALLENGEMEMBER.CHALLENGEID AND
CHALLENGEMEMBER.MEMBERID <> 0;

vMemberPoints CHALLENGEMEMBER.POINTS%TYPE;
varMaxPoints CHALLENGEMEMBER.POINTS%TYPE;


varNotifyRequired NUMBER;


TYPE MAX_POINTS_REC IS RECORD
(MEMBER CHALLENGEMEMBER.MEMBERID%TYPE);

TYPE MAX_POINTS_TAB_TYPE IS TABLE OF MAX_POINTS_REC
INDEX BY BINARY_INTEGER;


MAX_POINTS_TAB MAX_POINTS_TAB_TYPE;
i NUMBER;
LEADER_CHANGE NUMBER;
VAR_ISLEADER NUMBER;
ISLEADER_RECS NUMBER;

BEGIN

FOR Challenge_REC IN userChallenges

LOOP
varMaxPoints := 0;
i := 1;

MAX_POINTS_TAB.DELETE;
LEADER_CHANGE := 1;

FOR Mem_REC IN ChallengeMembers(Challenge_REC.CHALLENGEID)

LOOP
vMemberPoints := 0;
vMemberPoints := nvl(CHALLENGEMEMBER_PKG.GetGHGByChallengeMem(Mem_REC.MEMBERID, Mem_REC.STARTDATE, Mem_REC.ENDDATE), 0);

IF (vMemberPoints > varMaxPoints) THEN
varMaxPoints := vMemberPoints;

MAX_POINTS_TAB.DELETE;
MAX_POINTS_TAB(1).MEMBER := Mem_REC.MEMBERID;

ELSIF (vMemberPoints = varMaxPoints AND vMemberPoints > 0) THEN
i := i +1;
MAX_POINTS_TAB(i).MEMBER := Mem_REC.MEMBERID;

END IF;
END LOOP;

SELECT NVL(COUNT(ISLEADER),0) INTO ISLEADER_RECS
FROM CHALLENGEMEMBER
WHERE CHALLENGEID = Challenge_REC.CHALLENGEID AND
ISLEADER = 1 ;

IF ISLEADER_RECS <> MAX_POINTS_TAB.COUNT THEN

UPDATE CHALLENGEMEMBER
SET UPDATENOTIFIED = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

ELSE
FOR x IN 1 .. MAX_POINTS_TAB.COUNT
LOOP
SELECT ISLEADER INTO VAR_ISLEADER
FROM CHALLENGEMEMBER
WHERE CHALLENGEID = Challenge_REC.CHALLENGEID AND
MEMBERID = MAX_POINTS_TAB(x).MEMBER;

IF VAR_ISLEADER <> 1 THEN
UPDATE CHALLENGEMEMBER
SET UPDATENOTIFIED = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;
END IF;

END LOOP;

END IF;

UPDATE CHALLENGEMEMBER
SET ISLEADER = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

FOR x IN 1 .. MAX_POINTS_TAB.COUNT
LOOP
UPDATE CHALLENGEMEMBER
SET ISLEADER = 1
WHERE CHALLENGEMEMBER.MEMBERID = MAX_POINTS_TAB(x).MEMBER AND
CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

END LOOP;
END LOOP;

END UpdateLeader;


Tuesday, February 19, 2008

How to Install ColdFusion 8 on Linux?

Grant Execute permission to the Coldfusion‐8‐lin.bin binary file

chmod +x Coldfusion‐8‐lin.bin

Execute Coldfusion‐8‐lin.bin
./Coldfusion‐8‐lin.bin

Enter the Locale
1 (English)

Hit Enter 28 times to continue the agreement

Enter “Y” to accept the agreement

Enter the version to install
3 (Developer Edition)

Enter the next task to perform
1 (Server Configuration)

Enter whether you have an existing server configuration
2 (No existing server configuration)

Enter the next task to perform
5 (continue installation)

Enter the installation path
Hit enter to accept the default path

Hit Enter 30 times to continue the agreement

Enter “Y” to accept the agreement

Enter the serial number

Hit Enter for no serial number

Enter whether you have an existing CF installation
2 (no existing CF installation)

Enter the next task to perform
1 (Add Web Server Configuration)

Enter the web server to configure
1 (Apache)

Enter the path to the directory containing the Apache config file (httpd.conf)
/etc/httpd/conf

Enter the path to the Apache binary files
/usr/sbin/httpd

Enter the path to the Apache control file
/etc/init.d/httpd

Enter the next task to perform
4 (continue with the installation)

Enter the path to the web root

Hit enter to select the default location

Create a new Linux user

Enter the username / password

Confirm password

Enter whether you need to enable RDS
Y (Enable RDS)

Enter the password for RDS and confirm

Hit enter to exit the installer

When installation completes the installer will prompt you to start the ColdFusion server

Change directory to the ColdFusion binary directory
cd /opt/coldfusion8/bin

Start coldfusion8
./coldfusion start

There are three possible messages you will get
- Installation successful
- Installation failed
- Installation failed and retry for 12 times

If you are done with the installation successfully please ignore following instructions

Installer normally don’t update file paths correctly please edit following two files for the given file paths,

Files:
/opt/coldfusion8/bin/cf‐connectors.sh
/opt/coldfusion8/bin/connectors/apache_connector.sh

Paths:
Apache config file (httpd.conf): /etc/httpd/conf
Apache binary files: /usr/sbin/httpd
Apache control file: /etc/init.d/httpd

Check whether you have following file on the file system,
/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

If Yes, go to label “Final

If No, Continue with following instructions,

Grant permission to following three files (permissions should be same as the httpd file)

Files:
/opt/coldfusion8/bin/cf‐connectors.sh
/opt/coldfusion8/bin/connectors/apache_connector.sh
/opt/coldfusion8/runtime/lib/wsconfig.jar

Command:
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/bin/cf‐connectors.sh
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/bin/connectors/apache_connector.sh
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/runtime/lib/wsconfig.jar

Start ColdFusion Server

Change directory to the ColdFusion binary directory
cd /opt/coldfusion8/bin

Restart coldfusion8
./coldfusion stop
./coldfusion start

Check whether you have following file on the file system,
/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

Yes it should be in the file system. Continue with the following instructions,


Final:

Grant permission to following file (permissions should be same as the httpd file)

File:
/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

Command:
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

Restart coldfusion8
./coldfusion stop
./coldfusion start

Start the apache server

You are done with the installation

Access the site http://localhost/CFIDE/administrator/login.cfm

Continue the wizard and you are ready to use the ColdFusion server.
Congratulations!

Thanks, Darshatha and Gayan for preparing this document. Hope it will help you all.