In this tutorial, we will show you how to use Spring Boot JDBC SimpleJdbcCall to call a stored procedure and stored function from a Oracle database. Technologies used : Spring Boot 2.1.2.RELEASE Spring JDBC 5.1.4.RELEASE Oracle database 19c HikariCP 3.2.0 Maven 3 Java 8 Unlike JdbcTemplate, Spring Boot didn’t create any SimpleJdbcCall automatically, we have […]
This is caused by the requested SID doesn’t exist in {ORACLE_HOME}/network/admin/tnsnames.ora P.S Tested with Oracle database 19c with ojdbc8.jar 1. JDBC try (Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "system", "password")) { //… } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } Output: SQL State: 66000 Listener refused the connection with the following […]
To uninstall Oracle database on Windows, we have to delete the all the related Ora* registry keys, files and folders manually. P.S Tested with Oracle database 19c on Windows 10 Note I can’t find any uninstaller to uninstall the Oracle database automatically? 1. Stop Oracle* services Press Windows key, type services, stop all Oracle related […]
This article will help you to understand how to create a user defined function. It’s also known as stored function or user function. User defined functions are similar to procedures. The only difference is that function always returns a value. User defined functions can be used as a part of an SQL expression. Note Oracle […]
The BITAND function treats its inputs and its output as vectors of bits, the output is the bitwise AND of the inputs. Basically it performs below steps. Converts the inputs into binary. Performs a standard bitwise AND operation on these two strings. Converts the binary result back into decimal and returns the value. 1. BITAND […]
This article show you how to use ATAN() and ATAN2() function in Oracle PL/SQL. 1. ATAN function examples The ATAN function returns the arc tangent of n, the n can be in an unbounded range. The function will returns a value in the range of -pi/2 to pi/2, expressed in radians. SELECT ATAN(1.5) FROM DUAL; […]
The CEIL function round the specified number up, and return the smallest number that is greater than or equal to the specified number. CEIL function examples SELECT CEIL(1.2) FROM DUAL; — output : 2 SELECT CEIL(1.5) FROM DUAL; — output : 2 SELECT CEIL(1.7) FROM DUAL; — output : 2 SELECT CEIL(1.99) FROM DUAL; — […]
The FLOOR() function round the specified number down, and return the largest number that is less than or equal to the specified number. FLOOR function examples SELECT FLOOR(1.2) FROM DUAL; — output : 1 SELECT FLOOR(1.5) FROM DUAL; — output : 1 SELECT FLOOR(1.7) FROM DUAL; — output : 1 SELECT FLOOR(1.99) FROM DUAL; — […]
The ACOS() function returns the arc cosine of input n, the input n must be in the range of -1 to 1. The function will return a value in the range of 0 to pi, expressed in radians. ACOS function examples SELECT ACOS(.2) FROM DUAL; — output 1.36943840600456582777619613942212803186 SELECT ACOS(-.4) FROM DUAL; — output 1.98231317286238463861605958925708704694 […]
The ASIN() function returns arc sine of input n, the input n must be in the range of -1 to 1. The function will return a value in the range of -pi/2 to pi/2, expressed in radians. ASIN function examples SELECT ASIN(.25) FROM DUAL; — output 0.25268025514207865348565743699370756609 SELECT ASIN(-.5) FROM DUAL; — output -0.52359877559829887307710723054658381405 SELECT […]
This ABS() function returns absolute value of a number, for example, the absolute value of 5 is 5, and the absolute value of -5 is also 5. In Mathematics, absolute value can be think of as a distance from zero. ABS function examples SELECT ABS(-15) FROM DUAL; — 15 SELECT ABS(-51.321) FROM DUAL; –51.321 SELECT […]
This article shows you how to use INSTEAD OF TRIGGER to update A noneditioning view A nested table column of a noneditioning view This INSTEAD OF triggers provide a way to modify views that cannot be modified directly through DML statements like INSERT, UPDATE and DELETE An INSTEAD OF trigger is Always a row-level trigger. […]
This article shows you how to use BEFORE DELETE TRIGGER, it will fire before the delete operation is executed. In real life scenarios, it is mostly used for purposes like: Restrict invalid DELETE operation. Delete data from another table. 1. Restrict invalid DELETE operation In this example, We have two tables item_details and order_details. The […]
This article shows you how to use DROP FUNCTION to delete a function from Oracle database. 1. DROP function example 1.1 Create a function get_current_month. Then we will delete the function using DROP FUNCTION statement. –Creating function CREATE OR REPLACE FUNCTION get_current_month RETURN VARCHAR2 IS curr_month VARCHAR2(10); BEGIN SELECT to_char(sysdate, ‘MONTH’) INTO curr_month FROM dual; […]
Check the USER_TRIGGERS table, you can get the Trigger status easily : — display all triggers for users SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS; — display status for a specified trigger SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = ‘TRIGGER_NAME’; SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME IN(‘TRIGGER_NAME_A’, ‘TRIGGER_NAME_B’); Sample data. TRIGGER_NAME STATUS TRIGGER_NAME_A ENABLED TRIGGER_NAME_B DISABLED TRG_BEFORE_EMP_UPDATE ENABLED […]
This article shows you how to use BEFORE UPDATE TRIGGER, it’s fire before the update operation is executed. In real life scenarios, it is mostly used for purposes like: Data validation Update values automatically Data logging, or auditing 1. Data Validation Suppose some companies have job openings and already having application data and the criteria […]
This article shows you how to use DROP TRIGGER to delete a trigger. — delete a trigger DROP TRIGGER trigger_name; Note In DROP TRIGGER statement, recommend to specify a schema name before trigger name. If you don’t specify a schema name, then database will assume that the trigger is in your own schema. 1. Table […]
This article shows you how to use ALTER TRIGGER and ALTER TABLE to enable and disable triggers. — enable / disable a trigger ALTER TRIGGER trigger_name ENABLE; ALTER TRIGGER trigger_name DISABLE; — enable / disable all triggers for a specific table ALTER TABLE table_name ENABLE ALL TRIGGERS; ALTER TABLE table_name DISABLE ALL TRIGGERS; 1. Table […]
This article shows you how to use BEFORE INSERT TRIGGER, it’s fire BEFORE an INSERT operation is executed. In real life scenarios, it is mostly used for purposes like Data validation Update values automatically (e.g CREATED_BY, CREATION_DATE etc) 1. Table Create a employee_details, we will try to insert different values into this table and observe […]
This article shows you how to use AFTER INSERT TRIGGER, it will fire after the insert operation is executed. 1. After INSERT Trigger In this example, if a new user is created in user_details, but fields like passport_no or driving_license_no is missing, a new record will be inserted into user_reminders via ‘after insert’ trigger on […]
The following SQL shows you how to get the source code for a specified trigger : select text from user_source where name =’&trigger_name’ and type =’TRIGGER’; References Oracle database – USER_SOURCE table
This article shows you how to use AFTER UPDATE TRIGGER, it will fire after the update operation is executed. 1. Logging example In this example, after each update on ‘SALARY’ column of employee_salary, it will fire a ‘after update’ trigger and insert the new updated data into a employee_salary_log table, for audit purpose. 1.1 Create […]
A quick JPA + Oracle sequences example, for self reference. 1. Oracle Database Issue the following SQL script to create a table and a sequence. CREATE TABLE CUSTOMER( ID NUMBER(10) NOT NULL, NAME VARCHAR2(100) NOT NULL, EMAIL VARCHAR2(100) NOT NULL, CREATED_DATE DATE NOT NULL, CONSTRAINT CUSTOMER_PK PRIMARY KEY (ID) ); CREATE SEQUENCE customer_seq MINVALUE 1 […]
This article shows you how to use ALTER TRIGGER to rename a trigger. — rename a trigger ALTER TRIGGER original_name RENAME TO new_name; 1. Table + Trigger electricity_bill create table electricity_bill ( bill_id number(5) primary key, amount number(5) ); — Table ELECTRICITY_BILL created. trg_rename_example CREATE OR REPLACE TRIGGER trg_rename_example BEFORE UPDATE OR DELETE OR INSERT […]
In this article, we will show you how to create a Spring Boot + Spring Data JPA + Oracle + HikariCP connection pool example. Tools used in this article : Spring Boot 1.5.1.RELEASE Spring Data 1.13.0.RELEASE Hibernate 5 Oracle database 11g express Oracle JDBC driver ojdbc7.jar HikariCP 2.6 Maven Java 8 1. Project Structure A […]
In this article, we will show you how to create a Spring Boot JDBC application + Oracle database + Commons DBCP2 connection pool. Tools used in this article : Spring Boot 1.5.1.RELEASE Oracle database 11g express Oracle JDBC driver ojdbc7.jar Commons DBCP2 2.1.1 Maven Java 8 Note Related – Spring Boot JDBC + MySQL + […]
This article shows you how to use AFTER DELETE TRIGGER, it will fire after the delete operation is executed. In real life scenarios, it is mostly used for purposes like: Auditing or logging 1. After DELETE Trigger In this example, if the user deleted a row of medical_bills, the deleted row will be inserted into […]
This tutorial will reuse and modify the previous Hibernate3.6 XML mapping tutorial, but replace the Hibernate mapping file (hbm) with Hibernate / JPA Annotation code. Technologies in this article : Maven 3.0.3 JDK 1.6.0_13 Hibernate 3.6.3.final Oracle 11g 1. pom.xml No change in pom.xml file, all previous Hibernate3.6 XML mapping tutorial dependency can be reused. […]
In this article, we show you how to integrate Maven3, Hibernate3.6 and Oracle11g together. In the end of this article, you will create a Java project with Maven, and insert a record into Oracle database via Hibernate framework. Tools & technologies used in this article : Maven 3.0.3 JDK 1.6.0_13 Hibernate 3.6.3.final Oracle 11g 1. […]
Problem Here’s my PC environment : Database : Oracle 11g OS Platform : Windows 7 Ultimate 64 bits JDK : 1.6 .0_24, 64 bits When I click on the Oracle SQL developer, it prompts me to key in the JDK folder. However, after I specified the JDK folder, it prompts me following error dialog box […]
Here’s a simple guide to show you how to add an Oracle JDBC driver into your Maven local repository, and also how to reference it in pom.xml Tested with Oracle database 19c and Java 8 Note Due to Oracle license restrictions, the Oracle JDBC driver is not available in the public Maven repository. To use […]
A JDBC CallableStatement example to call a stored procedure which accepts IN and OUT parameters. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure which accepts IN and OUT parameters. CREATE OR REPLACE PROCEDURE get_employee_by_id( p_id IN EMPLOYEE.ID%TYPE, o_name OUT […]
Here’s a SELECT INTO stored procedure example in Oracle database. 1. Table SQL Script DBUSER table creation script. CREATE TABLE DBUSER ( USER_ID NUMBER (5) NOT NULL, USERNAME VARCHAR2 (20) NOT NULL, CREATED_BY VARCHAR2 (20) NOT NULL, CREATED_DATE DATE NOT NULL, PRIMARY KEY ( USER_ID ) ) 2. Stored Procedure A stored procedure, uses SELECT […]
A JDBC CallableStatement example to call a stored procedure which returns a cursor. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure which returns a cursor. CREATE OR REPLACE PROCEDURE get_employee_by_name( p_name IN EMPLOYEE.NAME%TYPE, o_c_dbuser OUT SYS_REFCURSOR) AS BEGIN OPEN […]
Here’s a stored procedure example in Oracle database, using Cursor to manipulate or navigate the records. 1. Table SQL Script DBUSER table creation script. CREATE TABLE DBUSER ( USER_ID NUMBER (5) NOT NULL, USERNAME VARCHAR2 (20) NOT NULL, CREATED_BY VARCHAR2 (20) NOT NULL, CREATED_DATE DATE NOT NULL, PRIMARY KEY ( USER_ID ) ) 2. Stored […]
Here’s a DELETE stored procedure example in Oracle database. 1. Table SQL Script DBUSER table creation script. CREATE TABLE DBUSER ( USER_ID NUMBER (5) NOT NULL, USERNAME VARCHAR2 (20) NOT NULL, CREATED_BY VARCHAR2 (20) NOT NULL, CREATED_DATE DATE NOT NULL, PRIMARY KEY ( USER_ID ) ) 2. Stored Procedure A stored procedure, delete the record […]
Here’s an UPDATE stored procedure example in Oracle database. 1. Table SQL Script DBUSER table creation script. CREATE TABLE DBUSER ( USER_ID NUMBER (5) NOT NULL, USERNAME VARCHAR2 (20) NOT NULL, CREATED_BY VARCHAR2 (20) NOT NULL, CREATED_DATE DATE NOT NULL, PRIMARY KEY ( USER_ID ) ) 2. Stored Procedure A stored procedure, accept 2 IN […]
A JDBC CallableStatement example to call a stored procedure which accepts IN parameters. Tested with Java 8 and Oracle database 19c pom.xml <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc</artifactId> <version>8</version> <scope>system</scope> <systemPath>path.to/ojdbc8.jar</systemPath> </dependency> 1. JDBC CallableStatement 1.1 A PL/SQL stored procedure to insert a row. CREATE OR REPLACE PROCEDURE insert_employee( p_name IN EMPLOYEE.NAME%TYPE, p_salary IN EMPLOYEE.SALARY%TYPE, p_date IN EMPLOYEE.CREATED_DATE%TYPE) […]
Here’s an INSERT stored procedure example in Oracle database. 1. Table SQL Script DBUSER table creation script. CREATE TABLE DBUSER ( USER_ID NUMBER (5) NOT NULL, USERNAME VARCHAR2 (20) NOT NULL, CREATED_BY VARCHAR2 (20) NOT NULL, CREATED_DATE DATE NOT NULL, PRIMARY KEY ( USER_ID ) ) 2. Stored Procedure A stored procedure, accept 4 IN […]
List of quick examples to create stored procedures (IN, OUT, IN OUT and Cursor parameter) in Oracle database. PL/SQL code is self-explanatory. 1. Hello World A stored procedure to print out a “Hello World” via DBMS_OUTPUT. CREATE OR REPLACE PROCEDURE procPrintHelloWorld IS BEGIN DBMS_OUTPUT.PUT_LINE(‘Hello World!’); END; / Run it exec procPrintHelloWorld; Output Hello World! 2. […]
