Oracle Stored procedures JAVA

Purpose : To demonstrate that Oracle database has an embedded JVM

Objective: PL/SQL is great, but it is an Oracle Proprietary language that cannot be ported to other database platforms,, Java is portable

Environment : The example uses SQL*Plus, but other tools also work

Oracle Version: I Used Oracle9i, Oracle8i should work similarly

SQL> CREATE OR REPLACE and RESOLVE JAVA SOURCE NAMED "Hello" AS
public class Hello {
static public String Msg(String tail) {
return "Hello " + tail;
}
}
/
Java created.


SQL> CREATE OR REPLACE FUNCTION hello( str VARCHAR2 )
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME
'Hello.Msg (java.lang.String)
return java.lang.String';
/

SQL>SELECT hello (ENAME) from EMP;

HELLO(ENAME)
-----------------------------
Hello SMITH
Hello ALLEN
Hello WARD
Hello JONES
Hello MARTIN
Hello BLAKE
Hello CLARK
Hello SCOTT
Hello KING
Hello TURNER
Hello ADAMS

HELLO(ENAME)
-----------------------------
Hello JAMES
Hello FORD
Hello MILLER

Using Loggers in Java Applications

In order to add logging features to your applications, you can use apache log4j API

You need 3 things

1) have access to log4j jar file

2) have a properties file that will act as a configuration environment for the logging process

3) write the appropriate Java code

here is more details using the Oracle Jdeveloper IDE

Download log4j1.3.jar from http://logging.apache.org

Put the log4j.jar in the classpath, or add it to the Jdeveloper project as shown

You need to specify where the properties file is located, otherwise logging will fail

As an example, I have create a properties file and called it log4j.properties and added placed the file at C:\

You can use the project properties dialogue to define the location of the Properties file

Invoke project properties à Run/Debug à Edit the default setting à and add the Java option shown below

-Dlog4j.configuration = file:c:/log.properties

The following is a sample properties file in which the logging output is specified (log4j.appender.R.File=C:\x.log)

Log.properties

log4j.rootLogger=INFO, R

# first appender writes to a file

log4j.appender.R=org.apache.log4j.RollingFileAppender

#RollingFileAppender OPTIONS

log4j.appender.R.Threshold=INFO

log4j.appender.R.ImmediateFlush=true

log4j.appender.R.File=C:\x.log

log4j.appender.R.MaxFileSize=6000KB

log4j.appender.R.MaxBackupIndex=2

#log4j.debug=true

#log4j.disable=fatal

# Pattern to output the caller's file name and line number.

log4j.appender.R.layout=org.apache.log4j.PatternLayout

log4j.appender.R.layout.ConversionPattern=%p %c %d{dd/MM/yyyy HH:mm:ss} - %m%n

#log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n

#log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n

Finally, this is an example code

package model;

import org.apache.log4j.Logger;

public class Class1 {

public Class1() {

Logger log = Logger.getLogger(Class1.class.getName());

log.warn("hello warning");

}

public static void main(String[] args) {

Class1 class1 = new Class1();

}

}

Note: the (Class1.class.getName()) retrieves the name of the current class that is being executes. Every logging message entry will have this variable printed so that the users can know which class was responsible for this entry.. off course, if you choose to use a string value instead, that strng value shall appear in each logging entry.

Thanks to Mr. Ammar for this Article..

How to load BLOB in the database?

I have been asked yesterday how to read and write blobs in the database.

With java :
read from an input stream

InputStream myBlobInputStream =
connection .
createStatement() .
executeQuery("select myBlob from t") .
getBlob(1) .
getBinaryStream();

write to an output stream

OutputStream myBlobStream =
connection .
createStatement() .
executeQuery("select myBlob from t for update") .
getBlob(1) .
getBinaryOutputStream();

where connection is your java.sql.connection.

You can find a working demo here
http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/LOBSample/LOBSample.zip

You could also use PL/SQL and the DBMS_LOB API. There is a complete book (306 pages!) in the doc about working with large objects : Application Developer’s Guide - Large Objects.

Sometimes, you can use plain SQL.

SQL> create table t(x BLOB);
Table created

SQL> insert into t values(utl_raw.cast_from_number(1));
1 row inserted

SQL> select utl_raw.cast_to_number(x) from t;
UTL_RAW.CAST_TO_NUMBER(X)
-------------------------
1

A smart move may be to use SQL Loader. You can specify one file per row

LOAD DATA INFILE '/tmp/x.txt' INTO TABLE "T"
(name filler char(255), x lobfile(name) terminated by EOF)

and your import file /tmp/x.txt will look like

x.gif
y.gif

but you could also load a 10000 long characters column from your input file in a CLOB column, just by specifying VARCHARC(4,10000) as a datatype