Java调用Oracle存储过程传入数组参数的方法
Java调用Oracle存储过程传入数组参数的方法 周四的时候，修改我们系统的代码，应另外一个公司的要求，要修改我们系统调用他们公司的提供的存储过程的调用方式。修改前，是通过配置的数据库用户名和密码直接访问到他们的数据库，再调用存储过程；现在要修改成
Java调用Oracle存储过程传入数组参数的方法
周四的时候，修改我们系统的代码，应另外一个公司的要求，要修改我们系统调用他们公司的提供的存储过程的调用方式。修改前，是通过配置的数据库用户名和密码直接访问到他们的数据库，再调用存储过程；现在要修改成从我们的数据库中直接访问他们授权的存储过程。以前之所以通过用户名密码访问他们的数据库后再调用，是因为储过程的一个输入参数是数组类型的，传递数组参数的时候，Connection的类型一定要是OracleConnection的，当时只知道使用用户名密码直接通过JDBC访问Oracle，获取连接，再传递参数进去是没有问题的，其他的方式试了下不行，时间紧，也就没去折腾。正好这次又要改，而且不允许直接JDBC访问拿Connection了，系统部署在WebLogic上，必须从WebLogic的连接池中获取连接。
Java向Oracle传递数组参数的方法：
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor(sql_type_name, connection); //sql_type_name 是Oracle数据库中定义的数组类型的名称 ARRAY array = new ARRAY(arraydesc, connection, elements); //elements是Java数组类型的对象
这里的array对象，就可以在调用存储过程时作为数组参数传入。需注意的是，connection必须是Native Connection，如果connection是通过JDBC建立数据库连接并获取的，就不会有问题，但是如果是通过JNDI数据源获取的，就不行，因为从数据源获取的Connection是Native Connection的代理类，比如Tomcat中通过配置的JNDI数据源获取连接，连接类型是org.apache.commons.dbcp.PoolableConnection类型的；在WebLogic、JBoss等其他服务器中，得到的Connection类型又是不同的。
通过JNDI数据源获取连接的情况下，需要对获取到的连接进行一定的处理，获取到Native Connection。
如果项目中使用了Spring，那么，在使用JNDI数据源获取数据库连接的情况下，可以方便地通过Spring的NativeJdbcExtractor获取Native Connection。Spring中所包含的NativeJdbcExtractor实现有：C3P0NativeJdbcExtractor、CommonsDbcpNativeJdbcExtractor、JBossNativeJdbcExtractor、NativeJdbcExtractor、SimpleNativeJdbcExtractor、WebLogicNativeJdbcExtractor、WebSphereNativeJdbcExtractor、XAPoolNativeJdbcExtractor，根据应用部署环境的不同选择相应的NativeJdbcExtractor实现。
如果项目中没有使用Spring呢，大家可以去下载Spring的源码，找找这几个NativeJdbcExtractor的代码，参考一下就OK啦。 开源就是好嘛！
在成功获得Native Connection后，运行代码，却没有出现预期的成功调用的结果，而是抛出如下异常：
java.lang.ClassCastException: oracle.jdbc.driver.T4CConnection cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:149)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115)
搞到这里，我就糊涂了，我拿到了Native Connection了啊，你还要我怎样啊……心里那个郁闷啊……
郁闷归郁闷，还得抓点紧解决问题不是，结果又是一顿狂搜，还行，最终找到了解决方案：
部署环境下，删除WEB-INF/lib目录下的Oracle数据库驱动！
删除完毕，程序跑起来，果然这个异常没了……真TMD的不容易啊……感觉技术方面还是比较菜，需要多学习……
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1386
52
PHP: The Foundation of Many Websites
Apr 13, 2025 am 12:07 AM
The reasons why PHP is the preferred technology stack for many websites include its ease of use, strong community support, and widespread use. 1) Easy to learn and use, suitable for beginners. 2) Have a huge developer community and rich resources. 3) Widely used in WordPress, Drupal and other platforms. 4) Integrate tightly with web servers to simplify development deployment.
PHP: A Key Language for Web Development
Apr 13, 2025 am 12:08 AM
PHP is a scripting language widely used on the server side, especially suitable for web development. 1.PHP can embed HTML, process HTTP requests and responses, and supports a variety of databases. 2.PHP is used to generate dynamic web content, process form data, access databases, etc., with strong community support and open source resources. 3. PHP is an interpreted language, and the execution process includes lexical analysis, grammatical analysis, compilation and execution. 4.PHP can be combined with MySQL for advanced applications such as user registration systems. 5. When debugging PHP, you can use functions such as error_reporting() and var_dump(). 6. Optimize PHP code to use caching mechanisms, optimize database queries and use built-in functions. 7
How to delete all data from oracle
Apr 11, 2025 pm 08:36 PM
Deleting all data in Oracle requires the following steps: 1. Establish a connection; 2. Disable foreign key constraints; 3. Delete table data; 4. Submit transactions; 5. Enable foreign key constraints (optional). Be sure to back up the database before execution to prevent data loss.
How to solve the problem of closing oracle cursor
Apr 11, 2025 pm 10:18 PM
The method to solve the Oracle cursor closure problem includes: explicitly closing the cursor using the CLOSE statement. Declare the cursor in the FOR UPDATE clause so that it automatically closes after the scope is ended. Declare the cursor in the USING clause so that it automatically closes when the associated PL/SQL variable is closed. Use exception handling to ensure that the cursor is closed in any exception situation. Use the connection pool to automatically close the cursor. Disable automatic submission and delay cursor closing.
How to create cursors in oracle loop
Apr 12, 2025 am 06:18 AM
In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.
How to create oracle dynamic sql
Apr 12, 2025 am 06:06 AM
SQL statements can be created and executed based on runtime input by using Oracle's dynamic SQL. The steps include: preparing an empty string variable to store dynamically generated SQL statements. Use the EXECUTE IMMEDIATE or PREPARE statement to compile and execute dynamic SQL statements. Use bind variable to pass user input or other dynamic values to dynamic SQL. Use EXECUTE IMMEDIATE or EXECUTE to execute dynamic SQL statements.
PHP vs. Python: Core Features and Functionality
Apr 13, 2025 am 12:16 AM
PHP and Python each have their own advantages and are suitable for different scenarios. 1.PHP is suitable for web development and provides built-in web servers and rich function libraries. 2. Python is suitable for data science and machine learning, with concise syntax and a powerful standard library. When choosing, it should be decided based on project requirements.
How to paginate oracle database
Apr 11, 2025 pm 08:42 PM
Oracle database paging uses ROWNUM pseudo-columns or FETCH statements to implement: ROWNUM pseudo-columns are used to filter results by row numbers and are suitable for complex queries. The FETCH statement is used to get the specified number of first rows and is suitable for simple queries.
