`
wuhuizhong
  • 浏览: 681156 次
  • 性别: Icon_minigender_1
  • 来自: 中山
社区版块
存档分类
最新评论

Pass a nested table to Java from a pl/sql procedure

 
阅读更多

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

分享到:
评论

相关推荐

    Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发

    2. **复合数据类型**:介绍PL/TABLE、记录类型(%ROWTYPE)以及集合类型(VARRAY和NESTED TABLE),这些都是处理大量数据或构建复杂数据结构的关键工具。 3. **游标**:讲解如何使用显式和隐式游标进行查询和遍历...

    oracle 10g WDP-PL/SQL 官方培训 PPT

    3. **过程与函数**:`PROCEDURE` 和 `FUNCTION` 是PL/SQL中的核心概念。过程是无返回值的子程序,而函数则返回一个值。学习如何定义、调用和重载它们,以及如何使用参数传递数据,是提高PL/SQL编程技能的关键。 4. ...

    Oracle Database PL/SQL Packages and Types Reference 11g Release

    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 Packages and Types Reference》是一本针对Oracle数据库编程的重要参考资料,主要涵盖了PL/SQL包和类型的详细信息。这本书对于那些具有英文阅读能力的开发者来说,是开发过程中的重要工具,能够帮助...

    Introduction to Oracle SQL and PL/SQL

    《Introduction to Oracle SQL and PL/SQL》是一本专为初学者设计的教程,全面涵盖了Oracle数据库管理系统中的核心语言——SQL(结构化查询语言)和PL/SQL(过程化语言/SQL)。这本书分为两卷,旨在帮助读者从基础到...

    精通Oracle 10g PL/SQL编程

    同时,也会讨论PL/SQL中的集合类型,如VARRAYs(可变数组)和NESTED TABLES(嵌套表),这些高级数据类型在处理大量数据时非常有用。 再者,Oracle 10g的PL/SQL中引入了游标概念,用于处理单行或多行结果集。书中会...

    Oracle PL SQL专家指南_高级PLSQL解决方案的设计与开发

    Oracle PL/SQL是Oracle数据库系统中的过程化语言,它结合了SQL的数据库操作能力和传统的编程语言特性,使得开发者能够创建复杂的数据处理逻辑和业务规则。《Oracle PL SQL专家指南:高级PLSQL解决方案的设计与开发》...

    PL/SQL袖珍手册第4版

    5. **集合类型**:深入理解PL/SQL中的索引数组、关联数组、集合类型(VARRAYs和Nested Tables)及其使用方法。 6. **记录类型**:学习如何定义和操作记录类型,以及如何在PL/SQL中复制和传递记录。 7. **动态SQL**...

    精通Oracle10g PL/SQL编程

    - **复合数据类型**:如记录(RECORD)和集合(TABLE、VARRAY、NESTED TABLE)类型,允许创建自定义数据结构。 3. **变量与常量**: - **变量**:用于存储数据,其值可以改变。 - **常量**:一旦声明,其值不能...

    Oracle Database PL/SQL Language Reference 11g Release 2 (11.2)-7

    Oracle Database PL/SQL Language Reference 11g Release 2 (11.2) 是一本官方文档,详尽地介绍了在Oracle数据库中使用PL/SQL语言的相关知识。PL/SQL是Oracle专为数据库管理设计的一种过程化编程语言,它结合了SQL...

    PL/SQL

    4. **集合类型**:PL/SQL提供了数组(PLS_INTEGER INDEX BY TABLE_TYPE)、关联数组(VARRAY)、嵌套表(NESTED TABLE)和集合类型(COLLECTION),用于处理一组值。 5. **过程和函数**:过程(PROCEDURE)是一组无...

    Oracle Database 10g PL-SQL Programming

    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/...

    PLSQL programming

    4. **集合类型**:如VARRAY(固定大小数组)和NESTED TABLE(可变大小数组),用于存储一组相关的数据。 5. **异常处理**:通过BEGIN...EXCEPTION块,能够捕获和处理运行时错误。 6. **游标**:用于遍历查询结果集,...

    pl/sql学习小结笔记

    - **嵌套表(Nested Table)**:内嵌在单个行内的表格。 - **Index-By表(联合数组)**:使用索引而非行号访问的数组。 4. **引用类型**: - **REF CURSOR(游标变量)**:允许在存储过程和函数中返回动态查询的...

    Oracle Database 12c PL-SQL programming

    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 ...

    Oracle PL-SQL语言初级教程

    记录类似于结构体,可以由不同的域组成,而集合则由多个元素组成,包括数组类型的列表(PL/SQL Table)和类似字典的映射表(Nested Table)。PL/SQL中定义和使用记录和集合的方法将在后续章节中详细解释。 函数和组...

    精通Oracle 10g PL.SQL编程

    4. **集合和记录类型**:学习使用PL/SQL内置的集合类型(如VARRAY和NESTED TABLE),以及自定义记录类型,实现更复杂的逻辑操作。 5. **动态SQL**:理解动态SQL的概念,如何使用EXECUTE IMMEDIATE和PREPARE语句执行...

    Oracle PLSQL最佳实践 第二版

    12. **PL/SQL与Java、.NET等的集成**:了解如何在PL/SQL中调用外部程序,实现与不同平台之间的接口通信。 通过本书的学习,你将能够编写出高效、健壮的PL/SQL代码,优化数据库性能,解决实际工作中遇到的问题,并且...

Global site tag (gtag.js) - Google Analytics