You Asked
Hi Tom,
Can I pass a nested table from pl/sql procedure to java , I am using JDBC.If yes can you
please provide me a simple example or point me to a documentation on this.My developers
don't know how to do this.
(I have seen an example on this side about how to pass an array from function (not from
procedure) to java ).
Thanks so much .
Ajeet
and we said...
If you have my book "Expert one on one Oracle", this will look familar -- it is a subset
of the example I have in the chapter on java stored procedures where I demonstrate how to
send back and forth all of the SQL types as well as collections of them:
ops$tkyte@ORA920> create or replace type numArray as table of number;
2 /
Type created.
ops$tkyte@ORA920> create or replace type dateArray as table of date;
2 /
Type created.
ops$tkyte@ORA920> create or replace type strArray as table of varchar2(255);
2 /
Type created.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package demo_passing_pkg
2 as
3 procedure pass( p_in in numArray, p_out out numArray )
4 as language java
5 name 'demo_passing_pkg.pass_num_array( oracle.sql.ARRAY,
6 oracle.sql.ARRAY[] )';
7
8 procedure pass( p_in in dateArray, p_out out dateArray )
9 as language java
10 name 'demo_passing_pkg.pass_date_array( oracle.sql.ARRAY,
11 oracle.sql.ARRAY[] )';
12
13
14 procedure pass( p_in in strArray, p_out out strArray )
15 as language java
16 name 'demo_passing_pkg.pass_str_array( oracle.sql.ARRAY,
17 oracle.sql.ARRAY[] )';
18 end;
19 /
Package created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> set define off
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace and compile
2 java source named "demo_passing_pkg"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import java.math.*;
7 import oracle.sql.*;
8 import oracle.jdbc.driver.*;
9
10 public class demo_passing_pkg extends Object
11 {
12
13 private static void show_array_info( oracle.sql.ARRAY p_in )
14 throws SQLException
15 {
16 System.out.println( "Array is of type " +
17 p_in.getSQLTypeName() );
18 System.out.println( "Array is of type code " +
19 p_in.getBaseType() );
20 System.out.println( "Array is of length " +
21 p_in.length() );
22 }
23
24 public static void pass_num_array( oracle.sql.ARRAY p_in,
25 oracle.sql.ARRAY[] p_out )
26 throws SQLException
27 {
28 show_array_info( p_in );
29 java.math.BigDecimal[] values = (BigDecimal[])p_in.getArray();
30
31 for( int i = 0; i < p_in.length(); i++ )
32 System.out.println( "p_in["+i+"] = " + values[i].toString() );
33
34 Connection conn = new OracleDriver().defaultConnection();
35 ArrayDescriptor descriptor =
36 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
37
38 p_out[0] = new ARRAY( descriptor, conn, values );
39
40 }
41
42 public static void
43 pass_date_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
44 throws SQLException
45 {
46 show_array_info( p_in );
47 java.sql.Timestamp[] values = (Timestamp[])p_in.getArray();
48
49 for( int i = 0; i < p_in.length(); i++ )
50 System.out.println( "p_in["+i+"] = " + values[i].toString() );
51
52 Connection conn = new OracleDriver().defaultConnection();
53 ArrayDescriptor descriptor =
54 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
55
56 p_out[0] = new ARRAY( descriptor, conn, values );
57
58 }
59
60 public static void
61 pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
62 throws java.sql.SQLException,IOException
63 {
64 show_array_info( p_in );
65 String[] values = (String[])p_in.getArray();
66
67 for( int i = 0; i < p_in.length(); i++ )
68 System.out.println( "p_in["+i+"] = " + values[i] );
69
70 Connection conn = new OracleDriver().defaultConnection();
71 ArrayDescriptor descriptor =
72 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
73
74 p_out[0] = new ARRAY( descriptor, conn, values );
75
76 }
77
78 }
79 /
Java created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> set serveroutput on size 1000000
ops$tkyte@ORA920> exec dbms_java.set_output( 1000000 )
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_in strArray := strArray();
3 l_out strArray := strArray();
4 begin
5 for i in 1 .. 5 loop
6 l_in.extend;
7 l_in(i) := 'Element ' || i;
8 end loop;
9
10 demo_passing_pkg.pass( l_in, l_out );
11 for i in 1 .. l_out.count loop
12 dbms_output.put_line( 'l_out(' || i || ') = ' || l_out(i) );
13 end loop;
14 end;
15 /
Array is of type OPS$TKYTE.STRARRAY
Array is of type code 12
Array is of length 5
p_in[0] = Element 1
p_in[1] = Element 2
p_in[2] = Element 3
p_in[3] = Element 4
p_in[4] = Element 5
l_out(1) = Element 1
l_out(2) = Element 2
l_out(3) = Element 3
l_out(4) = Element 4
l_out(5) = Element 5
PL/SQL procedure successfully completed.
|
|
|
Dear Tom,
What if the nested table was constructed of composite type, for example:
CREATE OR REPLACE TYPE OBJ1 AS OBJECT (
COLUMN1 VARCHAR2(10),
COLUMN2 VARCHAR2(10));
CREATE OR REPLACE TYPE TAB1 AS TABLE OF OBJ1;
What changes would be required?
Objects in nested tables can be used the same way:
(consider nestedTable as oracle.sql.ARRAY - a nested table with object types inside)
Object[] nestedObjects = (Object[]) nestedTable.getArray();
oracle.sql.STRUCT firstObject = (oracle.sql.STRUCT)nestedObjects[0];
String firstColumn = firstObject.getAttributes()[0];
Jpub is a great tool, but for simple tasks a handcrafted java code is cleaner and takes less
dependecies
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8908169959941
分享到:
相关推荐
2. **复合数据类型**:介绍PL/TABLE、记录类型(%ROWTYPE)以及集合类型(VARRAY和NESTED TABLE),这些都是处理大量数据或构建复杂数据结构的关键工具。 3. **游标**:讲解如何使用显式和隐式游标进行查询和遍历...
3. **过程与函数**:`PROCEDURE` 和 `FUNCTION` 是PL/SQL中的核心概念。过程是无返回值的子程序,而函数则返回一个值。学习如何定义、调用和重载它们,以及如何使用参数传递数据,是提高PL/SQL编程技能的关键。 4. ...
Oracle Database PL/SQL Packages and Types Reference 11g Release 2 (11.2) 是一本重要的技术文档,为数据库管理员、开发人员和系统架构师提供了关于Oracle数据库中PL/SQL包和类型的详细信息。该文档是Oracle公司...
《Oracle PL/SQL Packages and Types Reference》是一本针对Oracle数据库编程的重要参考资料,主要涵盖了PL/SQL包和类型的详细信息。这本书对于那些具有英文阅读能力的开发者来说,是开发过程中的重要工具,能够帮助...
《Introduction to Oracle SQL and PL/SQL》是一本专为初学者设计的教程,全面涵盖了Oracle数据库管理系统中的核心语言——SQL(结构化查询语言)和PL/SQL(过程化语言/SQL)。这本书分为两卷,旨在帮助读者从基础到...
同时,也会讨论PL/SQL中的集合类型,如VARRAYs(可变数组)和NESTED TABLES(嵌套表),这些高级数据类型在处理大量数据时非常有用。 再者,Oracle 10g的PL/SQL中引入了游标概念,用于处理单行或多行结果集。书中会...
Oracle PL/SQL是Oracle数据库系统中的过程化语言,它结合了SQL的数据库操作能力和传统的编程语言特性,使得开发者能够创建复杂的数据处理逻辑和业务规则。《Oracle PL SQL专家指南:高级PLSQL解决方案的设计与开发》...
5. **集合类型**:深入理解PL/SQL中的索引数组、关联数组、集合类型(VARRAYs和Nested Tables)及其使用方法。 6. **记录类型**:学习如何定义和操作记录类型,以及如何在PL/SQL中复制和传递记录。 7. **动态SQL**...
- **复合数据类型**:如记录(RECORD)和集合(TABLE、VARRAY、NESTED TABLE)类型,允许创建自定义数据结构。 3. **变量与常量**: - **变量**:用于存储数据,其值可以改变。 - **常量**:一旦声明,其值不能...
Oracle Database PL/SQL Language Reference 11g Release 2 (11.2) 是一本官方文档,详尽地介绍了在Oracle数据库中使用PL/SQL语言的相关知识。PL/SQL是Oracle专为数据库管理设计的一种过程化编程语言,它结合了SQL...
4. **集合类型**:PL/SQL提供了数组(PLS_INTEGER INDEX BY TABLE_TYPE)、关联数组(VARRAY)、嵌套表(NESTED TABLE)和集合类型(COLLECTION),用于处理一组值。 5. **过程和函数**:过程(PROCEDURE)是一组无...
PL/SQL (Procedural Language for SQL) is a procedural extension to SQL that provides a powerful way to manipulate data within the Oracle database environment. This section introduces the basics of PL/...
4. **集合类型**:如VARRAY(固定大小数组)和NESTED TABLE(可变大小数组),用于存储一组相关的数据。 5. **异常处理**:通过BEGIN...EXCEPTION块,能够捕获和处理运行时错误。 6. **游标**:用于遍历查询结果集,...
- **嵌套表(Nested Table)**:内嵌在单个行内的表格。 - **Index-By表(联合数组)**:使用索引而非行号访问的数组。 4. **引用类型**: - **REF CURSOR(游标变量)**:允许在存储过程和函数中返回动态查询的...
Filled with detailed examples and expert strategies from an Oracle ACE, Oracle Database 12c PL/SQL Programming explains how to retrieve and process data, write PL/SQL statements, execute effective ...
记录类似于结构体,可以由不同的域组成,而集合则由多个元素组成,包括数组类型的列表(PL/SQL Table)和类似字典的映射表(Nested Table)。PL/SQL中定义和使用记录和集合的方法将在后续章节中详细解释。 函数和组...
4. **集合和记录类型**:学习使用PL/SQL内置的集合类型(如VARRAY和NESTED TABLE),以及自定义记录类型,实现更复杂的逻辑操作。 5. **动态SQL**:理解动态SQL的概念,如何使用EXECUTE IMMEDIATE和PREPARE语句执行...
12. **PL/SQL与Java、.NET等的集成**:了解如何在PL/SQL中调用外部程序,实现与不同平台之间的接口通信。 通过本书的学习,你将能够编写出高效、健壮的PL/SQL代码,优化数据库性能,解决实际工作中遇到的问题,并且...