AF
HomeTagSubmit NotesAsk AnythingLoginSubscribe Us
AF
1. Feel Free to ask and submit anything on Anyforum.in and get satisfactory answer
2. Registration is not compulsory, you can directly login via google or facebook
3. Our Experts are looking for yours ?.



sql-basics: How to call Trigger and cursor using JDBC?

Explain the trigger & cursor calling concepts using jdbc with sample code.

sql x 47
basics x 169
Posted On : 2016-09-06 08:50:41.0
profile MOHAMMAD SALEEM BASHA - anyforum.in MOHAMMAD SALEEM BASHA
266150
up-rate
4
down-rate

Answers


Trigger is invoked by Oracle engine automatically whenever a specified event occurs.Trigger is stored into database and invoked repeatedly, when specific condition match.

Triggers are stored programs, which are automatically executed or fired when some event occurs.

Triggers are written to be executed in response to any of the following events.

* A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
* A database definition (DDL) statement (CREATE, ALTER, or DROP).
* A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).


For Oracle stored procedure returns CURSOR parameter, you can:
Registered via JDBC CallableStatement.registerOutParameter(index,OracleTypes.CURSOR).
Get it back via callableStatement.getObject(index).

//getDBUSERCursor is a stored procedure
String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, "anyforum");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);

// execute getDBUSERCursor store procedure
callableStatement.executeUpdate();

// get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);

// loop it like normal
while (rs.next()) {
String userid = rs.getString("USER_ID");
String userName = rs.getString("USERNAME");
}

JDBC CallableStatement CURSOR Example:
-----------------------------------------------------------------------------------
create a procedure:
----------------------------------------------
CREATE OR REPLACE PROCEDURE getDBUSERCursor(
p_username IN DBUSER.USERNAME%TYPE,
c_dbuser OUT SYS_REFCURSOR)
IS
BEGIN

OPEN c_dbuser FOR
SELECT * FROM DBUSER WHERE USERNAME LIKE p_username || ´%´;

END;
/

JDBC example to call above stored procedure, cast the returned CURSOR to ResultSet and loop through the records sequentially.

JDBCCallableStatementCURSORExample.java:
-----------------------------------------------------------------------------------------------
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

public class JDBCCallableStatementCURSORExample {

private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:XE";
private static final String DB_USER = "user";
private static final String DB_PASSWORD = "password";

public static void main(String[] argv) {

try {

callOracleStoredProcCURSORParameter();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

private static void callOracleStoredProcCURSORParameter()
throws SQLException {

Connection dbConnection = null;
CallableStatement callableStatement = null;
ResultSet rs = null;

String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";

try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);

callableStatement.setString(1, "anyforum");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);

// execute getDBUSERCursor store procedure
callableStatement.executeUpdate();

// get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);

while (rs.next()) {
String userid = rs.getString("USER_ID");
String userName = rs.getString("USERNAME");
String createdBy = rs.getString("CREATED_BY");
String createdDate = rs.getString("CREATED_DATE");

System.out.println("UserName : " + userid);
System.out.println("UserName : " + userName);
System.out.println("CreatedBy : " + createdBy);
System.out.println("CreatedDate : " + createdDate);
}

} catch (SQLException e) {

System.out.println(e.getMessage());

} finally {

if (rs != null) {
rs.close();
}

if (callableStatement != null) {
callableStatement.close();
}

if (dbConnection != null) {
dbConnection.close();
}

}

}

private static Connection getDBConnection() {

Connection dbConnection = null;

try {

Class.forName(DB_DRIVER);

} catch (ClassNotFoundException e) {

System.out.println(e.getMessage());

}

try {

dbConnection = DriverManager.getConnection(
DB_CONNECTION, DB_USER,DB_PASSWORD);
return dbConnection;

} catch (SQLException e) {

System.out.println(e.getMessage());

}

return dbConnection;

}

}

Posted On : 2016-09-06 16:03:33
Satisfied : 1 Yes  0 No
profile Rishi Kumar - anyforum.in Rishi Kumar
523185319307
Reply This Thread
up-rate
4
down-rate



Post Answer
Please Login First to Post Answer: Login login with facebook - anyforum.in