PLS-00382 expression is of wrong type

[Tim Slattery]

I'm getting a PLS-00382 error and cannot decrypt the message.

I'm invoking a synonym of a function from a java program. This
database has recently been jiggled so that instead of directly
invoking the function, I have to call a synonym. The synonym is
(apparently) properly defined, when I do:

select SOII_getSurveyControl from dual;

in SQLDeveloper, I get correct results. But when I invoke it from
Java, I get the error. The Java code has not changed from when I was
invoking the function directly instead of the synonym. It looks like
this:

CallableStatement sproc_stmt = dbConnection.prepareCall("{? = call
SOII_getSurveyControl}");
sproc_stmt.registerOutParameter(1, OracleTypes.CURSOR);
sproc_stmt.execute();
result = (ResultSet)sproc_stmt.getObject(1);

It blows up trying to run the execute() method. The exact error being
reported is:

java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-06550:
line 1, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

It references two columns in line 1, neither of which makes any sense.

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

[joel garry]

> Slatter...@bls.govhttp://members.cox.net/slatteryt

No idea about any of this stuff, but I do note maybe you have a
scoping error (perhaps some combo of jdbc and synonyms), see MOS
1019403.102 for what I'm referring to. Searching on the two errors
with && between them on MOS may give other clues.

Also googling +ORA-06550 +PLS-00382 +jdbc gives things like
http://forums.oracle.com/forums/thread.jspa?threadID=936149 which
refers to workarounds for some jdbc limitations.

jg
--
@home.com is bogus.
http://www.itproportal.com/2011/08/11/oracle-europe-recruitment-drive-add-1700-employees/
Expect recruiting spam on twitter.

[Tim Slattery]

joel garry <joel-...@home.com> wrote:

>No idea about any of this stuff, but I do note maybe you have a
>scoping error (perhaps some combo of jdbc and synonyms), see MOS
>1019403.102 for what I'm referring to. Searching on the two errors
>with && between them on MOS may give other clues.

I've solved it. This is a web system running under a WebLogic server.
I was using a Weblogic driver in my connection pool, when I switched
to an Oracle driver the problem vanished.

[Robert Klemme]

On 11.08.2011 19:38, Tim Slattery wrote:
> joel garry<joel-...@home.com> wrote:
>
>
>> No idea about any of this stuff, but I do note maybe you have a
>> scoping error (perhaps some combo of jdbc and synonyms), see MOS
>> 1019403.102 for what I'm referring to. Searching on the two errors

>> with&& between them on MOS may give other clues.

>
> I've solved it. This is a web system running under a WebLogic server.
> I was using a Weblogic driver in my connection pool, when I switched
> to an Oracle driver the problem vanished.

Then the reason was OracleTypes.CURSOR which is specific to Oracle.
With JBoss similar things can happen: the JEE container wraps JDBC
connections with its own wrapper class to be able to cache
PreparedStatement and handle XA logic. Whenever you need functionality
which is specific to the real driver used underneath you need to obtain
the wrapped connection and work with that.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/