Re: What is the precise syntax to call stored procedure?

"Jacob Nikom" <jnikom_at_angstrommicro.com> wrote in message news:39C9191F.9C74D825_at_angstrommicro.com...
> Hi,
>
> I am trying to figure out the precise syntax to call stored procedure
> suing Java and
> Oracle database. I was able to create the store procedure using the
> following snippet
> and verified it with SQLplus tool:
>
> String createProcedure = "CREATE OR REPLACE PROCEDURE PROC1 "+
> "AS " +
> "BEGIN " +
> "INSERT INTO URDB_TEST VALUES " +
> "( " +
> "'stored str'," +
> "111," +
> "11111," +
> "111111," +
> "4.12," +
> "3.62," +
> "1 " +
> "); end";
>
> createStatement ();
> executeUpdate (createProcedure);
> closeStatement ();
>
> To call the stored procedure I used the syntax from Sun's tutorial:
> prepareCall ("{ CALL PROC1 }");
> execute();
>
> and it always produced error message:
>
> SQLException: ORA-06550: line 1, column 7:
> PLS-00905: object RMA.PROC1 is invalid
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> SQLState: 65000
> SQLVendorError: 6550
>
> I could not find any specific example how to call the stored procedure
> for Oracle database and Java. I know that Oracle is different, but how?
>
> I would appreciate any help,
>
> Jacob Nikom

Did you read the error message?
It tells you your procedure didn't compile!!! May I ask you a question?
I see your stored procedure is created dynamically, on the fly (which is IMO bad practice, and I would, as a DBA, definitely disallow), the procedure contains only one INSERT statement, with all values hardcoded (which is also bad practice).
So why aren't you using that INSERT statement directly, without the procedure?
BTW the error in your statement is residing in the line 'stored str'

Regards,

Sybrand Bakker, Oracle DBA

>
>
>
Received on Wed Sep 20 2000 - 00:00:00 CDT