Warm tip: This article is reproduced from stackoverflow.com, please click
jdbc oracle sqlplus

JDBC: PL/SQL object (procedure) is invalid

发布于 2020-04-08 09:27:18

JDBC: PL/SQL object (procedure) is invalid.

String sql = "create procedure foo(name varchar2) as " +
"begin " +
...
"end";

Statement stmt = connection.createStatement();
stmt.execute(sql);
stmt.close();


String sql1 = "begin foo('world'); end;";
CallableStatement s = connection.prepareCall(sql1);
s.execute();
s.close();

ERROR when executing the second SQL:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00905: object MY_DB.FOO is invalid
ORA-06550: line 1, column 7:

MY_DB is current connection user. After the 1st statement, the procedure is not found by desc Foo from sqlplus.

NO error if running these two statements in sqlplus.

Questioner
Sunnyday
Viewed
60
分享
community wiki 2020-02-01 09:05
  • A semi-colon is required to terminate every PL/SQL statement, declaration & block.
  • The outermost PL/SQL block is usually also terminated by a slash / character.
  • In contrast, each atomic item of SQL code is a single statement and is typically terminated by a semi-colon ; character.

In SQL/Plus (and SQL Developer) the terminating characters are used to signify ends of executable pieces of code (and depending on the user interface's settings a blank line can do this too) and the start of the next.

In JDBC, when you invoke a piece of code you do not need the terminating character as each call only contains a single statement (for SQL) or (outer) block (for PL/SQL) and the terminator would be redundant. So for a PL/SQL statement being called via JDBC, you do not need the / to terminate the code but you do require the ; to terminate the statements, declaraions & block(s) that the code is comprised of (including the outer-most BEGIN/END block).

So your code should be:

String sql = "create procedure foo(name varchar2) as " +
"begin " +
...
"end;";

Related issues

1
How do I pivot in Oracle SQL 19
2
dbms_output.put_line not working for type variable
3
How to extract words from space in oracle?
4
Oracle 19c
5
How can I add condition for total sales per month for specific category
6
Dynamic pivot in oracle sql
7
How to group by month and year?
8
Procedure is not running
9
How do I update CLOB column on oracle with PHP
10
A trigger inserting a row (not new) after update