Having trouble calling Oracle stored procedure in java

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.

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.

The stored procedure LIVE will not be compiled without errors, since you have a SELECT STATEMENT without INTO clause in an SP which is not valid.

Hence the issue. Use a REF CURSOR to return the result set.

In addition to the other answers, which are all good ...

You're probably expecting that since the CREATE PROCEDURE does not raise a SQLException, the procedure is compiled successfully. But this just indicates that it succeeded as a SQL statement -- i.e. a procedure was created. The procedure then has to be compiled as PL/SQL code, and that is failing due to various syntax errors that have been pointed out by others. In this situation, the procedure is created, so the SQL succeeds, but it has errors and is invalid.

An Oracle-specific tool, like SQL*Plus, is aware of this and displays appropriate feedback ("Procedure created with compilation errors") as shown in Rajesh's answer. JDBC doesn't check this automatically. If you really need to do the creation from Java, you should immediately check for errors after executing the CREATE: SELECT * FROM USER_ERRORS WHERE NAME='LIVERESULTS'. This will return essentially the same information that is displayed by the SQL*Plus SHOW ERRORS command.

You need to resolve the PL/SQL compilation errors before you can call the procedure.

You are missing the [required] IS (or alternatively 'AS').

CREATE OR REPLACE PROCEDURE LIVE() 
IS 
BEGIN
   <the body here> 
END;

[edit]