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

From: Jacob Nikom <jnikom_at_angstrommicro.com>
Date: 2000/09/21
Message-ID: <39CA7B8B.731066B8@angstrommicro.com>#1/1

Sybrand Bakker wrote:

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

>


> >

> >
> >

Hi Sybrand,

I really appreciate your help. Yes, I read the error message, but it did not help me very much. First, if my statement did not compile why the compiler did not tell it to me during compilation? SQL plus actually does it, so those capabilities do exist in Oracle. I assumed it was compiled correctly because I did not get any complaints about compilation as SQLplus does.

Second, the error message format looks quite cryptic to me. I could not find where it is explained. What "line 1, column 7:" means? I could guess that "object RMA.PROC1 " is my procedure but what other numbers mean?

Your comments about dynamically created stored procedures and hardcoded values are correct. The reason why we are doing it is that it is not "real" code for inserting values into the table, but
simply testing routines for our other development. Nevertheless, even for testing purposes the values should not be hardcoded. We are doing so only because our software is going to change significantly quite soon, so it is little early for us to invest in regression
testing capabilities.

You said that the error in my statement is in the line 'stored str'. The syntax does not look like the wrong one. What kind of error is it? Why do you think it is incorrect syntax? Should I remove single quotes?

Add additional blank?

Do you have any suggestions?

Thank you very much,

Jacob Nikom

PS. I could not reply to you directly - my e-mail server could not your domain.

Jacob Nikom Received on Thu Sep 21 2000 - 00:00:00 CDT