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
分享到:
相关推荐
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 ...
This section describes new features of PL/SQL release 10g, and provides pointers to additional information. The following sections describe the new features in PL/SQL: ■ New Features in PL/SQL for ...
java解决nested exception is java.lang.OutOfMemoryError Java heap space 解决OOM
java解决Handler processing failed; nested exception is java.lang.NoClassDefFoundError
NULL 博文链接:https://ivan-yan.iteye.com/blog/1007671
SP-7522 : Fixed an issue of incorrect wildcard expansion from a nested SELECT containing CASE. SP-7545 : Fixed an issue where aliases were not being suggested within nested scopes. Fixed an issue ...
Designed as a guidebook for those who want to become a Java developer, Java 7: A Comprehensive Tutorial discusses the essential Java programming topics that you need to master in order teach other ...
NULL 博文链接:https://bijian1013.iteye.com/blog/2305804
Just as the organization of a telephone directory makes it easy for a person to search, SQL Server quickly searches a table with a clustered index. Because a clustered index determines the sequence ...
quaqua-7.3.4.nested.zip java美化包
第1章 全局在胸——用工具对SQL整体优化 1 1.1 都有哪些性能工具 1 1.1.1 不同调优场景分析 2 1.1.2 不同场景对应工具 2 1.2 整体性能工具的要点 4 1.2.1 五大性能报告的获取 5 1.2.2 五大报告关注的要点 10 ...
insert into tablea(cola,colb) select cola,colb from tableb; 2.1.3.2 创建相同的表结构并插入数据(备份表数据) create tab_new as select * from tab_old; 2.1.3.3 创建相同的表结构不插入数据 create tab_new ...
Intent locks improve performance because SQL Server examines intent locks only at the table level to determine whether a transaction can safely acquire a lock on that table. This removes the ...
Improvements to nested parentheses Case statements with comments now align correctly Now adds a space before aliases following function calls (forum) Added "Place BEGIN keyword on new line" option ...
第1章 全局在胸——用工具对SQL整体优化 1 1.1 都有哪些性能工具 1 1.1.1 不同调优场景分析 2 1.1.2 不同场景对应工具 2 1.2 整体性能工具的要点 4 1.2.1 五大性能报表的获取 5 1.2.2 五大报表关注的要点 10 ...
Improvements to nested parentheses Case statements with comments now align correctly Now adds a space before aliases following function calls Added "Place BEGIN keyword on new line" option. You can ...
专门介绍sql server 2008分区表和索引策略。
Type 异常报告 消息 Failed to convert ... nested exception is java.lang.IllegalStateException: Cannot convert value of type 'java.lang.String' to required type 'java.util.Date': no matching editors or co
java代码-Nested
java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK ……