Oracle in Java programs

 Java is a Built_in software
 Supports to execute Java programs with in oracle server or  client System.
 SQLJ : It is an Transalator.  It will check for SQL stmt syntax errors and translate       the Sql commands into Java equavalent code.
    After Translation it returns .Java file .

 Sql stmts are provided in below format in Sqlj script. #sql { DDL, DML , TCL , DCL };

 Steps:
 Provide Path settings and User details in   "connect.properties" system file. 1. Open Notepad and add the Code
 2. save file as   "filename.sqlj"
 3. C:\>sqlj  filename.sqlj -- check for sql syntax and                                                  translate into .java file
 4. C:\>javac  filename.java -- compiles java program
                                        and gives .class file
 5. C:\>java  filename  -- Execute .class file and gives                                                       final output.
 Iterators: Used to retrieve data from Oracle table to Java program.
 similar to cursors in PL/SQL. 2 Types
 1. Named Iterator - Similar to cursor with For loop
 2. Positional Iterator - Similar to cursor with Operations
--------------------------------------------------------------------------
 Path & Classpath :
 set   path=C:\oracle\ora92\bin;C:\oracle\ora92\jdk\bin;%path%

 set classpath=C:\oracle\ora92\sqlj\lib\translator.jar;
 C:\oracle\ora92\sqlj\lib\runtime12.jar;
 C:\oracle\ora92\jdbc\lib\classes12.jar;%classpath%

Connect.Properties File :

 # Users should uncomment one of the following URLs or      add their own.
 # (If using Thin, edit as appropriate.)
 sqlj.url=jdbc:oracle:thin:@localhost:1521:oracle
 #sqlj.url=jdbc:oracle:oci8:@
 #sqlj.url=jdbc:oracle:oci7:@

 # User name and password here (edit to use different     user/password)
 sqlj.user=sridhar
 sqlj.password=sri123
--------------------------------------------------------------------
 Example 1:    Demo1.sqlj

 /* Example to Create Table, Insert,
     Update and Commit */

 import java.sql.*;
 import java.sql.SQLException;
 import oracle.sqlj.runtime.Oracle;

 /* User Defined Class Holding User defined Funtion */
 class Myclass
 {
 public void myfun() throws SQLException
 {
 #sql { create table EMPLOY111(eno number(4),name    varchar2(20), basic number(10)) };
 System.out.println(" Table created ....! ");
 #sql { insert into EMPLOY111 values(101,'sridhar',8000) };
 #sql { update EMPLOY111 set basic = basic + 4000 where           eno = 101};
 #sql { commit };
 System.out.println(" Table data saved ....... ");
 }
 }

 /* Main Class Holding main Function */
  public class Demo1
  {
  public static void main(String args[])
  {
  try
  {
   Oracle.connect(Demo1.class,"connect.properties");
   Myclass obj1 = new Myclass();
   obj1.myfun();
   }
  catch(SQLException e)
  {
   System.out.println(" The error is " +e);
 }
 }
 }

 C:\>sqlj Demo1.sqlj
 C:\>javac Demo1.java
 C:\>java Demo1



Function:
Create or replace function f1 (veno number) return number is
vsal number(12,2);
Begin
Select sal into vsal from emp where empno = veno;
Return(round(vsal * 12));
End;

Example  2 :  Demo2.sqlj

/* Example to Call a Function from Database */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

/* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
int eno = 7900;
float asal;
#sql { asal = { VALUES f1(:eno) };
 /* VALUES - built_in to call a function in SQLJ */
System.out.println("Annual Salary of employ is:  " +asal);
}
}

/* Main Class Holding main Function */
public class Demo2
{
public static void main(string args())
{
try
{
Oracle.connect(Demo2.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is " +e);
}
}
}


Procedure:
Create or replace procedure p1 (veno number, incr number) is
Begin
Update emp set sal = sal + incr where empno = veno;
End ;

Example 3 :  Demo3.sqlj

/* Example to Call a Procedure from Database */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

/* User Defined Class Holding User defined Function */
class Myclass
{
public void myfun() throws SQLException
{
int e = 7788;
float s = 5000;
#sql { CALL p1(:e,:s) };  
/* CALL - built_in to call a procedure in SQLJ */
#sql { commit };
System.out.println(" Table data saved ....... ");
}
}

/* Main Class Holding main Function */
public class Demo3
{
public static void main(string args())
{
try
{
Oracle.connect(Demo3.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is " +e);
}
}
}

Example 4 :  Demo4.sqlj

/* Example for Creating Nested Tables  */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

 /* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
#sql{ create type book_type as object(bid number(3),title varchar2(15),doi date, dor date) };
#sql { create type books as table of book_type };
System.out.println("Nested table created .....");
#sql { create table student (roll number(4) primary key, name varchar2(20), binfo books) nested table binfo store as book_table };
System.out.println("Table created.....");
#sql { insert into student values( 1001 , 'Sridhar',
books (book_type(11,'sql',sysdate,sysdate + 5),
book_type(12,'pl/sql',sysdate,sysdate + 15),
book_type(13,'dba',sysdate,sysdate + 10))) } ;
System.out.println("Record Inserted .....");
}
}

/* Main Class Holding main Function */
public class Demo4
{
public static void main(string args())
{
try
{
Oracle.connect(Demo4.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}

Example 5: Demo5.sqlj

/* Example for Creating Varrying Arrays */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

/* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
#sql{ CREATE OR REPLACE TYPE CATY AS VARRAY(3) OF VARCHAR2(30) };
System.out.println(" Type created ...... ");
#sql { create table TV (tcode number(3),brand caty) };
#sql { insert into TV values(101, caty('LG','ONIDA','BPL')) };
#sql{commit};
System.out.println("Data Saved ........!");
}
}

/* Main Class Holding main Function */
public class Demo5
{
public static void main(string args())
{
try
{
Oracle.connect(Demo5.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}

Example 6: Demo6.sqlj

/* Example Using Named Iterator to retrieve data  */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

/* Declaring a Named iterator */
#sql iterator ss (String ename,float sal,float comm);

/* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
ss myiter;
#sql myiter = { select ename,sal,nvl(comm,0) as comm from emp };
while (myiter.next())
{
System.out.print("Name is :"+myiter.ename());
System.out.print("Salary is :"+myiter.sal());
System.out.println("Commision is :"+myiter.comm());
}
}
}

/* Main Class Holding main Function */
public class Demo6
{
public static void main(string args())
{
try
{
Oracle.connect(Demo6.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}

Example 7:  Demo7.sqlj

/* Example Using Positional Iterator to retrieve data  */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

/* Declaring a Positional Iterator */
#sql iterator ss (String ,float ,String);

/* User Defined Class Holding User defined Function */
class Myclass
{
public void myfun() throws SQLException
{
ss myiter = null;
String n = null;
String j = null;
float s = 0;
#sql myiter = { select ename,sal,job from emp };
while (true)
{
#sql {fetch :myiter into :n,:s,:j};
if (myiter.endFetch())
{ break };
System.out.print("Name is :"+n);
System.out.print("Salary is :"+s);
System.out.println("Designation is :"+j);
}
myiter.close();
}
}

/* Main Class Holding main Function */
public class Demo7
{
public static void main(string args())
{
try
{
Oracle.connect(Demo7.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}