RE: Running a packaged Stored Procedure

From: Williams, Beth (beth.william..FIB.ORG)
Date: Fri Nov 12 2004 - 14:07:18 EST

  • Next message: Brian Plotner: "Using SetDistinct()"

    Thanks Cris and Andrus. That did it!! I appreciate the quick response.

    Beth

    -----Original Message-----
    From: Cris Daniluk [mailto:cris.danilu..laraview.com]
    Sent: Friday, November 12, 2004 12:44 PM
    To: cayenne-use..bjectstyle.org
    Subject: RE: Running a packaged Stored Procedure

    We are using this to access packaged proceudres in Oracle.. Andrus is
    correct in how the procedure should be named in the modeler, and
    consequently in the application. It's somewhat annoying to have a dependency
    on the package name, but its not really *that* bad, as the stored
    procedures, and even then moreso, the packages, tend to be more stable than
    the underlying tables.

    -----Original Message-----
    From: Andrus Adamchik [mailto:andru..bjectstyle.org]
    Sent: Friday, November 12, 2004 1:29 PM
    To: cayenne-use..bjectstyle.org
    Subject: Re: Running a packaged Stored Procedure

    Hi Beth,

    > I also tried to call the procedure with the package name
    > (ProcedureQuery procQuery = new ProcedureQuery("TESTPKG.TESTPROC2");),
    > however, I then get a "No DataMap found for query with root:
    TESTPKG.TESTPROC2" error.

    This seems like the right way to specify the package. To make it work, in
    the modeler set the procedure name to include package, i.e.
    "TESTPKG.TESTPROC2", just like in the query.

    Andrus

    > I have a stored procedure (TESTPROC2) in Cayenne (pulled in through
    > reverse-engineering) that is part of a package (TESTPKG). I am trying
    > to run this procedure using the API. I noticed that in the *map.xml
    > the procedure has a catalog parameter pointing to the package. I
    > guess the first question is, can a packaged procedure be run through
    > Cayenne? If so, how should it be done? I have used the following code
    with no luck:
    >
    > *** code ***
    > DataContext cayenneContext = DataContext.createDataContext();
    > ProcedureQuery procQuery = new ProcedureQuery("TESTPROC2");
    > procQuery.addParameter("I_NUM1", "2"); List rows =
    > cayenneContext.performQuery(procQuery);
    > ******
    >
    > This code generates the following error:
    > java.sql.SQLException: ORA-06550: line 1, column 7:
    > PLS-00201: identifier 'WILLIAMSBE.TESTPROC2' must be declared
    > ORA-06550: line 1, column 7:
    > PL/SQL: Statement ignored
    >
    > I also tried to call the procedure with the package name
    > (ProcedureQuery procQuery = new ProcedureQuery("TESTPKG.TESTPROC2");),
    > however, I then get a "No DataMap found for query with root:
    TESTPKG.TESTPROC2" error.
    > Is this simply an issue with how I'm calling the procedure? Any help
    > would be much appreciated. Thanks.
    >
    > Beth



    This archive was generated by hypermail 2.0.0 : Fri Nov 12 2004 - 14:07:19 EST