Re: problems with Oracle stored procedure

From: Bryan Lewis (brya..aine.rr.com)
Date: Fri Jul 06 2007 - 16:18:07 EDT

  • Next message: mr.abanjo: "How to create cayenne datamap on the fly"

    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

    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.

    Greg Boshart wrote:
    > Hello,
    >
    > I'm having trouble executing an Oracle stored procedure. It runs but
    > then gets stuck in an endless loop. I'm using Cayenne 2.0.3 and Oracle
    > 10g.
    >
    > Here is the stored procedure:
    >
    > CREATE OR REPLACE PROCEDURE BSO_DATAPUMP_START_EXPORT (handle VARCHAR,
    > dumpfile VARCHAR,
    > dumpdir VARCHAR) IS
    > h1 NUMBER;
    > BEGIN
    > h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL, handle, 'LATEST');
    > DBMS_DATAPUMP.ADD_FILE(h1, dumpfile, dumpdir);
    > DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''BSO_SCHEMA'')');
    > DBMS_DATAPUMP.START_JOB(h1);
    > DBMS_DATAPUMP.DETACH(h1);
    > END;
    > /
    >
    > It basically calls the Oracle DBMS_DATAPUMP API to start an schema
    > export job and then detaches, allowing the job to continuing executing
    > in the database server.
    >
    > I have mapped the stored procedure as follows:
    >
    > <procedure name="BSO_DATAPUMP_START_EXPORT">
    > <procedure-parameter name="handle" type="VARCHAR" direction="in"/>
    > <procedure-parameter name="dumpfile" type="VARCHAR" direction="in"/>
    > <procedure-parameter name="dumpdir" type="VARCHAR" direction="in"/>
    > </procedure>
    >
    > In my Java class I use the ProcedureQuery class like the example in the
    > user doc:
    > DataContext context = DataContext.createDataContext();
    > ProcedureQuery query = new ProcedureQuery("BSO_DATAPUMP_START_EXPORT");
    > query.addParameter("handle", handle);
    > query.addParameter("dumpfile", dumpfile);
    > query.addParameter("dumpdir", dumpdir);
    > List list = context.performQuery(query);
    >
    > The procedure runs successfully but never returns, instead endless
    > logging:
    >
    > INFO [main] - === updated 1 row.
    >
    > INFO [main] - === updated 1 row.
    >
    > INFO [main] - === updated 1 row.
    >
    > If I open up a standard JDBC connection like below, it executes and
    > returns properly:
    >
    > CallableStatement stmt = conn.prepareCall(
    > "begin BSO_DATAPUMP_START_EXPORT (?, ?, ?); end;");
    > stmt.setString(1, handle);
    > stmt.setString(2, dumpfile);
    > stmt.setString(3, dumpdir);
    > stmt.execute();
    >
    > In both scenarios, the stored procedure executes successfully however
    > when I use Cayenne I can never get past the DataContext.performQuery()
    > method. Can anyone think of a reason why this might be occuring or
    > suggest a workaround where I can directly use a connection from the
    > Cayenne DataSource pool?
    >
    > Thanks,
    >
    > Greg
    >
    >
    >



    This archive was generated by hypermail 2.0.0 : Fri Jul 06 2007 - 16:18:38 EDT