I'm at a total impasse. I have a stored function that returns a
SYS_REFCURSOR. It works fine, I can call it in SQL*Plus and see the
data that's been extracted. I'm trying to call it from Java:

String spName;
try
{
getDBConnection(); /* sets "dbConnection */

spName = "{ call ? := getReporterData(?) }";

sprocStmt = dbConnection.prepareCall(spName);
sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
sprocStmt.setString(2, key);
sprocStmt.execute();

And I get ORA-00911 Invalid character. What invalid character?

--
Tim Slattery
Slattery_T@bls.gov
http://members.cox.net/slatteryt

Tim Slattery (Slattery_T@bls.gov) wrote:
: I'm at a total impasse. I have a stored function that returns a
: SYS_REFCURSOR. It works fine, I can call it in SQL*Plus and see the
: data that's been extracted. I'm trying to call it from Java:

: String spName;
: try
: {
: getDBConnection(); /* sets "dbConnection */
:
: spName = "{ call ? := getReporterData(?) }";
:
: sprocStmt = dbConnection.prepareCall(spName);
: sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
: sprocStmt.setString(2, key);
: sprocStmt.execute();

: And I get ORA-00911 Invalid character. What invalid character?

What are the { } braces for in the SQL statement ?

On Aug 19, 12:23*am, Tim Slattery <Slatter...@bls.gov> wrote:
> I'm at a total impasse. I have a stored function that returns a
> SYS_REFCURSOR. It works fine, I can call it in SQL*Plus and see the
> data that's been extracted. I'm trying to call it from Java:
>
> * * * *String spName;
> * * * *try
> * * * *{
> * * * * * *getDBConnection(); * * * */* sets "dbConnection */
>
> * * * * * *spName = "{ call ? := getReporterData(?) }";
>
> * * * * * *sprocStmt = dbConnection.prepareCall(spName);
> * * * * * *sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
> sprocStmt.setString(2, key);
> * * * * * *sprocStmt.execute();
>
> And I get ORA-00911 Invalid character. What invalid character?
>
> --
> Tim Slattery
> Slatter...@bls.govhttp://members.cox.net/slatteryt

Shouldn't this be spName = "{ ?= call getReporterData(?) }"; ? At
least if you're using JDBC escape syntax... Otherwise it can be spName
= "BEGIN ? := getReporterData(?); END;", in this case it'll be Oracle-
specific.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

yf110@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:

>Tim Slattery (Slattery_T@bls.gov) wrote:
>: I'm at a total impasse. I have a stored function that returns a
>: SYS_REFCURSOR. It works fine, I can call it in SQL*Plus and see the
>: data that's been extracted. I'm trying to call it from Java:
>
>: String spName;
>: try
>: {
>: getDBConnection(); /* sets "dbConnection */
>:
>: spName = "{ call ? := getReporterData(?) }";
>:
>: sprocStmt = dbConnection.prepareCall(spName);
>: sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
>: sprocStmt.setString(2, key);
>: sprocStmt.execute();
>
>: And I get ORA-00911 Invalid character. What invalid character?
>
>What are the { } braces for in the SQL statement ?

That's JDBC syntax, it's not the problem.

<Sigh>....the java folks thought maybe the colon before the equal sign
was the problem. That's not it either.

--
Tim Slattery
Slattery_T@bls.gov
http://members.cox.net/slatteryt

"Vladimir M. Zakharychev" <vladimir.zakharychev@gmail.com> wrote:

>Shouldn't this be spName = "{ ?= call getReporterData(?) }"; ? At
>least if you're using JDBC escape syntax... Otherwise it can be spName
>= "BEGIN ? := getReporterData(?); END;", in this case it'll be Oracle-
>specific.

I don't think so, I have ample documentation for :=. And I've tried it
without the colon and gotten the same result.

FWIW, I've tried the Oracle syntax (begin...end) and also gotten an
error: expression is of wrong type. I don't understand that one
either.

--
Tim Slattery
Slattery_T@bls.gov
http://members.cox.net/slatteryt

Tim Slattery <Slattery_T@bls.gov> wrote:
> I'm at a total impasse. I have a stored function that returns a
> SYS_REFCURSOR. It works fine, I can call it in SQL*Plus and see the
> data that's been extracted. I'm trying to call it from Java:
>
> String spName;
> try
> {
> getDBConnection(); /* sets "dbConnection */
>
> spName = "{ call ? := getReporterData(?) }";
>
> sprocStmt = dbConnection.prepareCall(spName);
> sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
> sprocStmt.setString(2, key);
> sprocStmt.execute();
>
> And I get ORA-00911 Invalid character. What invalid character?

According to the documentation, the syntax is wrong, see
http://download-uk.oracle.com/docs/c...c.htm#i1008346

They have two samples:

// SQL-92 syntax
conn.prepareCall("{? = call func (?,?)}");

// PL/SQL anonymous block
conn.prepareCall("begin ? := func(?,?); end;");

But you shouldn't mix these two.

Yours,
Laurenz Albe

On Aug 19, 8:31*pm, Tim Slattery <Slatter...@bls.gov> wrote:
> "Vladimir M. Zakharychev" <vladimir.zakharyc...@gmail.com> wrote:
>
> >Shouldn't this be spName = "{ ?= call getReporterData(?) }"; ? At
> >least if you're using JDBC escape syntax... Otherwise it can be spName
> >= "BEGIN ? := getReporterData(?); END;", in this case it'll be Oracle-
> >specific.
>
> I don't think so, I have ample documentation for :=. And I've tried it
> without the colon and gotten the same result.
>
> FWIW, I've tried the Oracle syntax (begin...end) and also gotten an
> error: *expression is of wrong type. I don't understand that one
> either.
>
> --
> Tim Slattery
> Slatter...@bls.govhttp://members.cox.net/slatteryt

JDBC docs suggest otherwise and your syntax does not look correct:
assignment to the return variable should precede the CALL keyword, not
follow it (" ?= call <procname>", not "call ?= <procname>" as in your
example.) Anyway, which statement throws the error? prepareCall() or
execute(), or maybe one of the binds? I don't see where you declare
sprocStmt either - is it a CallableStatement or an
OracleCallableStatement? And what's the function prototype? Is it's
argument indeed a VARCHAR2?

You see, my crystal ball is malfunctioning lately, so the more input
you give, the more chances there are we will be able to help you
pinpoint the source of the problem and fix it.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

"Vladimir M. Zakharychev" <vladimir.zakharychev@gmail.com> wrote:

>JDBC docs suggest otherwise and your syntax does not look correct:
>assignment to the return variable should precede the CALL keyword, not
>follow it (" ?= call <procname>", not "call ?= <procname>" as in your
>example.)

Oh geez, you're right, of course. My typing here was sloppy. I do have
it correct in the code: { call ? := getReporterData(?) }

> Anyway, which statement throws the error? prepareCall() or
>execute(), or maybe one of the binds? I don't see where you declare
>sprocStmt either - is it a CallableStatement or an
>OracleCallableStatement? And what's the function prototype? Is it's
>argument indeed a VARCHAR2?

execute throws the error. I've never heard of
"OracleCallableStatement", the examples I've seen use
"CallableStatement", and that's what sprocStmt is.

I just tried "OracleCallableStatement" in my code. Now when I call the
PrepareStatement method of the connection object, I get told that it's
returning the wrong thing, namely a CallableStatement object that it's
trying to stuff into an OracleCallableStatement object.

The argument to getReporterData is char.

>You see, my crystal ball is malfunctioning lately, so the more input
>you give, the more chances there are we will be able to help you
>pinpoint the source of the problem and fix it.

Absolutely, I've said the same to many posters in other groups (where
I actually have a clue as to the subject matter)!

--
Tim Slattery
Slattery_T@bls.gov
http://members.cox.net/slatteryt

"Vladimir M. Zakharychev" <vladimir.zakharychev@gmail.com> wrote:


>> >Shouldn't this be spName = "{ ?= call getReporterData(?) }"; ? At
>> >least if you're using JDBC escape syntax... Otherwise it can be spName
>> >= "BEGIN ? := getReporterData(?); END;", in this case it'll be Oracle-
>> >specific.

Just looked back in the thread. My original post had the syntax
correct, I don't know where it got inverted.

--
Tim Slattery
Slattery_T@bls.gov
http://members.cox.net/slatteryt

Laurenz Albe <invite@spam.to.invalid> wrote:

>They have two samples:
>
>// SQL-92 syntax
>conn.prepareCall("{? = call func (?,?)}");
>
>// PL/SQL anonymous block
>conn.prepareCall("begin ? := func(?,?); end;");

I've tried both, I get illegal character from the first and something
about illegal type from the second. Neither works.

--
Tim Slattery
Slattery_T@bls.gov
http://members.cox.net/slatteryt