ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lee Shinlever <lshinle...@lexesinc.com>
Subject RE: How to call procedure in oracle package using IBatis.Net.
Date Mon, 24 Sep 2007 13:38:13 GMT
Sure,

 First download and install the provider from:
http://www.oracle.com/technology/tech/windows/odpnet/index.html . I just
checked and we switched to the 10.2 version and it works fine. Also the
application is pulling data from a SQL Server database and the Oracle
database at the same time and works great.

Make sure you have this entry in your provider.config. Make sure the
enabled and default values are set appropriately.:

            <provider 
		name="oracle10.1" 
		description="Oracle, oracle provider V10.1.0.301"
	        enabled="true"
              default="true" 
		assemblyName="Oracle.DataAccess, Version=10.1.0.301,
Culture=neutral,PublicKeyToken=89b483f429c47342" 

connectionClass="Oracle.DataAccess.Client.OracleConnection" 
		commandClass="Oracle.DataAccess.Client.OracleCommand" 
		parameterClass="Oracle.DataAccess.Client.OracleParameter" 
		parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType" 
		parameterDbTypeProperty="OracleDbType" 
		dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter" 
		commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder" 
		usePositionalParameters="true" 
		useParameterPrefixInSql="true"
		useParameterPrefixInParameter="true" 
		parameterPrefix=":" 
		useDeriveParameters="false"/>

 In your SqlMap.config make sure you have this entry:
                <database>
                         <provider name="oracle10.1"/>
                                  <dataSource name="YourDataSourceName" 
                                            connectionString=
                                                     "Data
Source=Service name from your tnsnames.ora file;
                                                      User ID=UserId for
the schema owner of the packages;
                                                      Password=The
password;"/>
               </database>

Here I'll just put my entries for my database so you can see how we use
it.
My statements:
                 <statements>
                            <!-- Get more than one basic tool-->
                            <procedure id="sp_get_basic_tools" 

parameterMap="BasicToolsRefCursor" 
                                       resultMap="BasicTool">
                              PKG_TESTING.sp_get_basic_tools
                            </procedure>

                            <!-- Get just a single basic tool-->
                            <procedure id="sp_get_basic_tool"
                                       parameterMap="GetABasicTool"
                                       resultMap="BasicTool">
                              PKG_TESTING.sp_get_basic_tool
                            </procedure>
                 </statements>

My parameter maps:
                <parameterMaps>
                        <parameterMap id="BasicToolsRefCursor">
                              <parameter property="bt_rc_list"
direction="Output" column="bt_rc_list" dbType="RefCursor"/>
                        </parameterMap>

                        <parameterMap id="GetABasicTool">
                              <parameter property="PI_BT_BT_KEY"/>
                                      <parameter property="PO_CUR_OUT"
direction="Output" column="PO_CUR_OUT" dbType="RefCursor"/>
                               </parameterMap>
                   </parameterMaps>

Things to watch out for:
    1) Using a log on that doesn't own or have access to the packages
    2) Parameters that are interpreted as strings in .Net seem to always
need to have their dbType and size set
 
Let me know if you need any more information.

Hope this helps,
Lee


On Mon, 2007-09-24 at 10:12 +0800, pei allen wrote:

> 
> Dear Lee,
>      Could you please give me a sample?
>      Thanks!
> Allen
> 
> 
>         
>         ______________________________________________________________
>         Subject: RE: How to call procedure in oracle package using
>         IBatis.Net.
>         From: lshinlever@lexesinc.com
>         To: user-cs@ibatis.apache.org
>         Date: Fri, 21 Sep 2007 09:28:30 -0400
>         
>         Greetings,
>         
>         We use Oracle packages successfully here but we use the
>         ODP.Net 10.1 provider from Oracle not OleDb. Could you try
>         that?
>           
>         Lee
>         
>         On Fri, 2007-09-21 at 20:47 +0800, pei allen wrote:
>         
>                 
>                 Dear all,
>                 The prodedure with no package,like below (it runs
>                 ok!):
>                 ********************************************************
>                 CREATE OR REPLACE 
>                 PROCEDURE allen_test (i_input in number,o_result out
>                 number) 
>                 IS   
>                 v_seq                  NUMBER;                
>                 BEGIN    
>                     o_result := i_input;
>                     return;
>                 END allen_test;
>                 **************************************
>                 <procedure id="Pr ocedureTest"
>                 parameterMap="ProcedureTestMap" >
>                         ALLEN_TEST
>                       </procedure>
>                  
>                 parameterMap id="ProcedureTestMap" class="Hashtable">
>                         <parameter property="input" column="i_input"
>                 dbType="integer" direction="Input" />
>                         <parameter property="result" column="o_result"
>                 dbType="integer" direction="Output" />
>                       </parameterMap>
>                 ****************************************
>                 public long ProcedureTest()
>                         {
>                  & nbsp;          Hashtable ht = new Hashtable();
>                             ht["input"] = 1;
>                             ht["result"] = 0;
>                 
>                 m_DaoFacade.QueryForObject<Hashtable>("ProcedureTest",
>                 ht);
>                             return Convert.ToInt32(ht["result"]);
>                         }
>                         #endregion
>                 **********************************************
>                  
>                 So please help how to call procedure in oracle package
>                 using IBatis .Net.
>                  
>                 Thanks very much!
>                 allen
>                 2007.9.21
>                 
>                 
>                 
>                         
>                         ______________________________________________
>                         
>                         From: allenpei0805@hotmail.com
>                         To: user-cs@ibatis.apache.org
>                         Subject: RE: How to call procedure in oracle
>                         package using IBatis.Net.
>                         Date: Fri, 21 Sep 2007 20:32:17 +0800
>                         
>                         Dear all,
>                                my whole sqlmap:
>                            <?xml version="1.0" encoding="utf-8" ?>
>                         <sqlMap namespace="CommonBase"
>                         xmlns="http://ibatis.apache.org/mapping"
>                         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
>                             <statements>
>                               <procedure id="ProcedureTestPkg"
>                         parameterMap="ProcedureTestMap Pkg" >
>                                 ALLEN_PKG.ALLEN_TEST
>                               </procedure>
>                                 
>                             </statements>
>                             <parameterMaps>
>                               <parameterMap id="ProcedureTestMapPkg"
>                         class="Hashtable">
>                                 <parameter property="input"
>                         column="i_input " dbType="integer"
>                         direction="Input" />
>                                 <parameter property="result"
>                         column="o_result" dbType="integer"
>                         direction="Output" />
>                               </parameterMap>
>                             </p arameterMaps>
>                         </sqlMap>
>                         
>                         *******************************************
>                             SqlMapConfig like below database node, the
>                         othe node is like the standard config:
>                         <database>
>                          <provider name="OleDb1.1"/>
>                             <dataSource name="darwine_test"
>                         connectionString="Provider=OraOLEDB.Oracle.1;Password=oracle;Persist
Security Info=True;User ID=rddarwine;Data Source=darwine_rd"/>
>                         </database>
>                          
>                         I only know "Queryforobject","Queryforlist",
>                         any other?
>                         
>                         but with this config,i can call the procedure
>                         successfully if this procedure not in
>                         package,but in oracle, i don't konw why? like
>                         below:
>                         <procedure id="Pr ocedureTest"
>                         parameterMap="ProcedureTestMap" >
>                                 ALLEN_TEST
>                               </procedure>
>                          
>                         parameterMap id="ProcedureTestMap"
>                         class="Hashtable">
>                                 <parameter property="input"
>                         column="i_input" dbType="integer"
>                         direction="Input" />
>                                 <parameter property="result"
>                         column="o_result" dbType="integer"
>                         direction="Output" />
>                               </parameterMap>
>                         
>                         Thanks!
>                         2007.9.21
>                         
>                         > Subject: RE: How to call procedure in oracle
>                         package using IBatis.Net.
>                         > Date: Fri, 21 Sep 2007 08:15:55 -0400
>                         > From: Samuel_Clough@princetonrg.com
>                         > To: user-cs@ibatis.apache.org
>                         > 
>                         > I think we need to see more of your sql map.
>                         All I see is your
>                         > procedure name, and we need to see the xml
>                         node that the call is defined
>                         > in.
>                         > 
>                         > I 'm also not sure you're using the correct
>                         call. Unless there's an
>                         > attribute I'm not aware of, Queryforobject
>                         is to read an object from a
>                         > result set, not load it from an input/output
>                         parameter.
>                         > 
>                         > -----Original Message-----
>                         > From: pei allen
>                         [mailto:allenpei0805@hotmail.c om] 
>                         > Sent: Friday, September 21, 2007 8:10 AM
>                         > To: user-cs@ibatis.apache.org
>                         > Subject: How to call procedure in oracle
>                         package using IBatis.Net.
>                         > 
>                         > 
>                         > Dear all,
>                         > when i call procedure in oracle package
>                         using IBatisNet, it always
>                         > failed.
>                         > I can call oracle procedre using IBatisNet
>                         successfully,but to
>                         > package 's procedure, it dosen't work.
>                         > The code like this:
>                         > 
>                         > Oracle's Package like below:
>                         > **************************************
>                         > CREATE OR REPLACE 
>                         > PACKAGE allen_pkg
>                         > IS
>                         > 
>                         > PROCEDURE ALLEN_TEST (i_input in
>                         number,o_result out number) ;
>                         &g t; END;
>                         > /
>                         > CREATE OR REPLACE 
>                         > PACKAGE BODY allen_pkg
>                         > IS
>                         > PROCEDURE ALLEN_TEST (i_input in
>                         number,o_result out number) 
>                         > IS 
>                         > v_seq NUMBER; 
>                         > BEGIN 
>                         > o_result := i_input;
>                         > return;
>                         > END ALLEN_TEST;
>                         > 
>                         > END allen_pkg;
>                         > ***************************************
>                         > 
>                         > The SqlMap like below:
>                         >
>                         *****************************************************
>                         > 
>                         > 
>                         > ALLEN_PKG.ALLEN_TEST
>                         > 
>                         > 
>                         > 
>                         > 
>                         > 
>                         > 
>                         > 
>                         > 
>                         >
>                         ******************************************************
>                         > 
>                         > The Calling function like below:
>                         >
>                         *****************************************************
>                         > public long ProcedureTest()
>                         > {
>                         > Hashtable ht = new Hashtable();
>                         > ht["input"] = 1;
>                         > ht["result"] = 0;
>                         >
>                         m_DaoFacade.QueryForObject("ProcedureTestPkg",
>                         ht);
>                         > 
>                         > return Convert.ToInt32( ht["result"]);
>                         > }
>                         >
>                         ********************************************************
>                         > 
>                         > when run to
>                         "m_DaoFacade.QueryForObject("ProcedureTestPkg", ht);", an
>                         > exception was thrown like below:
>                         > "PLS-00306: wrong number or types of
>                         arguments ......"
>                         > 
>                         > I use Oracle 10gR2, VS2005 C#,
>                         IBatisNet.DataMapper(1.5.1.0)
>                         > 
>                         > Thanks!
>                         > 2007.9.21
>                         > 
>                         > 
>                         > 
>                         > 
>                         >
>                         _________________________________________________________________
>                         > Invite your mail contacts to join your
>                         friends list with Windows Live
>                         > Spaces. It's easy!
>                         >
>                         http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.a
>                         > spx&mkt=en-us 
>                         >
>                         --------------------------------------------------------
>                         > 
>                         > Princeton Retirement Group, Inc - Important
>                         Terms 
>                         > This E-mail is not intended for distribution
>                         to, or use by, any person or entity in any
>                         location where such distributio n or use w
>                         ould be contrary to law or regulation, or
>                         which would subject Princeton Retirement
>                         Group, Inc. or any affiliate to any
>                         registration requirement within such
>                         location. 
>                         > This E-mail may contain privileged or
>                         confidential information or may otherwise be
>                         protected by work product immunity or other
>                         legal rules. No confidentiality or privilege
>                         is waived or lost by any mistransmission.
>                         Access, copying or re-use of information by
>                         non-intended or non-authorized recipients is
>                         prohibited. If you are not an intended
>                         recipient of this E-mail, please notify the
>                         sender, delete it and do not read, act upon,
>                         print, disclose, copy, retain or redistribute
>                         any portion of this E-mail. 
>                         > The transmission and content of this E-mail
>                         cannot be guaranteed to be secure or
>                         error-free. Therefore, we cannot represent
>                         that the information in this E-mail is
>                         complete, accurate, uncorrupted, timely or
>                         free of viruses, and Princeton Retiremen t
>                         Group, Inc. cannot accept any liability fo r
>                         E-mails that have been altered in the course
>                         of delivery. Princeton Retirement Group, Inc.
>                         reserves the right to monitor, review and
>                         retain all electronic communications,
>                         including E-mail, traveling through its
>                         networks and systems (subject to and in
>                         accordance with local laws). If any of your
>                         details are incorrect or if you no longer wish
>                         to receive mailings such as this by E-mail
>                         please contact the sender by reply E-mail. 
>                         > 
>                         >
>                         --------------------------------------------------------
>                         
>                         
>                         
>                         ______________________________________________
>                         
>                         Discover the new Windows Vista Learn more! 
>                 
>                 
>                 
>                 ______________________________________________________
>                 
>                 Explore the seven wonders of the world Learn more! 
> 
> 
> 
> ______________________________________________________________________
> Get news, entertainment and everything you care about at Live.com.
> Check it out!

Mime
View raw message