CODEWITHSUNDEEP
javaoracle-databasejdbcplsql
Ashish Parab
Ashish Parab

Reputation: 196

issue in executing pl sql block through java

I have written a following code in java.

package demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class Sample {

    private Connection c = null;

    public Sample()
    {
        try {
            c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "jboss");
            String plsql = ""+
            "declare"+
                    "x number;"+
            "begin "+
                    "select HEADCOUNT  FROM PROJECT where deptid=1;"+
            "end;";
            CallableStatement cs = c.prepareCall(plsql);
            //cs.registerOutParameter(0, Types.INTEGER);

            cs.execute();
            System.out.println("Result is "+cs.getInt(1));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void main(String[] args) 
    {
        Sample z = new Sample();
    }
}

I just want explore the mechanism by which I can execute plsql blocks through java. above one is just simple query. on executing above java code, I get error as

java.sql.SQLException: Invalid SQL type: sqlKind = UNINITIALIZED
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:75)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1043)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
    at demo.Sample.<init>(Sample.java:26)
    at demo.Sample.main(Sample.java:36)

Let me know if there is any prior setting required before executing such pl/sql blocks.

Upvotes: 0

Views: 1606

Answers (3)

N00b Pr0grammer
N00b Pr0grammer

Reputation: 4647

Providing proper spaces in your String to form the SQL queries. This is what it looks like after changing with the necessary spaces!

String plsql = "declare x number; "+
                "begin "+
                   "select HEADCOUNT  FROM PROJECT where deptid=1; "+
                "end;";

You need to correct your code with right spaces or else the SQL that you want to pass itself is going to go wrong through your Java / JDBC code.

You could change your PL/SQL code to the following:

CallableStatement cs = con.prepareCall(
    "BEGIN SELECT HEADCOUNT INTO :1 FROM PROJECT WHERE deptid=1; END;"
);

Hope this answers your question well!

Upvotes: 0

MT0
MT0

Reputation: 168671

A few errors:

  1. The string will be concatenated to declarex and you need a space between them.
  2. You are not selecting the value from the table INTO a PL/SQL variable.
  3. The PL/SQL block is not putting the value into a bind variable to return it to Java (and if you use the bind variable directly then you don't need to declare a separate variable in PL/SQL).
  4. You have commented out the line to register an output parameter and the parameters are 1-indexed (not 0-indexed).

Something like this:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class Sample {
  public static void main( final String[] args ) 
  {
    try {
      Class.forName( "oracle.jdbc.OracleDriver" ); // If you are using the Oracle driver.

      Connection con = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:XE",
        "username",
        "password"
      );

      CallableStatement cs = con.prepareCall(
        "BEGIN SELECT HEADCOUNT INTO :1 FROM PROJECT WHERE deptid=1; END;"
      );
      cs.registerOutParameter( 1, Types.NUMERIC );
      cs.execute();
      System.out.println( cs.getInt( 1 ) );
    }
    catch ( ClassNotFoundException | SQLException ex )
    {
      System.err.println( ex.getMessage() );
    }
  }
}

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

1) First problem is declarex... that was mentioned in other answers.
2) Secound problem is select HEADCOUNT FROM PROJECT where deptid=1; In plsql this query is invalid. Should be select HEADCOUNT into x ...

Upvotes: 1

Related Questions