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
