RE: problems with Oracle stored procedure

From: Greg Boshart (gboshar..operiscope.com)
Date: Fri Jul 13 2007 - 10:58:55 EDT

  • Next message: Andrus Adamchik: "Re: Modifying projects in Java"

    Thanks Andrus, I appreciate your help. What version of Cayenne are you
    using? What about your Oracle JDBC drivers?

    Does your stored procedure mapping look like:

            <procedure name="cayenne_tst_upd_proc">
                    <procedure-parameter name="painting_price"
    type="INTEGER" direction="in"/>
            </procedure>

    Greg

    -----Original Message-----
    From: Andrus Adamchik [mailto:andru..bjectstyle.org]
    Sent: Wednesday, July 11, 2007 5:12 PM
    To: use..ayenne.apache.org
    Subject: Re: problems with Oracle stored procedure

    Interesting... All stored procedure unit tests succeed for me on Oracle
    10. E.g. a procedure like that:

    CREATE OR REPLACE PROCEDURE cayenne_tst_upd_proc
      (painting_price IN NUMBER) AS
      BEGIN
             UPDATE PAINTING SET ESTIMATED_PRICE = ESTIMATED_PRICE * 2
             WHERE ESTIMATED_PRICE < painting_price;
      END;

       ProcedureQuery q = new ProcedureQuery("cayenne_tst_upd_proc");
       q.addParameter("paintingPrice", new Integer(3000));
       ctxt.performGenericQuery(q);

    Andrus

    On Jul 10, 2007, at 9:49 PM, Andrus Adamchik wrote:

    > BTW, I was able to install Oracle 10.2.0.1 on a virtual Linux server
    > running on my Mac. Hopefully I'll get a few spare minutes in the next
    > couple of days to do sme stored procedure testing.
    >
    > Andrus
    >
    >
    > On Jul 9, 2007, at 8:14 PM, Andrus Adamchik wrote:
    >
    >> Hi Greg,
    >>
    >> Unfortunately I don't have access to Oracle at present. I think I can

    >> tweak the OracleProcedureAction to use a bit different strategy when
    >> processing result set. But I can only do that if I have the test
    >> environment, which I don't :-/ Let me see if I can resolve the Oracle

    >> situation (maybe put it on a virtual Linux partition on my Mac... ).
    >> In the meantime if you find anything of relevance, please share.
    >>
    >> Andrus
    >>
    >>
    >> On Jul 9, 2007, at 7:45 PM, Greg Boshart wrote:
    >>> After further testing, I wrote a do-nothing stored procedure in
    >>> Oracle:
    >>>
    >>> CREATE OR REPLACE PROCEDURE skeleton
    >>> IS
    >>> BEGIN
    >>> DBMS_OUTPUT.PUT_LINE('Hello World!');
    >>> END;
    >>> /
    >>>
    >>> This procedure runs fine via JDBC but once again gets stuck in an
    >>> endless loop in Cayenne. I have verified this behavior in
    >>> Solaris 9 and
    >>> Windows 2003 environments.
    >>>
    >>> Thanks,
    >>>
    >>> Greg
    >>>
    >>> ________________________________
    >>>
    >>> From: Greg Boshart
    >>> Sent: Monday, July 09, 2007 8:24 AM
    >>> To: use..ayenne.apache.org
    >>> Subject: RE: problems with Oracle stored procedure
    >>>
    >>>
    >>> I appreciate the response. It turns out there was already no adapter
    >>> specified. Any other ideas?
    >>>
    >>> Thanks,
    >>>
    >>> Greg
    >>>
    >>> ________________________________
    >>>
    >>>
    >>> This sounds very similar to a problem I had last year. I got an
    >>> infinite loop of "updated rows" log messages on Oracle. See
    >>> CAY-464 at
    >>> https://issues.apache.org/cayenne/browse/CAY-464
    >>> <https://issues.apache.org/cayenne/browse/CAY-464>
    >>>
    >>> Maybe it won't help -- we thought the problem appeared only on an
    >>> older
    >>> version of Oracle -- but it's worth a try. Clear the Custom Adapter
    >>> fields in the modeler.
    >>>
    >>
    >>
    >
    >



    This archive was generated by hypermail 2.0.0 : Fri Jul 13 2007 - 10:59:26 EDT