Path & Classpath:
set
path=F:\oracle\ora92\bin;F:\oracle\ora92\jdk\bin;%path%
set
classpath=F:\oracle\ora92\sqlj\lib\translator.jar;F:\oracle\ora92\sqlj\lib\runtime12.jar;
F:\oracle\ora92\jdbc\lib\classes12.jar;%classpath%
Connect.Properties System 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 Function */
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);
}
}
}
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 Funtion */
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 Funtion */
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);
}
}
}
No comments:
Post a Comment