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