Calling Oracle StoreProcedure FUNCTION

[Andrew P Chan]

Hi guy,

I would love to use the iBatis to call a store procedure function from the database server which is in the package. The configuration is listed below:

<parameterMap id="Accesses" class="System.Hashtable">
   <parameter property="code" dbType="VARCHAR2">
   <parameter property="message" dbType="VARCHAR2">
</parameterMap>
.....
...
..
<procedure id="Process" parameterMap="Accesses">
      dba_lib_package_a.update_Process(?, ?)
</procedure>

What's wrong with my configuration. I've got the Oracle Exception.
Can anyone pls verify the correct way to invoke a store procedure?

Many Thanks

[Michael Schall]

I have not used iBATIS with Oracle, but with SQL Server, you don't need the (?,?).  iBATIS caches a derive parameters call, so it knows the number of parameters and matches them with your parameter map.

Try

<procedure id="Process" parameterMap="Accesses">
     dba_lib_package_a.update_Process
</procedure>

Mike

On Mon, Jul 20, 2009 at 5:16 AM, Andrew P Chan <andrewnik820@...> wrote:

Hi guy,

I would love to use the iBatis to call a store procedure function from the
database server which is in the package. The configuration is listed below:

<parameterMap id="Accesses" class="System.Hashtable">
  <parameter property="code" dbType="VARCHAR2">
  <parameter property="message" dbType="VARCHAR2">
</parameterMap>
.....
...
..
<procedure id="Process" parameterMap="Accesses">
     dba_lib_package_a.update_Process(?, ?)
</procedure>

What's wrong with my configuration. I've got the Oracle Exception.
Can anyone pls verify the correct way to invoke a store procedure?

Many Thanks

--
View this message in context: http://www.nabble.com/Calling-Oracle-StoreProcedure-FUNCTION-tp24567140p24567140.html
Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-cs-unsubscribe@...
For additional commands, e-mail: user-cs-help@...

[Juan Pablo Araya]

As Mike said yo don't need the (?,?). Here is an example that returns a refCursor:

  <parameterMaps>
    <parameterMap id="parameterFoliosPreImpresosByColillaId" class="HashTable">
      <parameter property="IdColilla" column="P_ID" direction="Input"/>
      <parameter property="RefCursor" column="REFCURSOR" direction="Output" dbType="RefCursor"/>
    </parameterMap>
</parameterMaps>

  <resultMaps>
    <resultMap id="FolioPreImpresosPAMMap" class="FolioPreImpresosPAM">
      <result property="Id" column="CPAM_ID"/>
      <result property="ColillaId" column="COLL_ID"/>
      <result property="Folio" column="CPAM_NFOLIO_PAM"/>
      <result property="FechaIngreso" column="CPAM_FINGRESO"/>
    </resultMap>
  </resultMaps>

<procedure id="FindFolioPreImpresosPAMByColillaId" parameterMap="parameterFoliosPreImpresosByColillaId" resultMap="FolioPreImpresosPAMMap">
      PAMPKG_FOLIOSPREIMPRESOS.FIND_BY_COLILLA_ID
    </procedure>


And then you call it with something like this:

public List<FolioPreImpresoPAM> FindFolioPreImpresoByColillaId(string colillaId)
        {
            Hashtable parametros = new Hashtable();
            parametros.Add("IdColilla", colillaId);
            parametros.Add("RefCursor", null);
            try
            {
                return (List<FolioPreImpresoPAM>)Instance().QueryForList<FolioPreImpresoPAM>("FolioPreImpresosPAM.FindFolioPreImpresosPAMByColillaId", parametros);
            }
            catch (Exception ex)
            {
                throw new IBatisNetException("No es posible obtener los Folios Pre Impresos. ", ex);
            }
        }

As you can see, iBatis take the RefCursor and uses the resultmap in order to cast the results in the List<Object> you desire.

Heres is another example, the call of an update:

<parameterMap id="parameterUpdate" class="FolioPreImpresosPAM">
      <parameter direction="Input" property="Id" column="P_CPAM_ID"/>
      <parameter direction="Input" property="ColillaId" column="P_COLL_ID"/>
      <parameter direction="Input" property="Folio" column="P_CPAM_NFOLIO_PAM"/>
      <parameter direction="Input" property="FechaIngreso" column="P_CPAM_FINGRESO"/>
    </parameterMap>

<procedure id="UpdateFolioPreImpresosPAM" parameterMap="parameterUpdate">
      PAMPKG_FOLIOSPREIMPRESOS.UPDATE_FOLIOPREIMPRESOS
    </procedure>

In the case of Oracle i recommend you to set the column="" (in the parameter and result maps) in capital letter. Oracle is case sensitive, so if the procedure has a parameter

p_ParaMeTer_one

and you call it as

p_parameter_one then it fails, but if you set it as P_PARAMETER_ONE the procedure is executed. Weird!

Greetings and sorry for my poor English!

On Mon, Jul 20, 2009 at 9:19 AM, Michael Schall <mike.schall@...> wrote:

I have not used iBATIS with Oracle, but with SQL Server, you don't need the (?,?).  iBATIS caches a derive parameters call, so it knows the number of parameters and matches them with your parameter map.

Try

<procedure id="Process" parameterMap="Accesses">
     dba_lib_package_a.update_Process
</procedure>

Mike

On Mon, Jul 20, 2009 at 5:16 AM, Andrew P Chan <andrewnik820@...> wrote:

Hi guy,

I would love to use the iBatis to call a store procedure function from the
database server which is in the package. The configuration is listed below:

<parameterMap id="Accesses" class="System.Hashtable">
  <parameter property="code" dbType="VARCHAR2">
  <parameter property="message" dbType="VARCHAR2">
</parameterMap>
.....
...
..
<procedure id="Process" parameterMap="Accesses">
     dba_lib_package_a.update_Process(?, ?)
</procedure>

What's wrong with my configuration. I've got the Oracle Exception.
Can anyone pls verify the correct way to invoke a store procedure?

Many Thanks

--
View this message in context: http://www.nabble.com/Calling-Oracle-StoreProcedure-FUNCTION-tp24567140p24567140.html
Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-cs-unsubscribe@...
For additional commands, e-mail: user-cs-help@...

[Andrew P Chan]

It works... Thank you so much!!!