Data Management Using Java (JDBC) In Oracle
This code shows how to use java technology (jdbc) in managing data in an Oracle database. The code and objects can be enhanced to suit the needs of applications, such as use of triggers, constraints etc. This will work on version Oracle 8i and higher. This shows how java and PL/SQL are used together in data management. In version 9i and higher, SQLJ can also be used. This code has been tested/implemented in Oracle 9i.
 
The code is divided into three steps:
1. Creation of database objects
2. Java Objects - code/class and
3. PL/SQL procedures and functions
 
This code basically shows how to create a table, execute DML such a SELECT, INSERT, UPDATE, DELETE and also to call a stored procedure with parameters using java class and PL/SQL code.
 
To make a call to the database from an external application, the code has to be modified to have a database connection (login information or proper connection string). This code uses the default or currently open connection.
 
1. Database Objects
 
CREATE TABLE customer
(customer_idNUMBER(6),
 customer_nameVARCHAR2(50),
 customer_phoneVARCHAR2(20),
 created_dateDATE DEFAULT SYSDATE,
 updated_dateDATE);
 
CREATE SEQUENCE cust_seq
START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE update_customer(p_cust_id IN NUMBER) AS
BEGIN
 UPDATE customer
  SET customer_name = UPPER(customer_name),
       updated_date = SYSDATE
 WHERE customer_id = p_cust_id;
 COMMIT;
EXCEPTION
  WHEN OTHERS THEN RAISE;
END;
 
2. Java Objects - code/class
 
 
3. PL/SQL Code (Functions and Procedures)
 




For corrections or feedback, other useful links please contact webmaster


  1310