Announcement Announcement Module
Collapse
No announcement yet.
Oracle ROWTYPE in a Stored Procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle ROWTYPE in a Stored Procedure

    Hi there,

    Been lurking for the last two weeks while getting to grips with Spring. This is my first query.

    Spring 2.0.3 in Weblogic 8.1 sp6 Environment
    Oracle 9i

    I am attempting to call an Oracle Stored Procedure that contains in its signature an IN parameter using the pl/sql %ROWTYPE attribute.

    I was wondering if there is a way around having to explicitly create an Object Definition in the database for the Table Row and instead dynamically use the ROWTYPE.

    There is also one Table Type parameter but I'm hoping that if I can figure out the ROWTYPE issue then it will be straightforward.

    If I have to I can write a wrapper for the Stored Procedure with simple datatypes.

    Note:
    Previously using the BEA Data Services Platform with some additional pragma statements in XQuery I was able to query the Metadata for an Oracle Stored Procedure and create an XML signature for the Stored Procedure that worked perfectly. However this is not available for this particular project).

    Any input or pointers would be greatly appreciated.

    Edit: Some further thoughts:
    Would returning a REF CURSOR be a way to do this?
    Is there functionality in Hibernate 3.0 that I can leverage?

    The complete Oracle Procedure Signature (for what it's worth is)

    Code:
    PROCEDURE release_batch (
          p_api_version                IN              NUMBER
                := gme_api_pub.api_version,
          p_validation_level           IN              NUMBER
                := gme_api_pub.max_errors,
          p_init_msg_list              IN              BOOLEAN := FALSE ,
          p_commit                     IN              BOOLEAN := FALSE ,
          x_message_count          OUT NOCOPY      NUMBER,
          x_message_list              OUT NOCOPY      VARCHAR2,
          x_return_status             OUT NOCOPY      VARCHAR2,
          p_batch_header             IN              gme_batch_header%ROWTYPE,
          x_batch_header             OUT NOCOPY      gme_batch_header%ROWTYPE,
          p_ignore_shortages         IN              BOOLEAN,
          p_consume_avail_plain_item   IN              BOOLEAN DEFAULT FALSE ,
          x_unallocated_material     OUT NOCOPY      gme_api_pub.unallocated_materials_tab,
          p_ignore_unalloc             IN              BOOLEAN DEFAULT FALSE
       )
    Last edited by cousin_borat; Mar 16th, 2007, 06:02 PM.
Working...
X