Today is Thursday, 21st June 2018

Archive for the ‘Oracle’ Category


Oracle Database Java JDBC Tutorial: #2 – Setup Dev Environment

In this video we will learn how to setup our development environment for JDBC work. You will learn how to connect Java to an Oracle database with JDBC.

Please SUBSCRIBE to this channel

Download Java Source Code

File: table-setup.sql

--
-- DROP TABLE IF EXISTS
--
declare
   c int;
begin
   select count(*) into c from user_tables where table_name = upper('employees');
   if c = 1 then
      execute immediate 'drop table employees';
   end if;
end;
/

--
-- CREATE TABLE
--
CREATE TABLE employees (
  id integer PRIMARY KEY,
  last_name varchar(64) DEFAULT NULL,
  first_name varchar(64) DEFAULT NULL,
  email varchar(64) DEFAULT NULL,
  department varchar(64) DEFAULT NULL,
  salary DECIMAL(10,2) DEFAULT NULL
);

-- DROP SEQUENCE IF EXISTS
declare
   c int;
begin
   select count(*) into c from user_sequences where sequence_name = upper('employees_sequence');
   if c = 1 then
      execute immediate 'drop sequence employees_sequence';
   end if;
end;

CREATE SEQUENCE employees_sequence;

CREATE OR REPLACE TRIGGER employees_trigger
BEFORE INSERT ON employees
FOR EACH ROW

BEGIN
	SELECT employees_sequence.NEXTVAL
	INTO :new.id
	FROM dual;
END;
/

INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00);

INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00);

INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00);

INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00);
INSERT INTO employees (id,last_name,first_name,email, department, salary) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);

commit;

File: JdbcTest.java

import java.sql.*;

/**
 * 
 * @author www.luv2code.com
 *
 */
public class JdbcTest {

	public static void main(String[] args) throws SQLException {

		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;
		
		try {
			// 1. Get a connection to database
			myConn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:xe", "student", "student");
			
			System.out.println("Database connection successful!\n");
			
			// 2. Create a statement
			myStmt = myConn.createStatement();
			
			// 3. Execute SQL query
			myRs = myStmt.executeQuery("select * from employees");
			
			// 4. Process the result set
			while (myRs.next()) {
				System.out.println(myRs.getString("last_name") + ", " 
								+ myRs.getString("first_name"));
			}
		}
		catch (Exception exc) {
			exc.printStackTrace();
		}
		finally {
			if (myRs != null) {
				myRs.close();
			}
			
			if (myStmt != null) {
				myStmt.close();
			}
			
			if (myConn != null) {
				myConn.close();
			}
		}
	}

}



How To Install Oracle SQL Developer on Windows 8

In this video you will learn how to install Oracle SQL Developer on Windows 8.

Oracle SQL Developer is a free development tool that simplifies the development and management of Oracle Database.

SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution.

Please SUBSCRIBE to this channel



How To Install Oracle Database Express on Windows 8

In this video you will learn how to install Oracle Database Express on Windows 8.

Please SUBSCRIBE to this channel