iBatis应用--编译Oracle存储过程

这是一个比较有意思的应用，通过解决这个问题，可以有效的学习iBatis的配置，另外还可以积累一些Oracle相关的知识。在解决问题的过程中，首先有一个基本的解决方法，然后在遇到问题、解决问题后，得到最终的版本。

假定当前有如下的存储过程（注意没有通常需要的“/”，这个很关键），我们需要在程序运行期间动态的编译上述存储过程（编译的目的当然是运行给出的存储过程）。

create or replace procedure p_timeout is

begin

    dbms_lock.sleep(30);

end p_timeout;

版本一

编译存储过程的方法比较简单，只要把上述语句执行后就算是编译了。联想到Oracle的PL/SQL有动态执行语句的能力，因而有如下版本的编译存储过程。

create or replace procedure p_compile_proc(proc_code in varchar2) is

begin

    execute immediate proc_code;

end p_compile_proc;

/

内容比较简单，仅仅是把传入的代码“执行”一下。为了在程序中调用上述的编译脚本，需要开发一部分代码和配置文件。

Java代码样例如下。

    public static void main(final String[] args) throws Exception {

        final InputStream sqlMapConfig = Resources.getResourceAsStream("SqlMapConfig.xml");

        final InputStream jdbcProperties = Resources.getResourceAsStream("jdbc.properties");

        final Properties properties = new Properties();

        properties.load(jdbcProperties);

        final StringBuilder sb = new StringBuilder();

        final File procFile = new File(new StringBuilder().append(System.getProperty("user.dir"))

                        .append(File.separator).append("p_timeout.prc").toString());

        BufferedReader reader = null;

        try {

            reader = new BufferedReader(new FileReader(procFile));

            String line = null;

            while ((line = reader.readLine()) != null) {

                line = line.trim();

                if (!line.equals("")) {

                    sb.append(line).append("\n");

                }

            }

        }

        catch (final FileNotFoundException e) {

            e.printStackTrace();

        }

        finally {

            reader.close();

        }

        final SqlMapClient sqlClient = SqlMapClientBuilder.buildSqlMapClient(

                        sqlMapConfig, properties);

        try {

            final Map<String, Object> conditions = new HashMap<String, Object>();

            conditions.put("proc_code", sb.toString());

            sqlClient.queryForObject("test.test_compile_proc", conditions);

        }

        catch (final SQLException e) {

            e.printStackTrace();

        }

        try {

            final Map<String, Object> conditions = new HashMap<String, Object>();

            conditions.put("proc_code", sb.toString());

            sqlClient.queryForObject("test.test_compile_proc2", conditions);

        }

        catch (final SQLException e) {

            e.printStackTrace();

        }

    }

配置文件样例。

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

    <parameter property="proc_code" jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/>

  </parameterMap>

 

  <procedure id="test_compile_proc" parameterMap="param_compile_proc">

    {call p_compile_proc(?)}

  </procedure>  

这时会遇到版本一的问题，权限不足，异常信息如下。

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

--- The error occurred in test.xml.  

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

--- Check the test.param_compile_proc.  

--- Check the statement (update procedure failed).  

--- Cause: java.sql.SQLSyntaxErrorException: ORA-01031: 权限不足

根据网上的资料可以得知，编译存储过程属于DDL操作，而Oracle对于存储过程中执行类似的行为有权限上的要求，而解决上述的异常，有一个简单的方法，修改编译代码的存储过程，增加一部分描述，这样即得到了版本二。

版本二

调整后的编译存储过程的代码如下，红色部分即是新增的部分。

create or replace procedure p_compile_proc(proc_code in varchar2) AUTHID CURRENT_USER is

begin

    execute immediate proc_code;

end p_compile_proc;

/

OK，这时再进行编译操作，发现Java代码不再报错，在SQLPLUS窗口执行命令exec p_timeout，发现存储过程执行成功，说明当前的方案是可行的。

版本二对于一般的应用是足够了，但是在项目中，偶尔会有如下的错误。

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

--- The error occurred in test.xml.  

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

--- Check the test.param_compile_proc.  

--- Check the statement (update procedure failed).  

--- Cause: java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 

这让人摸不到头脑，原因是被编译的存储过程在SQLPLUS窗口手工执行编译操作时是可以正常编译通过的，这样排除了代码自身的语法问题，但却在动态编译时报错，且每次报错都一样。

版本三

究竟是什么问题呢？检查代码其实看不出来问题，但对比上述报错的存储过程和可以正常编译通过的存储过程，发现二者有一个有趣的差异，即长度不同，出现问题的存储过程通常会比较长，那么问题和存储过程的长度有关系吗？

这里手工构造一个代码比较长的存储过程，代码如下。

create or replace procedure p_timeout is

v_int integer := 0;

begin

    select 0 into v_int from dual;

    select 0 into v_int from dual;--这行语句重复2400遍

................................

    select 0 into v_int from dual;

end p_timeout;

执行Java代码，果然报出了和生产环境一样的错误。说明刚才的问题和存储过程的长度有关系。

回过头来看看异常信息，结合Java代码、编译存储过程的代码，看看会不会有什么新的发现。首先从异常信息看，错误应当是在执行编译的脚本中抛出来的，这样编译存储过程的脚本有比较大的嫌疑。为了讨论方便，这里把版本二时使用的编译脚本放在这里。

create or replace procedure p_compile_proc(proc_code in varchar2) AUTHID CURRENT_USER is

begin

    execute immediate proc_code;

end p_compile_proc;

/

可以发现，这个脚本中与长度能扯上关系的大概只有变量proc_code，而proc_code被定义为varchar2类型，好像有点眉目。查阅Oracle的官方文档，varchar2类型的长度是有上限的，最大容量时可以容纳4000个字符，而刚才用来重现问题的存储过程中包含的字符数远远超出了这个限制。这样看来，问题的原因就找到了，解决问题的方法也就不远了。Oracle提供了字符串大对象类型CLOB，这里正好可以派上用场。调整编译存储过程的代码，样例如下：

create or replace procedure p_compile_proc(proc_code in clob) AUTHID CURRENT_USER is

begin

    execute immediate proc_code;

end p_compile_proc;

/

同时调整iBatis的配置。

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

    <parameter property="proc_code" jdbcType="CLOB" javaType="java.lang.String" typeHandler="com.ibatis.sqlmap.engine.type.ClobTypeHandlerCallback" mode="IN"/>

  </parameterMap>

   

  <procedure id="test_compile_proc" parameterMap="param_compile_proc">

    {call p_compile_proc(?)}

  </procedure>

再次执行Java程序，出现编译问题的存储可以正常编译通过了。