Tag: store procedure

jdbc June 19, 2019 by mkyong 0 comments

java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!

Ordinal binding or index binding: String name = stat.getString(2); BigDecimal salary = stat.getBigDecimal(3); Timestamp createdDate = stat.getTimestamp(4); Named binding: String name = stat.getString("NAME"); BigDecimal salary = stat.getBigDecimal("SALARY"); Timestamp createdDate = stat.getTimestamp("CREATED_DATE"); If we mixed both like this: String name = stat.getString(2); BigDecimal salary = stat.getBigDecimal("SALARY"); Timestamp createdDate = stat.getTimestamp(4); Error: java.sql.SQLException: operation not allowed: Ordinal […]

JDBC CallableStatement – Stored Procedure IN parameter example

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) […]

Oracle Stored Procedure INSERT example

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 […]