Having trouble calling Oracle stored procedure in java

Question

I'm having trouble calling an Oracle stored procedure in Java. I added a stored procedure to the database like this:

         String SQL = "CREATE OR REPLACE PROCEDURE LIVERESULTS() " +
                    "IS " +
                    "BEGIN" +
                            " SELECT POOL_ID, POOL_MBR_ID, BSLN_CD, PGE_TYP_NM, SERV_NM, CL_FILE_NM" +
                            " FROM LBMADM.TPPO_MSTR_MAP " +
                            " ORDER BY SERV_NM" +
                            " WHERE PGE_TYP_NM = 'live' ; " +
                    "END";

                    stmtLIVE = con.createStatement();
                    stmtLIVE.executeUpdate(SQL);

And now I'm trying to call it like this :

CallableStatement cs;

                 try {
                         cs = con.prepareCall("{call LIVERESULTS() }");
                         rs = cs.executeQuery();

                         while (rs.next()) {
 .....

However, I'm getting the following errors:

 java.sql.SQLException: ORA-06550: line 1, column 7:
 PLS-00905: object UT9J.GENERATE_SQL_FOR_LIVE is invalid
 ORA-06550: line 1, column 7:
 PL/SQL: Statement ignored

I can't quite figure out where I'm going wrong. I read Oracle's documentation on it and I believe I did everything right, but I guess not. If anyone can shed some light on the situation I'd really appreciate it.

Answer 1

There are multiple issues here..

1) Why do you want to create the procedure inside Java Code? You should create it directly in Oracle using SQLPLUS or any other Database tool.

2) Oracle does not expect () when there are no in/out/in-out parameters, so you don't need them. You also need as "as" or "is" keyword after the "create or replace procedure". Otherwise, This will give the compile time error that you are seeing..

  1  CREATE OR REPLACE PROCEDURE LIVE
  2  IS
  3  begin
  4    null;   ---This would usually have your logic, 
               --Null indicates "DO nothing". Just to concentrate on 
               --the "declare" issues
  5* end;
SQL> /

Procedure created.

3) The third error is that you are selecting fields from a table, but there are no varibles "INTO" which you are selecting them.

  1  CREATE OR REPLACE PROCEDURE LIVE
  2  AS
  3    v_ename scott.emp.ename%type;
  4  begin
  5    select ename
  6      from scott.emp
  7      where empno = 7369;
  8* end;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE LIVE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PLS-00428: an INTO clause is expected in this SELECT statement

  1  CREATE OR REPLACE PROCEDURE LIVE
  2  AS
  3    v_ename scott.emp.ename%type;
  4  begin
  5    select ename
  6      into v_ename
  7      from scott.emp
  8      where empno = 7369;
  9* end;
SQL> /

Procedure created.

4) You are creating a procedure called "LIVE" and calling "LIVERESULTS".Again, you don't need () after the Procedure call.

5) Based on Allan's comments below and Cybernate's answer(+1), if you are trying to return a result set, you should open a refcursor for your select and then return it to the calling program.