syntax of stored procedure returning data

2004-11-02
2013-04-11
  • Nobody/Anonymous

    What is the correct syntax of calling a stored procedure that returns resultset (using REF Cursor) back?  I am using Oracle.

    Any help is appreciated

     
    • Nobody/Anonymous

      me too
      want to know

       
    • Nobody/Anonymous

      Hi,
      I'am using version 1.3.1. According to the documentation (pg. 20) output parameters are not supported.
      Here an example of my code:
      <mapped-statement name="exportHierarchy" is-stored-procedure="true">
      {CALL HB.PCK_MHC_EXPORT.p_export(#target#, #code#)}
      </mapped-statement>

      Greetz
      Rule

       
    • Nobody/Anonymous

      I am using a single INOUT parameter.But when I try to read the 'out' value I am geting null pointer exception.The procedure is excuting fine.

      Here is my XML...
      <parameterMap id="sayhello" class="map" >
      <parameter property="name" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
      </parameterMap>

      <procedure id="getSayGreeting" parameterMap="sayhello">
      {call say_greeting (?)}
      </procedure>

      and this is what I am doing in my code..............

      Map m1=new HashMap();
            m1.put("name","satya");
          m1=(Map) queryForObject("getSayGreeting",m1);
          return m1.get("name").toString();

      Any ideas why it is throwing null pointer exception.

      cheers
      Satya

       
    • Nobody/Anonymous

      Hi,
      To call a stored-procedure you need the 'is-stored-procedure="true" ' in the mapped statement.
      I guess you get the null pointer at your last line. Thats clear: the query return a null and is assigned to m1. m1.get() will throw a null pointer.

      Greetz
      Rule

       
    • Nobody/Anonymous

      Rule

      my sqlmap.xml didn't like the attribute 'is-stored-procedure="true"'.It says unknown attribute.

      This is how I inlcuded your suggestion .

      <procedure id="getSayGreeting"  is-stored-procedure="true" parameterMap="sayhello">
      { CALL say_greeting(?,?)}
      </procedure>

      I tried this way also...

      <statement id="getSayGreeting"  is-stored-procedure="true" parameterMap="sayhello">
      { CALL say_greeting(?,?)}
      </statement>

      Its says 'unrecognised' attribute for the element.

      thx
      satya

       
    • Nobody/Anonymous

      I figured out the problem.
      I was expecting the 'queryForObject' to constuct and return an Object or map which ever the case may be.But in case of stored procedures it always returns 'null'.

      Initially I was doing like this.
      Map m1=new HashMap();
      m1.put("name","satya");
      m1=(Map) queryForObject("getSayGreeting",m1);
      return m1.get("name").toString();

      The above code throws null pointer exception as I was expecting an Object(MAP) and assigning it to a map.

      Instead of trying to take the returned object if I make use of the passed-in object,I was able to get the modified OUT parameter from the procedure.

      Map m1=new HashMap();
      m1.put("name","satya");
      ///here I am not trying to get the object assigned to another map variable .I am just using the passed-in 'm1' to read the changed value./////

      queryForObject("getSayGreeting",m1);
      return m1.get("name").toString();

      Here is my xml map///////
      <parameterMap id="sayhello" class="map">
      <parameter property="name" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
      </parameterMap>

      <procedure id="getSayGreeting"  parameterMap="sayhello" >
      {call say_greeting(?)}
      </procedure>

      I am not sure why the 'queryForObject' returning a proper 'map' object.It alwyas returns null.May be its by design for the stored Procedure.

      thx
      Satya Sunkara

       

Log in to post a comment.