You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by David Balažic <da...@comtrade.com> on 2011/01/24 13:24:49 UTC

procedures in package not correctly called

Hi!

Using cayenne 3.0.1 with Oracle 10g DB, I discovered a problem with stored procedures.

First I used the modeller to reengineer existing stored procedures.

It created this mapping:

<procedure name="PROC_1" schema="SCH" catalog="PKG1">
(parameters omitted)

When calling the procedure from Java code like this:

ProcedureQuery q = new ProcedureQuery("PROC_1");
q.addParameter("PAR1", "value1");
// parameters...
context.performQuery(q);

This makes this SQL call:

{call SCH.PROC_1(?, ?, ?, ?)}

Which is wrong. The DB returns:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SCH.PROC_1' must be declared

The correct call would be:

{call SCH.PKG1.PROC_1(?, ?, ?, ?)}

At least this is what works when run directly (JDBC or sql tool from
Oracle: SqlPlus)

Regards,
David

David Balažic
Software Engineer

    ComTrade

    Litijska 51, 1000 Ljubljana
    Slovenia

    phone: +386 81 60 8937
    fax: +386 1 586 52 70

www.comtrade.com

Re: procedures in package not correctly called

Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah, there's no proper catalog support when Cayenne builds fully qualified names. You may enter "PKG1.SCH" as your schema name. 

Separately we may discuss the need for supporting JDBC "catalogs" in Cayenne separately from "schemas".

Andrus


On Jan 24, 2011, at 2:24 PM, David Balažic wrote:
> Hi!
> 
> Using cayenne 3.0.1 with Oracle 10g DB, I discovered a problem with stored procedures.
> 
> First I used the modeller to reengineer existing stored procedures.
> 
> It created this mapping:
> 
> <procedure name="PROC_1" schema="SCH" catalog="PKG1">
> (parameters omitted)
> 
> When calling the procedure from Java code like this:
> 
> ProcedureQuery q = new ProcedureQuery("PROC_1");
> q.addParameter("PAR1", "value1");
> // parameters...
> context.performQuery(q);
> 
> This makes this SQL call:
> 
> {call SCH.PROC_1(?, ?, ?, ?)}
> 
> Which is wrong. The DB returns:
> ORA-06550: line 1, column 7:
> PLS-00201: identifier 'SCH.PROC_1' must be declared
> 
> The correct call would be:
> 
> {call SCH.PKG1.PROC_1(?, ?, ?, ?)}
> 
> At least this is what works when run directly (JDBC or sql tool from
> Oracle: SqlPlus)
> 
> Regards,
> David
> 
> David Balažic
> Software Engineer
> 
>    ComTrade
> 
>    Litijska 51, 1000 Ljubljana
>    Slovenia
> 
>    phone: +386 81 60 8937
>    fax: +386 1 586 52 70
> 
> www.comtrade.com
>