Technology

Planning for failure

2011-09-22T23:05:00.000-07:00

Process in IT companies looks a very ambiguous word. What looks fine in one company may not be acceptable in another company. I come across this site http://www.planningforfailure.com which compared the agile/Scrum with Kanban/Lean startup. It is an interesting read. Also the links mentioned in the blog are good.

I also like the definition of a hacker.
"Someone who thinks outside the box. Someone who discards conventional wisdom and does something else instead. Someone who looks at the edge and wonders what's beyond. Someone who sees a set of rules and wonders what happens if you don't follow them. - Bruce Schneie"

from http://www.thehackerchickblog.com/

Debug maven application with jetty plugin

2011-02-05T05:51:00.000-08:00

Have the jetty plugin configured.
<plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>maven-jetty-plugin</artifactId>
<version>6.1.12</version>
<configuration>
<scanIntervalSeconds>10</scanIntervalSeconds>
<connectors>
<connector implementation="org.mortbay.jetty.nio.SelectChannelConnector">
<port>8888</port>
<maxIdleTime>60000</maxIdleTime>
</connector>
</connectors>
</configuration>
</plugin>

After that It’s time to configure eclipse for debugging the web app by selecting the Run > Debug … from the menu
# Set Main Class to "org.codehaus.classworlds.Launcher"

Go to the argument tab:
# Set Program arguments to "jetty:run"
# Set VM arguments to "-Xmx512M -Dclassworlds.conf=[MAVEN_HOME]/bin/m2.conf -Dmaven.home=[MAVEN_HOME]"
(Replace MAVEN_HOME with the location of maven on your system)

Go to the classpath tab:
# remove the application from the user entries
# add the "[MAVEN_HOME]/lib/classworlds.jar" to the user entries. in case if the classworlds.jar have version number in the name, please include. Essentially we need to add the correct jar to the classpath

Go to the source tab:
# add the current project to debug

Now you can start debugging your application like you do it always

It is very interesting to set the –o option to the maven command in order to accelerate the fix/debug process.

how to change oracle xe default http port number?

2010-08-02T00:34:00.000-07:00

login to sqlpus with system account.
sqlplus system@xe

see the present value

select dbms_xdb.gethttpport as "HTTP-Port", bms_xdb.getftpport as "FTP-Port" from dual;

--it shows actual value. now u want to change them, use the following?

begin

dbms_xdb.sethttpport('9999');
dbms_xdb.setftpport('2100');
end;
/

vmserver 2.0.2 ubuntu 9.10

2010-01-16T14:35:00.000-08:00

vmserver 2.0.2 licenses:
VMware Server for Windows A05FN-F2R0Z-V9380-4T085
VMware Server for Linux AA4D0-F8D2G-U7JGM-4100H

I had problem in installing vmserver in ubuntu 9.10. This link helped me to fix the problem. While trying the patch, it was showing error for a specific patch file that i have downloaded manually and placed along with the attahcment given in the post.

it worked, have to see bringing up my vm images.

Ubuntu - hdmi, video, webcam

2009-08-28T00:05:00.000-07:00

I had problem with hdmi audio from ubuntu. Video is going fine to LCD tv through HDMI cable, but no audio.

Not sure if that is a problem with operating system or the multimedia softwares, but I found mplayer supporting audio as well.

Following line has fixed hdmi audio for me.
mplayer -afm hwac3,hwdts, -ao alsa:device=hdmi -channels 2 file

Skype, web cam:
Web cam was not detected by skype on ubuntu (9.04). After searching on internet, installed gspcs-source, followed by a system restart.

sudo apt-get install gspca-source

Skype recognized my webcam and when ever I am on voice call with my skype contacts, I am able to share video through webcam.

Kopete, yahoo IM problem:
Kopete was not able to connect to Yahoo server. fix I found on internet is add subdomain "cn" in the server name (account preferences). this "cn.scs.msg.yahoo.com" one worked for me. the default port of 5050 is same.

kopete is not able to detect webcam. It needs jasper conversion utility installed. I tried to to install libjasper-progs, but it seems replaced by another one in the ubuntu repository.
Here is the line that worked for me.

sudo apt-get install libjasper-runtime

Low volumne in ubuntu

2009-08-04T01:58:00.000-07:00

If you are struck up with low volumn in Ubuntu (even in 9.04)
do the following:

sudo apt-get install gnome-alsamixer

after installing gnome-alsamixer run it, it pops up a UI. Increase the PCM value towards maximum.

try the sound now, you got it full right?

Linux /Ubuntu tomcat -> too many open files

2009-07-29T06:00:00.000-07:00

Issue:
How do I set the maximum number of files allowed to be open on a system
scenario: I am running 3 war files of magnolia CMS on local tomcat, third one is not getting deployed properly because of the "too many files" opened.

Resolution:
The current setting for maximum number of open files can be viewed with the command:

ulimit -n

This number indicates the maximum number of files normal users (i.e. non-root) can have open in a single session. Note that for the root user, ulimit -n will sometimes output 1024 even after following the procedure to increase the maximum number of open files. This won't effect root's ability to open large numbers of files, as only normal users are bound by this value.

To increase the maximum number of open files beyond the default of 1024, two changes to the system may be necessary. In these examples, we will increase the maximum number of open files to the arbitrary value of 2048. All changes need to be made by the root user and users will need to log out and log back in before the changes will take effect.

1. Configure the system to accept the desired value for maximum number of open files Check the value in /proc/sys/fs/file-max to see if it is larger than the value needed for the maximum number of open files:

# cat /proc/sys/fs/file-max

If the value isn't large enough, echo an appropriate number into the variable and add the change to /etc/sysctl.conf to make it persistent across reboots. If the number is already larger than the value you wish to use, skip to step 2.

# echo 2048 > /proc/sys/fs/file-max

and edit /etc/sysctl.conf to include the line:

fs.file-max = 2048

2. Set the value for maximum number of open files In the file /etc/security/limits.conf, below the commented line that reads

#

add this line:

* - nofile 2048

This line sets the default number of open file descriptors for every user on the system to 2048. Note that the "nofile" item has two possible limit values under the header: hard and soft. Both types of limits must be set before the change in the maximum number of open files will take effect. By using the "-" character, both hard and soft limits are set simultaneously.

The hard limit represents the maximum value a soft limit may have and the soft limit represents the limit being actively enforced on the system at that time. Hard limits can be lowered by normal users, but not raised and soft limits cannot be set higher than hard limits. Only root may raise hard limits.

When increasing file limit descriptors, you may want to simply double the value. For example, if you need to increase the default value of 1024, increase the value to 2048 first. If you need to increase it again, try 4096, etc.

vmware keys not working on vmware server 2.0.1

2009-07-19T05:29:00.000-07:00

Keys like del, arrows are not working for me. ubuntu host and windows guest (xp,2003).
as per the forum suggestion, i have appended the following lines to /etc/vmware/config file (sudo credentials required on ubuntu).

xkeymap.keycode.108 = 0x138 # Alt_R
xkeymap.keycode.106 = 0x135 # KP_Divide
xkeymap.keycode.104 = 0x11c # KP_Enter
xkeymap.keycode.111 = 0x148 # Up
xkeymap.keycode.116 = 0x150 # Down
xkeymap.keycode.113 = 0x14b # Left
xkeymap.keycode.114 = 0x14d # Right
xkeymap.keycode.105 = 0x11d # Control_R
xkeymap.keycode.118 = 0x152 # Insert
xkeymap.keycode.119 = 0x153 # Delete
xkeymap.keycode.110 = 0x147 # Home
xkeymap.keycode.115 = 0x14f # End
xkeymap.keycode.112 = 0x149 # Prior
xkeymap.keycode.117 = 0x151 # Next
xkeymap.keycode.78 = 0x46 # Scroll_Lock
xkeymap.keycode.127 = 0x100 # Pause
xkeymap.keycode.133 = 0x15b # Meta_L
xkeymap.keycode.134 = 0x15c # Meta_R

well, a short cut suggestion was adding the following line alone.
xkeymap.keycode.135 = 0x15d # Menu

But I pasted both of them, not sure which one caused the fix.

FTP problem - Asci converted binary data

2009-07-07T00:49:00.000-07:00

Recently I have taken all my data backup from ubuntu (laptop) to windows xp (desktop) to re-install OS in laptop. But ironically, filezilla ftp client transferred all images, pdf files, doc files in asci format.

As I read on this, ascii format means, it strips off 8th bit of every byte (this bit used for a special purpose flag). Restoration may not be possible.

See a sample, how I lost lot of data, print screen image of jetspeed tutorial page (in pdf)

Eclipse Too many open files fix

2009-07-01T08:40:00.001-07:00

Solution:
Fix is adding the following entry (to increase the open file count)
in configuration/config.ini
osgi.bundlefile.limit=100

Problem:
I got this exception

ZoneInfo: /usr/share/javazi/ZoneInfoMappings (Too many open files)
ZoneInfo: /usr/share/javazi/ZoneInfoMappings (Too many open files)
!SESSION 2009-07-01 20:47:34.805 -----------------------------------------------
eclipse.buildId=M20090211-1700
java.version=1.6.0_0
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=linux, ARCH=x86, WS=gtk, NL=en_IN
Command-line arguments: -os linux -ws gtk -arch x86

!ENTRY org.eclipse.tptp.platform.examples 4 0 2009-07-01 20:47:39.058
!MESSAGE
!STACK 0
java.util.zip.ZipException: error in opening zip file
at java.util.zip.ZipFile.open(Native Method)
at java.util.zip.ZipFile.(ZipFile.java:131)
at java.util.zip.ZipFile.(ZipFile.java:148)
at org.eclipse.osgi.framework.util.SecureAction.getZipFile(SecureAction.java:251)
at org.eclipse.osgi.baseadaptor.bundlefile.ZipBundleFile.basicOpen(ZipBundleFile.java:81)
at org.eclipse.osgi.baseadaptor.bundlefile.ZipBundleFile.getZipFile(ZipBundleFile.java:94)
at org.eclipse.osgi.baseadaptor.bundlefile.ZipBundleFile.checkedOpen(ZipBundleFile.java:67)
at org.eclipse.osgi.baseadaptor.bundlefile.ZipBundleFile.getEntry(ZipBundleFile.java:239)
at org.eclipse.osgi.baseadaptor.BaseData.getEntry(BaseData.java:97)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.getEntry(AbstractBundle.java:1310)
at org.eclipse.core.internal.registry.osgi.EclipseBundleListener.getExtensionURL(EclipseBundleListener.java:120)
at org.eclipse.core.internal.registry.osgi.EclipseBundleListener.addBundle(EclipseBundleListener.java:158)
at org.eclipse.core.internal.registry.osgi.EclipseBundleListener.processBundles(EclipseBundleListener.java:90)
at org.eclipse.core.internal.registry.osgi.RegistryStrategyOSGI.onStart(RegistryStrategyOSGI.java:210)
at org.eclipse.core.internal.registry.ExtensionRegistry.(ExtensionRegistry.java:699)
at org.eclipse.core.runtime.RegistryFactory.createRegistry(RegistryFactory.java:59)
at org.eclipse.core.internal.registry.osgi.Activator.startRegistry(Activator.java:135)
at org.eclipse.core.internal.registry.osgi.Activator.start(Activator.java:56)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$2.run(BundleContextImpl.java:1009)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:1003)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:984)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:346)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:265)
at org.eclipse.osgi.framework.util.SecureAction.start(SecureAction.java:400)
at org.eclipse.core.runtime.internal.adaptor.EclipseLazyStarter.postFindLocalClass(EclipseLazyStarter.java:111)
at org.eclipse.osgi.baseadaptor.loader.ClasspathManager.findLocalClass(ClasspathManager.java:427)
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.findLocalClass(DefaultClassLoader.java:193)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findLocalClass(BundleLoader.java:370)
at org.eclipse.osgi.framework.internal.core.SingleSourcePackage.loadClass(SingleSourcePackage.java:33)
at org.eclipse.osgi.framework.internal.core.MultiSourcePackage.loadClass(MultiSourcePackage.java:31)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findClassInternal(BundleLoader.java:443)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:399)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:387)
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:87)
at java.lang.ClassLoader.loadClass(ClassLoader.java:268)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:336)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:186)
at org.eclipse.equinox.internal.app.Activator.start(Activator.java:55)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$2.run(BundleContextImpl.java:1009)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:1003)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:984)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:346)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:265)
at org.eclipse.osgi.framework.util.SecureAction.start(SecureAction.java:400)
at org.eclipse.core.runtime.internal.adaptor.EclipseLazyStarter.postFindLocalClass(EclipseLazyStarter.java:111)
at org.eclipse.osgi.baseadaptor.loader.ClasspathManager.findLocalClass(ClasspathManager.java:427)
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.findLocalClass(DefaultClassLoader.java:193)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findLocalClass(BundleLoader.java:370)
at org.eclipse.osgi.framework.internal.core.SingleSourcePackage.loadClass(SingleSourcePackage.java:33)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findClassInternal(BundleLoader.java:443)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:399)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findClass(BundleLoader.java:387)
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:87)
at java.lang.ClassLoader.loadClass(ClassLoader.java:268)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:336)
at org.eclipse.core.internal.runtime.PlatformActivator.startAppContainer(PlatformActivator.java:47)
at org.eclipse.core.internal.runtime.PlatformActivator.start(PlatformActivator.java:32)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$2.run(BundleContextImpl.java:1009)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:1003)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:984)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:346)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.resume(AbstractBundle.java:355)
at org.eclipse.osgi.framework.internal.core.Framework.resumeBundle(Framework.java:1074)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:616)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.incFWSL(StartLevelManager.java:508)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.doSetStartLevel(StartLevelManager.java:299)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:489)
at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:211)
at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:321)

(eclipse:30567): Pango-WARNING **: error opening config file '/etc/pango/pangorc': Too many open files

(eclipse:30567): Pango-WARNING **: error opening config file '/home/harinath/.pangorc': Too many open files

(eclipse:30567): Pango-CRITICAL **: No modules found:
No builtin or dynamically loaded modules were found.
PangoFc will not work correctly.
This probably means there was an error in the creation of:
'/etc/pango/pango.modules'
You should create this file by running:
pango-querymodules > '/etc/pango/pango.modules'
Fontconfig error: Cannot load default config file

(eclipse:30567): Pango-WARNING **: failed to choose a font, expect ugly output. engine-type='PangoRenderFc', script='latin'

Building portlets with Jetspeed 2.0

2009-06-23T23:40:00.000-07:00

If you are looking at open source based portlet 2.0 development, then a good starting point could be jetspeed 2.0

You can create a maven project with the custom archetype of jetspeed by following similar syntax as below.

mvn org.apache.maven.plugins:maven-archetype-plugin:2.0-alpha-4:generate -DarchetypeGroupId=org.apache.portals.jetspeed-2 -DarchetypeArtifactId=jetspeed-archetype -DarchetypeVersion=2.2.0 -DartifactId=hello -Dpackage=com.springboard -DgroupId=com.springboard -Dversion=1.0-SNAPSHOT

You might come across an exception something like this

Caused by: java.lang.ClassNotFoundException: org.apache.jetspeed.tools.db.serializer.JetspeedSerializerApplicationImpl
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at org.codehaus.classworlds.RealmClassLoader.loadClassDirect(RealmClassLoader.java:195)
at org.codehaus.classworlds.DefaultClassRealm.loadClass(DefaultClassRealm.java:255)
at org.codehaus.classworlds.DefaultClassRealm.loadClass(DefaultClassRealm.java:274)
at org.codehaus.classworlds.RealmClassLoader.loadClass(RealmClassLoader.java:214)
at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:242)
at org.apache.jetspeed.maven.utils.Serializer.execute(Serializer.java:73)
... 19 more
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 9 seconds

the fix is that db plugin dependency is missing, I got rid of it by adding the dependency.

org.apache.portals.jetspeed-2
jetspeed-db-tools
${org.apache.portals.jetspeed.version}

Do not forget to add repository (might be optional).

central
central
http://repo1.maven.org/maven2

Unix command to uncompress

2009-03-17T21:42:00.001-07:00

Compressed UNIX tar archives (typically tar.gz or .tgz extensions) can be extracted in a single command. This is faster and works with less disk space available.

To extract the compressed tar archive target.tar.gz into the current working directory with:

gzip -dc target.tar.gz | tar xf -

If the file was compressed with bzip2 (i.e., .tar.bz2), you can substitute the bzip2 command for gzip.

If the archive was compressed with the UNIX compress command (archive ending in .Z) you can use:

zcat target.tar.Z | tar xf -

fix for sql injection

2009-02-24T08:43:00.000-08:00

SET NOCOUNT ON

DECLARE @stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @object_id INT

SET @stringToFind = '<>'

SET @stringToReplace = ''

DECLARE TAB_CURSOR CURSOR FOR
SELECT B.NAME AS SCHEMANAME,
A.NAME AS TABLENAME,
A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B
ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = 'U'
ORDER BY 1

OPEN TAB_CURSOR

FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME
FROM sys.columns A
INNER JOIN sys.types B
ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id
AND IS_COMPUTED = 0
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')

OPEN COL_CURSOR

FETCH NEXT FROM COL_CURSOR
INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'

SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''

EXEC( @sqlCommand + @where)

SET @count = @@ROWCOUNT

IF @count > 0
BEGIN
PRINT @sqlCommand + @where
PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
PRINT '----------------------------------------------------'
END

FETCH NEXT FROM COL_CURSOR
INTO @columnName
END

CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR

FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
END

CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR

Apache MyFaces Trinidad

2009-02-04T20:56:00.000-08:00

NOTE: some how all the xml related syntax is removed while rendering the post in blogspot.

My team is exploring the use of JSF into one of our applications.

We had smooth sail until we decided to use trinidad components. We wanted to have out of box support for the features we wanted to build into application and tomahawk and trinidad seem to be the way to go.
Some of the dependencies we had in our project POM file(related with myfaces and trinidad) are

org.apache.myfaces.core
myfaces-api
1.2.2
compile

org.apache.myfaces.core
myfaces-impl
1.2.2
compile

org.apache.myfaces.tomahawk
tomahawk
1.1.8
runtime

javax.servlet
jstl

org.apache.myfaces.core
myfaces-api

org.apache.myfaces.core
myfaces-impl

jstl
jstl
1.2
runtime

com.sun.facelets
jsf-facelets
1.1.11

org.apache.myfaces.trinidad
trinidad-api
1.2.1

org.apache.myfaces.trinidad
trinidad-impl
1.2.1

org.apache.myfaces.trinidad
trinidad-build
1.2.1

Contents of web.xml

contextConfigLocation

classpath:applicationContext.xml

org.springframework.web.context.ContextLoaderListener

org.springframework.web.context.request.RequestContextListener

org.jboss.jbossfaces.WAR_BUNDLES_JSF_IMPL

true

facelets.LIBRARIES

/WEB-INF/tomahawk.taglib.xml

javax.faces.DEFAULT_SUFFIX

.xhtml

facelets.DEVELOPMENT

true

javax.faces.CONFIG_FILES

/WEB-INF/backoffice-config.xml

javax.faces.STATE_SAVING_METHOD

client

org.apache.myfaces.NUMBER_OF_VIEWS_IN_SESSION

org.apache.myfaces.SERIALIZE_STATE_IN_SESSION

true

org.apache.myfaces.COMPRESS_STATE_IN_SESSION

true

org.apache.myfaces.ALLOW_JAVASCRIPT

true

org.apache.myfaces.DETECT_JAVASCRIPT

false

org.apache.myfaces.PRETTY_HTML

true

org.apache.myfaces.AUTO_SCROLL

true

org.apache.myfaces.SECRET

NzY1NDMyMTA=

org.apache.myfaces.VALIDATE

true

org.apache.myfaces.READONLY_AS_DISABLED_FOR_SELECTS

true

org.apache.myfaces.ADD_RESOURCE_CLASS

org.apache.myfaces.renderkit.html.util.DefaultAddResource

org.apache.myfaces.RESOURCE_VIRTUAL_PATH

/faces/myFacesExtensionResource

org.apache.myfaces.CHECK_EXTENSIONS_FILTER

true

javax.faces.PARTIAL_STATE_SAVING_METHOD

false

extensionsFilter

org.apache.myfaces.webapp.filter.ExtensionsFilter

uploadMaxFileSize

100m

uploadThresholdSize

100k

extensionsFilter

*.jsf

extensionsFilter

/faces/*

org.apache.myfaces.webapp.StartupServletContextListener

Faces Servlet

javax.faces.webapp.FacesServlet

Faces Servlet

*.jsf

index.jsp

index.html

trinidad

org.apache.myfaces.trinidad.webapp.TrinidadFilter

trinidad

Faces Servlet

resources

org.apache.myfaces.trinidad.webapp.ResourceServlet

resources

/adf/*

org.apache.myfaces.trinidad.ALTERNATE_VIEW_HANDLER

com.sun.facelets.FaceletViewHandler

facelets.VIEW_MAPPINGS

*.xhtml

faces-config.xml content is

org.springframework.web.jsf.DelegatingVariableResolver

org.apache.myfaces.trinidad.core

Eclipse design time didnt handle trinidad tags properly (no UI rendering for tr components),but it worked fine.

What I needed was to select rows in table using radio button option. It looks simple functionality but always it created problem. I remember doing some custom java script implementation to handle it, then we had difficult time even in .Net development. Now in java also we spent few days in fixing some very basic issue.
It is not technology which need to be blamed all the time. if we started with the formal instructions, it would have been very easy for us.

VMWare Server 2.0 on Ubuntu - Guest windows 2003 server

2009-02-04T00:48:00.000-08:00

I had a need to refresh my M$ Knowledge and tried to do that.

As my laptop (inspiron 1525) runs with Ubuntu, I have downloaded vmware server 2.0 (available for free from vmware site). And then installed windows 2003 on it.

Few issues and how I resolved it.

1. default windows desktop size was very small.
fix: increase the resolution, and it made my windows desktop to use full laptop screen.
2. Login problem:
I was not able to send Ctrl+Alt+Del signal after installing it, but then some one on internet suggested to try
Ctrl+Alt+ PrintScrn combination, it worked.
3. Keyboard problem: Del button and arrow keys were not working. probably some other keys were too not working. down arrow was behaving as if it is the windows button, and backspace was working as del button. Yes I had no way of backspacing my text.

Fix: I followed this link and found the fix working.

I tried solution 3 mentioned in the blog post.
i.e login as root and execute 'echo "xkeymap.nokeycodeMap = true" >> ~/.vmware/config'
It worked for me. Yes it is not the typical sudo. sudo didnt work for me (also mentioned in the article). Opened new shell and logged in as root. then executed this command.

Surprising, it worked. now I am very much comfortable in the vmware win2k3 image.

Yes, as it is free ware, vmware might have left some known issues along with it. But then I am able to solve the issues in front of me.

Now, I can stick to Ubuntu and occassionally use windows by vmware server web console for specific needs.

I am happy.

JOSSO 1.8

2009-01-21T02:17:00.000-08:00

Today I tried playing with JOSSO, I spent around 4 to 5 hours. Wanted to setup JOSSO with NTLM authentication, but not successful yet.

Here is the log of what I have done.

Follow instructions from
http://www.josso.org/confluence/display/JOSSO1/Quick+Start
there is some confusion here, as the article talks of using commands to install josso, but the downloaded file is not having the stuff related with the documentation.

Download Josso 1.8 release from sourceforge.net download lcoation.

http://sourceforge.net/project/showfiles.php?group_id=116854&package_id=129496&release_id=653379

you can download the .tgz file which is around 42MB. this contains josso-gsh shell, which can be used to deploy the war file (documents on josso confluence site use this shell to deploy, it took a while for me to identify which file has this gsh file)
gateway install --target "D:/servers/Tomcat 6.0" --platform tc60

Either you can do this or download the tomact version and use it directly/build from source and use it.

in case if you need any further information best option is to google and if the josso site pages are not coming properly (in my case its getting timedout) then read the google cache.

As the 1.8 release happened almost a week back, we might face problems in setting up things propely, more help might come into internet in the comming weeks.

FREE Cheat Sheets for Developers

2009-01-15T20:34:00.000-08:00

FREE Cheat Sheets for Developers

Open source alternatives for visio

2009-01-02T03:38:00.000-08:00

As I am moving away from use of microsoft softwares, I was trying to find a proper alternative for visio, the famous microsoft modeling tool.

There seem to be 2 of them as proper alternatives(dia and drawing of open office), but dia leads the way because of the similarity with visio and certain special objects which make it better in casual designing. http://projects.gnome.org/dia/.

Check the URL for more details. It is a wondeful contribution by Free Software Foundation.

Maven

2008-12-31T03:34:00.000-08:00

Maven comes really handly when it comes to managing project structure, building code etc. Its flexible dependency management is the best feature I go for.

In case if you want to start with maven, and looking for a starting point, Download latest maven (comes as a zip file, unzip it) and set your PATH environment variable to bin folder.
Now type "mvn archetype:generate" it lists out possible project types available. the follow up steps are self explanatory.

If you are looking for a specific project type (like a simple web application with springs configured etc), you might need to google for it. With little bit of effort you will get to find proper archtypes.

One problem I faced with maven POM (Project Object Model) files is with specific dependencies (jar files) which are not at all available on internet. I resolve them by creating similar structure locally and getting the near best possible jar file (yes I create folder strtucture, file name as required).

RESTful web services

2008-12-30T00:30:00.000-08:00

RESTful services are a nice way of integrating how the web interacts with Humans and Machines in the simplest format. Though developer community doesn't feel any pain in building standard web services using SOAP, WSDL over HTTP, there is lot of complexity behind implementing web services.

RESTful web services offer simplest solution, making same HTTP protocol used for web services as well.

There are 2 important aspects in RESTful services.

1. Web method
2. Scope

When we use standard web services, web service method name is very important. RESTful removes this overhead. Just use the standard HTTP GET, POST,PUT,DELETE etc methods for any transaction.

Scope of the request is similar to the query string logic of HTTP. Query string must be able to define the scope of a client request, on what he needs to be done.

I will post more about it as and when I get time.

Sify broadband from Ubuntu/Kubuntu

2008-12-27T01:17:00.000-08:00

Here are the instructions to install sify broadband client in Kubuntu/Ubuntu 8.10

Download the client from the location http://202.144.65.70:8090
When I clicked the link for linux, it opened up a PDF file with the details. look for the li. for ux client link http://202.144.65.70:8090/bbandclient_v30/sify_bbclient-3.0.tar.gz

then execute the following commands (after downloading the file).

gunzip sify_bbclient-3.0.tar.gz
tar -xvf sify_bbclient-3.0.tar

From the exploded location, execute install.sh

execute sudo ./install.sh

gksudo ln -s /usr/lib/libcrypto.so /usr/lib/libcrypto.so.4

gksudo ln -s /usr/lib/libssl.so /usr/lib/libssl.so.4

now, you can execute "sifyconnect" command from terminal and it pops up sify bb client.

Make sure you call sify customer care to register your system/laptop physical address so as you can connect without any problem.

killing oracle session

2007-06-02T13:41:00.000-07:00

select s.username, s.osuser, s.sid, s.serial#, p.spid
from v$session s,v$process p
where s.paddr = p.addr
and s.username is not null;

This will return the database username, the operating system username, the session id, the serial number, and the system process id (SPID). Then, a DBA user can issue the following (using the SID and SERIAL# information from the previous query):

ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM KILL SESSION '9,203';

disabling browser back button

2007-02-14T23:53:00.000-08:00

Its a very common question that many of the developers need often.

I am too interested how we can do this.
Oftent he problem is that after logout, if user clicks back in the browser and is able to see content, this is not expected. there will be many advantages if we can disable back button especially when we require it.

Cache control:
one of the best and easiest way to handle issues popup because of clicking back button is to set no cache for the pages.

setting no cache:

You can use client-side code to force the user's browser to not cache a Web page.

<html>
<head>
<meta http-equiv="Expires" CONTENT="0">
<meta http-equiv="Cache-Control" CONTENT="no-cache">

<meta http-equiv="Pragma" CONTENT="no-cache">
</head>

There are a couple things to keep in mind when using the above method to force a browser to not
cache a Web page:

Pragma: no-cache prevents caching only when used over a secure connection.
A Pragma: no-cache META tag is treated identically to Expires: -1 if used in a
non-secure page. The page will be cached but marked as immediately expired.
Cache-Control META HTTP-EQUIV tags are ignored and have no effect in Internet
Explorer versions 4 or 5.

You can use both in your code. I tried this but this was not the solution because it did not work in all the
browsers so I guess if one had an intranet environment where there was some control in place then they could
use this method.

Javascript
My next area of research focused on the various rewiring the back button suggestions. An article by TJ
Sylvester, Rewiring the Back Button, makes interesting reading but I
noticed that when one clicks back it does not indeed take you to the page you entered the data but if I
clicked back twice it does and we would not want that too. Basically a determined user could always figure
out a way to circumvent the preventative measures.

Another way to "disable the back button" is to use client-side JavaScript code to open a new window that
doesn't have the toolbar. (This makes it harder (not impossible) for the user to go back to the previous
page.)
Another, more failsafe approach (although quite annoying) is, when a form is submitted, to open a new window
and close the window that the form existed in. I didn't give this method serious thought because
I would not like my site opening up a new window everytime a user submitted a form.

Next I examined the possibility of adding client-side JavaScript code on the page that we do not want to let
the user return to. Such JavaScript code could be used to have the effect of hitting the forward button, which would
counter any action by a user clicking the back button. The JavaScript code to accomplish this can be seen below:



<script language="JavaScript">
<!--
  javascript:window.history.forward(1);
//-->
</script>

Again this is workable but it is far from the best way.
I was then given the suggestion to use location.replace to navigate form one page to another.
What this does is it replaces the current history entry with the new page so only one page will be maintained
in the history and the back button will never get enabled. This is, I guess, what a lot of people are looking
for, but again this would not be the best answer in all cases.

For one thing you will have to use client side script for this. For an anchor tag this will be easy by just
using:


<A HREF="PageName.htm" onclick="javascript:location.replace(this.href); event.returnValue=false; ">No back button when you do this.</A>

[Try out the above link!]

The above technique has its disadvantages: simply using Response.Redirect will not work, since,
each time a user jumps from one page to another, you need to clear out the location.history field
through client-side code. Also, keep in mind that this will just remove the last history entry, not all
of them. Go ahead and click the above hyperlink, you will be taken to a simple HTML page. Try clicking the back button
and you will notice you will be taken to the page you were visiting before you came to this page! (Assuming,
of course, you have client-side JavaScript code enabled in your browser.)

After my exhaustive search I found that there is still no way of truly disabling the back button for all cases.
All the methods I discussed in this article will, with varying degrees of success, prevent the
user from viewing the previous page, but they, of course, all have their limitations. The best solution involves
a mixture of both client-side and server-side script; regardless, there is no way to completely disable the back
button

JSP
%
Response.Buffer = True
Response.ExpiresAbsolute = Now() - 1
Response.Expires = 0
Response.CacheControl = "no-cache"
%>

ASP.NET

Response.CacheControl = "no-cache";

Proxy Server Caching-

Response.CacheControl = "private";

It disables the proxy server caching and page is cached on local machine.

Response.CacheControl = "public";

Proxy server cache is enabled.

Users request pages from a local server instead of direct from the source.

SQL server,Date formats

2007-02-02T05:18:00.000-08:00

http://www.sql-server-helper.com/tips/date-formats.aspx

Setting default browser

2007-02-01T22:59:00.000-08:00

To make Internet Explorer the default web browser,

In Internet Explorer -> View menu -> Internet Options -> Programs
Enable
'Internet Explorer should check to see whether it is the default web browser'

To make firefox as your default browser

Go to Tools -> Options.
under general tab, check the check box for "Firefox should check to see if it is the default browser when starting".

or you have a button "check now" if you click that you will get an alert if firefox is not the default browser. if you click yes, then it will become default browser.

Browser detection

2007-01-18T23:32:00.000-08:00

Using the navigator object to detect client's browser
Until one browser remains standing on the web (if ever), browser detection will continue to be part of any good JavaScripter's life. Whether you're gliding a div across the screen or creating an image rollover, it's fundamental that only relevant browsers pick up on your code. In this tutorial we'll probe the navigator object of JavaScript, and show how to use it to perform browser detection, whether the subject is Firefox, Internet Explorer 6, or even Opera 8.
The navigator object
The navigator object was conceived back in the days when Netscape Navigatorreined supreme. These days it serves as much as an irony of NS's diminished market share as way of probing browser information.
The navigator object of JavaScript contains the following core properties:
Properties
Description
appCodeName
The code name of the browser.
appName
The name of the browser (ie: Microsoft Internet Explorer).
appVersion
Version information for the browser (ie: 4.75 [en] (Win98; U)).
cookieEnabled
Boolean that indicates whether the browser has cookies enabled. IE4 and NS6+.
language
Returns the default language of the browser version (ie: en-US). NS4 and NS6+ only.
mimeTypes[]
An array of all MIME types supported by the client. NS4 and NS6+ only. Array is always empty in IE.
platform[]
The platform of the client's computer (ie: Win32).
plugins
An array of all plug-ins currently installed on the client. NS4 and NS6+ only. Array is always empty in IE.
systemLanguage
IE4+ property that returns the default language of the operating system. Similar to NS's language property.
userAgent
String passed by browser as user-agent header. (ie: Mozilla /4.0 (compatible; MSIE 6.0; Windows NT 5.1))
userLanguage
IE4+ property that returns the preferred language setting of the user. Similar to NS's language property.
Let's see exactly what these properties reveal of your browser:
with (document){
write("AppCodeName: "+navigator.appCodeName+"")
write("AppName: "+navigator.appName+"")
write("AppVersion: "+navigator.appVersion+"")
write("UserAgent: "+navigator.userAgent+"")
write("Platform: "+navigator.platform+"")
}
AppCodeName: MozillaAppName: Microsoft Internet ExplorerAppVersion: 4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727)UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727)Platform: Win32
At a glance
You probably think you have a very solid idea now of how to utilize the navigator object to detect your client's browser type. At its most basic form, the following two properties are used:
navigator.appNamenavigator.appVersion
For example:
//detect Netscape 4.7+
if (navigator.appName=="Netscape"&&parseFloat(navigator.appVersion)>=4.7)
alert("You are using Netscape 4.7+")
However, depending on the browser you're trying to detect, you'll find the above two properties too limiting.
Detecting Firefox 1.0+
Take for example, Firefox 1.0+. It shares the same "appName" value as older Netscape browsers, which is "Netscape." The appVersion value returned is also out of wack, which is "5." So we need to look to another property, which turns out to be "UserAgent." For Firefox 1.04 for example, its userAgent property reads:
UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.8) Gecko/20050511 Firefox/1.0.4
Ah ha, the text at the end of the string apparently contains the information we want. Based on this new found discovery, the below code detects if you're using Firefox 1.0+:
if(navigator.userAgent.indexOf("Firefox")!=-1){
var versionindex=navigator.userAgent.indexOf("Firefox")+8
if (parseInt(navigator.userAgent.charAt(versionindex))>=1)
alert("You are using Firefox 1.x or above")
}
The code assumes that all Firefox versions contain the string "Firefox/" immediately followed by the version number (ie "1").
Detecting IE 5.5+
Detecting IE using the navigator object also poses a similar problem as Firefox, since its "navigator.appVersion" property contains more than just the version number of IE:
4.0 (compatible; MSIE 5.5; Windows 98; Hotbar 3.0)
If we were to use parseFloat to try and extract out the browser version in "navigator.appVersion", we'd get 4.0, instead of the correct number (5.5). This is due to the way parseFloat works- by returning the first number it encounters. So, how does one go about getting the version number in IE? As with Firefox, it really comes down to how you wish to approach the issue. Lets stick with using the "navigator.appVersion" property this time, though you can certainly use "navigator.userAgent" as well:
//Detect IE5.5+
version=0
if (navigator.appVersion.indexOf("MSIE")!=-1){
temp=navigator.appVersion.split("MSIE")
version=parseFloat(temp[1])
}
if (version>=5.5) //NON IE browser will return 0
alert("You're using IE5.5+")
In the above, string.split() is first used to divvy up navigator.appVersion into two parts, using "MSIE" as the separator:
4.0 (compatible; MSIE 5.5; Windows 98; Hotbar 3.0)
As a result temp[1] contains the part after "MSIE", with the browser version appearing first. Doing a parseFloat() on temp[1] therefore retrieves the browser version of IE.
The Opera pitfall
Using the navigator object to screen for Opera is even a little more complex, as Opera by default identifies itself as IE, with Opera 8 identifying itself as IE6, inside "navigator.userAgent." The rationale for this is so that scripts screening for IE would also allow Opera through, so that Opera will render these pages properly. Lets take a look at what the userAgent of Opera 8.5 returns depending on what it is set to identify as:
As IE6: Mozilla/4.0 (compatible; MSIE 6.0; Windows XP) Opera 8.5 [en]As Moz5: Mozilla/5.0 (Windows XP; U) Opera 8.5 [en]As Opera: Opera/8.5 (Windows XP; U) [en]
"What's the problem? Opera 8.5 appears in each case." you may say. Well, look closer, and notice how when Opera is set to identify as itself, the Opera string returned is slightly different ("Opera/8.5" versus "Opera 8.5"). So ironically enough, the expressionif (navigator.userAgent.indexOf("Opera 8.5")!=-1)
will fail if the user has their browser set to identify as Opera, but not if any other browser!
Despite this oddity, the code to detecting Opera need not be any different than the one used to detect Firefox:
if(navigator.userAgent.indexOf("Opera")!=-1){
var versionindex=navigator.userAgent.indexOf("Opera")+6
if (parseInt(navigator.userAgent.charAt(versionindex))>=8)
alert("You are using Opera 8 or 9")
}
This also assumes that all Opera versions contain the string "Opera" immediately followed by either a space or "/", then the version number (ie "8"). And since it only looks at the first number within the version, it can only detect up to Opera 9, not 10 for example.

Using object detection to sniff out different browsers
Since different browsers support different objects, you can use Object Detection as a quick though less than infallible way of detecting various browsers. For example, since only Opera supports the property "window.opera", you can use that to instantly separate an Opera browser from the herd. Here are a few sample test cases, and what they *imply* about the browser running it:
Example objects used to "rough" detect a particular browser
Scheme
Description
document.getElementById
Detects modern browsers in general, which covers IE5+, Firefox1+, and Opera7+
window.getComputedStyle
Detects Firefox1+ and Opera 8+
Array.every
Detects Firefox1.5+ (method detection)
window.Iterator
Detects Firefox2+
document.all
Detects IE4+
window.attachEvent
Detects IE5+
window.createPopup
Detects IE5.5+
document.compatMode && document.all
Detects IE6+
window.XMLHttpRequest
Detects IE7, Firefox1+, and Opera8+
window.XMLHttpRequest && document.all
Detects IE7. Note: Will fail if visitor explicitly disables native xmlHTTPRequest support (under Toolbar-> Internet Options-> Advanced)
document.documentElement && typeof document.documentElement.style.maxHeight!="undefined"
Another way of detecting IE7 that is more reliable than the above.
window.opera
Detects Opera (any version).
* Since Opera by default also identifies itself as IE (apart from Opera), with support for many of IE's proprietary objects, the IE detection schemes above will also return true for Opera. Use "window.opera" in combination to filter out Opera browsers.
It's important to mention that object detection's chief purpose is to help you detect within your script whether the browser supports a particular object/method/property before using it, not browser detection. As the later it may be convenient over probing the Navigator object, but is only as reliable as your understanding of which objects are supported in which browsers. In other words, it's not a 100% reliable way of sniffing out the user's browser. Having said that, the below uses object detection to test for IE7:

Again object detection is really about feature detection. It detects whether your browser supports the feature your script intends to use and manipulate. For the lazy, that will substitute for browser detection just fine!

Task Manager menu /tabs disappear

2007-01-18T21:13:00.000-08:00

last one or 2 days when ever i open task manager to kill some process, it just shows up without any menu bar/ tabs

i thought it could be a virus effect or something, but when i searched on net it gave a solution.

SYMPTOMS
loadTOCNode(1, 'symptoms');
When you start Task Manager, the menu bar and tabs may not be visible.

Back to the top
CAUSE
loadTOCNode(1, 'cause');
This behavior can occur if Task Manager is running in Tiny Footprint mode. When you double-click the empty space in the border around the tabs, Task Manager switches to this mode.

Back to the top
RESOLUTION
loadTOCNode(1, 'resolution');
To switch Task Manager to its normal display mode, double-click the top border of the window.

Back to the top
WORKAROUND
loadTOCNode(1, 'workaround');
To work around this behavior, perform the following steps:
1.
Click Start, and then click Run.
2.
Type taskmgr.exe.
3.
Hold down CTRL+ALT+SHIFT at the same time, and while holding them down press ENTER.

Reference :http://support.microsoft.com/kb/193050

Service unavailable

2007-01-18T03:45:00.000-08:00

Hi guys,

Service unavailable error in IIS 6 (Windows 2003 server) is really a painful one.

It is occurring because the application pool supporting your asp.net application has just crashed.

One way to get away from this error is to increase the error condition.

Set the health condtions as shown in the image. increase threshold of error count.
I am not sure if this is the right solution, but this worked for me.

Your HTML and javascript source

2007-01-18T03:43:00.000-08:00

I found this site to be informative.
http://www.yourhtmlsource.com/

You can learn

2007-01-18T02:35:00.000-08:00

check this you can learn site. got lot of tips that might help us

System internals

2007-01-11T08:55:00.000-08:00

http://www.microsoft.com/technet/sysinternals/default.mspx

The Sysinternals web site was created in 1996 by Mark Russinovich and Bryce Cogswell to host their advanced system utilities and technical information. Microsoft acquired Sysinternals in July, 2006.

client application - intermittently receiving an error

2007-01-11T08:33:00.000-08:00

A client application may intermittently receive an error message when a client application tries to create a COM+ component
View products that this article applies to.
Article ID : 911359
Last Review : January 20, 2006
Revision : 2.0

SYMPTOMS
When a client application tries to create a Microsoft COM+ component, the client application may intermittently receive an error message. Microsoft C++ applications may receive the following error message:
E_INVALIDARG: "The parameter is incorrect" (0x80070057/-2147024809)
Microsoft Visual Basic 6.0 applications may receive the following error message:
Run-time error '5': "Invalid procedure call or argument" (0x800a0005/-2146828283)
Client applications that are built on the Microsoft .NET Framework may receive the following error message:
System.ArgumentException: "The parameter is incorrect." at System.Runtime.Type.CreateInstanceImpl(Boolean publicOnly) at System.Activator.CreateInstance(Type type, Boolean nonPublic) (with _HResult = 0x80070057/-2147024809)
The COM+ application will typically function without error for some time immediately after the COM+ application is opened. The problem occurs intermittently, but increases in frequency over time until the application eventually fails on every activation request.

Note When the client application is running on a remote computer, the following error message is logged in the system event log on the client computer:
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10006
Description:
DCOM got error "One or more arguments are invalid " from the computer SERVERNAME when attempting to activate the server: GUID

Back to the top

CAUSE
This problem occurs because the "COM initialization count" for a thread is incremented and decremented when the CoInitialize function and the CoUninitialize function are called. When this count reaches zero after you call the CoUninitialize function, COM will be uninitialized on the thread. When a COM+ STA ThreadPool thread is uninitialized, the thread's apartment activator is destroyed. The next activation request that is routed to this thread will fail with the E_INVALIDARG error message. This problem occurs because the thread apartment activator is no longer available. Only activation requests that are routed to the particular uninitialized thread will fail. As more COM+ ThreadPool threads become uninitialized, a larger percentage of requests will fail. When all COM+ ThreadPool threads become uninitialized, all requests will fail. If the process is restarted, the problem will recover for some time. However, the cycle will be repeated.
Back to the top

RESOLUTION
To resolve this problem, remove the CoInitialize calls and the CoUninitialize calls from the affected COM DLL. A COM DLL that exposes COM components and is loaded through COM calls from a client should not call the CoInitialize function or the CoUninitialize function on any thread that is used to load or to start the DLL. These threads are owned by the client application, by the COM runtime, or by the COM+ runtime. Only the COM DLL should call these APIs on additional threads that were explicitly created by the COM DLL. However, it is a common bug for COM DLLs to call the CoInitialize function and the CoUninitialize function in response to attach events and detach events in the DllMain function.

Note It is correct for a standard Win32 DLL that is not loaded through COM to call these APIs if the standard Win32 DLL plans to use COM APIs. However, these functions should not be called from the DllMain function.

For more information about the CoInitialize function, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/com/html/0f171cf4-87b9-43a6-97f2-80ed344fe376.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/com/html/0f171cf4-87b9-43a6-97f2-80ed344fe376.asp)
For more information about the DllMain function, visit the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/dllmain.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/dllmain.asp)
Note One Microsoft component that contains this bug is the Cdo.dll component. This component is not supported in multithreaded environments. Instead, we recommend that you use the Cdosys.dll component or the Cdonts.dll component. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
247288 (http://support.microsoft.com/kb/247288/) CDO applications are not supported in MTS or COM+

Service Unavailable - Application pool crash

2007-01-11T08:30:00.000-08:00

HOWTO: Understand and Diagnose an Application Pool Crash
Problems statements similar to the following questions pop up all the time on various IIS newsgroups, and the user usually claims that they have either seen (or not seen) many posts that look like theirs, and never any concrete solutions. I am going to try and explain the whole thought process, why things work the way it does, as well as useful next steps.

Question:
#1

Our production server has recently started experiencing AppPool crashes. These seem to occur sporadically. sometimes three times a day, sometimes not for a couple of days. The error manifests itself to the client as "Service Unavailable". In the system event log we see the following:
A process serving application pool 'DefaultAppPool' suffered a fatal communication error with the World Wide Web Publishing Service With error number: 8007006d

At the same time (but not always) we see this error in the Application Log
"Faulting application w3wp.exe, version 6.0.3790.0, faulting module kernel32.dll, version 5.2.3790.0, fault address 0x000249d3"

The application does usually start working again after anywhere between 2 and 15 minutes (although no worker process restarts or recycles appear in our perf logs).

This problems seems to have started occuring following the latest MS patches being applied to our server. It may just be a coincidence though.

We are running Windows Server 2003 Standard. We are about to apply SP1 in an attempt to solve this problem, but I wanted to find out if anyone else has had this problem and what the solution was. I have seen several similarish posts but nothing concrete as a solution.

I have seen this article (http://support.microsoft.com/Default.aspx?id=885654) but it doesn't quite match our situation as we are not running as a domain controller. We haven't yet tried running registry monitor as I wanted to find out if error 8007006d always equates to a registry permission problem?? before installing
3rd party freeware onto our production server.

Any help is much appreciated

#2

There is a similar question posted today, but the event ID is different

I see the following error anywhere from 2-3 times a day on Windows 2003 server.
Event ID- 1009, a process serving application pool 'Q' terminated unexpectedly. The process ID was 'xxxx' . The process code was 'xxx'

So far we have not noticed anything on the application side, but once in a while W3WP.EXE starts using up all the CPU and the server comes to a halt. This is not associated with the application pool terminating, but wondering if it is conntributing it.

I have applied SP1 for Windows 2003, that did not make any difference.

Any help is apprciated.

#3

Hello Together!

I've got a problem with my Windows Server 2003 SP 1 Web-Edition

If I look in the event Viewer, I allways find the following Error:

Event Type: Error
Event Source: Application Error
Event Category: (100)
Event ID: 1000
Date: 28.08.2005
Time: 22:00:26
User: N/A
Computer: {removed by the author}
Description:
Faulting application w3wp.exe, version 6.0.3790.1830, faulting module , version , fault address 0x.

Why does this error occur? What is it and what can I do to resolve this problem?

Please help me

Answer:
You are looking at what is commonly refered to as a "crash" or "access violation" on the server. To be clear, this is different from a "hang" on the server, though the web browser may appear to "hang" or the browser's icon motions for some time (the length of time depends on various network connection timeout periods as well as whether the server-side connection stays open or closed) before reporting some random error response relating to missing server, DNS, or other service error.

Crash vs. Hang
What distinguishes between a crash and a hang on the server? Ok, for the astute reader in the back, just be quiet with this simplification. We want folks to to internalize and understand the issue, not regurgitate dry documentation. :-)

Practically speaking, a crash is something that will simply wipe out its host process; this will stop whatever server side work and response generation that the process was supposed to perform. Now, prior to IIS6, this same process also held the connection open, so as soon as a user-mode crash happens, IIS will go down and the client browsers see a disconnected connection and usually report some sort of service disconnected/not-found error. With IIS6 Worker Process Isolation Mode, HTTP.SYS holds the connections in kernel-mode, so regardless if user code running in w3wp.exe crashes, the connection stays connected and IIS starts up a new process to handle future requests - so browser clients will NOT see a disconnection for a crash. Unfortunately, the request caught by the crash cannot be re-executed (suppose that request was a bank withdraw...) unless you implemented Transaction semantics, so any unsent response is lost.

A hang, on the other hand, will usually keep its host process around but the hang prevents any real work from being done. There are many possible ways to do this - user code can be waiting for a lock that never gets released, either because it was leaked or there was a logical deadlock or livelock, or it could be in a clever infinite loop, etc. Once again, any unsent response will never happen once your code gets hung.

As you can see, from the perspective of the client browser, both a crash and a hang on the server can prevent a complete HTTP response from being sent back, so they can LOOK similar. Add to the fact that browsers may have bugs that cause itself to either crash and hang, and sound security practices on the server should limit information disclosure of errors to the client... so I never use browser behavior or returned HTML to diagnose server-side issues - I always diagnose based on information from various server log files.

About Diagnosing AppPool Failures...
Unfortunately, there are multiple event log entries from IIS that indicate a "crash" has happened, so you cannot just key-in on any particular event ID for a resolution pattern. For example, the earlier questions illustrate two such events, and there are other related ones.

Now, we did not intentionally try to make crashes harder to diagnose by making them appear as different events. What is happening is that the W3SVC component of IIS and the user-code execution component of IIS run in separate processes which execute independently of each other, yet asynchronously pass messages back and forth to indicate status. Suppose something crashes the process responsible for user-code execution...

Sometimes, IIS first notices it when a ping response fails to arrive; other times, IIS notices that the process handle has gone away... and while IIS understands that these are both catestrophic events that should be reported in the event log, it maintains good system design by reporting them uniquely. It is the responsibility of any analysis layers on top of IIS to abstract such detailed reporting and present a logical "process crashed" information to the user so that they can take action.

Unfortunately, that analysis layer is frequently the user's brain, who may not be able to abstract the details... and gets confused. But hey, I do not think that IIS should stop giving the details. On the contrary, I think you just have to look at the problem harder. :-) Or complain loudly and get us to provide a better debugging tool (like DebugDiag...).

What is a Crash
You can consider a crash as an unrecoverable event that resulted from some bug in the program that is executing - in the case of IIS, it simply provides a thin process and support infrastructure for your user code to run. And a bug is basically a logical flaw that results in unintended behavior given some arbitrary set of inputs. Notice that the behavior is unintended, and the set of causes is arbitrary. This basically means that bugs can cause crashes that happen sporadically or periodically... all depending on the set of causes, which is arbitrary!

Now, since the set of causes to a given bug is arbitrary, I would also caution against trying to "fix" crashes by blindly installing hotfixes, Service Packs, or making configuration changes. Crashes are caused by bugs, which are logical flaws, and the only way to "fix" the situation is to either:

fix the logical flaw itself, which requires diagnosing the crash to figure out the root of the problem.
change software configuration to avoid the logic flaw causing the crash, which can also require diagnosing the crash to figure out what is causing the issue.
To make things even more interesting... a variety of logic flaws can cause crashes, all of which look the same from an IIS and event log entry perspective (to IIS, the crash ended the process; does not matter what; so just report it).

Thus, you may see similar looking events, sometimes with similar looking error codes, but no single concrete solution. The reason should be clear to you now - one flaw causing a crash with a certain error code is NOT the same as another flaw causing a crash with an identical error code. You are talking about two different flaws, and depending on the code path taken by your server configuration choices, you may need to do different things.

So, the take-away here is that the event log entry, the error code, and any other details you may discern are simply good clues to what is going on, but none are independently reliable for diagnosis. Treat them as pieces of a puzzle that you need to put together to correctly diagnose the issue, which is ultimately what you are trying to identify and resolve.

For example, I treat these events as simply crashes that need to be caught and their stack trace logs analyzed to determine further action. I would not immediately pattern match crashes to "solutions" without other information, and I certainly would not change any system configuration in response.

Frankly, I think it is rash for users to attempt to resolve their crashes without diagnosing the cause. However, most users seem to love pattern matching problem symptoms and event log entries with supposed solutions and blindly try them all, hoping some might work... all the while sinking deeper into some other problem due to their random changes. And the rationale should be clear now - if you do not know the bug causing the fault, how can you determine the configuration change to avoid that bug's path, or find the right patch to fix the bug?

How to approach the Crash
Ok, now that I have thoroughly trashed most people's usual methods of "dealing" with a crash, let me walk through another troubleshooting pattern on IIS. :-) I realize that you are probably under the gun to take some action to fix a problem, so you are willing to try anything and if it works, great; if it doesn't, then there is always product support or newsgroup/forums support to fall back on. I just want to propose a more actionable way to deal with a crash, so that you may be able to take care of things yourself... doesn't that feel good? :-)

Since you never notice a server crash until you interact with it (usually with a browser), when something unexpected happened and you do not think it is a bug in the application ("hanging" browser or server-error responses are reasonable clues), it is time to look for any signs of a crash on the server. When IIS6 runs in its default Worker Process Isolation Mode, you will see event log entries similar to the sort given in the questions - either IIS noticed the w3wp.exe handle signaled process exit/crash of some sort, or the w3wp.exe fails to respond to a ping, or Windows notices that a process crashed. In IIS5 Compatibility Mode, you will probably notice other event log entries, either saying that the IISADMIN or W3SVC service crashed and has done this for # times, or that dllhost.exe has crashed, etc. Anyways, all these events talk about something related to IIS crashing; you have no idea whether it is due to your code or not.

Now that you have identified that your issue belongs to a crash, it is time to set up debugging traps so that you can catch the NEXT crash and diagnose it. Yes, you heard me - you cannot do anything about the crash that has already happened - and since you have no idea what it is, you CANNOT change any server settings to avoid it. So, the only reasonable thing you can do is to set up debugging monitors like IIS State or DebugDiag on the necessary processes running code that is crashing and then WAIT for the next crash. Hopefully, you can trigger the failure condition easily, to shorten this wait.

On this next crash, these tools will produce a stack trace log as well as memory dumps to allow debugging, and you want to either analyze the stack trace log yourself, pay someone to perform an analysis (for example, Microsoft PSS) or post to a newsgroup like microsoft.publit.inetserver.iis to see if anyone will do a free analysis. Only after analysis of the crash can you determine what is truly going wrong

Hopefully, you only have one crash happening on your server, but even if there are multiple crashes on your server, you simply apply the same technique in serial. You catch one crash, resolve it, get a patch/fix, and run again with debugging enabled to catch the next crash, get it resolved and a patch/fix, etc... As developers will say, crashes are the most straight forward to diagnose and fix - they are truly the low-hanging-fruit of bad behavior on the server, so you should pick them off real early...

Now, I dissuade against tracking down multiple crashes in parallel because you simply do not know if the crashes are caused by the same or different bugs. If it is caused by the same bug, you are just wasting time diagnosing the other crashes. If it is caused by different bugs, you have no idea whether the bugs interact with each other or not in causing the crash. Ideally, you want to find and fix non-interacting bugs in parallel because interacting bugs may not be real bugs after you fix the original issue (so you would be just wasting resources again). In short, tracking parallel can be complicated and the pay-off is not certain... you have been warned!

Yes, this is very similar to how the IIS product team approaches bug fixing during our stress/reliability test runs. We start up all IIS processes under the debugger and monitor it for any problems, and as soon as anything crashes/hangs, the debugger is already there so we can diagnose the first occurrence instead of waiting for a second occurrence. And as soon as we get a fix, we just crank everything up again under debuggers and wait for the next failure. Highly efficient and no wasted effort.

Conclusion
I hope that this helps clarify what is a crash on IIS and how to best deal with it.

Resist your natural urge to pattern match event log messages or failure codes to a solution, and do not be discouraged if you do not find your particular failure code or if others have similar but supposedly unsolved issues. Crashes are usually arbitrary and requires stack trace analysis to determine the real cause and the next step. If you do not use tools like IIS State or DebugDiag to catch the crash, you will rarely figure out the real culprit and the correct next step. Pattern matching is nothing more than a random guess, so do not take chances.

Personally, I always attach a debugger to gather a stack trace whenever I suspect a crash. Depending on your debugging skills, this can tell you a whole lot of info, sometimes sufficient to directly fix the issue. Guessing at solutions based on non-specific symptomes can never do this reliably. It is all up to you. :-)

//David

Thanks to David

The XML page cannot be displayed

2006-12-19T03:55:00.000-08:00

A copy of a web page on net.
source

Okay, well it's been a while since my last post and not with lack of anything to write about - instead I've just been too busy, and all round "tired". Yes, in the physical sense, but also somewhat in the mental sense with all thats going on at work...

Yeah, I know my coworkers will find that funny because not so much is going on at work, but with my slight tendancy to stress things are not fun for the moment - but looking up! :)

Okay, now onto todays topic.

I've had the worst time getting Community Server to work on my local machine. You'll have noticed that Mabster has skinned his site over, which is something I've been meaning to do for a long time (along with those 500 other projects I never get onto).

Well I decided to put Community Server onto my machine so I could play around and see what is indeed possible and I came up with an error message as follows:

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

A name was started with an invalid character. Error processing resource 'http://localhost/cs/Default.aspx'. Line 1, ...

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
-^Okay, well that gives you the gist of it, the code might not be exact as I set up another script to mirror the issue.I found some similar input on the Community Server forums:http://communityserver.org/forums/thread/531227.aspxhttp://communityserver.org/files/40/betareleases/entry506323.aspx

http://communityserver.org/forums/thread/521265.aspx

http://communityserver.org/forums/thread/511415.aspx

As well as this I sent off an issue to Telligent (or the Community Server site) team and got no response on this issue, or on the one that means I cannot actually log in to their forums (from two accounts I've set up - it's probably user error in some way, but I won't know since they haven't answered me).

I've been searching around for the solution and found a lot of mixed up responses all of which end with the advice: run "%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i"

This did not work for me, time and time again.

What I found out in my case was, there may have been a conflict with either:

I installed IIS after I installed the .NET Framework.
I installed the Atlas beta - which apparently has issues.
I have Zone Alarm.
Theres something wrong out there in the air that causes bugs in computers.
As I couldn't quite narrow it down I had to do some searching.

What I did come up with, was that although the .NET framework was installed, and IIS could see it on my local machine - .aspx files were not mapped to my IIS file mappings.

You can find your mappings under:

Control Panel -> Administrative Tools -> Internet Information Services

then:

Right-Click on the Site you want.
Choose Properties.
Go to the "Directory" tab.
Click the "Configuration" button.
Go to the "Mappings" tab.
Look in the "Application Mappings" list.
If you do not see ".aspx" listed as a mapping, your IIS to .NET Framework mappings are not correct.

In this case, try to run the command: "%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i"

(You can do this from a command prompt or from the Start -> Run menu).

If that does not correct your mappings you may have to do what I did, which is uninstall IIS.

This becomes a bit more difficult, but I can assume if you're trying to install CS then you're probably not terrible with the computer thingy, right?

Have your Windows XP discs on standby!

You can uninstall IIS from:

Control Panel -> Add or Remove Programs

Choose the: Add/Remove Windows Components button.
Click off Internet Information Services (IIS) checkbox.
Hit "Next" and proceed to uninstall.
Then reinstall IIS from exactly the same place - Add/Remove Windows Components.

Then run the script: "%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i"

Make sure your ASP .NET site is back in the IIS web sites menu, and you may want to make sure its got the icon for a website and not just a web folder.

If it doesn't, just go to the sites properties (Right-Click) and on the "Directory" tab, check to see if the "Application Settings" are filled in. If they aren't there will be a "Create" button.

Hit that "Create" button, and then check the "Configuration" button that the mappings are all in there.

Test your ASP .NET page and it should, hopefully come up.

If they aren't - well, theres something else thats wrong and give me a holler and I'll try to help out where I can. I would answer this issue on the Community Server forums, but see the above issue of them not telling me why I cannot log in :)

Note 1: You're version of the .NET Framework version may vary, check the c:\Windows\Microsoft.NET\Framework directory if the aspnet_regiis script doesn't work as I've stated.

Note 2: This issue isn't just to do with Community Server, but all ASP .NET pages you'll serve up on your IIS server - I've just posted it this way because it was Community Server I was trying to install (read: It's not a CS problem, it's a .NET and IIS problem).

regular expression checker

2006-12-12T04:49:00.000-08:00

2006-11-21T04:45:00.000-08:00

In Visual Studio 2005 you may encounter this error when starting an ASP.NET 2.0 debugging session. Particularly you might encounter it if you are using the ASP.NET development server local web server running a local filesystem web site project.

Unable to attach. The binding handle is invalid.

Do you want to continue anyway?

Yes / No

To fix this error, go to Start Menu --> Administrative Tools --> Services

Select the Terminal Services service and make sure it is started.

I got this error because in my machine the Terminal services was disabled.

huge file transfer over http (asp.net way)

2006-11-16T22:25:00.000-08:00

/*
Here is a method which reads the contents of the file in chunks and sends it to the client. this way we avoid unnecessary reading of the full file into the memory

Courtesy: Microsoft website
*/

public void DownloadFile(HttpResponse response, string filepath)
{
System.IO.Stream iStream = null;

// Buffer to read 10K bytes in chunk:
byte[] buffer = new Byte[10000];

// Length of the file:
int length;

// Total bytes to read:
long dataToRead;

// Identify the file to download including its path.
// string filepath = "DownloadFileName";

// Identify the file name.
string filename = System.IO.Path.GetFileName(filepath);

try
{
// Open the file.
iStream = new System.IO.FileStream(filepath, System.IO.FileMode.Open,
System.IO.FileAccess.Read, System.IO.FileShare.Read);

// Total bytes to read:
dataToRead = iStream.Length;

response.ContentType = "application/octet-stream";
response.AddHeader("Content-Disposition", "attachment; filename=" + filename);

// Read the bytes.
while (dataToRead > 0)
{
// Verify that the client is connected.
if (response.IsClientConnected)
{
// Read the data in buffer.
length = iStream.Read(buffer, 0, 10000);

// Write the data to the current output stream.
response.OutputStream.Write(buffer, 0, length);

// Flush the data to the HTML output.
response.Flush();

buffer = new Byte[10000];
dataToRead = dataToRead - length;
}
else
{
//prevent infinite loop if user disconnects
dataToRead = -1;
}
}
}
catch (Exception ex)
{
// Trap the error, if any.
response.Write("Error : " + ex.Message);
}
finally
{
if (iStream != null)
{
//Close the file.
iStream.Close();
}
}

/*
Other links related to this, may be useful for a reading.
http://www.odetocode.com/Articles/111.aspx
http://www.codecomments.com/archive290-2005-5-471328.html
http://www.ondotnet.com/pub/a/dotnet/2002/04/01/asp.html
http://www.yoda.arachsys.com/csharp/readbinary.html
http://msdn.microsoft.com/msdnmag/issues/05/11/BasicInstincts/
http://asp-net-whidbey.blogspot.com/
http://support.microsoft.com/kb/812406
http://www.eggheadcafe.com/articles/20011006.asp

*/

}

IIS Problem

2006-10-18T23:55:00.000-07:00

When try to access an ASPX page on IIS, I got the following error yesterday. i spent few hours before finding a proper solution on this.

problem description (copied from net)

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

A name was started with an invalid character. Error processing resource 'http://localhost/IssueTracker/Default.aspx'. Line...

<%@ Page Language="vb" AutoEventWireup="false" Inherits="ASPNET.StarterKit.IssueTracker._Default" CodeFile="Default.aspx">

Solution

register the dotnet framework again. be sure of the specific version.
you will be getting it in C:\Sysroot(it could be windows\system32) all the installed .net versions are listed out there. go the correct folder and execute the "aspnet_regiis -i" from command prompt. it worked for me.

C:\SYSROOT\Microsoft.NET\Framework\v2.0.50727
\aspnet_regiis -i

some other info on this blog

Java magic

2006-10-06T02:07:00.000-07:00

Today a collegue of me was having problem in running a Hello.java program which prints plain hello on the console.

It was complaining time and again "NoSuchMethodError:main"...

we thought its problem with the JDK in that machine.

But actual culprit is my collegue only, what she did is that she wrote a Hello.java program with invalid main method (with no arguments) and placed it in JDK\bin directory. she compiled in that location itself so JDK\bin has the Hello.class file too.

Now she is trying to create Hello.java in a different directory and with proper main method. but she is getting the error.

When i gave java -verbose Hello
it is showing me that the class is loaded from JDK\bin, that was the mistake.

i removed the Hello.class file from JDK\bin and it is now referring the class file that is created in the current directory.

Summary

JVM gives preference to the JDK\bin directory for locating class files.

uselinks

2006-10-06T01:28:00.000-07:00

http://ajaxsearch.blogspot.com/
http://www.hibernate.org/118.html

http://www.javalobby.org/java/forums/t33002.html

http://www.javalobby.org/articles/hibernate-query-101/

Log4j

2006-09-20T05:17:00.000-07:00

Log4j is a nice logging solution for java developers.

http://www.vipan.com/htdocs/log4jhelp.html

http://rei1.m-plify.net/log4j/

http://www.onjava.com/pub/a/onjava/2002/08/07/log4j.html?page=2

downloading a web page using java code

2006-09-11T00:54:00.000-07:00

U can use the following code to call the following methods and classes:

HttpURLConnection conn = getHttpURLConnectionUsingProxy(“http://www.google.com” "", "", "11.11.11.11", "80");

conn.connect();

InputStream xmlStream = null;

xmlStream = conn.getInputStream();

The code follows:

public HttpURLConnection getHttpURLConnectionUsingProxy(String urlstring, String username, String password, String proxyHost, String proxyPort){

String input = username + ":" + password;

String encoding = base64Encode(input);

// Enable the properties used for proxy support

System.getProperties().put("proxySet", "true" );

System.getProperties().put("proxyHost",proxyHost);

System.getProperties().put("proxyPort", proxyPort);

HttpURLConnection connection = null;

try{

URL url = new URL(urlstring);

connection = (HttpURLConnection)url.openConnection();

// Set up the connection so it knows you are sending

// proxy user information

connection.setRequestProperty( "Proxy-Authorization", encoding );

}catch(MalformedURLException murle){

System.out.println("MalformedURLException: " + murle.getMessage());

}catch(IOException ioe){

System.out.println("IOException: " + ioe.getMessage());

}

if (connection==null) System.out.println("Returning null connection");

return connection;

}

public String base64Encode(String s){

ByteArrayOutputStream bOut = new ByteArrayOutputStream();

Base64OutputStream out = new Base64OutputStream(bOut);

try{

out.write(s.getBytes());

out.flush();

}catch (IOException exception){

}

return bOut.toString();

}

A separate class:

public class Base64OutputStream extends FilterOutputStream {

private int col = 0;

private int i = 0;

private int[] inbuf = new int[3];

public Base64OutputStream(OutputStream out){

super(out);

}

public void write(int c) throws IOException{

inbuf[i] = c;

i++;

if (i==3){

super.write(toBase64[(inbuf[0] & 0xFC) >> 2]);

super.write(toBase64[((inbuf[0] & 0x03) << 4) | ((inbuf[1] & 0xF0) >> 4)]);

super.write(toBase64[((inbuf[1] & 0x0F) << 2) | ((inbuf[2] & 0xC0) >> 6)]);

super.write(toBase64[inbuf[2] & 0x3F]);

col += 4;

i = 0;

if (col >= 76){

super.write('\n');

col = 0;

}

}

}

public void flush() throws IOException{

if (i==1){

super.write(toBase64[(inbuf[0] & 0xFC) >> 2]);

super.write(toBase64[(inbuf[0] & 0x03) << 4]);

super.write('=');

super.write('=');

}else if (i==2){

super.write(toBase64[(inbuf[0] & 0xFC) >> 2]);

super.write(toBase64[((inbuf[0] & 0x03) << 4) | ((inbuf[1] & 0xF0) >> 4)]);

super.write(toBase64[(inbuf[1] & 0x0F) << 2]);

super.write('=');

}

}

private char[] toBase64 = { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H',

'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',

'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X',

'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f',

'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n',

'o', 'p', 'q', 'r', 's', 't', 'u', 'v',

'w', 'x', 'y', 'z', '0', '1', '2', '3',

'4', '5', '6', '7', '8', '9', '+', '/'

};

}

Thanks to chaya Infosys, chennai

Database Connection Pooling with Tomcat

2006-06-14T21:55:00.000-07:00

Database Connection Pooling with Tomcat
by Kunal Jaggi
04/19/2006
Software object pooling is not a new concept. There are many scenarios where some type of object pooling technique is employed to improve application performance, concurrency, and scalability. After all, having your database code create a new Connection object on every client request is an expensive process. Moreover, with today's demanding applications, creating new connections for data access from scratch, maintaining them, and tearing down the open connection can lead to massive load on the server.

Connection pooling eliminates JDBC overhead. Further, object pooling also helps to reduce the garbage collection load. In this article, we'll look at an elegant way of creating a pool of open database-connection objects in Tomcat, so that they are handy whenever an application needs to access a DB resource.
With Database Connection Pooling (DBCP), we can scale our applications to handle increased load and deliver high performance benefits. Using recycled database connection objects cuts the time taken to re-instantiate and load frequently used objects, thus reducing unnecessary overheads. Configuring a DB pool can be a daunting task, because there has to be a way for different components within an application to know about the available pooled objects, and a mechanism to locate them. This is exactly where JNDI steps in, tying these dependencies together.
JNDI to the Rescue
The Java Naming and Directory Interface (JNDI) has been at the core of Java EE since its inception. JNDI offers a generic mechanism for Java EE components to find other components, resources, or services indirectly at runtime. The primary role of JNDI in a Java EE application is to provide an indirection layer, so that components can find required resources without being particularly aware of the indirection. This indirection is almost transparent. JNDI helps in holding applications together, but this coupling is very flexible, so that apps can be easily reassembled. JNDI spares you from providing direct references to the data source, JDBC driver class names, user names and passwords, or any vendor-specific quirks of setting up pooling. We just look up all of these dependencies at runtime through a JNDI call. The developer, on the other hand, is ignorant of the external resources.
Tomcat Configuration
Our approach to DBCP uses the Jakarta-Commons database connection pool. But first, we need to configure the JNDI DataSource in Tomcat by adding a declaration for the resource to server.xml file, which resides inside the /conf directory of your Tomcat installation (indicated by the environment variable CATALINA_HOME). The JNDI DataSource is used as a factory for connections. One of the major advantages of using a configuration like this is that the characteristics of the pool can be changed without affecting the application code. Our application's use of connection pooling is almost transparent. The following code snippet shows us how to configure the container to enable connection pooling.
reloadable="true" crossContext="true">

type="javax.sql.DataSource" removeAbandoned="true"
removeAbandonedTimeout="30" maxActive="100"
maxIdle="30" maxWait="10000" username="kunal"
password="java_facier"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/dbcptest"/>

We can configure a maximum number of DB connections in the pool. Make sure you choose a maximum connection count large enough to handle all of your database connections--alternatively, you can set 0 for no limit. Further, we can set the maximum number of idle database connections to be retained in the pool. Set this value to -1 for no limit. The most optimal performance is attained when the pool in its steady state contains just enough connections to service all concurrent connection requests, without having to create new physical database connections at runtime. We can also specify the maximum time (in milliseconds) to wait for a database connection to become available, which in this example is 10 seconds. An exception is thrown if this timeout is exceeded. You can set this value to -1 to wait indefinitely. Please make sure your connector driver, such as mysql.jar, is placed inside the /common/lib directory of your Tomcat installation.
To achieve performance and high throughput, we also need to fine-tune the container to work under heavy traffic. Here's how we'll configure the Connector element for the maxProcessors and acceptCount parameters in the server.xml file:

maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="150"
connectionTimeout="20000" disableUploadTimeout="true" />
Configuring JNDI Reference
In order for JNDI to resolve the reference, we have to insert a tag into the web.xml deployment descriptor file. We first begin by setting a tag for registering a ServletContextListener as shown below:

com.onjava.dbcp.DBCPoolingListener

DB Connection Pooling

jdbc/TestDB

javax.sql.DataSource

Container

EnrolledStudents

com.onjava.dbcp.CourseEnrollmentServlet

EnrolledStudents

/enrollment.do

This binding is vendor-specific, and every container has its own mechanism for setting data sources. Please note that this is just a declaration for dependency on an external resource, and doesn't create the actual resource. Comprehending the tags is pretty straightforward: this indicates to the container that the local reference name jdbc/TestDB should be set by the app deployer, and this should match with the resource name, as declared in server.xml file.
Putting DBCP into Action
As our application first starts asking the pool for Connection objects, they will be newly created, but when the application has finished with an object, it's returned to the pool rather than destroyed. This has huge performance benefits. Now, as the application needs more Connection objects, the pool will be able to issue recycled objects that have previously been returned by the application.
As an example, let's create a listener class to work with the pool. Our listener class implements the ServletContextListener interface; thus, it'll be initialized when the container starts and creates a ServletContext for this web app. Remember, there's only one ServletContext per web app. Any class implementing the ServletContextListener interface is initialized when the container starts. This early initialization cuts unnecessary overhead later, since it's ideal to have a cached set of open database connection objects available when the container starts rather than waiting for a client request. Inside the listener class, we'll do the necessary JNDI lookup and then set the DataSource as a ServletContext attribute so that it's available to the entire web app. The following code snippet shows us how to extract DataSource through a JNDI call:
public class DBCPoolingListener implements
ServletContextListener{
public void contextInitialized
(ServletContextEvent sce){

try {
// Obtain our environment naming context
Context envCtx = (Context) new InitialContext().
lookup("java:comp/env");

// Look up our data source
DataSource ds = (DataSource) envCtx.lookup
("jdbc/TestDB");

sce.getServletContext().setAttribute
("DBCPool", ds);
} catch(NamingException e){ e.printStackTrace();
}
}
public void contextDestroyed(ServletContextEvent
sce){
}
}
The component naming context is indicated by the prefix java:comp/env/.
For the sake of simplicity, we'll create a simple servlet, hard-coding the business logic and presentation. We'll use the JDBC 2.0 Standard Extension API, which specifies that a database service provider can implement a pooling technique that can allow multiple Connection objects to be shared among several requesting clients. Here's how we'll extract DataSource from the ServletContext attribute and then establish a Connection to pooled DB connection objects.
public void init() throws ServletException {
try {
//Create a datasource for pooled connections.
datasource = (DataSource) getServletContext().
getAttribute("DBCPool");

//Register the driver for non-pooled connections.
Class.forName("com.mysql.jdbc.Driver").
newInstance();
}
catch (Exception e) {
throw new ServletException(e.getMessage());
}
}
The servlet is written to use either pooled or non-pooled database connections, depending on the query string passed in its URL. The servlet fetches a pooled connection object using Tomcat DBCP, and non-pooled connections directly from MySQL connector.
Here's an example of obtaining a Connection object. If the pooledConnection flag is set, it simply calls getConnection() on the DataSource. If not, it manually creates a new Connection object:
private synchronized Connection getConnection
(boolean pooledConnection)
throws SQLException {
if (pooledConnection) {
pooledCount++;

// Allocate and use a connection from the pool
return datasource.getConnection();
}
else {

nonPooledCount++;
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost/dbcptest","kunal",
"java_facier");
return con; //return a newly created object
}
}
Having acquired a Connection, the servlet executes a simple join between the course and enrollment tables, and then formats and outputs the results as HTML. The example uses PreparedStatement to pre-compile SQL and run it repeatedly. This eliminates the tedious task of parsing and compiling the SQL query on every client request. Pre-compilation improves performance and offers enhanced security by preventing SQL injection attacks. For thread safety, we'll keep Connection, PreparedStatement, and ResultSet as local variables inside of the doGet() method.

Connections issued from the JNDI DataSource factory will be returned to the pool when closed. Clients use a connection pool by borrowing a connection object, using it, and then returning it to the pool by closing it. We have to make sure that after we are done with the Connection, we close it. If a Connection is not closed, it will never be returned to the pool and become available for reuse. Of course, that would tie up resources. The finally block guarantees that used ResultSet, PreparedStatement, and Connection objects are closed and prevents any connection pool leaks, as shown below:

finally {
try {if (rs != null) rs.close();} catch (SQLException e) {}
try {if (pstmt != null) pstmt.close();} catch (SQLException e) {}
try {if (connection != null) connection.close();} catch (SQLException e) {}
}
Performance Measurement
Before our application hits the ground running, we would like to stress test the app, evaluate its performance, and compare the results between the cached set of pooled connection objects and the non-pooling alternative. For this, we'll rely on JMeter, an open source tool for load testing with a drag-and-drop-style GUI. I have written a test plan for stress testing the web app. I have set up JMeter to stimulate 50 concurrent users, accessing a common servlet two times without any interval. The results are pretty apparent after looking at the JMeter graph results shown in Figures 1 and 2, below.

Figure 1. Pooled DB connections deviation (click for full-size image)

Figure 2. Non-pooled DB connections deviation (click for full-size image)
After several test runs, it turned out that connection pooling is at least four times faster than explicitly creating a DB connection object from the ground up. Admittedly, to get more accurate results, JMeter should run on a different machine. The ramp-up period, which describes the amount of time for creating the total number of threads, should be carefully chosen. It's not considered to be a good idea to set it to zero if you have a large number of threads, because that would create all of the threads at once and send out requests immediately. At the same time, a higher ramp-up period is also not appropriate, as it might underestimate the results.
Conclusion
Connection pooling is a technique used for sharing a cached set of open database connections among several requesting clients. It doesn't require you to modify your code significantly; rather, it provides enhanced performance benefits. Object pooling should be used with care. It does require additional overhead for such tasks as managing the state of the object pool, issuing objects to the application, and recycling used objects. Pooling is best suited for objects that have a short lifetime. If you are already working in a rich Java EE environment, then most likely you would be using an out-of-box connection pooling facility provided by your app server, and your applications' use of connection pooling is almost transparent.
Resources
http://www.onjava.com/pub/a/onjava/2006/04/19/database-connection-pooling-with-tomcat.html
• Example source code used in this article

Writing a custom session listener:

listener>

central.OurSessionListener

Here is my listener class:
package central;

import java.util.HashMap;
import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpSessionEvent;
import javax.servlet.http.HttpSessionListener;

import wwxchange.utility.*;
import wwxchange.beans.*;

public class OurSessionListener implements HttpSessionListener {

public void sessionCreated(HttpSessionEvent se) {
HttpSession session = se.getSession();
UserAcctBean user = (UserAcctBean) session.getAttribute("currentuser");
String loginID = user.getLoginID();
System.out.println("Added session: " + session.getId() + " for user " + user.getLoginID());
SystemControl.addActiveUser(session.getId(), user.getLoginID() );
}

public void sessionDestroyed(HttpSessionEvent se) {
HttpSession session = se.getSession();
SystemControl.removeActiveUser(session.getId());
System.out.println("Removed session: " + session.getId());
}
}

source: http://www.onjava.com/pub/a/onjava/2006/04/19/database-connection-pooling-with-tomcat.html?page=3

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/t0010264.htm

Developer zone

2006-03-31T08:29:00.000-08:00

Developer zone

java and software performance

2006-03-27T05:00:00.000-08:00

Sachin's blog on Java and software performance

Here another one
http://www.me.umn.edu/%7Eshivane/blogs/cafefeed/

Data Mining

2006-03-23T09:47:00.000-08:00

www.luigidragone.com/datamining/

http://www.helsinki.fi/~holler/datamining/algorithms.html
http://www.csc.liv.ac.uk/~frans/KDD/Software/Apriori-TFP/aprioriTFP.html

http://www.helsinki.fi/~holler/datamining/

java Exceptions

2006-03-09T04:57:00.001-08:00

http://radio.weblogs.com/0122027/stories/2003/04/01/JavasCheckedExceptionsWereAMistake.html

oracle top n query

2006-02-14T05:05:00.000-08:00

http://www.devx.com/gethelpon/10MinuteSolution/16608/0/page/5

select * from
emp a
where 3 = (select count(distinct(sal)) from emp b
where b.sal >a.sal)

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank =2;

oracle top n query

2006-02-14T04:55:00.000-08:00

select * from
emp a
where 3 = (select count(distinct(sal)) from emp b
where b.sal >a.sal)

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank =2;

VB and oracle

2006-02-10T07:22:00.000-08:00

Links i Referred

http://www.orafaq.com/faqmsvb.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;300596&sd=tech

Create a System DSN in Windows XP

1. Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources(ODBC).
2. Click the System DSN tab, and then click Add.
3. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
4. Type the data source name. Make sure that you choose a name that you can remember. You will need to use this name later.
5. Click Select.
6. Click the correct database, and then click OK.
7. Click OK, and then click OK.

o Create a System DSN in Windows 2000
1. Click Start, point to Programs, point to Administrative Tools, and then double-click Data Sources (ODBC).

NOTE: In Windows 2000 Professional, click Start, point to Settings, click Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Click the System DSN tab.
3. Click Add.
4. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
5. Type the data source name. Make sure to you a name that you will remember. You will need to use this name later.
6. Click Select.
7. Click the correct database, and then click OK.
8. Click OK in the next two dialog boxes.

ow does one connect to Oracle from VB?
Connectivity to Oracle is provided via ODBC or OO4O (Oracle Objects for OLE). For more information about ODBC, read the ODBC FAQ. For information about OO4O, read the OO4O FAQ. Look at this examples:

' DAO Example (Data Access Objects)

Dim wstemp As Workspace
Dim dbtemp As Database
Dim rstemp As Recordset

Set wstemp = DBEngine.Workspaces(0)
Set dbtemp = wstemp.OpenDatabase("", False, False, "ODBC;DSN=Oracle;USR=scott;PWD=tiger")
Set rstemp = dbtemp.OpenRecordset(myquery.Text, dbOpenDynaset, dbSQLPassThrough)
howmany = 0
Combo1.Clear
Do Until rstemp.EOF
msgbox rstemp(0)
rstemp.MoveNext
howmany = howmany + 1
Loop

' RDO Example (Remote Data Objects)

Dim contemp As New rdoConnection
Dim rstemp As rdoResultset
Dim envtemp As rdoEnvironment
Set envtemp = rdoEngine.rdoEnvironments(0)
envtemp.CursorDriver = rdUseServer
' or rdUseOdbc, rdUseNone, rdUseIfNeeded, rdUseClientBatch
With contemp
.Connect = "ODBC;DSN=Oracle;USR=scott;PWD=tiger"
.EstablishConnection rdDriverNoPrompt, false, rdoForwardOnly
' or rdoStatic, rdoKeyset, rdoDynamic
End With

Set rstemp = contemp.OpenResultset("select ...") ' Your SQL here

howmany = 0
With rstemp
Do Until .EOF Or howmany > 2000
msgbox .rdoColumns(0) ' Popup a message box showing the 1st column
.MoveNext
howmany = howmany + 1
Loop

ADO Example

Option Explicit

Public m_adoCnn As New ADODB.Connection
Public m_adoRst As New ADODB.Recordset

Public Sub Command1_Click()
m_adoCnn.ConnectionString = "Provider=MSDAORA;Password=tiger;User ID=scott;Data Source=database"
m_adoCnn.Open
End Sub

Private Sub Form_Unload(Cancel As Integer)
m_adoCnn.Close
End Sub

OO4O Example

Option Explicit

Dim m_oraSession As Object
Dim m_oraDatabase As Object

Private Sub Command1_Click()
Set m_oraSession = CreateObject("OracleInProcServer.XOraSession")
Set m_oraDatabase = m_oraSession.DBOpenDatabase("Database", "user/password", 0&)

MsgBox "Connected to " & m_oraDatabase.Connect & "@" & m_oraDatabase.DatabaseName
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set m_oraDatabase = Nothing
Set m_oraSession = Nothing

End Sub

# Back to top of file
Why is there only one record in my recordset?
When you do a recordcount and it return only one record in the recordset, while you know there are more records, you need to move to the last record before doing the count. Look at this example.

Dim rs As Recordset
rs.MoveLast
TxtNumRows.Text = rs.RecordCount

NOTE: Don't forget to do a rs.MoveFirst to get back to the first record again.

P2P SSL authentication and JAVA with OpenSSL

2006-02-08T05:05:00.000-08:00

This post contains some of my work about java and openssl.

Pre-requisites

install apache, mod_ssl(if it is not available with apache), openssl
configure openssl configuration file openssl.cnf

The Certificate Stuff:
there are 3 important options with openssl.
i.e, genrsa, req,ca
check the openssl help for all the detail input/output description.
creating key and self certified certificate

first create the key..
openssl genrsa -des3 -out ca.key 1024

then use the following command to create the CA certificate
openssl req -new -x509 -days 365 -key ca.key -out ca.crt

now we generate key and certificate request key..
openssl genrsa -des3 -out server.key 1024

certificate request (CSR)
openssl req -new -key server.key -out server.csr
(generating key, request are done at client side and sent across to the CA for
certification)

Command to certify using our own CA.
openssl ca -in -out -keyfile -cert
example: openssl ca -in server.csr -out server.crt -keyfile ca.key -cert ca.crt
this way we can sign any reguest use openssl.cnf file to put certifying
criteria like city name should be or country name should
Now generating keystores so that our application can use SSL certificates for
secured communication.
use pkcs12 facility to export certificates to a pkcs12 store with a command
similar to the following..

for CA..
openssl pkcs12 -export -in cacert.pem -inkey cakey.pem -out ca.p12

for client
penssl pkcs12 -export -in client.crt -inkey client.key -out client.p12

NOTE:
keytool -list -keystore test.p12 -storetype PKCS12
keytool error: java.io.IOException: Set tag error
You could try to clean up your keystore. Execute commands:
openssl pkcs12 -in test.p12 -clcerts -out file.pem
openssl pkcs12 -export -in file.pem -out clean.p12

Using keystores

we need to put the ca keystore in trust store and the client certificate in keystore

in java it is just setting the system properties as shown in the following program and the magic works..

import java.io.IOException;
import java.io.PrintStream;
import java.io.InputStreamReader;
import java.io.BufferedReader;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.io.*;
import java.net.*;
import java.util.*;

public class TestClient {
public TestClient(){
try{
java.security.Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());
//System.setProperty("javax.net.debug", "all");
System.setProperty"java.protocol.handler.pkgs","com.sun.net.ssl.internal.www.protocol");
//System.setProperty("javax.net.ssl.trustStore", "truststore");
System.setProperty("javax.net.ssl.trustStore", "ca.p12");
System.setProperty("javax.net.ssl.trustStoreType", "pkcs12");
System.setProperty("javax.net.ssl.trustStorePassword","password");
System.out.println("after truststore setp");
System.setProperty("javax.net.ssl.keyStoreType", "pkcs12");
System.setProperty("javax.net.ssl.keyStore", "client.p12");
System.setProperty("javax.net.ssl.keyStorePassword", "password");
System.out.println("after keystore setting");
System.out.println("Sending XML with http ");
URL url = new URL("https://SERVER_USRL");
System.out.println("after url created");
//URLConnection uc = (URLConnection)url.openConnection();
//System.out.println("after connection establisht");
//uc.setDoOutput(true);
//uc.setDoInput(true);
//uc.setAllowUserInteraction(false);
//BufferedReader in = new BufferedReader(new InputStreamReader(url.openStream()));
//System.out.println("The instance of url.openConnection |" +url.openConnection()"|");
com.sun.net.ssl.HttpsURLConnection connection = (com.sun.net.ssl.HttpsURLConnection)url.openConnection();
BufferedReader in = new BufferedReader(new InputStreamReader(connection.getInputStream()));
PrintWriter pw = new PrintWriter(new FileWriter("output.xml"));
String inputLine;
while ((inputLine = in.readLine()) != null)
pw.println(inputLine);
in.close();
pw.close();
}catch(Exception e){
e.printStackTrace();
}

}
public static void main(String arg[]){
System.out.println("before");
TestClient obj= new TestClient();
System.out.println("after");
}
}

General notes about OPENSSL
1)openssl uses openssl.cnf while creating certificate request as well as when we are certifying request with our own CA.

so this is a crucial one, we can change the default options like country name,
city, province etc., and while certifying also this configuration file is used to validate the client CSR file

openssl uses 2 text files while certifying requests.
serial
index.txt
serial will contain the certifiate serial number
intially it contains 01 and after that it will be incremented automatically for each request.
index.txt contains the created certificate information ( i think so) initially
it will contain nothing..
see that this file will have no content in the beginning
may be size should be 0 kb
this 2 things will create lot of errors if there is something wrong.
use online help for setting up openssl and to resolve errors generated during
creating .csr or .crt files..
http://tirian.magd.ox.ac.uk/~nick/openssl-certs/index.shtml
APACHE configuration:
apache needs mod_ssl module for dealing with SSL. Load this module and ssl.conf will be used to create virtualhosts with SSL configurations.
read apache documentation for various attribues in ssl.conf
sample virtualhost configuration in ssl.conf

ServerName testing.cybernetsoft.com
SSLEngine on
ErrorLog logs/ssl_error_log
TransferLog logs/ssl_access_log
SSLCipherSuite ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP
SSLCertificateFile /etc/httpd/conf/ssl.crt/server.crt
SSLCertificateKeyFile /etc/httpd/conf/ssl.key/server.key
SSLCertificateChainFile /etc/httpd/conf/ssl.crt/ca.crt
SSLCACertificateFile /etc/httpd/conf/ssl.crt/ca.crt
SSLCACertificatePath /etc/httpd/conf/ssl.crt
SSLVerifyDepth 1
HostnameLookups double
#SSLVerifyClient require
#SSLRequire (%(SSL_CLIENT_I_DN_CN) eq %(HTTP_HOST))
#SSLRequire (%(SSL_CLIENT_I_DN_O) eq "Cybernet Software Systems Inc")
# and %{SSL_CLIENT_S_DN_OU} in {"Staff", "CA", "Dev"}
# and %{TIME_WDAY} >= 1 and %{TIME_WDAY} <= 5
# and %{TIME_HOUR} >= 8 and %{TIME_HOUR} <= 20 )
#SLRequire ( %{SSL_CIPHER} !~ m/^(EXP|NULL)/
# and %{SSL_CLIENT_S_DN_O} eq "Snake Oil, Ltd."
# and %{SSL_CLIENT_S_DN_OU} in {"Staff", "CA", "Dev"}
# and %{TIME_WDAY} >= 1 and %{TIME_WDAY} <= 5
# and %{TIME_HOUR} >= 8 and %{TIME_HOUR} <= 20 )
# or %{REMOTE_ADDR} =~ m/^11\.1\.14\.[0-9]+$/

CustomLog logs/ssl_request_log
"%t %h cn %{SSL_CLIENT_S_DN_CN}x remoteaddr %{REMOTE_ADDR}x remotehost %{REMOTE_HOST}x remoteident %{REMOTE_IDENT}x \"%r\" %b"

WebLogicHost 11.1.14.13
WebLogicPort 7001
StatPath true
HungServerRecoverSecs 300
ConnectTimeoutSecs 10

#SSLRequire (%{SSL_CLIENT_S_DN_CN} eq %{REMOTE_HOST})
#SSLRequire (%{SSL_CLIENT_VERIFY} eq "SUCCESS")
WebLogicHost 11.1.14.13
WebLogicPort 7001
SetHandler weblogic-handler

SETTING UP tomcat
Setting up Apache Tomcat and a Simple Apache SOAP Client for SSL Communication.
http://ws.apache.org/soap/docs/install/FAQ_Tomcat_SOAP_SSL.html
create key and sertificate from keytool for server.
keytool -genkey -alias tomcat-sv -dname "CN=harinath.cybernetsoft.com, OU=Java services team, O=Cybernet software systems, L=Chennai,

S=TN, C=IN" -keyalg RSA -keypass changeit -storepass changeit -keystore server.keystore
keytool -export -alias tomcat-cv -storepass changeit -file server.cer -keystore server.keystore
do the same thing for client the following steps worked fine for tomcat
CREATING KEYSTORE FOR TOMCAT:
1) keytool -genkey -alias tomcat-cl -dname "CN=harinath.cybernetsoft.com,OU=J2EE TEAM, O=CSS, L=CHENNAI, S=TN, C=IN" -keyalg RSA -keypass changeit -storepass changeit -keystore client.keystore
2)keytool -export -alias tomcat-cl -storepass changeit -file client.cer -keystore client.keystore
keytool -export -alias tomcat -storepass password -file c.crt -keystore client.keystore
3) and now change ur server.xml for ssl
enableLookups="true" disableUploadTimeout="true" acceptCount="100" debug="0" scheme="https" secure="true" keystoreFile="/usr/local/openssl/client.keystore"
clientAuth="false" keystorePass="changeit" sslProtocol="TLS"/>
Tomcat by default uses keystore named ".keystore" inside the home directory of
the user.
I think we can provide keystore and truststore and their passwords in connector itself.
Read the tomcat documentation of the version you are using.
LINKS:
http://www.flatmtn.com/computer/Linux-SSLCertificatesApache.html
installing CA Into IE
http://wasd.vsm.com.au/ht_root/other/faq/msie_ca_cert/
http://httpd.apache.org/docs-2.0/
http://www.impetus.us/~rjmooney/projects/misc/clientcertauth.html
http://www.samspublishing.com/articles/article.asp?p=30115&seqNum=11
http://tirian.magd.ox.ac.uk/~nick/openssl-certs/index.shtml
http://www.modssl.org/docs/2.8/ssl_reference.html#table3
http://httpd.apache.org/docs-2.0/mod/mod_ssl.html#sslrequiressl
http://www.samspublishing.com/articles/article.asp?p=30115&seqNum=5
http://www.impetus.us/~rjmooney/projects/misc/clientcertauth.html
sample config file openssl.cnf
http://paf.se/openssl/openssl.cnf.html
A very useful stuff.. on how to create certificates etc.. just a text file but
really useful.
http://rei1.m-plify.net/howtoon-certificates.txt
http://www.openssl.org/docs/apps/ca.html
http://www.drh-consultancy.demon.co.uk/pkcs12faq.html
http://search.netscape.com/ns/boomframe.jsp?query=installing+client+certificate+in+browser&page=1&offset=0&result_url=redir%3Fsrc%3Dwebsearch%26requestId%3Deab426fbf64c7db%26clickedItemRank%3D1%26userQuery%3Dinstalling%2Bclient%2Bcertificate%2Bin%2Bbrowser%26clickedItemURN%3Dhttp%253A%252F%252Fzeus.phys.uconn.edu%252Fprod%252Fdocs%252FcertSetup.html%26invocationType%3D-%26fromPage%3Ddshnnp%26amp%3BampTest%3D1&remove_url=http%3A%2F%2Fzeus.phys.uconn.edu%2Fprod%2Fdocs%2FcertSetup.html
http://www.cs.sunysb.edu/documentation/jsse/api_users_guide.html
http://java.sun.com/j2se/1.4.2/docs/api/javax/net/ssl/SSLSocketFactory.html
http://forum.java.sun.com/thread.jsp?forum=60&thread=204299
http://forum.java.sun.com/thread.jsp?forum=60&thread=525858
http://forum.java.sun.com/thread.jsp?forum=2&thread=258908
http://java.sun.com/j2se/1.4.2/docs/guide/security/jsse/samples/sockets/client/SSLSocketClientWithClientAuth.java
http://java.sun.com/products/jsse/doc/apidoc/com/sun/net/ssl/TrustManagerFactory.html
http://www.javaworld.com/javaworld/javatips/jw-javatip96.html
http://www.javaolympus.com/J2SE/NETWORKING/JavaSockets/JavaSockets.jsp
http://www-106.ibm.com/developerworks/java/library/ws-secmap/
http://java.sun.com/products/jsse/
The following helps in customization of JSSE like keymanager, trustmanger etc. and includes a sample server/clients application with SSL.
http://www-106.ibm.com/developerworks/java/library/j-customssl/
file:///usr/weblogic81/samples/server/examples/src/examples/security/sslclient/SSLClient.html
http://www-106.ibm.com/developerworks/java/library/j-customssl/
http://archives.java.sun.com/cgi-bin/wa?A2=ind0201&L=java-security&D=0&P=736
permissions in java
http://java.sun.com/j2se/1.4.2/docs/guide/security/permissions.html
http://mindprod.com/jgloss/certificate.html

Design pattern links

2006-01-22T06:03:00.000-08:00

patterns
patterns 1
patterns 2
patterns introduction
oo patterns
Principles of Design patterns PDF
common design patterns
UI design patterns
java camp design patterns
net objects design patterns

Design pattern links

2006-01-22T05:51:00.000-08:00

patterns
patterns 1
patterns 2
patterns introduction
oo patterns

sql - returning top n records

2006-01-21T06:17:00.000-08:00

Returning only the first N records in a SQL query differs quite a bit between database platforms. Here's some samples:

Microsoft SQL Server

SELECT TOP 10 column FROM table

PostgreSQL and MySQL

SELECT column FROM table
LIMIT 10

Oracle

SELECT column FROM table
WHERE ROWNUM <= 10
(use order by too for the specific column - harinath)
Due to these differences if you want to keep your code database independent you should use the maxrows attribute in the cfquery tag. The tradeoffs to database independance is performance, I would expect maxrows to be slower than specifying the rows in the SQL.

SELECT column FROM table

PostgreSQL has a cool feature that will let you return an arbitrary range of rows (eg return rows 10-20). This is very handy for displaying pages of records:

SELECT column FROM table
LIMIT 10 OFFSET 20

The above query will return rows 20-30

oracle - removing duplicate records

2006-01-21T06:16:00.000-08:00

Question:

I have a table T1 with columns C1, C2 and C3. Currently, there are duplicate
records in T1 with the C1, C2 combination.
T1:
C1 C2 C3
-- -- --
1 50 a
1 50 b
2 89 x
2 89 y
2 89 z

I would like to delete the duplicate record on this combination. What is the best way of writing the PL/SQL block or query to
do this?

Answer:

delete from T1
2 where rowid <> ( select max(rowid)
3 from t1 b
4 where b.c1 = t1.c1
5 and b.c2 = t1.c2 )
6 /

3 rows deleted.

select * from t1;

C1 C2 C
---------- ---------- -
1 50 b
2 89 z

oracle getting rows N through M of a result

2006-01-21T06:03:00.000-08:00

Question:

I would like to fetch data after joining 3 tables and
sorting based on some field. As this query results into approx
100 records, I would like to cut the result set into 4, each of
25 record. and I would like to give sequence number to each
record. Can I do using SQL Plus ?

Answer:

n Oracle8i, release 8.1 -- yes.

select *
from ( select a.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS
/

that'll do it. It will *not* work in 8.0 or before.

Here is a discussion on the same

Your first query "where rownum between 90 and 100" never returns ANY data. that
predicate will ALWAYS evaluate to false -- always.

I've already proven in another question (believe it was with you again) that

select * from (
select p.*, rownum rnum
from (select * from hz_parties ) p
where rownum < 100
) where rnum >= 90

is faster then:

select * from (
select p.*, rownum rnum
from (select * from hz_parties ) p
) where rnum between 90 and 100

which is what I believe you INTENDED to type. It has to do with the way we
process the COUNT(STOPKEY) and the fact that we must evaluate

select p.*, rownum rnum
from (select * from hz_parties ) p

AND THEN apply the filter where as the other will find the first 100 AND THEN
stop.

so, say I have an unindexed table:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from big_table;

COUNT(*)
----------
1099008

(a copy of all_objects over and over and over) and I run three queries. Yours
to show it fails (no data), what I think you meant to type and what I would
type:

select p.*, rownum rnu
from ( select * from big_table ) p
where rownum between 90 and 100

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 6.17 15.31 14938 14985 81 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 6.17 15.31 14938 14985 81 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 216

Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY
0 FILTER
1099009 TABLE ACCESS FULL BIG_TABLE

your query -- no data found.... Look at the number of rows inspected
however

select *
from (
select p.*, rownum rnum
from ( select * from big_table ) p
)
where rnum between 90 and 100

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 7.93 17.03 14573 14986 81 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 7.93 17.03 14573 14986 81 11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 216

Rows Row Source Operation
------- ---------------------------------------------------
11 VIEW
1099008 COUNT
1099008 TABLE ACCESS FULL BIG_TABLE

what I believe you mean to type in -- agein -- look at the rows processed!

Now, what I've been telling everyone to use:

select * from (
select p.*, rownum rnum
from (select * from big_table ) p
where rownum < 100
) where rnum >= 90

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 1 7 12 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 1 7 12 10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 216

Rows Row Source Operation
------- ---------------------------------------------------
10 VIEW
99 COUNT STOPKEY
99 TABLE ACCESS FULL BIG_TABLE

HUGE difference. Beat that...

Claims -- don't want em.
Benchmark, metrics, statistics -- love em -- want em -- need em.

PDF password protection removal

2006-01-21T05:49:00.000-08:00

tips to get rid of password protection of PDF files

Easy thing is that we install Ghostscript and GhostViewer

java free obfuscation tool

2006-01-21T05:25:00.000-08:00

y guard from ywworks, works as an excellent tool for java source code obfuscation.

software security

2006-01-21T05:17:00.000-08:00

Silicon valley
insecure.org
Astalavista
aladdin
http://tomcat.apache.org/tomcat-5.0-doc/ssl-howto.html

quick reference

2006-01-18T22:44:00.000-08:00

There are lot of simple PDF files, which work as quick reference for many of tech stuff like java, oracle etc. Here is the web link for
quick references

links for interview questions on oracle

2006-01-17T23:02:00.000-08:00

http://www.geekinterview.com/Interview-Questions/Oracle
http://blogs.cocoondev.org/tomk/archives/001062.html
http://www.hotscripts.com/Detailed/51514.html
http://www.jguru.com/forums/view.jsp?EID=1184213
http://www.fundoosite.com/interview-questions/type.asp?iType=57
http://www.databasejournal.com/features/oracle/article.php/3085171
http://www.geekinterview.com/Global-Interview-Questions/Oracle

http://www.arikaplan.com/oracle.html

http://www.macromedia.com/v1/documents/jrun30/acrobatdocs/jrun_qref.pdf

cyber crimes

2006-01-09T00:25:00.000-08:00

cyber crime definations

E-commerce and cyber crime

http://www.asianlaws.org/cyberlaw/library/cc/what_cc.htm
http://www.crime-research.org/library/Pecul.htm
http://www.indiaforensic.com/compcrime1.htm
http://www.computerworld.com/securitytopics/security/cybercrime
http://www.ehealthstrategies.com/files/cybersecurity.pdf
http://www.itu.int/dms_pub/itu-s/md/03/wsis/c/S03-WSIS-C-0006!!PDF-E.pdf

http://www.osborne-group.com/Fall_2003_-_Insert.pdf

oracle useful links

2006-01-07T05:36:00.000-08:00

http://www.uaex.edu/srea/#Oracle_Scripts
http://www.orafaq.com/scripts/index.htm#GENSQL
http://www.bytelife.com/sql4dba.htm
http://www.orsweb.com/
http://www.geocities.com/SiliconValley/Lakes/1261/
http://www.oracle.com/oramag/code/index.html
http://www.materialdreams.com/oracle/scripts.html
http://www.bijoos.com/oracle/

http://www.akadia.com/services/ora_analytic_functions.html

queries

2006-01-07T04:58:00.000-08:00

Oracle:

1. Top 10 records
sing the ROWNUM Pseudocolumn
One way to solve this problem is by using the Oracle pseudocolumn ROWNUM. ROWNUM is available in Oracle versions 7, 8.0 and 8i (8.1); I am not sure about Oracle 6. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use the ROWNUM pseudocolumn to limit the number of rows returned by a query to 5:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6;

advertisement

Get comprehensive solutions for building software.

# Download: IBM Rational Rose XDE Developer for Visual Studio

# Download: IBM Rational Application Developer for WebSphere Software

# Download: IBM Rational Software Architect

# On-Demand Webcast: Making Java Development Easier

Listing 2 shows the output of the above code. As you see, the first five rows have been returned.

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause.

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
WHERE ROWNUM < 6
ORDER BY Sal;

Listing 3 shows the output of the above code.

Watch Out! Because the ROWNUM is assigned upon retrieval, it is assigned prior to any sorting! This is opposite to the result you would get in SQL Server using the TOP clause. In order to select employees with the highest five salaries, you have to force sorting and then apply ROWNUM condition. Here is the syntax for a top-N query where N = 5 (this syntax with the subquery works only in Oracle 8i):

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
ORDER BY NVL(Sal, 0) DESC)
WHERE ROWNUM < 6;

Listing 4 shows the output of the above code. I used the NVL() function to sort the expression because sorting just by Emp_Salary would have put all records with NULL salary before those with the highest salaries, and that's not what we wanted to achieve.

Utilizing Oracle 8i's Ranking Functions
Another way to perform a top-N query uses the new Oracle 8i feature called "analytic functions." The SQL language, while extremely capable in many areas, has never provided strong support for analytic tasks, such as computing rankings, cumulative and moving averages, lead/lag comparisons, and reporting. These tasks require extensive PL/SQL programming, often with performance issues. Oracle 8i now provides a new wide set of analytic functions that address this need.
advertisement

For a top-N query you can use two ranking functions: RANK and DENSE_RANK. Both allow you to rank items in a group—for example, finding the top-five employees by salary, which is exactly what we need to achieve.

The difference between RANK() and DENSE_RANK() is that RANK() leaves gaps in the ranking sequence when there are ties. In our case, Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK() but only fourth place using RANK():

SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Rank,
DENSE_RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Drank
FROM Emp
ORDER BY SAL Desc NULLS LAST;

Listing 5 shows the output of the above code.

The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.

If the sequence is in descending order, then NULLS LAST implies that NULL values are smaller than non-NULL ones and rows with NULLs will appear at the bottom of the list. If the NULLS FIRST | NULLS LAST clause is omitted, then NULL values are considered larger than any other values and their ordering position depends on the ASC | DESC arguments.

If the ordering sequence is ascending (ASC), then rows with NULLs will appear last; if the sequence is descending (DESC), then rows with NULLs will appear first. NULLs are considered equal to other NULLs and, therefore, the order in which rows with NULLs are presented is nondeterministic.

sing RANK() to Obtain a Top-N Query
To obtain a top-N query, use RANK() in a subquery and then apply a filter condition outside the subquery:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank < 6;

advertisement
Listing 6 shows the output of the above code.

Using the same technique, you can retrieve the bottom-five employees by salary:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL ASC NULLS FIRST) AS Emp_Rank
FROM Emp
ORDER BY SAL ASC NULLS FIRST)
WHERE Emp_Rank < 6;

Listing 7 shows the output of the above code.

Ranking functions can be used to operate within groups, too—that is, the rank value gets reset whenever the group changes. This is achieved with a PARTION BY subclause. Here is the syntax to retrieve the top employee by salary per manager group:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(PARTITION BY MGR ORDER BY MGR, SAL DESC NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY MGR, SAL DESC NULLS LAST)
WHERE Emp_Rank = 1;

Listing 8 shows the output of the above code.

As you can see, analytic functions are extremely useful in all types of analysis and computations, and they provide substantial SQL extensions to Oracle 8i.

Sample SQL matrix report

rem -----------------------------------------------------------------------
rem UPDATED VERSION
rem Filename: matrix.sql
rem Purpose: Example of a CROSS MATRIX report implemented using
rem standard SQL.
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem
rem Updated By Mahesh Pednekar. (bunty609@hotmail.com)
rem Description Removed the Main query because the sub query itself
rem will full fill the requirement.
rem -----------------------------------------------------------------------

rem -----------------------------------------------------------------------
rem Filename: oerr.sql
rem Purpose: Lookup Oracle error messages. Similar to unix "oerr" command.
rem This script is handy on platforms like NT with no OERR support
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on
set veri off feed off

prompt Lookup Oracle error messages:
prompt
prompt Please enter error numbers as negatives. E.g. -1
prompt

exec dbms_output.put_line('==> '||sqlerrm( &errno ) );

set veri on feed on
undef errno
SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job
/

-- Sample output:
--
-- JOB DEPT10 DEPT20 DEPT30 DEPT40
-- --------- ---------- ---------- ---------- ----------
-- ANALYST 6000
-- CLERK 1300 1900 950
-- MANAGER 2450 2975 2850
-- PRESIDENT 5000
-- SALESMAN 5600

Some other common sql queries from orafaqs.com

SQL

General SQL Scripts

Sample SQL matrix report

rem -----------------------------------------------------------------------
rem UPDATED VERSION
rem Filename: matrix.sql
rem Purpose: Example of a CROSS MATRIX report implemented using
rem standard SQL.
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem
rem Updated By Mahesh Pednekar. (bunty609@hotmail.com)
rem Description Removed the Main query because the sub query itself
rem will full fill the requirement.
rem -----------------------------------------------------------------------

SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job
/

-- Sample output:
--
-- JOB DEPT10 DEPT20 DEPT30 DEPT40
-- --------- ---------- ---------- ---------- ----------
-- ANALYST 6000
-- CLERK 1300 1900 950
-- MANAGER 2450 2975 2850
-- PRESIDENT 5000
-- SALESMAN 5600
--

Lookup Oracle error messages

rem -----------------------------------------------------------------------
rem Filename: oerr.sql
rem Purpose: Lookup Oracle error messages. Similar to unix "oerr" command.
rem This script is handy on platforms like NT with no OERR support
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on
set veri off feed off

prompt Lookup Oracle error messages:
prompt
prompt Please enter error numbers as negatives. E.g. -1
prompt

exec dbms_output.put_line('==> '||sqlerrm( &errno ) );

set veri on feed on
undef errno

Display Database version, installed options and port string

rem -----------------------------------------------------------------------
rem Filename: ver.sql
rem Purpose: Show database version with options intalled
rem (handy for your HELP/ABOUT menu)
rem Date: 12-Nov-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set head off feed off pages 0 serveroutput on

col banner format a72 wrap

select banner
from sys.v_$version;

select ' With the '||parameter||' option'
from sys.v_$option
where value = 'TRUE';

select ' The '||parameter||' option is not installed'
from sys.v_$option
where value <> 'TRUE';

begin
dbms_output.put_line('Port String: '||dbms_utility.port_string);
end;
/

set head on feed on

• "Who am I" script

rem -----------------------------------------------------------------------
rem Filename: whoami.sql
rem Purpose: Reports information about your current database context
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off
store set store rep
set head off
set pause off
set termout on

select 'User: '|| user || ' on database ' || global_name,
' (term='||USERENV('TERMINAL')||
', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT
from global_name;

@store
set termout on

Select the Nth highest value from a table

rem -----------------------------------------------------------------------
rem Filename: maxvalue.sql
rem Purpose: Select the Nth highest value from a table
rem Date: 18-Apr-2001
rem Author: Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------

select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;

-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
--
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
--

Select the Nth lowest value from a table

rem -----------------------------------------------------------------------
rem Filename: minvalue.sql
rem Purpose: Select the Nth lowest value from a table
rem Date: 18-Apr-2001
rem Author: Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------

select level, min('col_name') from my_table
where level = '&n'
connect by prior ('col_name') < 'col_name')
group by level;

-- Example:
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second lowest salary:
--
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
--

Demonstrate default column values
rem -----------------------------------------------------------------------
rem Filename: default.sql
rem Purpose: Example script to demonstrate DEFAULT column values
rem Date: 25-Apr-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- drop table x
-- /

create table x (a char, b number default 99999, c date, d varchar2(6))
/

alter table x modify (c date default sysdate)
/

insert into x(a, d) values ('a', 'qwerty')
/

select * from x
/

--
-- Expected output:
--
-- A B C D
-- - ---------- ----------- ------
-- a 99999 25-APR-2001 qwerty
rem -----------------------------------------------------------------------
rem Filename: comments.sql
rem Purpose: Display table and column comments for the current schema
rem Handy for getting to know the database schema
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
set null 'No Comments'

tti 'Table Comments'
col comments format a29 wrap word

select * from user_tab_comments;

tti 'Column Comments'
col comments format a18 wrap word
break on table_name skip 1
select * from user_col_comments;
clear break

set null ''
set pages 23

Pass application info through to the Oracle RDBMS

rem -----------------------------------------------------------------------
rem Filename: appinfo.sql
rem Purpose: Example of how to pass application info through to Oracle RDBMS
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- The following code tells the database what the application is up to:

begin
dbms_application_info.set_client_info('BANCS application info');
dbms_application_info.set_module('BANCS XYZ module', 'BANCS action name');
end;
/

-- Retrieve application info from the database:

select module, action, client_info
from sys.v_$session where audsid = USERENV('SESSIONID')
/

select sql_text
from sys.v_$sqlarea
where module = 'BANCS XYZ module'
and action = 'BANCS action name'
/

SQL*Plus Help script
rem -----------------------------------------------------------------------
rem Filename: help.sql
rem Purpose: Access the SQL*Plus Help table
rem Notes: If the HELP table doesn't exist, see the SQL*Plus FAQ for
rem installation instructions.
rem Date: 05-July-98
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

select info
from system.help
where upper(topic)=upper('&1')
/
• Test for Leap Years
rem -----------------------------------------------------------------------
rem Filename: leapyear.sql
rem Purpose: Check if a year is a leap year
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

select year,
decode( mod(year, 4), 0,
decode( mod(year, 400), 0, 'Leap Year',
decode( mod(year, 100), 0, 'Not a Leap Year', 'Leap Year')
), 'Not a Leap Year'
) as leap_year_indicator
from my_table
/
Spell out numbers to words
rem -----------------------------------------------------------------------
rem Filename: spellout.sql
rem Purpose: This script will spell out numbers to words (handy for
rem cheque printing). Example '10' --> Ten
rem Date: 12-Sep-2000
rem Author: Anonymous
rem -----------------------------------------------------------------------

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/
Demonstrate simple encoding and decoding of messages
rem -----------------------------------------------------------------------
rem Filename: encode.sql
rem Purpose: Demonstrate simple encoding and decoding of secret messages
rem This method can be extended to create simple password
rem encryption
rem Date: 15-Feb-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

SELECT TRANSLATE(
'HELLO WORLD', -- Message to encode
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/

SELECT TRANSLATE(
'85@@%._%*@4', -- Message to decode
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/
• Count the number of rows for ALL tables in current schema
rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in the current schema
rem Date: 30-Apr-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off echo off feed off trimspool on head off pages 0

spool countall.tmp
select 'SELECT count(*), '''||table_name||''' from '||table_name||';'
from user_tables
/
spool off

set termout on
@@countall.tmp

set head on feed on

Demonstrate Oracle database types and object tables
rem -----------------------------------------------------------------------
rem Filename: objopt.sql
rem Purpose: Demonstrate Oracle database types and object tables
rem Date: 12-Feb-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

drop type employee_typ;

create type employee_typ as object (
empno NUMBER,
emp_name varchar2(30),
hiredate date,
member function days_at_company return NUMBER,
pragma restrict_references(days_at_company, WNDS)
)
/

create type body employee_tye is
begin
member function days_at_company return number is
begin
return (SYSDATE-hiredate);
end;
end;
/
show errors

drop type department_typ;

create type department_typ as object (
deptno NUMBER(5),
manager ref employee_typ
)
/

select * from user_types
where predefined = 'NO';

-- Create a object table
create table emp1 as employee_typ;

create table employee (emp_no NUMBER, emp employee_typ);

insert into employee values (1, employee_typ(1, 'Frank Naude', SYSDATE));

commit;

select * from employee;

select x.emp.emp_name from employee x;
Demonstrate VARRAY database types
rem -----------------------------------------------------------------------
rem Filename: varray.sql
rem Purpose: Demontrate VARRAY (variable array in one database column)
rem collection types
rem Date: 12-Aug-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128);
/
CREATE TABLE varray_table (id number, col1 vcarray);

INSERT INTO varray_table VALUES (1, vcarray('A'));
INSERT INTO varray_table VALUES (2, vcarray('B', 'C'));
INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F'));

SELECT * FROM varray_table;
SELECT * FROM USER_VARRAYS;
-- SELECT * FROM USER_SEGMENTS;

-- Unnesting the collection:
select t1.id, t2.COLUMN_VALUE
from varray_table t1, TABLE(t1.col1) t2
/

-- Use PL/SQL to access the varray...
set serveroutput on
declare
v_vcarray vcarray;
begin
for c1 in (select * from varray_table) loop
dbms_output.put_line('Row fetched...');
FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
dbms_output.put_line('...property fetched: '|| c1.col1(i));
END LOOP;
end loop;
end;
/

-- Clean-up...
DROP TABLE varray_table;
DROP TYPE vcarray;
Demonstrate Oracle temporary tables
rem -----------------------------------------------------------------------
rem Filename: temptab.sql
rem Purpose: Demonstrate Oracle 8i temporary tables
rem Date: 23-Apr-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

drop table x
/

create global temporary table x (a date)
on commit delete rows -- Delete rows after commit
-- on commit preserve rows -- Delete rows after exit session
/

select table_name, temporary, duration
from user_tables
where table_name = 'X'
/

insert into x values (sysdate);

select * from x;

commit;

-- Inserted rows are missing after commit
select * from x;

Convert LONG data types to LOBs
rem -----------------------------------------------------------------------
rem Filename: conv2lob.sql
rem Purpose: Convert LONG datatypes to LOBs (Large Objects)
rem Dependancies: Oracle 8.1.x or higher
rem Date: 17-Sep-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

create table old_long_table(c1 number, c2 long);
insert into old_long_table values (1, 'LONG data to convert to CLOB');

create table new_lob_table(c1 number, c2 clob);

-- Use TO_LOB function to convert LONG to LOB...
insert into new_lob_table
select c1, to_lob(c2) from old_long_table;

-- Note: the same procdure can be used to convert LONG RAW datatypes to BLOBs.

Delete duplicate values from a table

rem -----------------------------------------------------------------------
rem Purpose: Delete duplicate values from a table
rem Date: 04-Mar-2005
rem Notes: Verify that the correct rows are deleted before you COMMIT!
rem Author: Dharmendra Srivastava,Associate,
rem MindTree Consulting Pvt Ltd. India
rem -----------------------------------------------------------------------

DELETE FROM my_table
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM my_table
GROUP BY delete_col_name);

-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- To delete the duplicate values:
--
-- DELETE FROM emp
-- WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);
--
-- COMMIT;
--

General PL/SQL: Scripts
1. Update/ delete from a huge table with intermittent commits
rem -----------------------------------------------------------------------
rem Filename: plsloop.sql
rem Purpose: Example: UPDATE/DELETE in a loop and commit very X records
rem Handy for huge tables that cause rollback segment problems
rem DON'T ISSUE COMMIT TOO FREQUENTLY!
rem Date: 09-Apr-1999; Updated: 25-Nov-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

declare
i number := 0;
cursor s1 is SELECT rowid, t.* FROM tab1 t WHERE col1 = 'value1';
begin
for c1 in s1 loop
update tab1 set col1 = 'value2'
where rowid = c1.rowid;

i := i + 1; -- Commit after every X records
if i > 10000 then
commit;
i := 0;
end if;

end loop;
commit;
end;
/

-- Note: More advanced users can use the mod() function to commit every N rows.
-- No counter variable required:
--
-- if mod(i, 10000)
-- commit;
-- dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount);
-- end if;
--
Simple program to demonstrate BULK COLLECT and BULK BIND operations

rem -----------------------------------------------------------------------
rem Filename: bulkbind.sql
rem Purpose: Simple program to demonstrate BULK COLLECT and BULK BIND.
rem Notes: Bulk operations on ROWTYPE only work from and above.
rem Date: 12-Feb-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000

DECLARE
CURSOR emp_cur IS SELECT * FROM EMP;

TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tab emp_tab_t; -- In-memory table

rows NATURAL := 10000; -- Number of rows to process at a time
i BINARY_INTEGER := 0;
BEGIN
OPEN emp_cur;
LOOP
-- Bulk collect data into memory table - X rows at a time
FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
EXIT WHEN emp_tab.COUNT = 0;

DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');

FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
-- Manipumate data in the memory table...
dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
END LOOP;

-- Bulk bind of data in memory table...
FORALL i in emp_tab.FIRST..emp_tab.LAST
INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);

END LOOP;
CLOSE emp_cur;
END;
/
Profile PL/SQL code for execution statistics

rem -----------------------------------------------------------------------
rem Filename: profiler.sql
rem Purpose: Profile PL/SQL code to get run-time statistics.
rem Shows execution time for each PL/SQL line. This data can
rem be used to improve performance.
rem Date: 02-Mar-2004
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

conn / as sysdba

-- Install the profiler...
@?/rdbms/admin/proftab
@?/rdbms/admin/profload
@?/plsql/demo/profrep.sql

-- Create a test procedure to time...
CREATE OR REPLACE PROCEDURE proc1 IS
v_dummy CHAR;
BEGIN
FOR i IN 1..100 LOOP
SELECT dummy INTO v_dummy FROM dual;
END LOOP;
END;
/
SHOW ERRORS

-- Do the profilling and print the report...
set line 5000 serveroutput on size 1000000
DECLARE
v_run NUMBER;
BEGIN
DBMS_PROFILER.START_PROFILER('test','test1',v_run);
proc1;
DBMS_PROFILER.STOP_PROFILER;
DBMS_PROFILER.ROLLUP_RUN(v_run);
PROF_REPORT_UTILITIES.PRINT_RUN(v_run);
END;
/
Select records from a cursor into PL/SQL table
rem -----------------------------------------------------------------------
rem Filename: plstable.sql
rem Purpose: Example: how to populate a PL/SQL Table from a cursor
rem Date: 09-Apr-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

declare
-- Declare the PL/SQL table
type deptarr is table of dept%rowtype
index by binary_integer;
d_arr deptarr;

-- Declare cursor
type d_cur is ref cursor return dept%rowtype;
c1 d_cur;

i number := 1;
begin
-- Populate the PL/SQL table from the cursor
open c1 for select * from dept;
loop
exit when c1%NOTFOUND;
fetch c1 into d_arr(i);
i := i+1;
end loop;
close c1;

-- Display the entire PL/SQL table on screen
for i in 1..d_arr.last loop
dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
dbms_output.put_line('DNAME : '||d_arr(i).dname );
dbms_output.put_line('LOC : '||d_arr(i).loc );
dbms_output.put_line('---------------------------');
end loop;
end;
/

Password encrypt/decrypt using DBMS Obfuscation Toolkit
rem -----------------------------------------------------------------------
rem Filename: password.sql
rem Purpose: Simple password encryption package to demonstrate how
rem values can be encrypted and decrypted using Oracle's
rem DBMS Obfuscation Toolkit
rem Note: Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Date: 18-Mar-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PACKAGE PASSWORD AS
function encrypt(i_password varchar2) return varchar2;
function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors

CREATE OR REPLACE PACKAGE BODY PASSWORD AS

-- key must be exactly 8 bytes long
c_encrypt_key varchar2(8) := 'key45678';

function encrypt (i_password varchar2) return varchar2 is
v_encrypted_val varchar2(38);
v_data varchar2(38);
begin
-- Input data must have a length divisible by eight
v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
input_string => v_data,
key_string => c_encrypt_key,
encrypted_string => v_encrypted_val);
return v_encrypted_val;
end encrypt;

function decrypt (i_password varchar2) return varchar2 is
v_decrypted_val varchar2(38);
begin
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
input_string => i_password,
key_string => c_encrypt_key,
decrypted_string => v_decrypted_val);
return v_decrypted_val;
end decrypt;

end PASSWORD;
/
show errors

-- Test if it is working...
select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;

Pass result sets (REF CURSOR) between procedures and functions
rem -----------------------------------------------------------------------
rem Filename: refcurs.sql
rem Purpose: Pass result sets (REF CURSOR) between procedures and
rem functions
rem Date: 15-Jun-2001
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

-- Define TYPES package separately to be available to all programming
-- environments...
CREATE OR REPLACE PACKAGE types AS
TYPE cursortyp is REF CURSOR; -- use weak form
END;
/

-- Create test package to demonstrate passing result sets...
CREATE OR REPLACE PACKAGE test_ref_cursor AS
PROCEDURE main;
FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp;
PROCEDURE process_cursor(cur types.cursortyp);
END;
/
show errors

CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS

-- Main program entry point
PROCEDURE main IS
BEGIN
process_cursor( get_cursor_ref(1) );
process_cursor( get_cursor_ref(2) );
END;

-- Get and return a CURSOR REF/ Result Set
FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS
cur types.cursortyp;
BEGIN
if typ = 1 THEN
OPEN cur FOR SELECT * FROM emp WHERE ROWNUM < 5;
ELSE
OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5;
END IF;
RETURN cur;
END;

-- Process rows for an EMP or DEPT cursor
PROCEDURE process_cursor(cur types.cursortyp) IS
empRec emp%ROWTYPE;
deptRec dept%ROWTYPE;
BEGIN
LOOP
FETCH cur INTO empRec; -- Maybe it was an EMP cursor, try to fetch...
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('EMP ROW: '||empRec.ename);
END LOOP;
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN -- OK, so it was't EMP, let's try DEPT.
LOOP
FETCH cur INTO deptRec;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('DEPT ROW: '||deptRec.dname);
END LOOP;
END;

END;
/
show errors

EXEC test_ref_cursor.main;

Convert between different numbering systems (binary, octal, decimal and hex)
rem -----------------------------------------------------------------------
rem Filename: dbms_numsystem.sql
rem Purpose: Package with functions to convert numbers between the
rem Decimal, Binary, Octal and Hexidecimal numbering systems.
rem Usage: See sampels at the bottom of this file
rem Author: Frank Naude, 17 February 2003
rem -----------------------------------------------------------------------

set serveroutput on

CREATE OR REPLACE PACKAGE dbms_numsystem AS
function bin2dec (binval in char ) RETURN number;
function dec2bin (N in number) RETURN varchar2;
function oct2dec (octval in char ) RETURN number;
function dec2oct (N in number) RETURN varchar2;
function hex2dec (hexval in char ) RETURN number;
function dec2hex (N in number) RETURN varchar2;
END dbms_numsystem;
/
show errors

CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS

FUNCTION bin2dec (binval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(binval);
for i in 1..digits loop
current_digit := SUBSTR(binval, i, 1);
current_digit_dec := to_number(current_digit);
result := (result * 2) + current_digit_dec;
end loop;
return result;
END bin2dec;

FUNCTION dec2bin (N in number) RETURN varchar2 IS
binval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
binval := mod(N2, 2) || binval;
N2 := trunc( N2 / 2 );
end loop;
return binval;
END dec2bin;

FUNCTION oct2dec (octval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(octval);
for i in 1..digits loop
current_digit := SUBSTR(octval, i, 1);
current_digit_dec := to_number(current_digit);
result := (result * 8) + current_digit_dec;
end loop;
return result;
END oct2dec;

FUNCTION dec2oct (N in number) RETURN varchar2 IS
octval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
octval := mod(N2, 8) || octval;
N2 := trunc( N2 / 8 );
end loop;
return octval;
END dec2oct;

FUNCTION hex2dec (hexval in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(hexval);
for i in 1..digits loop
current_digit := SUBSTR(hexval, i, 1);
if current_digit in ('A','B','C','D','E','F') then
current_digit_dec := ascii(current_digit) - ascii('A') + 10;
else
current_digit_dec := to_number(current_digit);
end if;
result := (result * 16) + current_digit_dec;
end loop;
return result;
END hex2dec;

FUNCTION dec2hex (N in number) RETURN varchar2 IS
hexval varchar2(64);
N2 number := N;
digit number;
hexdigit char;
BEGIN
while ( N2 > 0 ) loop
digit := mod(N2, 16);
if digit > 9 then
hexdigit := chr(ascii('A') + digit - 10);
else
hexdigit := to_char(digit);
end if;
hexval := hexdigit || hexval;
N2 := trunc( N2 / 16 );
end loop;
return hexval;
END dec2hex;

END dbms_numsystem;
/
show errors

-- Examples:
select dbms_numsystem.dec2bin(22) from dual;
select dbms_numsystem.bin2dec('10110') from dual;
select dbms_numsystem.dec2oct(44978) from dual;
select dbms_numsystem.oct2dec(127662) from dual;
select dbms_numsystem.dec2hex(44978) from dual;
select dbms_numsystem.hex2dec('AFB2') from dual;

Random number/ string generator package
/*
------------------------------------------------------------------------------
Filename: random.txt
Purpose: Random number/ string generator package
Author: Unknown
Original: http://orafaq.net/scripts/sql/random.txt
Edits:
19990908 Phil Rand Added functions rand_string(), smaller().
------------------------------------------------------------------------------
*/

create or replace package random
is
procedure srand(new_seed in number);
procedure get_rand(r OUT number);
procedure get_rand_max(r OUT number, n IN number);
function rand return number;
function rand_max(n IN number) return number;
function rand_string(ssiz IN number) return varchar2;
function smaller(x IN number, y IN number) return number;
pragma restrict_references(rand, WNDS);
pragma restrict_references(rand_max, WNDS);
pragma restrict_references(random, WNDS, RNPS);
pragma restrict_references(rand_string, WNDS);
pragma restrict_references(smaller, WNDS);
end random;
/

create or replace package body random
is
multiplier constant number := 22695477;
increment constant number := 1;
"2^32" constant number := 2 ** 32;
"2^16" constant number := 2 ** 16;
"0x7fff" constant number := 32767;
Seed number := 1;

function smaller(x IN number, y IN number) return number is
begin
if x <= y then
return x;
else
return y;
end if;
end smaller;

function rand_string(ssiz IN number) return varchar2 is
i number;
m number;
c char;
result varchar2(2000) := '';
begin
m := smaller(ssiz,2000);
for i in 1..m loop
c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
result := result || c;
end loop;
return result;
end rand_string;

procedure srand(new_seed in number) is
begin
Seed := new_seed;
end srand;

function rand return number is
begin
Seed := mod(multiplier * Seed + increment, "2^32");
return bitand(Seed/"2^16", "0x7fff");
end rand;

procedure get_rand(r OUT number) is
begin
r := rand;
end get_rand;

function rand_max(n IN number) return number is
begin
return mod(rand, n) + 1;
end rand_max;

procedure get_rand_max(r OUT number, n IN number) is
begin
r := rand_max(n);
end get_rand_max;

begin
select userenv('SESSIONID')
into Seed
from dual;
end random;
/

-- Some examples:
select random.rand_max(10) from dual;
select random.rand_max(10) from dual;
select random.rand_string(20) from dual;
select random.rand_string(20) from dual;

Function to test for Leap Years
rem -----------------------------------------------------------------------
rem Filename: leapyear.sql
rem Purpose: Check if a year is a leap year
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION isLeapYear(i_year NUMBER) RETURN boolean AS
BEGIN
-- A year is a leap year if it is evenly divisible by 4
-- but not if it's evenly divisible by 100
-- unless it's also evenly divisible by 400

IF mod(i_year, 400) = 0 OR ( mod(i_year, 4) = 0 AND mod(i_year, 100) != 0) THEN
return TRUE;
ELSE
return FALSE;
END IF;
END;
/
show errors

-- Let's test it
SET SERVEROUTPUT ON
BEGIN
IF isLeapYear(2004) THEN
dbms_output.put_line('Yes, it is a leap year');
ELSE
dbms_output.put_line('No, it is not a leap year');
END IF;
END;
/

• Print the ASCII table
rem -----------------------------------------------------------------------
rem Filename: asciitab.sql
rem Purpose: Print ASCII table
rem Date: 13-Jun-2000
rem Author: Anonymous
rem -----------------------------------------------------------------------

set serveroutput on size 10240

declare
i number;
j number;
k number;
begin
for i in 2..15 loop
for j in 1..16 loop
k:=i*16+j;
dbms_output.put((to_char(k,'000'))||':'||chr(k)||' ');
if k mod 8 = 0 then
dbms_output.put_line('');
end if;
end loop;
end loop;
end;
/
show errors

Recursive algorithms to calculate Fibonacci and Factorials

rem -----------------------------------------------------------------------
rem Filename: recurse.sql
rem Purpose: Script to demonstrate how recursive algorithms like
rem Fibonacci and Factorials can be implemented in PL/SQL
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- Computing the Factorial of a number (n!)
CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
/

-- Test n!
SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;

-- Sample output:
-- FAC(1) FAC(2) FAC(3) FAC(4) FAC(5)
-- ---------- ---------- ---------- ---------- ----------
-- 1 2 6 24 120

-- Computing the Nth Fibonacci number
CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN -- terminating condition
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2); -- recursive call
END IF;
END fib;
/

-- Test Fibonacci Series:
SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;

-- Sample output:
-- FIB(1) FIB(2) FIB(3) FIB(4) FIB(5)
-- ---------- ---------- ---------- ---------- ----------
-- 1 1 2 3 5
--

Fetch LOB column values piece-wise from PL/SQL
rem -----------------------------------------------------------------------
rem Filename: readlob.sql
rem Purpose: Fetch LOB column values piece-wise from PL/SQL
rem Date: 12-Jun-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

DROP TABLE lob_table; -- Create table to hols LOBs
CREATE TABLE lob_table (
id INTEGER,
b_lob BLOB,
c_lob CLOB,
b_file BFILE );

INSERT INTO lob_table -- Create sample record
VALUES (1, EMPTY_BLOB(), 'abcde', NULL);

DECLARE
clob_locator CLOB;
charbuf VARCHAR2(20);
read_offset INTEGER;
read_amount INTEGER;
BEGIN
-- First we need to get the lob locator
SELECT c_lob INTO clob_locator FROM lob_table WHERE id = 1;

DBMS_OUTPUT.PUT_LINE('CLOB Size: ' ||
DBMS_LOB.GETLENGTH(clob_locator));

-- Read LOB field contents
read_offset := 1;
read_amount := 20;
dbms_lob.read(clob_locator, read_amount, read_offset, charbuf);
dbms_output.put_line('CLOB Value: ' || charbuf);
END;
/

Upload and save binary files (like pictures, documents, etc) to/from the DB
rem -----------------------------------------------------------------------
rem Filename: loadlob.sql
rem Purpose: Load a binary file (images, documents, etc) into a
rem database table.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on
DROP TABLE lob_table;
DROP SEQUENCE lob_seq;

CREATE OR REPLACE DIRECTORY my_dir AS '/app/oracle/';

CREATE TABLE lob_table (id NUMBER, fil BLOB);
CREATE SEQUENCE lob_seq;

CREATE OR REPLACE PROCEDURE load_file(p_file VARCHAR2)
IS
src_lob BFILE := BFILENAME('MY_DIR', p_file);
dest_lob BLOB;
BEGIN
INSERT INTO lob_table VALUES(lob_seq.nextval, EMPTY_BLOB())
RETURNING fil INTO dest_lob;

DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
SRC_LOB => src_lob,
AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
DBMS_LOB.CLOSE(src_lob);

COMMIT;
END;
/
show errors

-- Let's test it
exec load_file('pic1.gif');
SELECT id, DBMS_LOB.GETLENGTH(fil) AS bytes_loaded
FROM lob_table;

rem -----------------------------------------------------------------------
rem Filename: savelob.sql
rem Purpose: Save a binary file (images, documents, etc) from database
rem to a flat file.
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE save_file(p_id NUMBER, p_file VARCHAR2)
IS
v_lob_loc BLOB;
v_lob_len NUMBER;
v_buffer RAW(32767);
v_buffer_size BINARY_INTEGER := 32767;
v_offset NUMBER := 1;
v_out_file UTL_FILE.FILE_TYPE;
BEGIN
SELECT fil INTO v_lob_loc FROM lob_table WHERE id = p_id;
v_lob_len := DBMS_LOB.GETLENGTH(v_lob_loc);

DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);
v_out_file := UTL_FILE.FOPEN(location => 'MY_DIR',
filename => p_file,
open_mode => 'w',
max_linesize => 32767);

WHILE (v_offset <= v_lob_len) LOOP
dbms_output.put_line('v_start : ' || to_char(v_offset));
DBMS_LOB.READ(lob_loc => v_lob_loc,
amount => v_buffer_size,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_buffer_size;
UTL_FILE.PUT_RAW(file => v_out_file,
buffer => v_buffer);
END LOOP;

UTL_FILE.FCLOSE(v_out_file);
DBMS_LOB.CLOSE(v_lob_loc);
END;
/
show errors

-- Let's test it
exec save_file(1, 'pic2.gif');
! ls -l /app/oracle/pic*.gif

Fetch LONG column values piece-wise from PL/SQL
rem -----------------------------------------------------------------------
rem Filename: readlong.sql
rem Purpose: Fetch Long column values piece-wise from PL/SQL
rem Date: 12-Jan-1999
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on

-- Create test table
drop table longtable;
create table longtable (longcol long) tablespace TOOLS;
insert into longtable values ( rpad('x', 257, 'QWERTY') );

DECLARE
cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;;
rc NUMBER;
long_piece VARCHAR2(256);
piece_len INTEGER := 0;
long_tab DBMS_SQL.VARCHAR2S;
long_len INTEGER := 0;
BEGIN
DBMS_SQL.PARSE(cur1, 'select longcol from longtable', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1);
rc := DBMS_SQL.EXECUTE(cur1);
rc := DBMS_SQL.FETCH_ROWS(cur1); -- Get one row

-- Loop until all pieces of the long column are processed
LOOP
DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 256, long_len, long_piece, piece_len);
EXIT WHEN piece_len = 0;
DBMS_OUTPUT.PUT_LINE('Long piece len='|| piece_len);

long_tab( NVL(long_tab.LAST, 0)+1 ) := long_piece; -- Add piece to table
long_len := long_len + piece_len;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cur1);
DBMS_OUTPUT.PUT_LINE('Total long col fetched, len='|| long_len);
END;
/
Demonstrate writing to a file using the UTL_FILE package
rem -----------------------------------------------------------------------
rem Filename: utlfile.sql
rem Purpose: Demonstrate writing to a file using the UTL_FILE package
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE DIRECTORY test_dir AS 'c:\';
-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;
/
Map an external file to a database view
rem -----------------------------------------------------------------------
rem Filename: fileview.sql
rem Purpose: Create a database view on top of a file
rem Date: 27-Nov-2002
rem -----------------------------------------------------------------------

-- Utl_file_dir must be set to the directory you want to read from
show parameter utl_file_dir

-- Define the table's columns
CREATE OR REPLACE TYPE Alert_Row_Type AS OBJECT (
line NUMBER(8),
text VARCHAR2(2000)
);
/

-- Create a table of many row objects
CREATE OR REPLACE TYPE Alert_Type IS TABLE OF Alert_Row_Type;
/

-- Create a function to read the data into the table
CREATE OR REPLACE FUNCTION Get_Alert
RETURN Alert_Type
IS
Alert_Tab Alert_Type := Alert_Type(Alert_Row_Type(NULL, NULL));
v_file Utl_File.File_Type;
v_line NUMBER(10) := 1;
v_text VARCHAR2(2000);
b_read BOOLEAN := TRUE;
b_first BOOLEAN := TRUE;
BEGIN
dbms_output.put_line('About to open file...');
v_file := Utl_File.FOpen('/app/oracle/admin/orcl/bdump', 'alert_orcl.log', 'r');
WHILE b_read LOOP
BEGIN
Utl_File.Get_Line(v_file, v_text);
IF b_first THEN
b_first := FALSE;
ELSE
Alert_Tab.Extend;
END IF;

Alert_Tab(Alert_Tab.Last) := Alert_Row_Type(v_line, v_text);
v_line := v_line + 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
b_read := FALSE;
END;
END LOOP;
Utl_File.FClose(v_file);
RETURN Alert_Tab;
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.internal_error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR: utl_file.other_error');
END;
/
show errors

-- Create a view to get the info from the function
CREATE OR REPLACE FORCE VIEW alert_log_file AS
SELECT LINE, TEXT
FROM Table(Cast(Get_Alert() As Alert_Type))
/

-- Test it!!!
set pages 50000
select * from alert_log_file
where text like '%ORA-%'
/
• Demonstrate Dynamic SQL
rem -----------------------------------------------------------------------
rem Filename: dynasql.sql
rem Purpose: Example PL/SQL code to demonstrate Dynamic SQL
rem Date: 25-Feb-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE CREATE_TABLE1 AS
sql_stmt varchar2(4000);
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
END;
/
show errors

CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
show errors

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/
show errors
• Demonstrate Java stored procedures
rem -----------------------------------------------------------------------
rem Filename: java.sql
rem Purpose: Demonstrate Java stored procedures (available from Oracle 8i)
rem Date: 13-Jun-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

conn / as sysdba

-- @?/javavm/install/initjvm.sql
grant javauserpriv to scott;

conn scott/tiger

prompt Loading java source into database...

create or replace java source named "Hello" as
public class Hello { /* Pure Java Code */
static public String Msg(String tail) {
return "Hello " + tail;
}
}
/
-- SHOW ERRORS not needed
-- Note, you can also use "loadjava" to load source files into Oracle.

prompt Publish Java to PL/SQL...

create or replace function hello (str varchar2) return varchar as
language java name 'Hello.Msg(java.lang.String) return java.lang.String';
/
show errors

prompt Call Java function...

select hello('Frank') from dual
/

• Execute Operating System commands from PL/SQL (Java call)
rem -----------------------------------------------------------------------
rem Filename: oscmd.sql
rem Purpose: Execute operating system commands from PL/SQL
rem Notes: Specify full paths to commands, for example,
rem specify /usr/bin/ps instead of ps.
rem Date: 09-Apr-2005
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

rem -----------------------------------------------------------------------
rem Grant Java Access to user SCOTT
rem -----------------------------------------------------------------------

conn / as sysdba

EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.io.FilePermission', '/bin/sh', 'execute');
-- Other read ,write or execute permission may be requried

rem -----------------------------------------------------------------------
rem Create Java class to execute OS commands...
rem -----------------------------------------------------------------------

conn scott/tiger

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
} else { // Linux or Unix System
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}

// Execute the command...
final Process pr = Runtime.getRuntime().exec(finalCommand);

// Capture output from STDOUT...
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("stdout: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
} catch (IOException ioe) {
System.out.println("Error printing process output.");
ioe.printStackTrace();
} finally {
try {
br_in.close();
} catch (Exception ex) {}
}

// Capture output from STDERR...
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("stderr: " + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
} catch (IOException ioe) {
System.out.println("Error printing execution errors.");
ioe.printStackTrace();
} finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}

};
/
show errors

rem -----------------------------------------------------------------------
rem Publish the Java call to PL/SQL...
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE host (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
show errors

rem -----------------------------------------------------------------------
rem Let's test it...
rem -----------------------------------------------------------------------

CALL DBMS_JAVA.SET_OUTPUT(1000000);
SET SERVEROUTPUT ON SIZE 1000000
exec host('/usr/bin/ls');

FTP Client (GET and PUT files from PL/SQL)
rem -----------------------------------------------------------------------
rem Filename: ftpclient.sql
rem Purpose: PL/SQL FTP Client
rem Date: 19-Nov-2003
rem Author: Russ Johnson, Braun Consulting
rem -----------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BRNC_FTP_PKG
AS
/**
*
* PL/SQL FTP Client
*
* Created by: Russ Johnson, Braun Consulting
*
* www.braunconsult.com
*
* OVERVIEW
* --------------------
* This package uses the standard packages UTL_FILE and UTL_TCP to perform
* client-side FTP functionality (PUT and GET) for text files as defined in
* the World Wide Web Consortium's RFC 959 document - http://www.w3.org/Protocols/rfc959/
* The procedures and functions in this package allow single or multiple file transfer using
* standard TCP/IP connections.
*
* LIMITATIONS
* --------------------
* Currently the API is limited to transfer of ASCII text files only. This is
* primarily because UTL_FILE only supports text I/O, but also because the original
* design was for creating text files from data in the Oracle database, then transferring the file to a remote host.
* Furthermore, the API does not support SSH/Secure FTP or connection through a proxy server.
* Keep in mind that FTP passes the username/password combo in plain text over TCP/IP.
*
* DB versions - 8i (8.1.x) and above. 8.0.x may work if it has the SYS.UTL_TCP package.
*
*
* Note: Since UTL_FILE is used for the client-side I/O, this package is also limited to
* transfer of files that exist in directories available to UTL_FILE for read/write.
* These directories are defined by the UTL_FILE_DIR parameter in the init.ora file.
*
* USAGE
* --------------------

* Three functions are available for FTP - PUT, GET, and FTP_MULTIPLE. FTP_MULTIPLE takes
* a table of records that define the files to be transferred (filename, directory, etc.).
* That table can have 1 record or multiple records. The PUT and GET functions are included
* for convenience to FTP one file at a time. PUT and GET return true if the file is transferred
* successfully and false if it fails. FTP_MULTIPLE returns true if no batch-level errors occur
* (such as an invalid host, refused connection, or invalid login information). It also takes the
* table of file records IN and passes it back OUT. Each record contains individual error information.
*
* EXAMPLE
* --------------------
* Transfer multiple files - 1 GET and 2 PUT from a Windows machine to a host (assuming UNIX here).
* Display any errors that occur.
* DECLARE
*
* v_username VARCHAR2(40) := 'rjohnson';
* v_password VARCHAR2(40) := 'password';
* v_hostname VARCHAR2(255) := 'ftp.oracle.com';
* v_error_message VARCHAR2(1000);
* b_put BOOLEAN;
* t_files BRNC_FTP_PKG.t_ftp_rec; -- Declare our table of file records
*
* BEGIN
*
* t_files(1).localpath := 'd:\oracle\utl_file\outbound';
* t_files(1).filename := 'myfile1.txt';
* t_files(1).remotepath := '/home/oracle/text_files';
* t_files(1).transfer_mode := 'PUT';
*
* t_files(2).localpath := 'd:\oracle\utl_file\inbound';
* t_files(2).filename := 'incoming_file.xml';
* t_files(2).remotepath := '/home/oracle/xml_files';
* t_files(2).transfer_mode := 'GET';
*
* t_files(3).localpath := 'd:\oracle\utl_file\outbound';
* t_files(3).filename := 'myfile2.txt';
* t_files(3).remotepath := '/home';
* t_files(3).transfer_mode := 'PUT';
*
* b_put := BRNC_FTP_PKG.FTP_MULTIPLE(v_error_message,
* t_files,
* v_username,
* v_password,
* v_hostname);
* IF b_put = TRUE
* THEN
* FOR i IN t_files.FIRST..t_files.LAST
* LOOP
* IF t_files.EXISTS(i)
* THEN
* DBMS_OUTPUT.PUT_LINE(t_files(i).status||' | '||
* t_files(i).error_message||' | '||
* to_char(t_files(i).bytes_transmitted)||' | '||
* to_char(t_files(i).trans_start,'YYYY-MM-DD HH:MI:SS')||' | '||
* to_char(t_files(i).trans_end,'YYYY-MM-DD HH:MI:SS'));
* END IF;
* END LOOP;
* ELSE
* DBMS_OUTPUT.PUT_LINE(v_error_message);
* END IF;
*
* EXCEPTION
* WHEN OTHERS
* THEN
* DBMS_OUTPUT.PUT_LINE(SQLERRM);
* END;
*
* CREDITS
* --------------------
* The W3C's RFC 959 that describes the FTP process.
*
* http://www.w3c.org
*
* Much of the PL/SQL code in this package was based on Java code written by
* Bruce Blackshaw of Enterprise Distributed Technologies Ltd. None of that code
* was copied, but the objects and methods greatly helped my understanding of the
* FTP Client process.
*
* http://www.enterprisedt.com
*
* VERSION HISTORY
* --------------------
* 1.0 11/19/2002 Unit-tested single and multiple transfers between disparate hosts.
*
*
*/

/**
* Exceptions
*
*/

ctrl_exception EXCEPTION;
data_exception EXCEPTION;

/**
* Constants - FTP valid response codes
*
*/

CONNECT_CODE CONSTANT PLS_INTEGER := 220;
USER_CODE CONSTANT PLS_INTEGER := 331;
LOGIN_CODE CONSTANT PLS_INTEGER := 230;
PWD_CODE CONSTANT PLS_INTEGER := 257;
PASV_CODE CONSTANT PLS_INTEGER := 227;
CWD_CODE CONSTANT PLS_INTEGER := 250;
TSFR_START_CODE1 CONSTANT PLS_INTEGER := 125;
TSFR_START_CODE2 CONSTANT PLS_INTEGER := 150;
TSFR_END_CODE CONSTANT PLS_INTEGER := 226;
QUIT_CODE CONSTANT PLS_INTEGER := 221;
SYST_CODE CONSTANT PLS_INTEGER := 215;
TYPE_CODE CONSTANT PLS_INTEGER := 200;

/**
* FTP File record datatype
*
* Elements:
* localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* filetype - reserved for future use, ignored in code
* transfer_mode - 'PUT' or 'GET'
* status - status of the transfer. 'ERROR' or 'SUCCESS'
* error_message - meaningful (hopefully) error message explaining the reason for failure
* bytes_transmitted - how many bytes were sent/received
* trans_start - date/time the transmission started
* trans_end - date/time the transmission ended
*
*/

TYPE r_ftp_rec IS RECORD(localpath VARCHAR2(255),
filename VARCHAR2(255),
remotepath VARCHAR2(255),
filetype VARCHAR2(20),
transfer_mode VARCHAR2(5),
status VARCHAR2(40),
error_message VARCHAR2(255),
bytes_transmitted NUMBER,
trans_start DATE,
trans_end DATE);

/**
* FTP File Table - used to store many files for transfer
*
*/

TYPE t_ftp_rec IS TABLE of r_ftp_rec INDEX BY BINARY_INTEGER;

/**
* Internal convenience procedure for creating passive host IP address
* and port number.
*
*/

PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2,
p_pasv_host OUT VARCHAR2,
p_pasv_port OUT NUMBER);

/**
* Function used to validate FTP server responses based on the
* code passed in p_code. Reads single or multi-line responses.
*
*/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN;

/**
* Function used to validate FTP server responses based on the
* code passed in p_code. Reads single or multi-line responses.
* Overloaded because some responses can have 2 valid codes.
*
*/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code1 IN PLS_INTEGER,
p_code2 IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN;

/**
* Procedure that handles the actual data transfer. Meant
* for internal package use. Returns information about the
* actual transfer.
*
*/

PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_pasv_host IN VARCHAR2,
p_pasv_port IN PLS_INTEGER,
p_transfer_mode IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE);

/**
* Function to handle FTP of many files.
* Returns TRUE if no batch-level errors occur.
* Returns FALSE if a batch-level error occurs.
*
* Parameters:
*
* p_error_msg - error message for batch level errors
* p_files - BRNC_FTP_PKG.t_ftp_rec table type. Accepts
* list of files to be transferred (may be any combination of PUT or GET)
* returns the table updated with transfer status, error message,
* bytes_transmitted, transmission start date/time and transmission end
* date/time
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - hostname or IP address of server Ex: 'ftp.oracle.com' or '127.0.0.1'
* p_port - port number to connect on. FTP is usually on 21, but this may be overridden
* if the server is configured differently.
*
*/

FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2,
p_files IN OUT t_ftp_rec,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
p_port IN PLS_INTEGER DEFAULT 21)
RETURN BOOLEAN;

/**
* Convenience function for single-file PUT
*
* Parameters:
* p_localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* p_filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* p_remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1'
* v_status - status of the transfer. 'ERROR' or 'SUCCESS'
* v_error_message - meaningful (hopefully) error message explaining the reason for failure
* n_bytes_transmitted - how many bytes were sent/received
* d_trans_start - date/time the transmission started
* d_trans_end - date/time the transmission ended
* p_port - port number to connect to, default is 21
* p_filetype - always set to 'ASCII', reserved for future use, ignored in code
*
*/

FUNCTION PUT(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN;

/**
* Convenience function for single-file GET
*
* Parameters:
* p_localpath - full directory name in which the local file resides or will reside
* Windows: 'd:\oracle\utl_file'
* UNIX: '/home/oracle/utl_file'
* p_filename - filename and extension for the file to be received or sent
* changing the filename for the PUT or GET is currently not allowed
* Examples: 'myfile.dat' 'myfile20021119.xml'
* p_remotepath - full directory name in which the local file will be sent or the
* remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three'
* p_username - username for FTP server
* p_password - password for FTP server
* p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1'
* v_status - status of the transfer. 'ERROR' or 'SUCCESS'
* v_error_message - meaningful (hopefully) error message explaining the reason for failure
* n_bytes_transmitted - how many bytes were sent/received
* d_trans_start - date/time the transmission started
* d_trans_end - date/time the transmission ended
* p_port - port number to connect to, default is 21
* p_filetype - always set to 'ASCII', reserved for future use, ignored in code
*
*/

FUNCTION GET(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN;

END BRNC_FTP_PKG;
/
CREATE OR REPLACE PACKAGE BODY BRNC_FTP_PKG
AS

/*****************************************************************************
** Create the passive host IP and port number to connect to
**
*****************************************************************************/

PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2,
p_pasv_host OUT VARCHAR2,
p_pasv_port OUT NUMBER)
IS

v_pasv_cmd VARCHAR2(30) := p_pasv_cmd; --Host and port to connect to for data transfer
n_port_dec NUMBER;
n_port_add NUMBER;

BEGIN

p_pasv_host := REPLACE(SUBSTR(v_pasv_cmd,1,INSTR(v_pasv_cmd,',',1,4)-1),',','.');

n_port_dec := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,4)+1,(INSTR(v_pasv_cmd,',',1,5)-(INSTR(v_pasv_cmd,',',1,4)+1))));
n_port_add := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,5)+1,LENGTH(v_pasv_cmd)-INSTR(v_pasv_cmd,',',1,5)));

p_pasv_port := (n_port_dec*256) + n_port_add;

EXCEPTION
WHEN OTHERS
THEN
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;

END CREATE_PASV;

/*****************************************************************************
** Read a single or multi-line reply from the FTP server and validate
** it against the code passed in p_code.
**
** Return TRUE if reply code matches p_code, FALSE if it doesn't or error
** occurs
**
** Send full server response back to calling procedure
*****************************************************************************/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN
IS
n_code VARCHAR2(3) := p_code;
n_byte_count PLS_INTEGER;
v_msg VARCHAR2(255);
n_line_count PLS_INTEGER := 0;
BEGIN
LOOP
v_msg := UTL_TCP.GET_LINE(p_ctrl_con);
n_line_count := n_line_count + 1;
IF n_line_count = 1
THEN
p_reply := v_msg;
ELSE
p_reply := p_reply || SUBSTR(v_msg,4);
END IF;
EXIT WHEN INSTR(v_msg,'-',1,1) <> 4;
END LOOP;
IF to_number(SUBSTR(p_reply,1,3)) = n_code
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_reply := SQLERRM;
RETURN FALSE;
END VALIDATE_REPLY;

/*****************************************************************************
** Reads a single or multi-line reply from the FTP server
**
** Return TRUE if reply code matches p_code1 or p_code2,
** FALSE if it doesn't or error occurs
**
** Send full server response back to calling procedure
*****************************************************************************/

FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_code1 IN PLS_INTEGER,
p_code2 IN PLS_INTEGER,
p_reply OUT VARCHAR2)
RETURN BOOLEAN
IS
v_code1 VARCHAR2(3) := to_char(p_code1);
v_code2 VARCHAR2(3) := to_char(p_code2);
v_msg VARCHAR2(255);
n_line_count PLS_INTEGER := 0;
BEGIN
LOOP
v_msg := UTL_TCP.GET_LINE(p_ctrl_con);
n_line_count := n_line_count + 1;
IF n_line_count = 1
THEN
p_reply := v_msg;
ELSE
p_reply := p_reply || SUBSTR(v_msg,4);
END IF;
EXIT WHEN INSTR(v_msg,'-',1,1) <> 4;
END LOOP;
IF to_number(SUBSTR(p_reply,1,3)) IN(v_code1,v_code2)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_reply := SQLERRM;
RETURN FALSE;
END VALIDATE_REPLY;

/*****************************************************************************
** Handles actual data transfer. Responds with status, error message, and
** transfer statistics.
**
** Potential errors could be with connection or file i/o
**
*****************************************************************************/

PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION,
p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_pasv_host IN VARCHAR2,
p_pasv_port IN PLS_INTEGER,
p_transfer_mode IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE)
IS
u_data_con UTL_TCP.CONNECTION;
u_filehandle UTL_FILE.FILE_TYPE;
v_tsfr_mode VARCHAR2(3) := p_transfer_mode;
v_mode VARCHAR2(1);
v_tsfr_cmd VARCHAR2(10);
v_buffer VARCHAR2(32767);
v_localpath VARCHAR2(255) := p_localpath;
v_filename VARCHAR2(255) := p_filename;
v_host VARCHAR2(20) := p_pasv_host;
n_port PLS_INTEGER := p_pasv_port;
n_bytes NUMBER;
v_msg VARCHAR2(255);
v_reply VARCHAR2(1000);
v_err_status VARCHAR2(20) := 'ERROR';

BEGIN

/** Initialize some of our OUT variables **/

v_status := 'SUCCESS';
v_error_message := ' ';
n_bytes_transmitted := 0;

IF UPPER(v_tsfr_mode) = 'PUT'
THEN
v_mode := 'r';
v_tsfr_cmd := 'STOR ';

ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
v_mode := 'w';
v_tsfr_cmd := 'RETR ';
END IF;

/** Open data connection on Passive host and port **/

u_data_con := UTL_TCP.OPEN_CONNECTION(v_host,n_port);

/** Open the local file to read and transfer data **/

u_filehandle := UTL_FILE.FOPEN(v_localpath,v_filename,v_mode);

/** Send the STOR command to tell the server we're going to upload a file **/

n_bytes := UTL_TCP.WRITE_LINE(u_ctrl_con,v_tsfr_cmd||v_filename);
IF VALIDATE_REPLY(u_ctrl_con,TSFR_START_CODE1,TSFR_START_CODE2,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

d_trans_start := SYSDATE;

IF UPPER(v_tsfr_mode) = 'PUT'
THEN
LOOP
BEGIN
UTL_FILE.GET_LINE(u_filehandle,v_buffer);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;

n_bytes := UTL_TCP.WRITE_LINE(u_data_con,v_buffer);
n_bytes_transmitted := n_bytes_transmitted + n_bytes;

END LOOP;

ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
LOOP
BEGIN
v_buffer := UTL_TCP.GET_LINE(u_data_con,TRUE);

/** Sometimes the TCP/IP buffer sends null data **/
/** we only want to receive the actual data **/

IF v_buffer IS NOT NULL
THEN
UTL_FILE.PUT_LINE(u_filehandle,v_buffer);
n_bytes := LENGTH(v_buffer);
n_bytes_transmitted := n_bytes_transmitted + n_bytes;
END IF;

EXCEPTION
WHEN UTL_TCP.END_OF_INPUT
THEN
EXIT;
END;

END LOOP;

END IF;

/** Flush the buffer on the data connection **/

--UTL_TCP.FLUSH(u_data_con);

d_trans_end := SYSDATE;

/** Close the file **/

UTL_FILE.FCLOSE(u_filehandle);

/** Close the Data Connection **/

UTL_TCP.CLOSE_CONNECTION(u_data_con);

/** Verify the transfer succeeded **/

IF VALIDATE_REPLY(u_ctrl_con,TSFR_END_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

EXCEPTION
WHEN ctrl_exception
THEN
v_status := v_err_status;
v_error_message := v_reply;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.invalid_path
THEN
v_status := v_err_status;
v_error_message := 'Directory '||v_localpath||' is not available to UTL_FILE. Check the init.ora file for valid UTL_FILE directories.';
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.invalid_operation
THEN
v_status := v_err_status;

IF UPPER(v_tsfr_mode) = 'PUT'
THEN
v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for reading.';

ELSIF UPPER(v_tsfr_mode) = 'GET'
THEN
v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for writing.';

END IF;

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.read_error
THEN
v_status := v_err_status;
v_error_message := 'The system encountered an error while trying to read '||v_filename||' in the directory '||v_localpath;

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.write_error
THEN
v_status := v_err_status;
v_error_message := 'The system encountered an error while trying to write to '||v_filename||' in the directory '||v_localpath;

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN UTL_FILE.internal_error
THEN
v_status := v_err_status;
v_error_message := 'The UTL_FILE package encountered an unexpected internal system error.';

IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);

WHEN OTHERS
THEN
v_status := v_err_status;
v_error_message := SQLERRM;
IF UTL_FILE.IS_OPEN(u_filehandle)
THEN
UTL_FILE.FCLOSE(u_filehandle);
END IF;
UTL_TCP.CLOSE_CONNECTION(u_data_con);
END TRANSFER_ASCII;

/*****************************************************************************
** Handles connection to host and FTP of multiple files
** Files can be any combination of PUT and GET
**
*****************************************************************************/

FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2,
p_files IN OUT t_ftp_rec,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
p_port IN PLS_INTEGER DEFAULT 21)
RETURN BOOLEAN
IS
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(30) := p_password;
v_hostname VARCHAR2(30) := p_hostname;
n_port PLS_INTEGER := p_port;
u_ctrl_con UTL_TCP.CONNECTION;
n_byte_count PLS_INTEGER;
n_first_index NUMBER;
v_msg VARCHAR2(250);
v_reply VARCHAR2(1000);
v_pasv_host VARCHAR2(20);
n_pasv_port NUMBER;

invalid_transfer EXCEPTION;
BEGIN

p_error_msg := 'FTP Successful'; --Assume the overall transfer will succeed

/** Attempt to connect to the host machine **/

u_ctrl_con := UTL_TCP.OPEN_CONNECTION(v_hostname,n_port);
IF VALIDATE_REPLY(u_ctrl_con,CONNECT_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Send username **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'USER '||v_username);
IF VALIDATE_REPLY(u_ctrl_con,USER_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Send password **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASS '||v_password);
IF VALIDATE_REPLY(u_ctrl_con,LOGIN_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** We should be logged in, time to transfer all files **/

FOR i IN p_files.FIRST..p_files.LAST
LOOP
IF p_files.EXISTS(i)
THEN
BEGIN

/** Change to the remotepath directory **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'CWD '||p_files(i).remotepath);
IF VALIDATE_REPLY(u_ctrl_con,CWD_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Switch to IMAGE mode **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'TYPE I');
IF VALIDATE_REPLY(u_ctrl_con,TYPE_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

/** Get a Passive connection to use for data transfer **/

n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASV');
IF VALIDATE_REPLY(u_ctrl_con,PASV_CODE,v_reply) = FALSE
THEN
RAISE ctrl_exception;
END IF;

CREATE_PASV(SUBSTR(v_reply,INSTR(v_reply,'(',1,1)+1,INSTR(v_reply,')',1,1)-INSTR(v_reply,'(',1,1)-1),v_pasv_host,n_pasv_port);

/** Transfer Data **/

IF UPPER(p_files(i).transfer_mode) = 'PUT'
THEN
TRANSFER_ASCII(u_ctrl_con,
p_files(i).localpath,
p_files(i).filename,
v_pasv_host,
n_pasv_port,
p_files(i).transfer_mode,
p_files(i).status,
p_files(i).error_message,
p_files(i).bytes_transmitted,
p_files(i).trans_start,
p_files(i).trans_end);

ELSIF UPPER(p_files(i).transfer_mode) = 'GET'
THEN
TRANSFER_ASCII(u_ctrl_con,
p_files(i).localpath,
p_files(i).filename,
v_pasv_host,
n_pasv_port,
p_files(i).transfer_mode,
p_files(i).status,
p_files(i).error_message,
p_files(i).bytes_transmitted,
p_files(i).trans_start,
p_files(i).trans_end);
ELSE
RAISE invalid_transfer; -- Raise an exception here
END IF;

EXCEPTION
WHEN ctrl_exception
THEN
p_files(i).status := 'ERROR';
p_files(i).error_message := v_reply;

WHEN invalid_transfer
THEN
p_files(i).status := 'ERROR';
p_files(i).error_message := 'Invalid transfer method. Use PUT or GET.';

END;
END IF;
END LOOP;

/** Send QUIT command **/
n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'QUIT');

/** Don't need to validate QUIT, just close the connection **/

UTL_TCP.CLOSE_CONNECTION(u_ctrl_con);
RETURN TRUE;

EXCEPTION
WHEN ctrl_exception
THEN
p_error_msg := v_reply;
UTL_TCP.CLOSE_ALL_CONNECTIONS;
RETURN FALSE;
WHEN OTHERS
THEN
p_error_msg := SQLERRM;
UTL_TCP.CLOSE_ALL_CONNECTIONS;
RETURN FALSE;
END FTP_MULTIPLE;

/*****************************************************************************
** Convenience function for single-file PUT
** Formats file information for FTP_MULTIPLE function and calls it.
**
*****************************************************************************/

FUNCTION PUT(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN
IS
t_files t_ftp_rec;
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(50) := p_password;
v_hostname VARCHAR2(100) := p_hostname;
n_port PLS_INTEGER := p_port;
v_err_msg VARCHAR2(255);
b_ftp BOOLEAN;
BEGIN
t_files(1).localpath := p_localpath;
t_files(1).filename := p_filename;
t_files(1).remotepath := p_remotepath;
t_files(1).filetype := p_filetype;
t_files(1).transfer_mode := 'PUT';

b_ftp := FTP_MULTIPLE(v_err_msg,
t_files,
v_username,
v_password,
v_hostname,
n_port);
IF b_ftp = FALSE
THEN
v_status := 'ERROR';
v_error_message := v_err_msg;
RETURN FALSE;
ELSIF b_ftp = TRUE
THEN
v_status := t_files(1).status;
v_error_message := t_files(1).error_message;
n_bytes_transmitted := t_files(1).bytes_transmitted;
d_trans_start := t_files(1).trans_start;
d_trans_end := t_files(1).trans_end;
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_status := 'ERROR';
v_error_message := SQLERRM;
RETURN FALSE;
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END PUT;

/*****************************************************************************
** Convenience function for single-file GET
** Formats file information for FTP_MULTIPLE function and calls it.
**
*****************************************************************************/

FUNCTION GET(p_localpath IN VARCHAR2,
p_filename IN VARCHAR2,
p_remotepath IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_hostname IN VARCHAR2,
v_status OUT VARCHAR2,
v_error_message OUT VARCHAR2,
n_bytes_transmitted OUT NUMBER,
d_trans_start OUT DATE,
d_trans_end OUT DATE,
p_port IN PLS_INTEGER DEFAULT 21,
p_filetype IN VARCHAR2 := 'ASCII')
RETURN BOOLEAN
IS
t_files t_ftp_rec;
v_username VARCHAR2(30) := p_username;
v_password VARCHAR2(50) := p_password;
v_hostname VARCHAR2(100) := p_hostname;
n_port PLS_INTEGER := p_port;
v_err_msg VARCHAR2(255);
b_ftp BOOLEAN;
BEGIN
t_files(1).localpath := p_localpath;
t_files(1).filename := p_filename;
t_files(1).remotepath := p_remotepath;
t_files(1).filetype := p_filetype;
t_files(1).transfer_mode := 'GET';

b_ftp := FTP_MULTIPLE(v_err_msg,
t_files,
v_username,
v_password,
v_hostname,
n_port);
IF b_ftp = FALSE
THEN
v_status := 'ERROR';
v_error_message := v_err_msg;
RETURN FALSE;
ELSIF b_ftp = TRUE
THEN
v_status := t_files(1).status;
v_error_message := t_files(1).error_message;
n_bytes_transmitted := t_files(1).bytes_transmitted;
d_trans_start := t_files(1).trans_start;
d_trans_end := t_files(1).trans_end;
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_status := 'ERROR';
v_error_message := SQLERRM;
RETURN FALSE;
--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END GET;

END BRNC_FTP_PKG;
/

Send e-mail messages from PL/SQL
rem -----------------------------------------------------------------------
rem Filename: smtp.sql
rem Purpose: Send e-mail messages from PL/SQL
rem Notes: From Oracle8i release 8.1.6 one can send e-mail messages
rem directly from PL/SQL using either the UTL_TCP or UTL_SMTP
rem packages. JServer needs to be installed and configured.
rem Pont the IP Address to your local SMTP (Simple Mail
rem Transport) Server. No pipes or external procedures are
rem required.
rem Date: 27-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(
-20000, 'Unable to send e-mail message from pl/sql because of: '||
sqlerrm);
END;
/
show errors

-- Examples:
set serveroutput on

exec send_mail(msg_to =>'you@yourdomain.com');

exec send_mail(msg_to =>'you@yourdomain.com', -
msg_text=>'Look Ma, I can send mail from plsql' -
);
• Send e-mail messages from PL/SQL with MIME attachments

rem ----------------------------------------------------------------------
rem Filename: smtp-att.sql
rem Purpose: Send e-mail messages and attachments from PL/SQL
rem Notes: From Oracle8i release 8.1.6 one can send e-mail messages
rem directly from PL/SQL using either the UTL_TCP or UTL_SMTP
rem packages. Jserver needs to be installed and configured.
rem No pipes or external procedures required.
rem Date: 15-MAR-2001
rem Author: Virgilio Nunes (Virgilio@logtek.co.za)
rem ----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX SENDING THE EMAIL
msg_to varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX RECIEVING THE EMAIL
msg_subject varchar2 := 'Output file TEST1', ----- EMAIL SUBJECT
msg_text varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
v_output1 varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
c utl_tcp.connection;
rc integer;
crlf VARCHAR2(2):= CHR(13)||CHR(10);
mesg VARCHAR2( 32767 );
BEGIN
c := utl_tcp.open_connection('196.35.140.18', 25); ----- OPEN SMTP PORT CONNECTION
rc := utl_tcp.write_line(c, 'HELO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from); ----- MAIL BOX SENDING THE EMAIL
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to); ----- MAIL BOX RECIEVING THE EMAIL
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); ----- EMAIL MESSAGE BODY START
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
rc := utl_tcp.write_line(c, 'From: '||msg_from||' <'||msg_from||'>');
rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
rc := utl_tcp.write_line(c, 'To: '||msg_to||' <'||msg_to||'>');
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
rc := utl_tcp.write_line(c, ''); ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE
rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit');
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text); ----- TEXT OF EMAIL MESSAGE
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain;'); ----- 2ND BODY PART.
rc := utl_tcp.write_line(c, ' name="Test.txt"');
rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
rc := utl_tcp.write_line(c, ' filename="Test.txt"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, v_output1);
rc := utl_tcp.write_line(c, '-------SECBOUND--');
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, '.'); ----- EMAIL MESSAGE BODY END
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
when others then
raise_application_error(-20000, SQLERRM);
END;
/

Mailmerge: Merge data from the table/view data sources into a custom template

rem -----------------------------------------------------------------------
rem Filename: mailmerge.sql
rem Purpose: Merge data from the table/view data sources into a custom
rem template.
rem Date: 30 Dec 2004
rem Author: Claudiu Ariton (aritonc@yahoo.com)
rem
rem Package detail:
rem TEMP_MAIL_MERGE - temporary table to store the result
rem DATA_SOURCES - data sources table
rem mail_merge procedure - Merge data from list of values into template
rem generic_mail_merge - Merge data from a table/view into template
rem
rem To define a template use $n where n is the position of a field in
rem data_sources_v view (escape character is $).
rem
rem E.G.: $2 $3 born on $4 has to pay $$$5. Call us at $6.
rem -----------------------------------------------------------------------

/*************************** Create result temp table, test table and context *************************/

create GLOBAL TEMPORARY table TEMP_MAIL_MERGE
(
PKID NUMBER ,
RESULT VARCHAR2(4000) )
On Commit Delete Rows
/

create table data_sources
(ID Number(10) primary key,
P2 Varchar2(10),
P3 Varchar2(50),
P4 DATE,
P5 NUMBER
)
/

Insert into DATA_SOURCES
(ID, P2, P3, P4, P5)
Values
(1, 'Claudiu', 'Ariton', TO_DATE('09/21/1976 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100000)
/
Commit
/

create or replace context mm_ctx using mail_merge
/

create or replace view data_sources_v as
select ID PKID, P2, P3, P4, P5,
SYS_CONTEXT('mm_ctx','param1',2000) param1 from
data_sources
/

/*************************** Create package head *************************/

Create or Replace Package mail_merge as

/**************************************************************************************
*
* TITLE......: Mail Merge
* DESCRIPTION: Merge data from the data source into a custom template
*
* AUTHOR.....: Claudiu Ariton
* DATE.......: april 2004
*
* Modifications
*
**************************************************************************************/

type Argv is table of varchar2(4000) index by binary_integer;
emptyargv argv;

procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx' );

function mail_merge(
p_message in varchar2,
p_argv in argv ,
p_esc_char in varchar2 default '$') return varchar2;

Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv);

end mail_merge;
/

/*************************** Create package body *************************/

Create or Replace Package Body mail_merge as

-- -----------------------------------------------------------------------------------
-- Set_ctx - set generic context
-- -----------------------------------------------------------------------------------
procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx' )
as

begin
dbms_session.set_context( p_ctx, p_name, p_value, USER);
end;

-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from list of vaalues into a custom template
-- -----------------------------------------------------------------------------------
function mail_merge(
p_message in varchar2,
p_argv in argv ,
p_esc_char in varchar2 default '$') return varchar2 is
--
l_message long := null;
l_str long := p_message;
l_idx number := 1;
l_ptr number := 1;
l_poz varchar2(10);
l_on number;
begin
if nvl( instr( p_message, p_esc_char ), 0 ) = 0 and
nvl( instr( p_message, '\' ), 0 ) = 0 then
return p_message;
end if;
loop

l_on:=0;
l_ptr := instr( l_str, p_esc_char );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );

l_poz:=null;
while substr( l_str, 1, 1 ) in ('0','1','2','3','4','5','6','7','8','9') loop
l_poz:=l_poz||substr( l_str, 1, 1 );
l_str := substr( l_str, 2 );
l_on:=1;
end loop;

begin
l_message := l_message || p_argv(to_number(l_poz));
exception
when no_data_found then l_message := l_message || '';
when others then null;
end;

if (substr( l_str,1,1 ) = p_esc_char) and (l_on=0) then
l_message := l_message || p_esc_char;
l_str := substr( l_str, 2 );
end if;

end loop;

l_str := l_message || l_str;
l_message := null;
loop
l_ptr := instr( l_str, '\' );
exit when l_ptr = 0 or l_ptr is null;
l_message := l_message || substr( l_str, 1, l_ptr-1 );
l_str := substr( l_str, l_ptr+1 );
if substr( l_str, 1, 1 ) = 'n' then
l_message := l_message || chr(10);
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = 't' then
l_message := l_message || chr(9);
l_str := substr( l_str, 2 );
elsif substr( l_str, 1, 1 ) = '\' then
l_message := l_message || '\';
l_str := substr( l_str, 2 );
else
l_message := l_message || '\';
end if;
end loop;

return l_message || l_str;
end mail_merge;

-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from a table/viiew into a custom template
-- -----------------------------------------------------------------------------------
Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv)
is
l_theCursor integer default dbms_sql.open_cursor;
l_defcolumn varchar2(4000);
l_columnValue argv;
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
v_result varchar2(4000);
v_pkid number;
begin
execute immediate
'alter session set
nls_date_format='''|| p_date_format ||'''';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

if p_bindid is not null then
dbms_sql.bind_variable( l_theCursor, ':1', p_bindid );
end if;

dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

-- seteaza context
begin
for i in 1..p_list_val.count loop
set_ctx('param'||to_char(i),p_list_val(i));
end loop;
exception
when others then null;
end;

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_defcolumn, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue(i) );
if upper(l_descTbl(i).col_name)='PKID' then
v_pkid:=to_number(l_columnValue(i));
end if;
end loop;

v_result:=mail_merge( p_ttext,l_columnValue);
insert into TEMP_MAIL_MERGE(pkid,result) values (v_pkid,v_result);
end loop;
dbms_sql.close_cursor( l_theCursor);
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
if dbms_sql.is_open(l_theCursor) then
dbms_sql.close_cursor( l_theCursor);
end if;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;

end mail_merge;
/

/*************************** Test it *************************/
declare
v_list_val mail_merge.argv;
v_query varchar2(4000);
begin
v_list_val(1):='(040)-2313543';
v_query:= 'select * from data_sources_v vt where 1=1'||' AND vt.pkid=:1';
mail_merge.generic_mail_merge('$2 $3 born on $4 has to pay $$$5. Call us at $6. Thank you very much.',
v_query,'dd-MON-yyyy',1,v_list_val);
end;
/

select * from temp_mail_merge
/

• Read an Internet Web pages from PL/SQL
rem -----------------------------------------------------------------------
rem Filename: httpget.sql
rem Purpose: Access Internet Web pages from SQL or PL/SQL
rem Notes: From Oracle 8.0 one can retrieve web pages directly
rem from SQL or PL/SQL. Note you need to run utlhttp.sql as
rem SYS before this procedure will work.
rem Date: 27-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000

select utl_http.request('http://www.orafaq.net/') from dual;

Track DLL changes (create, drop, alter) within a schema

rem -----------------------------------------------------------------------
rem Filename: auditdll.sql
rem Purpose: Maintain an audit log of DDL changes (alter/ drop/ create)
rem within a schema
rem Date: 15-Feb-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

DROP TRIGGER audit_ddl_changes
/
DROP TABLE dll_audit_log
/

CREATE TABLE dll_audit_log (
stamp DATE,
username VARCHAR2(30),
osuser VARCHAR2(30),
machine VARCHAR2(30),
terminal VARCHAR2(30),
operation VARCHAR2(30),
objtype VARCHAR2(30),
objname VARCHAR2(30))
/

CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER create OR drop OR alter
ON scott.SCHEMA -- Change SCOTT to your schema name!!!
-- ON DATABASE
BEGIN
INSERT INTO dll_audit_log VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/
show errors

-- Now, let's test it
CREATE TABLE my_test_table (col1 DATE)
/
DROP TABLE my_test_table
/
set pages 50000
SELECT * FROM dll_audit_log
/

Count the number of rows in ALL tables for the current schema

rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in current schema
rem using PL/SQL
rem Date: 15-Apr-2000
rem Author: Eberhardt, Roberto (Bolton) (reberhar@husky.ca)
rem -----------------------------------------------------------------------

set serveroutput on size 1000000

DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with more rows
cursor c1 is select table_name from user_tables order by table_name;
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||
to_char(t_total_records,'99999999')||' record(s)');

end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/
List tables from schema with more than X rows
rem -----------------------------------------------------------------------
rem Filename: countall2.sql
rem Purpose: List tables from schema with more than X rows
rem Date: 15-Sep-2005
rem Author: Praveen Kumar Chugh
rem -----------------------------------------------------------------------

REM First of all create the following function - rowcount...

CREATE OR REPLACE FUNCTION rowcount(tname VARCHAR2) RETURN NUMBER IS
x NUMBER;
stmt VARCHAR2(200);
BEGIN
stmt := 'select count(*) from '||tname;
execute immediate stmt into x;
return x;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
/
SHOW ERRORS

REM Then write this query...

SELECT table_name, roucount(table_name) Records
FROM cat
WHERE roucount(table_name) >= 100;
/

Replace all occurences of a substring with another substring
rem -----------------------------------------------------------------------
rem Filename: strreplace.sql
rem Purpose: Replace all occurences of a substring with another substring
rem Date: 28-Jul-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

create or replace function strreplace(str varchar2, from_str varchar2, to_str varchar2)
return varchar2
AS
str_temp varchar2(4000);
str_pos� number := instr(str, from_str);
BEGIN
str_temp := str;
while ( str_pos > 0 ) loop
str_temp := substr(str_temp, 0, str_pos-1) || to_str ||
substr(str_temp, str_pos + length(from_str));
str_pos� := instr(str_temp, from_str);
end loop;
return str_temp;
END;
/
show errors

-- Examples

select strreplace('This is a beautiful day!', 'beautiful', 'horrible')
from dual
/

select 'mv '||name||' '||strreplace(name, 'OLDSID', 'NEWSID')
from v$datafile
/

Spell out numbers to words (handy for cheque printing)
rem -----------------------------------------------------------------------
rem Filename: cheqprint.sql
rem Purpose: This function will convert a number to words, handy for
rem ptinting cheques.
rem Notes: The number before the decimal should be between 1..5373484
rem Date: 24-Feb-2003
rem Author: Sebastian Thomas, sebastianthomas@rediffmail.com
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION NUMBER_CONVERSION(NUM NUMBER) RETURN VARCHAR2
IS
A VARCHAR2(1000);
B VARCHAR2(20);
X NUMBER;
Y NUMBER := 1;
Z NUMBER;
LSIGN NUMBER;
NO NUMBER;
BEGIN
X:= INSTR(NUM, '.');
LSIGN := SIGN(NUM);
NO := ABS(NUM);
IF X = 0 THEN
SELECT TO_CHAR(TO_DATE(NO, 'J'), 'JSP') INTO A FROM DUAL;
ELSE
SELECT to_char(to_date(SUBSTR(NO, 1,
NVL(INSTR(NO, '.')-1, LENGTH(NO))),
'J'), 'JSP') INTO A FROM DUAL;
SELECT LENGTH(SUBSTR(NO, INSTR(NO, '.')+1)) INTO Z FROM DUAL;
A := A ||' POINT ';
WHILE Y< Z+1 LOOP
SELECT TO_CHAR(TO_DATE(SUBSTR(NO, (INSTR(NO, '.')+Y), 1), 'J'), 'JSP')
INTO B FROM DUAL;
A := A || B ||' ';
y :=y+1;
END LOOP;
END IF;
IF LSIGN = -1 THEN
RETURN 'NEGATIVE '||A;
ELSE
RETURN A;
END IF;
END;
/
show errors

-- Examples:
Select number_conversion(-3786.9899876) from dual;
Select number_conversion(7685.78788) from dual;
Select number_conversion(7678) from dual;

Print cheque amounts in Indian Style
rem -----------------------------------------------------------------------
rem Filename: indicheq.sql
rem Purpose: This procedure will convert numbers to chars for printing
rem cheques amount in Indian Style. It will print amount from
rem Rs1 to Rs.989999999.
rem Note: In order to see your output in SQL*Plus, set serverout on
rem Date: 22-Feb-2003
rem Author: Birender Kumar, bir_canada@yahoo.com
rem Updateded: 04-Aug-2003
rem Updated By: marine, marine00072003@yahoo.com
rem -----------------------------------------------------------------------

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE cheq(n NUMBER)
as
L NUMBER := 0;
OUTPUT VARCHAR2(2000) := '';
X VARCHAR2(2000) := '';
X1 VARCHAR2(2000) := '';
C1 VARCHAR2(2000) := '';
BEGIN
L := length(N);

IF N < 0 OR N > 999999999 THEN
DBMS_OUTPUT.PUT_LINE('INVALID AMOUNT');
else

if ( N = 0 ) THEN
X := 'ZERO ';
elsif ( N <= 99999 ) THEN
X := to_char(to_date(N,'J'),'JSP') || ' ';
else

if ( to_number(substr(N, L - 5 + 1)) = 0 ) then
X := '';
else
X := to_char(to_date(to_number(substr(N, L - 5 + 1)),'J'),'JSP') || ' ';
end if;

if ( L = 6 ) then
X1 := to_char(to_date(to_number(substr(N, 1, L - 5)),'J'),'JSP') || ' LAKH ';
else
if ( to_number(substr(N, L - 5 -1, 2)) = 0 ) then
X1 := '';
else
X1 := to_char(to_date(to_number(substr(N, L - 5 - 1, 2)),'J'),'JSP') || ' LAKH ';
end if;

if ( L >= 8 ) then
C1 := to_char(to_date(to_number(substr(N, 1, L-7)),'J'),'JSP')||' CRORE ';
end if;
end if;
end if;

if ( N = 0 OR N = 1 ) THEN
DBMS_OUTPUT.PUT_LINE(N||' => '||X||'RUPEE ONLY');
else
DBMS_OUTPUT.PUT_LINE(N||' => '||C1||X1||X||'RUPEES ONLY');
end if;
end if;
END CHEQ;
/
show errors

NYSIIS function (an improvement on SoundeX)
rem -----------------------------------------------------------------------
rem Filename: nysiis.pls
rem Purpose: NYSIIS function (an improvement on soundex)
rem Notes: Convert a name to a phonetic coding of up to six characters
rem Date: 19-Jul-2004
rem Author: Trevor Fairhurst, trevgf@yahoo.com
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION NYSIIS (v_text IN VARCHAR2) RETURN VARCHAR2 is

v_sub varchar2(300);
v_length number(10);
v_textin varchar2(300);
v_key varchar2(1);

begin
SELECT UPPER ( v_text ) into v_textin from dual;
dbms_output.put_line( 'Entered surname :' || v_textin);

dbms_output.put_line( ' [1] remove all S and Z chars from the end of the surname ' );
LOOP
SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
dbms_output.put_line('Last letter :' || v_sub);
if v_sub = 'S' OR v_sub = 'Z' THEN
SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
dbms_output.put_line('As last letter s or z drop last letter giving :' || v_textin || ' and check new last letter');
else
dbms_output.put_line('Last letter not s or z completed step 1');
EXIT;
end if;
END LOOP;
dbms_output.put_line('Step 1 completed giving :' || v_textin );

dbms_output.put_line( ' [2] transcode initial strings MAC => MC and PF => F and PH => F ' );
if SUBSTR ( v_textin , 1 , 3 ) = 'MAC' THEN
SELECT 'MC' || SUBSTR ( v_textin , 4 ) into v_textin from dual ;
elsif SUBSTR ( v_textin , 1 , 2 ) = 'PH' THEN
SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
elsif SUBSTR ( v_textin , 1 , 2 ) = 'PF' THEN
SELECT 'F' || SUBSTR ( v_textin , 3 ) into v_textin from dual ;
end if;

dbms_output.put_line('Step 2 completed giving :' || v_textin );

dbms_output.put_line( ' [3] transcode trailing strings IX => IC and EX => EC and YE,EE,IE => Y and NT,ND => D ' );
SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
dbms_output.put_line('Last 2 letters :' || v_sub);
if UPPER(v_sub) in ('IX','EX','YE','EE','IE','NT','ND') THEN
SELECT decode ( UPPER(v_sub) , 'IX','IC',
'EX','EC',
'YE','Y',
'EE','Y',
'IE','Y',
'NT','D',
'ND','D', NULL ) into v_sub from dual ;
SELECT SUBSTR ( v_textin , 1, (length (v_textin) - 1)) || v_sub into v_textin from dual ;
end if;
dbms_output.put_line('Step 3 completed giving :' || v_textin );

dbms_output.put_line( '[4] transcode EV to EF if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'EV','EF') into v_textin from dual ;
dbms_output.put_line('Step 4 completed giving :' || v_textin );

dbms_output.put_line( '[5] first character of name as first character of key continue with remaining characters');
SELECT SUBSTR ( v_textin , 1 , 1) into v_key from dual;
SELECT SUBSTR ( v_textin , 2 ) into v_textin from dual;
dbms_output.put_line('Step 5 completed first character of key :' || v_key );

dbms_output.put_line( '[6] replace all vowels with A');
SELECT TRANSLATE( v_textin,'AEIOU','AAAAA') into v_textin from dual ;
dbms_output.put_line('Step 6 completed giving :' || v_textin );

dbms_output.put_line( '[7] remove any W that follows a vowel');
SELECT REPLACE( v_textin,'AW','A') into v_textin from dual ;
dbms_output.put_line('Step 7 completed giving :' || v_textin );

dbms_output.put_line( '[8] transcode GHT to GT ');
SELECT REPLACE( v_textin,'GHT','GT') into v_textin from dual ;
dbms_output.put_line('Step 8 completed giving :' || v_textin );

dbms_output.put_line( '[9] transcode DG to G ');
SELECT REPLACE( v_textin,'DG','G') into v_textin from dual ;
dbms_output.put_line('Step 9 completed giving :' || v_textin );

dbms_output.put_line( '[10] transcode PH to F ');
SELECT REPLACE( v_textin,'PH','F') into v_textin from dual ;
dbms_output.put_line('Step 10 completed giving :' || v_textin );

dbms_output.put_line( '[11] if not first character, eliminate all H preceded or followed by a vowel ');
if length(v_textin) > 1 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'HA','A') into v_textin from dual ;
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'AH','A') into v_textin from dual ;
end if;
dbms_output.put_line('Step 11 completed giving :' || v_textin );

dbms_output.put_line( '[12] change KN to N, else K to C ');
SELECT REPLACE( v_textin,'KN','N') into v_textin from dual ;
SELECT TRANSLATE( v_textin,'K','C') into v_textin from dual ;
dbms_output.put_line( 'Step 12 completed giving :' || v_textin );

dbms_output.put_line( '[13] transcode M to N if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'M','N') into v_textin from dual ;
dbms_output.put_line('Step 13 completed giving :' || v_textin );

dbms_output.put_line( '[14] transcode Q to G if not at start of name');
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Q','G') into v_textin from dual ;
dbms_output.put_line('Step 14 completed giving :' || v_textin );

dbms_output.put_line( '[15] transcode transcode SH to S ');
SELECT REPLACE( v_textin,'SH','S') into v_textin from dual ;
dbms_output.put_line('Step 15 completed giving :' || v_textin );

dbms_output.put_line( '[16] transcode transcode SCH to S ');
SELECT REPLACE( v_textin,'SCH','S') into v_textin from dual ;
dbms_output.put_line('Step 16 completed giving :' || v_textin );

dbms_output.put_line( '[17] transcode transcode YW to Y ');
SELECT REPLACE( v_textin,'YW','Y') into v_textin from dual ;
dbms_output.put_line('Step 17 completed giving :' || v_textin );

dbms_output.put_line( '[18] if not first or last character, change Y to A');
if length(v_textin) > 2 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2, length(v_textin) - 2),'Y','A') || SUBSTR ( v_textin , (length (v_textin))) into v_textin from dual ;
end if;
dbms_output.put_line('Step 18 completed giving :' || v_textin );

dbms_output.put_line( '[19] transcode transcode WR to R ');
SELECT REPLACE( v_textin,'WR','R') into v_textin from dual ;
dbms_output.put_line('Step 19 completed giving :' || v_textin );

dbms_output.put_line( '[20] if not first character, change Z to S ');
if length(v_textin) > 1 THEN
SELECT SUBSTR(v_textin, 1 , 1) || REPLACE( SUBSTR(v_textin, 2),'Z','S') into v_textin from dual ;
end if;
dbms_output.put_line('Step 20 completed giving :' || v_textin );

dbms_output.put_line( '[21] transcode terminal AY to Y');
if length(v_textin) > 1 THEN
SELECT SUBSTR ( v_textin , (length (v_textin) - 1)) into v_sub from dual;
if v_sub = 'AY' THEN
SELECT SUBSTR(v_textin, 1 , length(v_textin) - 2 ) || 'Y' into v_textin from dual ;
end if;
end if;
dbms_output.put_line('Step 21 completed giving :' || v_textin );

dbms_output.put_line( '[22] remove trailing vowels');
LOOP
SELECT SUBSTR ( v_textin , (length (v_textin))) into v_sub from dual;
dbms_output.put_line('Last letter :' || v_sub);
if v_sub = 'A' THEN
SELECT SUBSTR ( v_textin , 1 , (length (v_textin) -1 )) into v_textin from dual ;
dbms_output.put_line('As last letter A drop last letter giving :' || v_textin || ' and check new last letter');
else
dbms_output.put_line('Last letter not A step 22 completed');
EXIT;
end if;
END LOOP;
dbms_output.put_line('Step 22 completed giving :' || v_textin );

dbms_output.put_line( '[23] collapse all strings of repeated characters');
if length(v_textin) > 2 THEN
LOOP
SELECT v_textin into v_sub from dual;
SELECT REPLACE( v_textin,'AA','A') into v_textin from dual ;
SELECT REPLACE( v_textin,'BB','B') into v_textin from dual ;
SELECT REPLACE( v_textin,'CC','C') into v_textin from dual ;
SELECT REPLACE( v_textin,'DD','D') into v_textin from dual ;
SELECT REPLACE( v_textin,'FF','F') into v_textin from dual ;
SELECT REPLACE( v_textin,'GG','G') into v_textin from dual ;
SELECT REPLACE( v_textin,'HH','H') into v_textin from dual ;
SELECT REPLACE( v_textin,'JJ','J') into v_textin from dual ;
SELECT REPLACE( v_textin,'LL','L') into v_textin from dual ;
SELECT REPLACE( v_textin,'NN','N') into v_textin from dual ;
SELECT REPLACE( v_textin,'PP','P') into v_textin from dual ;
SELECT REPLACE( v_textin,'RR','R') into v_textin from dual ;
SELECT REPLACE( v_textin,'SS','S') into v_textin from dual ;
SELECT REPLACE( v_textin,'TT','T') into v_textin from dual ;
SELECT REPLACE( v_textin,'VV','V') into v_textin from dual ;
SELECT REPLACE( v_textin,'WW','W') into v_textin from dual ;
SELECT REPLACE( v_textin,'XX','X') into v_textin from dual ;
SELECT REPLACE( v_textin,'ZZ','Z') into v_textin from dual ;
dbms_output.put_line( v_textin );
if v_sub = v_textin OR v_sub is NULL THEN
EXIT;
end if;
END LOOP;
end if;

dbms_output.put_line('Step 23 completed giving :' || v_textin );

dbms_output.put_line('[24] put back in first letter and return' );
SELECT v_key || v_textin into v_textin from dual;

RETURN( v_textin );

end NYSIIS;
/

Converts a string of text into seperate soundex values
rem --------------------------------------------------------------------------
rem Filename: M_SOUNDEX.SQL
rem Purpose: Converts a string of text into seperate soundex values. Treating
rem it as space deliminated words. Useful when searching text strings for a sounds like.
rem
rem Notes: USEAGE "select M_SOUNDEX('the cat sat on the mat') from dual;"
rem M_SOUNDEX('THECATSATONTHEMAT')
rem -----------------------------------
rem T000 C300 S300 O500 T000 M300
rem
rem
rem select M_SOUNDEX('the cat sat on the mat') from dual where
rem M_SOUNDEX('the cat sat on the mat') like ('%' || SOUNDEX('cot') || '%');
rem
rem Date: 01-Mar-2005
rem Author: Trevor Fairhurst, trevgf@yahoo.com
rem --------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION "M_SOUNDEX" (v_text IN VARCHAR2) RETURN VARCHAR2 is
v_number number(10);
v_textin varchar2(4000);
v_textout varchar2(4000);
begin
SELECT UPPER (TRIM( v_text )) into v_textin from dual;
dbms_output.put_line( 'Entered text :' || v_textin);
SELECT '' into v_textout from dual;
LOOP
SELECT instr( v_textin , ' ' , 1 , 1 ) into v_number from dual;
if v_number = 0 THEN
SELECT v_textout || ' ' || SOUNDEX(v_textin) into v_textout from dual ;
EXIT;
else
SELECT v_textout || ' ' || SOUNDEX(substr (v_textin , 0 , v_number - 1 )) into v_textout from dual;
SELECT substr (v_textin , v_number + 1 ) into v_textin from dual;
end if;
END LOOP;
RETURN( v_textout );
end M_SOUNDEX;
/

General DBA Scripts
1. Show database uptime in days and hours
rem -----------------------------------------------------------------------
rem Filename: uptime.sql
rem Purpose: Display database uptime in days and hours
rem to SYS or SYSTEM
rem Date: 12-Jan-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from sys.v_$session
where sid=1 /* this is PMON */
/
Create database user like an exiting user (with exact privs)

rem -----------------------------------------------------------------------
rem Filename: cr8like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem database user. User data will not be copied.
rem Date: 02-Nov-1998
rem Author: Frank Naude, Oracle FAQ
rem Updated: Konstantin Krivosheyev - 7 Dec 2002
rem Updated: Frank Naude - 18 Dec 2003, 2 Dec 2004
rem -----------------------------------------------------------------------

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Tablespace Quotas...
select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');

-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/
• Switch from one database user to another without password (su.sql)

rem -----------------------------------------------------------------------
rem Filename: su.sql
rem Purpose: Switch from one database user to another (local db only)
rem Syntax: @su
rem Notes: Require ALTER USER and SELECT ON DBA_USERS privileges
rem Date: 12-Apr-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off head off
col nl newline
spool su.tmp

select 'alter user &&1 identified by &&1;' nl,
'connect &&1/&&1' nl,
'alter user &&1 identified by values '''||u.password||''';' nl
from sys.dba_users u
where u.username = upper('&&1')
and u.username <> user
/

spool off

@su.tmp

REM Delete the file. Use "rm" on Unix, "del" for Win/Dos
! rm su.tmp
! del su.tmp

set termout on

select 'Connected as '||USER||' on '||global_name||'.'
from global_name;

set termout on head on

• Dynamically ZIP large process trace files
----------------------------------------------------------------------------
-- -
-- Purpose: Dynamically zip a process's trace file -
-- -
-- Synopsis: @trc_zip -
-- -
-- Description: This script creates a named pipe in place of the process's -
-- trace file and spawns a gzip process to compress it. -
-- -
----------------------------------------------------------------------------

column trc_file new_value trc_file noprint
column zip_file new_value zip_file noprint

select p.value || '/ora_' || u.spid || '.trc' trc_file,
p.value || '/ora_' || u.spid || '.trc.gz' zip_file
from sys.v_$session s,
sys.v_$process u,
sys.v_$parameter p
where s.audsid = userenv('SESSIONID')
and u.addr = s.paddr
and p.name = 'user_dump_dest'
/

set define :
host mknod :trc_file p && nohup gzip < :trc_file > :zip_file &
set define &

alter session set max_dump_file_size = unlimited
/
Tabular display of redo-log archiving history (logs/hour)
rem -----------------------------------------------------------------------
rem Filename: archdist.sql
rem Purpose: Tabular display of redo-log archiving history (logs/hour)
rem - Can only run from sqlplus
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pagesize 50000
set veri off
set colsep ""

set termout off
def time="time" -- Oracle7
col time new_value time
select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time
from dual
where &&_O_RELEASE like '8%' -- Oracle8
/
set termout on

select substr(&&time, 1, 5) day,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'99') "23"
from sys.v_$log_history
group by substr(&&time,1,5)
/

set colsep " "
List control file structures with usage limits
rem -----------------------------------------------------------------------
rem Filename: ctlimits.sql
rem Purpose: List control file structures with usage limits
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
col PCT_USED format 990.09

-- Controlfile creation parameters:
-- Type DATAFILE is for MAXDATAFILES
-- Type REDO LOG is for MAXLOGFILES
-- Type LOG HISTORY is for MAXLOGHISTORY
-- Type REDO THREAD is for MAXINSTANCES
-- No entry for MAXLOGMEMBERS (?)

select type, records_used, records_total,
records_used/records_total*100 "PCT_USED"
from sys.v_$controlfile_record_section
/
Log all database errors to a table
rem -----------------------------------------------------------------------
rem Filename: db-error.sql
rem Purpose: Log all database errors to a table
rem Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and
rem GRANT SELECT ON SYS.V_$SESSION required
rem Date: 21-Mar-2000
rem Author: Nico Booyse (booysen@saps.org.za)
rem -----------------------------------------------------------------------

drop trigger log_errors_trig;
drop table log_errors_tab;

create table log_errors_tab (
error varchar2(30),
timestamp date,
username varchar2(30),
osuser varchar2(30),
machine varchar2(64),
process varchar2(8),
program varchar2(48));

create or replace trigger log_errors_trig
after servererror on database
declare
var_user varchar2(30);
var_osuser varchar2(30);
var_machine varchar2(64);
var_process varchar2(8);
var_program varchar2(48);
begin
select username, osuser, machine, process, program
into var_user, var_osuser, var_machine, var_process, var_program
from sys.v_$session
where audsid = userenv('sessionid');

insert into log_errors_tab
values(dbms_standard.server_error(1),sysdate,var_user,
var_osuser,var_machine,var_process,var_program);
end;
/
Demonstrate database and schema level triggers
rem -----------------------------------------------------------------------
rem Filename: db-trig.sql
rem Purpose: Demonstrate database triggers (available from Oracle 8i)
rem Need DBA or CREATE ANY TRIGGER privs
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

create or replace trigger restrict_login
after logon on database
-- after logoff on database
-- after servererror on database
-- after startup on database
-- after shutdown on database
-- after create on database
-- after drop on database
-- after alter on database
declare
flag number := 0;
begin
select 1 into flag from sys.v_$session where program like '%sqlplus%';
if flag = 1 then
raise_application_error(-20000, 'No access from sqlplus');
end if;
end;
/
show errors

CREATE OR REPLACE TRIGGER startup_db
after startup on database
begin
dbms_shared_pool.keep ('SYS.STANDARD','P');
dbms_shared_pool.keep ('SYS.DBMS_STANDARD','P');
end;
/
show errors
Limit resources using the Database Resource Manager
rem -----------------------------------------------------------------------
rem Filename: rsrc.sql
rem Purpose: Demonstrate resource manager capabilities (limit CPU,
rem degree and sessions, available from Oracle 8i)
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

---------------------------------------------------------------------------
-- Create plan with consumer groups
---------------------------------------------------------------------------

exec dbms_resource_manager.create_pending_area;

exec dbms_resource_manager.delete_plan_cascade('night_plan');

exec dbms_resource_manager.create_plan('night_plan', 'Plan to use after 6PM');
exec dbms_resource_manager.create_consumer_group('batch', 'Group for batch reports');

exec dbms_resource_manager.create_plan_directive('night_plan', 'batch', 'Rules for overnight batch jobs', -
cpu_p1 => 75, parallel_degree_limit_p1 => 20);
exec dbms_resource_manager.create_plan_directive('night_plan', 'OTHER_GROUPS', 'Rules for overnight batch jobs', -
cpu_p1 => 25, parallel_degree_limit_p1 => 0, -
max_active_sess_target_p1 => 1);

exec dbms_resource_manager.validate_pending_area;
exec dbms_resource_manager.submit_pending_area;

---------------------------------------------------------------------------
-- List plans and consumer groups
---------------------------------------------------------------------------

set pages 50000
col plan format a12
col status format a7
col cpu_p1 format 999
col cpu_p2 format 999
col cpu_p3 format 999
col group_or_subplan format a17
col parallel_degree_limit_p1 format 999

select plan, num_plan_directives, status, mandatory from sys.dba_rsrc_plans;

select plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3, parallel_degree_limit_p1 as parallel, status
from sys.dba_rsrc_plan_directives
order by plan;

---------------------------------------------------------------------------
-- Switch a user to a new consumer group
---------------------------------------------------------------------------

exec dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'batch', FALSE);
exec dbms_resource_manager.set_initial_consumer_group('SCOTT', 'batch');

-- exec dbms_resource_manager.switch_consumer_group_for_user('SCOTT', 'batch'); -- Switch on-line users

select username, initial_rsrc_consumer_group from sys.dba_users where username = 'SCOTT';

---------------------------------------------------------------------------
-- Enable resource management for this instance
---------------------------------------------------------------------------
alter system set resource_manager_plan = 'NIGHT_PLAN';

---------------------------------------------------------------------------
-- Monitor the resource manager
---------------------------------------------------------------------------

col program format a40
select program, resource_consumer_group from sys.v_$session where username = 'SCOTT';

-- select * from sys.v_$rsrc_plan;
select * from sys.v_$rsrc_consumer_group;

Log Miner - extract undo statements from log files

rem -----------------------------------------------------------------------
rem Filename: logmgr.sql
rem Purpose: Log Miner: extract undo statements from online and archived
rem redo log files based on selection criteria.
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- Create a dictionary file
-- (init.ora parameter utl_file_dir must be set)
exec dbms_logmnr_d.build('mydictfile', '/tmp');

-- Register log files, can be from a different db
-- (NEWFILE=start new list/ ADDFILE=add next file)
exec dbms_logmnr.add_logfile(
LogFileName =>
'/app/oracle/arch/oradba/log_1_0000000027.oradba',
Options => dbms_logmnr.NEW);
exec dbms_logmnr.add_logfile(
LogFileName =>
'/app/oracle/arch/oradba/log_1_0000000028.oradba',
Options => dbms_logmnr.ADDFILE);

-- Start the logminer session
exec dbms_logmnr.start_logmnr(DictFileName => '/tmp/mydictfile');

-- Query v_$logmnr_contents view to extract required info
select timestamp, sql_undo
from sys.v_$logmnr_contents
where seg_name = 'EMPLOYEES';

-- Stop the logminer session
exec dbms_logmnr.end_logmnr;

Database cursor usage (open_cursors parameter)

rem -----------------------------------------------------------------------
rem Filename: cursors.sql
rem Purpose: Track database cursor usage
rem Date: 29-Nov-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

prompt Open Cursor Limit
col value format a30 head "Open Cursors Parameter Value"

select value
from sys.v_$parameter
where name = 'open_cursors';

prompt Summary of Current Curor Usage
col name format a25

select min(value) min, max(value) max, avg(value) avg
from sys.v_$sesstat
where statistic# = (select statistic#
from sys.v_$statname
where name like 'opened cursors current');

prompt Top 10 Users With Most Open Cursors
col program format a15 trunc
col osuser format a15 trunc
col username format a15 trunc

select * from (
select s.sid, s.username, s.osuser, s.program, v.value "Open
Cursors"
from sys.v_$sesstat v, sys.v_$session s
where v.sid = s.sid
and v.statistic# = (select statistic#
from sys.v_$statname
where name like 'opened cursors current')
order by v.value desc
)
where rownum < 11;
On-line table reorganizaton using the DBMS_REDEFINITION package
rem -----------------------------------------------------------------------
rem Filename: tabreorg.sql
rem Purpose: Show how a table can be reorganized on-line using the
rem DBMS_REDEFINITION package introduced in Oracle9i.
rem Date: 22-May-2003
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

---------------------------------------------------------------------
-- Connect as SYSDBA to grant privs to scott...
---------------------------------------------------------------------
connect / as sysdba

grant execute on dbms_redefinition to scott;
grant dba to scott;

---------------------------------------------------------------------
-- Create new empty interim table...
---------------------------------------------------------------------
connect scott/tiger
CREATE TABLE emp_work AS SELECT * FROM emp WHERE 1=2;

---------------------------------------------------------------------
-- Test if table can be redefined...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'emp', 2);

---------------------------------------------------------------------
-- Start table redefinition...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'emp', 'emp_work', NULL, 2);

--------------------------------------------------------------------
-- Add ALL constraints, indexes, triggers, grants, etc...
---------------------------------------------------------------------
ALTER TABLE emp ADD PRIMARY KEY (empno);

---------------------------------------------------------------------
-- Finish the redefinition process (this will swap the two tables)...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_work');

---------------------------------------------------------------------
-- Drop the interim working table...
---------------------------------------------------------------------
DROP TABLE emp_work;

eclipse pecular behavior

2005-12-30T09:06:00.000-08:00

If we have a cycle dependency, then eclipse will go out of memory immediately.

Like if A extends B, and B extends A .. (by mistake if we do this kind of thing) eclipse will die.

security

2005-12-02T03:50:00.000-08:00

nice notes on des algorithm
http://www.aci.net/kalliste/des.htm

http://www.informit.com/guides/content.asp?g=java&seqNum=31&rl=1
http://www.angelfire.com/tx4/cus/notes/javaxcrypto.html

mysql commands

2005-11-30T00:23:00.000-08:00

Handy MySQL Commands
Description	Command
To login (from unix shell) use -h only if needed.	[mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server.	create database [databasename];
List all databases on the sql server.	show databases;
Switch to a database.	use [db name];
To see all the tables in the db.	show tables;
To see database's field formats.	describe [table name];
To delete a db.	drop database [database name];
To delete a table.	drop table [table name];
Show all data in a table.	SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.	show columns from [table name];
Show certain selected rows with the value "whatever".	SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'.	SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.	SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
Show all records starting with the letters 'bob' AND the phone number '3444444'.	SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.	SELECT * FROM [table name] WHERE rec RLIKE "^a$";
Show unique records.	SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).	SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Count rows.	SELECT COUNT(*) FROM [table name];
Join tables on common columns.	select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Switch to the mysql db. Create a new user.	INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));
Change a users password.(from unix shell).	[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
Change a users password.(from MySQL prompt).	SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
Switch to mysql db.Give user privilages for a db.	INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','db','user','Y','Y','Y','Y','Y','N');
To update info already in a table.	UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from a table.	DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privilages.	FLUSH PRIVILEGES;
Delete a column.	alter table [table name] drop column [column name];
Add a new column to db.	alter table [table name] add column [new column name] varchar (20);
Change column name.	alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.	alter table [table name] add unique ([column name]);
Make a column bigger.	alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.	alter table [table name] drop index [colmn name];
Load a CSV file into a table.	LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for backup.Backup file is sql commands to recreate all db's.	[mysql dir]/bin/mysqldump --user=root --password=blah --all-databases >/tmp/sql-01_backup.sql
Create Table Example 1.	CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3), officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.	create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastname varchar(50) default 'bato');

chat client

2005-11-29T21:18:00.000-08:00

here is a list of chat clients

security

2005-11-24T04:14:00.000-08:00

http://www.isi.edu/gost/publications/kerberos-neuman-tso.html

Webservices development IDE

2005-10-21T00:31:00.000-07:00

Here is a Eclipse based J2EE IDE.
Lomboz 3.1RC2
http://lomboz.objectweb.org/

Building a Simple Web Service – A Tutorial

A copy of the tutorial is here.

· By Jeevaraj Gnanaselvan Dhanaraj (jeevaraj_1970@yahoo.com)

(Jeeva has over 7 years of experience in designing and developing enterprise class web applications using JAVA and J2EE technologies.

He currently works for Itreya Technologies, Bangalore, leading a team of over 10 programmers and designers, developing a multi-user, distributed, web-based workflow application)

· Introduction

In this tutorial we will create a simple web service and a client web application using eclipse IDE along with Lomboz plug in. We will also deploy and test the web service on Tomcat 5.5.4 web application server. This application, while simple, provides a good introduction to Web service development and some of the Web development tools available.

· Environment

J2SDK 1.4.2
http://java.sun.com/

Eclipse 3.1

http://www.eclipse.org/

Tomcat 5.5.4

http://tomcat.apache.org/

Lomboz 3.1RC2
http://lomboz.objectweb.org/

· Installation

Install JDK (in D:\j2sdk1.4.2_04)

Install Tomcat (in E:\Tomcat5.5)

Install Eclipse (in E:\Eclipse3.1)

Install Lomboz (in E:\Eclipse3.1)

· Setting up

1. Set up the installed JRE in eclipse (Windows -> Preferences -> Java -> Installed JREs)

2. Set up the installed runtime for server in eclipse (Windows -> Preferences -> Server -> Installed Runtimes)

3. Set up the Server view in eclipse (Windows -> Show View -> Other)

4. Set up the Tomcat Server by right clicking and selecting New -> Server option from the Server view in eclipse

· Creating a Web service

1. Create a new Dynamic Web Project in eclipse (File -> New -> Other)

2. Enter name as “WebServiceTutorial”, select project location as “E:\Test” and select Apache Tomcat v5.5 as the Target server.

3. Now create a new Java class from the Project Explorer (Dynamic Web Projects -> Java Source -> New -> Class)

4. Enter name as “Hello” and package as “com.tutorial”.

5. Add a simple method in the “Hello” class as below.

public String sayHello(String name){

return "Hello " + name;

}

6. Save and build the project.
7. Create a new Web service in eclipse (File -> New -> Other)

8. Select Generate a proxy.
9. Select Test the Web service.
10. Select Overwrite files without warning.

11. Select or enter the Bean name as “com.tutorial.Hello”. This is the java class that we just now created.

12. Continue the wizard by clicking Next and finish.
13. On Finish, the Tomcat server starts up and launches the Test client.
14. Verify the generated contents. Look for Hello.class and the generated JSPs as below.

15. Verify the Tomcat folder and ensure the newly created web applications – WebServiceTutorial, WebServiceTutorialClient.

16. We can also run the following url from the browser to access/test the Web service.

http://localhost:8080/WebServiceTutorialClient/sampleHelloProxy/TestClient.jsp

17. If servlet error “org.eclipse.jst.ws.util.JspUtils cannot be resolved or is not a type” is thrown on the browser, then copy the webserviceutils.jar file from the E:\Eclipse3.1\eclipse\plugins\org.eclipse.jst.ws.consumption_0.7.0 into the WEB-INF\lib folder of the WebServiceTutorialClient application and restart the Tomcat server.

18. The browser displays the methods available in the web service.

19. Click on the sayHello(..) method, enter your name (for e.g. “Jeeva”) in the inputs section and click “Invoke”.

20. The browser greets using the web service.

21. The WSDL for the Hello Web service can be found in E:\Test\WebServiceTutorial\WebContent\wsdl\Hello.wsdl. On double-click, the WSDL opens in a graphical editor.

22. Right-click on the WSDL file and explore the options to test the web service / publish the WSDL file / generate client / etc.

· Conclusion

In this tutorial we learned how to create a simple web service and a client web application using eclipse IDE along with Lomboz plug in. We also deployed and tested the web service on Tomcat 5.5.4 web application server. This application, while simple, provides a good introduction to Web service development and some of the Web development tools available.

Google labs papers

2005-10-17T05:48:00.000-07:00

I found this site of google very useful. Interesting articles are available.I thought i could borrow thought for my future PhD.

tech-presentations

2005-09-29T04:30:00.000-07:00

presentations
http://www.ida.liu.se/~uweas/Lectures/DesignPatterns01/

Accenture Shaping the Future Forum 2005

2005-09-24T06:16:00.000-07:00

Today i attended a full day training at Leela palace on SAP Netweaver.
Actually my intention was to get lunch at Leela palace and so i paid 200/- when the entry fee for the event. But then i got enough stuff and a T-Shirt and importantly i got the first prize in the quiz which gave me 1500/- gift voucher.

It was interesting to listen about Netweaver. Accenture is working on Netweaver to provide solutions to its client. Both Accenture and SAP are big players in the market and are capable of capturing market share on the Composite applications domain.

Well, that was the interesting part of the session, that is the theme behind the Netweaver, its about Composite Applications. They seems ready to hit the market. At one point of time i was wondering is the Digital Harbor loosing its lead in the Composite Applications? they are talking more in detail about the Composite Applications. The theory looked better then what i heard at Digital Harbor.
And they are working going to hit the market soon with CAF (Composite Application Framework, which is an IDE based on Eclipse). They are doing wonderful stuff, generate spicy real time reports in no time. They have xApps for composite Applications.

Features of SAP NetWeaver are

1. People Integration
2. Information Integration
3. Process Integration
4. Application Integration and Platform

But then the issues that are ahead of composite applications are too discussed.

1. Managing the complexities of the Composite Applications
2. Life cycle management of the applications and making sure that those applications are running and talking with each other.

There was new thing that i come across, SAP is having a concept called ESA, which i havent heard before (as i know :-) ) well. This ESA (Enterprise Service Architecture) is a business implemetation of the SOA.

There was intersting discussion about how the innovation works.. first innovate something, then work on it and make sure you consolidate it. create a standardization of the innovation, then you outsource the work to somebody else and engage your guys to innovate something else.. Looks impressive isnt it?

Few of the components that are required for developing composite applications are
1. A single common execution architecture
2. An eAI Tool
3. A portal Engine
4. A BI (Business Intelligence) Platform
5. A custom Development Engine
6. A collabarative engine
7. Knowledge Management engine.
8. A work flow engine
9. A process management engine
10.A mobile development plaftform

Netweaver comes with a refrigiretor architecture and it looks impressive.

They are talking of various terms like XI (the SAP Exchange Infrastructure)
Master Data Management (MDM),xIEP, ALE,IDocs, EP (Enterprise Portal)

At the outset, it was an interesting session i attended.
now i got think of what to do with my gift vouchers..

White papers

2005-09-20T02:06:00.000-07:00

N-Tier Architecture

The same website has lots of other tech suppliments.

White papers from Panda software on software security

Tech Republic White papers
Article on B2B

small intro to B2B from http://wisegeek.com

"B2B" is contemporary shorthand for a longtime sales practice called business-to-business. B2B transactions primarily target companies and other wholesale buyers, while transactions targeting individuals are called B2C, or business-to-customer. Many organizations have both B2B and B2C components, but it's not unusual for a company to specialize in B2B services or sales. In fact, the vast majority of products and services sold are considered to be B2B in nature.

One major reason for the popularity of B2B sales and services is sheer volume. An individual customer may visit a clothing manufacturer's website catalog and order two pairs of shoes or a sweater. The buyer for a national chain of clothing stores, however, may order 5,000 pairs of shoes and 2,000 sweaters. Without a B2B component, the manufacturer would have lost out on a very lucrative sale. This is why many companies provide B2B options alongside the B2C offerings at their websites and other outlets.

B2B sales are also generated by providing a specialized product line or service not available to the general public. This form of B2B transaction is very common in the manufacturing world. A company which produces shaving cream in cans, for example, may need a specific plastic nozzle. Several plastic injection molding companies would send sales representatives to pitch their particular designs. These nozzles would be useless for individual customers, but a manufacturer may order thousands of them.

With the growth in electronic communications, B2B has taken on even more importance. Instead of simply focusing on business-to-business sales, modern corporations are conducting other financial transactions online. B2B communications are now being used to promote investment, trade stocks and form financial alliances. Because the price of these transactions is far beyond the reach of most individuals, there is no equivalent business-to-customer option available. Some B2B transactions handled electronically can literally run into the billions of dollars.