You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Robert Brueckmann <rb...@merlinsecurities.com> on 2006/12/28 19:08:05 UTC

oracle stored proc with out parameter of type CLOB

I cannot for the life of me get the following stored proc to work:

 

I have the following defined:

 

<sqlMap namespace="Security">

 

      <parameterMap id="getUserParameters" class="map" >

            <parameter property="username" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>

            <parameter property="xml" jdbcType="CLOB"
javaType="java.lang.String" mode="OUT"
typeHandler="org.springframework.orm.ibatis.support.ClobStringTypeHandle
r"/>

      </parameterMap>

 

      <procedure id="getUser" parameterMap="getUserParameters"
resultClass="java.lang.String">

            {call WEB.PKG_ADMIN.PR_GET_USER(?,?)}

      </procedure>

 

</sqlMap>

 

Then in my java code I have the following method:

 

public String login(Login login) throws DataAccessException {

            logger.debug("SqlMapSecurityDao::login(" +
login.getUsername() + ")");

 

            HashMap params = new HashMap();

 

            params.put("username", login.getUsername());

 

            getSqlMapClientTemplate().queryForObject("getUser", params);

 

            String xml = (String) params.get("xml");

 

            System.out.println("xml: " + xml);

 

            return xml;

      }

 

I'm using Spring obviously hand have my applicationContext.xml
configured as such:

 

          <!-- ========================= RESOURCE DEFINITIONS
========================= -->

      <!-- Main JNDI DataSource for J2EE environments -->

      <jee:jndi-lookup id="dataSource"
jndi-name="java:comp/env/jdbc/testDS"/>

 

      <!-- SqlMap setup for iBATIS Database Layer -->

      <bean id="simpleExtractor"
class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtra
ctor"/>

 

      <bean id="oracleLobHandler"
class="org.springframework.jdbc.support.lob.OracleLobHandler">

            <property name="nativeJdbcExtractor" ref="simpleExtractor"/>

      </bean>

 

      <bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">

            <property name="configLocation"
value="WEB-INF/sql-map-config.xml"/>

            <property name="dataSource" ref="dataSource"/>

            <property name="lobHandler" ref="oracleLobHandler"/>

      </bean>

 

      <!-- iBATIS and Spring database implementation -->

      <bean id="securityDAO"
class="com.merlinpb.dao.ibatis.SqlMapSecurityDao">

            <property name="sqlMapClient" ref="sqlMapClient"/>

      </bean>

 

When I call the login function above I get the following iBATIS nested
Exception:

 

org.springframework.jdbc.UncategorizedSQLException : SqlMapClient
operation; un

categorized SQLException for SQL []; SQL state [null]; error code [0];

--- The error occurred in com/merlinpb/dao/ibatis/maps/Security.xml.

--- The error occurred while applying a parameter map.

--- Check the Security.getUserParameters.

--- Check the output parameters (retrieval of output parameters failed).

--- Cause: java.sql.SQLException: Retrieving LOBs from a
CallableStatement is not supported; nested

exception is com.ibatis.common.jdbc.exception.NestedSQLException:

--- The error occurred in com/merlinpb/dao/ibatis/maps/Security.xml.

--- The error occurred while applying a parameter map.

--- Check the Security.getUserParameters.

--- Check the output parameters (retrieval of output parameters failed).

--- Cause: java.sql.SQLException: Retrieving LOBs from a
CallableStatement is not supported"

 

Is the exception my answer?  Are clobs just not supported as ouput
parameters for a procedure?  I'm using the latest version of
iBATIS...2.3 Beta downloaded yesterday and the latest Oracle drivers...

 

Thanks for any help!

 

robert l. brueckmann

vice president

merlin securities

712 fifth avenue

new york, ny 10019

p: 212.822.4821
f: 212.822.4820
 
--------------------------------------------------------

This message contains information from Merlin Securities, LLC, or from one of its affiliates, that may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify the sender immediately by telephone or by replying to this transmission.
 
Merlin Securities, LLC is a registered broker-dealer. Services offered through Merlin Securities, LLC are not insured by the FDIC or any other Federal Government Agency, are not deposits of or guaranteed by Merlin Securities, LLC and may lose value. Nothing in this communication shall constitute a solicitation or recommendation to buy or sell a particular security.

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

RE: oracle stored proc with out parameter of type CLOB

Posted by Robert Brueckmann <rb...@merlinsecurities.com>.
Hmm...here I thought I had to do all this special type handler stuff and
all I did was:

 

-         remove my OracleLobHandler injection in the
SqlMapClientFactoryBean from the applicationContext.xml file

-         remove the typeHandler attribute from my OUT parameter of type
clob

-         remove the resultClass from the procedure tag

 

Called my login function and the CLOB of XML was returned perfectly.  I
can't believe I didn't just try the most basic way first...based on all
of the forum entries and documentation I just assumed that CLOB handling
for an Oracle database was inherently complex...not so much in this
case.  Phew!


-- 
This message has been scanned for viruses and 
dangerous content by MailScanner <http://www.mailscanner.info/> , and is

believed to be clean.
 
--------------------------------------------------------

This message contains information from Merlin Securities, LLC, or from one of its affiliates, that may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify the sender immediately by telephone or by replying to this transmission.
 
Merlin Securities, LLC is a registered broker-dealer. Services offered through Merlin Securities, LLC are not insured by the FDIC or any other Federal Government Agency, are not deposits of or guaranteed by Merlin Securities, LLC and may lose value. Nothing in this communication shall constitute a solicitation or recommendation to buy or sell a particular security.