problems with Oracle stored procedure

From: Greg Boshart (gboshar..operiscope.com)
Date: Fri Jul 06 2007 - 12:19:47 EDT

  • Next message: Bryan Lewis: "Re: problems with Oracle stored procedure"

    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 - 12:45:29 EDT