Announcement Announcement Module
Collapse
No announcement yet.
How do I deal with an oracle cursor type, returned from a stored proc, with simpleJdb Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do I deal with an oracle cursor type, returned from a stored proc, with simpleJdb

    Here's are little oracle package, with the spec and the body:

    CREATE OR REPLACE PACKAGE bz_grh_test AS

    TYPE sztuser_rec IS RECORD ( studr_cde sztuser.sztuser_studr_cde%TYPE
    ,type_cde sztuser.sztuser_type_cde%TYPE
    ,user_id sztuser.sztuser_user_id%TYPE );

    -- (1) The cursor that returns the above record type
    TYPE reg_cursor IS REF CURSOR RETURN sztuser_rec;

    -- (2) The procedure that takes IN params and returns the above
    -- cursor as the OUT param
    PROCEDURE get_reg_stuff_p (p_iap IN sztuser.sztuser_iap_cde%TYPE
    , cur_get_reg_stuff OUT reg_cursor );

    END bz_grh_test;
    /

    CREATE OR REPLACE PACKAGE BODY bz_grh_test AS

    --
    -------------------------------------------------------------------------
    --
    PROCEDURE get_reg_stuff_p (p_iap IN sztuser.sztuser_iap_cde%TYPE
    , cur_get_reg_stuff OUT reg_cursor )
    IS
    BEGIN
    OPEN cur_get_reg_stuff
    FOR
    SELECT sztuser_studr_cde studr_cde
    , sztuser_type_cde type_cde
    , sztuser_user_id user_id
    FROM sztuser
    WHERE sztuser_iap_cde = p_iap;

    END get_reg_stuff_p;

    --
    -------------------------------------------------------------------------
    --

    END bz_grh_test;
    /


    Could anyone explain to me how the hell to deal with this "ref cursor" type returned from the procedure above!?! I'm damned if I can figure it out!

    This is what I have done so far:

    private SimpleJdbcCall procGetRegStuff;

    @SuppressWarnings("deprecation")
    public void setJdbcCall(SimpleJdbcCall jdbcCall){
    this.procGetRegStuff=jdbcCall.withCatalogName("BZ_ GRH_TEST").withProcedureName("get_reg_stuff_p").us eInParameterNames("p_iap")
    .returningResultSet("cur_get_reg_stuff", ParameterizedBeanPropertyRowMapper.newInstance(Reg User.class));
    }

    I have no clue what to do with this!

    Thanks for any help!
    --
    Guy
    Last edited by grheatley; Aug 11th, 2011, 06:08 AM. Reason: typo (pasted some parts twice)

  • #2
    The power of RTFM

    I was forced into drastic measures and resorted to RTFM

    http://static.springsource.org/sprin...toredProcedure
    The technique described here worked, although I still don't fully understand the code I originally posted, and why it didn't work. i.e:

    @SuppressWarnings("deprecation")
    public void setJdbcCall(SimpleJdbcCall jdbcCall){
    this.procGetRegStuff=jdbcCall.withCatalogName("BZ_ GRH_TEST").withProcedureName("get_reg_stuff_p").us eInParameterNames("p_iap")
    .returningResultSet("cur_get_reg_stuff", ParameterizedBeanPropertyRowMapper.newInstance(Reg User.class));
    }
    Last edited by grheatley; Aug 11th, 2011, 06:14 AM.

    Comment

    Working...
    X