wood burning stoves
The moose likes Other Java Products and Servers and the fly likes Error while inserting data through procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Groovy Fundamentals video training course this week in the Groovy forum!
JavaRanch » Java Forums » Products » Other Java Products and Servers
Bookmark "Error while inserting data through procedure" Watch "Error while inserting data through procedure" New topic

Error while inserting data through procedure

Sasvathan Sarvanandan

Joined: Nov 22, 2011
Posts: 2

Hello every one,

Im just a beginner to java and programming. I wrote this code for my school assignment, however it doesn't work. I created a procedure and tried to insert data through the procedure into a table, but it throws exception. I don't know y. Please some body tell what is the problem in my code....??? My code is bellow.

MY code:
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
//import java.sql.CallableStatement;
//import java.sql.Statement;

import javax.swing.JOptionPane;

public class DBControler {

static int StdID=1;

public static Connection geConnection() throws Exception{

String drive="oracle.jdbc.driver.OracleDriver";//get the driver
String url= "jdbcracle:thin:@localhost:1521rcl";//get the url

Class.forName(drive);//load driver
Connection conn= DriverManager.getConnection(url, userName, password);

return conn;

public static void createInsertProc(Connection conn) throws SQLException{

Statement stmt = conn.createStatement();

String procedure =
"CREATE OR REPLACE PROCEDURE std_insertProc( procStdID int, procfName varChar2,procLName Varchar2,procAddress VarChar2,procCity VarChar2,procprovince Varchar2,procpostalCode varchar2) AS "
+ "BEGIN "
+ "INSERT INTO Students VALUES(procStdID, procfName,procLName, procAddress, procCity, procprovince, procpostalCode );"
+ "END;";
//JOptionPane.showMessageDialog(null, "Success");

catch (Exception e) {


public static void insertStdInfo(Connection conn) throws SQLException{

try {
PreparedStatement cs=conn.prepareStatement("{std_insertProc(?,?,?,?,?,?,?)}");
cs.setInt(1, 1);
cs.setString(2, "Sasvathan");
cs.setString(3, "Sarvanandna");
cs.setString(4, "120");
cs.setString(5, "asdf");
cs.setString(6, "Ona");
cs.setString(7, "m1v3g5");

int val=cs.executeUpdate();

} catch (SQLException e) {






eclipse shows this Error message

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at DBControler.insertStdInfo(DBControler.java:64)
at Test.main(Test.java:15)[/color]

This is my student table
Create Table Students(
StudentID Int Primary Key,
FirstName Varchar2(20),
LastName Varchar2(20),
Address VarChar2(50),
City VarChar2(20),
Province Varchar2(20),
PostalCode varchar2(6)

Sasvathan Sarvanandan

Joined: Nov 22, 2011
Posts: 2

CallableStatement cs=conn.prepareCall("{call std_insertProc(?,?,?,?,?,?,?)}");
cs.setInt(1, 1);
cs.setString(2, "Sasvathan");
cs.setString(3, "Sarvanandna");
cs.setString(4, "120");
cs.setString(5, "asdf");
cs.setString(6, "Ona");
cs.setString(7, "m1v3g5");


This is the solution. I found some how......
I agree. Here's the link: http://aspose.com/file-tools
subject: Error while inserting data through procedure