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.

@JustYo: no the error is pretty specific (and OP should google ORA-nnn to see what it is). It is a general syntax error and indicates ORA never even got to calling the wrong proc. It can NOT generate the necessary SQL for "live()" -- e.g. PLS-00905 -- because "LINE 1" of the procedure def is missing required IS keyword (e.g. Syntax error ORA-06550).
–
alphazeroJul 22 '11 at 13:49

I actually had IS in there, along with the correct match of LIVERESULTS (I copied it over wrong). I have googled it but I'm still a bit stuck.
–
thomascircaJul 22 '11 at 14:02

You also have the ORDER BY before the WHERE clause. See Rajesh's response below.
–
Jeff MooreJul 22 '11 at 14:12

What are you trying to do with the procedure? Even if you fix it so that you're putting the results of the select statement into a variable, you're still not accomplishing anything.
–
AllanJul 22 '11 at 14:59

4 Answers
4

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..

I actually had the correct match between prodcure names and the addition of IS- I had a bad C/P. The problem is that I don't have any tools to directly access the DB from my machine- this is a workaround. How would I properly address your third point?
–
thomascircaJul 22 '11 at 14:07

1. You should be able to do download SQLPLUS from Oracle or Oracle's free tool "SQL Developer" to use as a development tool. Creating them from Java just isn't right. Also, there is every chance that your "J2USER" (or any other Java user that you login with ) might not have the "create PROCEDURE" privilege in a higher environment. (well.. they shouldn't be able to do that even in development environment..)
–
Rajesh ChamarthiJul 22 '11 at 14:13

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.