Home » Developer & Programmer » JDeveloper, Java & XML » How to return multivalues from Java to PL/SQL (merged) (11g r2)
How to return multivalues from Java to PL/SQL (merged) [message #589171] Wed, 03 July 2013 02:42 Go to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
Hi, I`m trying to make a project for my Master Of Science and i`m looking for hints or best way to accomplish something like that:

I have a PL/SQL function where i call java class. In java i`m making some calculations and i need to return to body of PL/SQL function some values ( like 6 to 10, few numbers, string, 1 xml and 1 html).
I will show pl/sql function..
CREATE OR REPLACE FUNCTION GET_TAB(P_CURS SYS_REFCURSOR) RETURN TYP1_TAB PIPELINED IS
OUT_REC TYP1:=TYP1(NULL,NULL,NULL,NULL);
V_NAZWA VARCHAR2(5);
V_NUMER NUMBER;
 
 
BEGIN
 
LOOP
FETCH P_CURS INTO V_NUMER,V_NAZWA;
##########
And here i would like to return array or object with results and attribute them to out_rec type.
Something like that
V_results[]:= BIOJAVACLOB(v_NAZWA, v_NUMER);
out_rec.WYNIK1:= v_results[0];
out_rec.WYNIK2:=v_results[1];
out_rec.WYNIK3:=v_results[2];
...
##########
EXIT WHEN P_CURS%NOTFOUND;
END LOOP;
 
RETURN;
END;
/

Please help me, it`s very important
Best regards
Radomir
Re: How to return multivalues from Java to PL/SQL [message #589176 is a reply to message #589171] Wed, 03 July 2013 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand why do you call a PL/SQL procedure to call a java procedure (which I think will access the database with embedded SQL) and returns the result in an array to Java?
Why all is not done in the calling Java program?

regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #589180 is a reply to message #589176] Wed, 03 July 2013 03:45 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
It`s my project assumption and i`m not connecting to SQL from java ( this function example is simpler then actuality, in real i`m getting 2 clobs from database using ref cursor and i`m passing them to java where i`m using a algorithm with multi returns..) I`m not sure that array as result is a good way.
Re: How to return multivalues from Java to PL/SQL [message #589183 is a reply to message #589180] Wed, 03 July 2013 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you can't modify and what you can.
In the same way, I don't understand why, if your BIOJAVACLOB does not use any SQL, it needs to be called from a PL/SQL procedure that is called from a Java program passing a ref cursor.

In short, why don't you loop on the cursor in your Java program calling directly the Java procedure wrapped in BIOJAVACLOB? Why there is this call to PL/SQL?

Regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #589185 is a reply to message #589183] Wed, 03 July 2013 04:03 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
Dear Michel,
My project is about extension the PL/SQL language. In target i would like to use the call like this>
Select wynik1,wynik2,wynik3,wynik4 etc.. from table(GET_TAB(select clob1, clob2 from table_name where id > 100 ));

The second thing is that my java class using in Function BIOJAVACLOB input can`t be changed . So i can give only 2 inputs ( clob1, clob2). Output can be modified.
Regards
Radomir
Re: How to return multivalues from Java to PL/SQL [message #589186 is a reply to message #589183] Wed, 03 July 2013 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or alternatively, if the java function does some formatting on the clob, why don't you do that in sql or pl/sql rather than java?
The process seems unnecessarily complicated.
Re: How to return multivalues from Java to PL/SQL [message #589188 is a reply to message #589186] Wed, 03 July 2013 04:07 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
Dear cookiemonster,
That clob formatting is very complex - these clobs are protein structures, in java i got algorithm to compare them ( look for biojava3, fatcat algorithm or CE algorithm)
Re: How to return multivalues from Java to PL/SQL [message #589214 is a reply to message #589188] Wed, 03 July 2013 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not sure I completely understand what you want.
Anyway, here's an example of what you can do:
SQL> create or replace type my_record is object (
  2    val1 varchar2(100),
  3    val2 varchar2(100),
  4    val3 varchar2(100),
  5    val4 varchar2(100))
  6  /

Type created.

SQL> create or replace type my_table is table of my_record
  2  /

Type created.

SQL> create or replace function BIOJAVACLOB (p1 clob, p2 clob)
  2  return sys.odcivarchar2list
  3  is
  4    l_val sys.odcivarchar2list := sys.odcivarchar2list();
  5  begin
  6    for i in 1..2 loop
  7      l_val.extend (2);
  8      l_val(2*i-1) := substr(p1, 10*(i-1)+1, 10);
  9      l_val(2*i)   := substr(p2, 10*(i-1)+1, 10);
 10    end loop;
 11    return l_val;
 12  end;
 13  /

Function created.

SQL> create or replace function get_tab (p_curs sys_refcursor)
  2  return my_table
  3  pipelined
  4  is
  5    l_v1  clob;
  6    l_v2  clob;
  7    l_val sys.odcivarchar2list;
  8  begin
  9    loop
 10      fetch p_curs into l_v1, l_v2;
 11      exit when p_curs%notfound;
 12      l_val := BIOJAVACLOB (l_v1, l_v2);
 13      pipe row (my_record(l_val(1),l_val(2),l_val(3),l_val(4)));
 14    end loop;
 15  end;
 16  /

Function created.

SQL> drop table t purge;

Table dropped.

SQL> col v1 format a20
SQL> col v2 format a20
SQL> create table t (id int, v1 clob, v2 clob);

Table created.

SQL> insert into t
  2  select level,
  3         rpad(chr(ascii('A')+2*(level-1)),10,chr(ascii('A')+2*(level-1)))||
  4           rpad(chr(ascii('A')+2*(level-1)+1),10,chr(ascii('A')+2*(level-1)+1)),
  5         rpad(chr(ascii('A')+2*level),10,chr(ascii('A')+2*level))||
  6           rpad(chr(ascii('A')+2*level+1),10,chr(ascii('A')+2*level+1))
  7  from dual
  8  connect by level <= 5
  9  /

5 rows created.

SQL> select * from t order by id;
        ID V1                   V2
---------- -------------------- --------------------
         1 AAAAAAAAAABBBBBBBBBB CCCCCCCCCCDDDDDDDDDD
         2 CCCCCCCCCCDDDDDDDDDD EEEEEEEEEEFFFFFFFFFF
         3 EEEEEEEEEEFFFFFFFFFF GGGGGGGGGGHHHHHHHHHH
         4 GGGGGGGGGGHHHHHHHHHH IIIIIIIIIIJJJJJJJJJJ
         5 IIIIIIIIIIJJJJJJJJJJ KKKKKKKKKKLLLLLLLLLL

5 rows selected.

SQL> col val1 format a10
SQL> col val2 format a10
SQL> col val3 format a10
SQL> col val4 format a10
SQL> select * from table(get_tab(cursor(select v1, V2 from t order by id)));
VAL1       VAL2       VAL3       VAL4
---------- ---------- ---------- ----------
AAAAAAAAAA CCCCCCCCCC BBBBBBBBBB DDDDDDDDDD
CCCCCCCCCC EEEEEEEEEE DDDDDDDDDD FFFFFFFFFF
EEEEEEEEEE GGGGGGGGGG FFFFFFFFFF HHHHHHHHHH
GGGGGGGGGG IIIIIIIIII HHHHHHHHHH JJJJJJJJJJ
IIIIIIIIII KKKKKKKKKK JJJJJJJJJJ LLLLLLLLLL

5 rows selected.

Regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #589220 is a reply to message #589214] Wed, 03 July 2013 07:13 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
Dear Michel

It`s almost that what i wanted. But there is one significant problem with:
create or replace function BIOJAVACLOB (p1 clob, p2 clob)
  2  return sys.odcivarchar2list
  3  is
  4    l_val sys.odcivarchar2list := sys.odcivarchar2list();
  5  begin
  6    for i in 1..2 loop
  7      l_val.extend (2);
  8      l_val(2*i-1) := substr(p1, 10*(i-1)+1, 10);
  9      l_val(2*i)   := substr(p2, 10*(i-1)+1, 10);
 10    end loop;
 11    return l_val;
 12  end;
 13  /

i would like to function biojavaclob looks like that (with calling java class):
create or replace function BIOJAVACLOB (p1 clob, p2 clob)
  2  return sys.odcivarchar2list
  3  is
  4    language java name 'demo.start(java.sql.clob,java.sql.clob) return Array/Object';
  5  end;
  6  /

Sorry for my bad explanation and thanks for your time..
Regards
Radomir

[Updated on: Wed, 03 July 2013 07:18]

Report message to a moderator

Re: How to return multivalues from Java to PL/SQL [message #589222 is a reply to message #589220] Wed, 03 July 2013 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I simulated your function as I have no Java function for this.
Do you mean you currently have not the function this function?
What is the Java type returned by demo.start?

Regards
Michel

Re: How to return multivalues from Java to PL/SQL [message #589223 is a reply to message #589222] Wed, 03 July 2013 07:50 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
I have function but now i`m testing with simpler version, from demo.Start i`m returning Array

	public static String[] start(...
		String[] myString = new String[]{a1,b1,c1};
						
		return myString;
		...

When i`m trying to call function get_tab i`m getting error that method start isn`t exist in class demo.It`s error with mapping array with odcivarchar2list? I checked 5 times, class is in DB, everything in function body is fine.
Regards
Radomir
Re: How to return multivalues from Java to PL/SQL [message #589228 is a reply to message #589223] Wed, 03 July 2013 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should say it!
One error is that the name of the type is java.sql.CLOB not java.sql.clob (remember Java is case sensitive).
This may not be the only problem, it is sure one.

Regards
Michel
Problem with pl/sql and java return array [message #589279 is a reply to message #589171] Thu, 04 July 2013 01:15 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
Hello, i`m having problem with returning array from java to pl/sql. Can someone tell me what i`m doing wrong??
I will show full code of simple example.
First i`m creating java class and with loadjava i`m loading that class into oracle db.

package demo;

public class Proba {

	public static String[] Start(String a1, String b1, String c1)
	{
		
		
		String[] myString = new String[]{a1,b1,c1};
		
		
		
		return myString;
		
	}
}

The next thing is creating type - array 3-elements and type for showing results.
Create or replace TYPE array_type IS VARRAY(3) OF VARCHAR2(100);

Create or replace type my_record is object (
val1 varchar2(100),
val2 varchar2(100),
val3 varchar2(100));


And function, first one.
create or replace function probna(p1 in VARCHAR2, p2 in VARCHAR2, p3 in VARCHAR2)
return array_type
is language java name 'demo.Proba.Start(java.lang.String,java.lang.String,java.lang.String) return java.lang.reflect.Array';

and second one
create or replace function probna2(item1 varchar2,item2 varchar2,item3 varchar2)
return my_table
pipelined
is
l_val array_type;
begin
l_val := probna(item1,item2,item3);
pipe row (my_record(l_val(1),l_val(2),l_val(3)));
end;

Can someone test it and if can, help me?
Regards
Radomir
Re: How to return multivalues from Java to PL/SQL [message #589282 is a reply to message #589228] Thu, 04 July 2013 01:20 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
Dear Michel,
Thanks for help and your time, you really help me a lot.I checked everything and i didn`t find soulution. I have change type from odcivarchar2list for my type and still not working.. If u want to see full code i just created new topic. http://www.orafaq.com/forum/t/188470/0/unread/179247/
Regards
Radomir
Re: Problem with pl/sql and java return array [message #589284 is a reply to message #589279] Thu, 04 July 2013 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT create a new topic for the same question BUT answer in your previous one.
It is a rude attitude to do what you did towards the previous answerer.

Regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #589285 is a reply to message #589282] Thu, 04 July 2013 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
radomir.adamek wrote on Thu, 04 July 2013 08:20
Dear Michel,
Thanks for help and your time, you really help me a lot.I checked everything and i didn`t find soulution. I have change type from odcivarchar2list for my type and still not working.. If u want to see full code i just created new topic. http://www.orafaq.com/forum/t/188470/0/unread/179247/
Regards
Radomir


I merged the 2 topics.
There is no need to create a new one.

Regards
Michel

Re: How to return multivalues from Java to PL/SQL [message #589287 is a reply to message #589285] Thu, 04 July 2013 01:44 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
I`m sorry Michel, my bad i was thinking that topic will be at the bottom of forum so nobody except you will see my reply (you help me a lot and i didn`t wanted to bother you).
I forgot code for creating table from type
create or replace type my_table is table of my_record;
Re: How to return multivalues from Java to PL/SQL [message #589289 is a reply to message #589285] Thu, 04 July 2013 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
from my previous test case:
SQL> create or replace type STRING_ARRAY as table of VARCHAR2(255)
  2  /

Type created.

SQL> create or replace procedure BIOJAVACLOB (p1 clob, p2 clob, p3 out STRING_ARRAY)
  2  is
  3    language java
  4    name 'demo.start (oracle.sql.CLOB, oracle.sql.CLOB, oracle.sql.ARRAY[])';
  5  /

Procedure created.

SQL> create or replace and compile java source named "demo" as
  2    import java.io.*;
  3    import java.sql.*;
  4    import oracle.sql.*;
  5    import oracle.jdbc.driver.*;
  6
  7    public class demo extends Object {
  8      public static void start (oracle.sql.CLOB    p1,
  9                                oracle.sql.CLOB    p2,
 10                                oracle.sql.ARRAY[] p3)
 11      throws SQLException, IOException {
 12        Reader   is1      = p1.getCharacterStream();
 13        Reader   is2      = p2.getCharacterStream();
 14        String[] values   = new String[4];
 15        char     buffer[] = new char[10];
 16        int      length;
 17
 18        for ( int i = 0 ; i < 2 ; i++ ) {
 19          length = is1.read(buffer, 10*i, 10);
 20           values[2*i] = new String(buffer);
 21          length = is1.read(buffer, 10*i, 10);
 22           values[2*i+1] = new String(buffer);
 23        }
 24
 25        is1.close();
 26        is2.close();
 27
 28        Connection conn            = new OracleDriver().defaultConnection();
 29        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor ("STRING_ARRAY", conn);
 30        p3[0] = new ARRAY (descriptor, conn, values);
 31      }
 32    }
 33  /

Java created.

SQL> create or replace function get_tab (p_curs sys_refcursor)
  2  return my_table
  3  pipelined
  4  is
  5    l_v1  clob;
  6    l_v2  clob;
  7    l_val string_array := string_array();
  8  begin
  9    loop
 10      fetch p_curs into l_v1, l_v2;
 11      exit when p_curs%notfound;
 12      BIOJAVACLOB (l_v1, l_v2, l_val);
 13      pipe row (my_record(l_val(1),l_val(2),l_val(3),l_val(4)));
 14    end loop;
 15  end;
 16  /

Function created.

SQL> select * from table(get_tab(cursor(select v1, V2 from t order by id)));
VAL1       VAL2       VAL3       VAL4
---------- ---------- ---------- ----------
AAAAAAAAAA BBBBBBBBBB BBBBBBBBBB BBBBBBBBBB
CCCCCCCCCC DDDDDDDDDD DDDDDDDDDD DDDDDDDDDD
EEEEEEEEEE FFFFFFFFFF FFFFFFFFFF FFFFFFFFFF
GGGGGGGGGG HHHHHHHHHH HHHHHHHHHH HHHHHHHHHH
IIIIIIIIII JJJJJJJJJJ JJJJJJJJJJ JJJJJJJJJJ

5 rows selected.

Regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #589290 is a reply to message #589287] Thu, 04 July 2013 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i was thinking that topic will be at the bottom of forum so nobody except you will see my reply


When you reply the topic jumps at the top of the forum.
(For information, I do not follow the topic by mail, just clicking on the forum page.)

Regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #589300 is a reply to message #589290] Thu, 04 July 2013 03:10 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
It`s working very well! Thanks a lot Michel! I would like you repay somehow.
Best Regards
Radomir
Re: How to return multivalues from Java to PL/SQL [message #590929 is a reply to message #589300] Wed, 24 July 2013 04:17 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
Hi, i know it`s old thread but i have additional question. How to make "mixed" type STRING_ARRAY? Now it`s type STRING_ARRAY as table of VARCHAR2`s, my quesiton is how to make table of numbers, strings and return as array from java to pl/sql.
Best Regards
Radomir
Re: How to return multivalues from Java to PL/SQL [message #590933 is a reply to message #590929] Wed, 24 July 2013 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just change the definition of the types:
create or replace type STRING_ARRAY as table of VARCHAR2(255)

String[] values   = new String[4];

to match the type of arrays you want.

Regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #590954 is a reply to message #590933] Wed, 24 July 2013 06:19 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
I want to have 1 array.. Maybe i will show what i created.

create or replace type my_record is object (
one varchar2(4),
two number,
three number,
four float)
/
create or replace type STRING_ARRAY as table of my_record;
create or replace type my_table is table of my_record;



create or replace procedure BIOJAVACLOB (p1 clob, p2 clob, p3 out STRING_ARRAY)
 is
    language java
    name 'demo.start (oracle.sql.CLOB, oracle.sql.CLOB, oracle.sql.ARRAY[])';
 /

create or replace function get_tab (p_curs sys_refcursor)
    return my_table
    pipelined
    is
      l_v1  clob;
      l_v2  clob;
      l_val string_array := string_array();
    begin
      loop
      fetch p_curs into l_v1, l_v2;
       exit when p_curs%notfound;
       BIOJAVACLOB (l_v1, l_v2, l_val);
       pipe row (my_record(l_val(1),l_val(2),l_val(3),l_val(4)));
     end loop;
   end;
   /


create or replace and compile java source named "demo" as
      import java.io.*;
      import java.sql.*;
      import oracle.sql.*;
      import oracle.jdbc.driver.*;
  
      public class demo extends Object {
        public static void start (oracle.sql.CLOB    p1,
                                  oracle.sql.CLOB    p2,
                                 oracle.sql.ARRAY[] p3)
       throws SQLException, IOException {
         Reader   is1      = p1.getCharacterStream();
         Reader   is2      = p2.getCharacterStream();

         Object[] values   = new Object[3];

         values[0]=new String("aaa");
	 values[1]=new Integer(11);
	 values[2]=new Integer(22);
	 values[3]=new Double(111.1);
        
 
         is1.close();
         is2.close();
 
         Connection conn            = new OracleDriver().defaultConnection();
         ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor ("STRING_ARRAY", conn);
         p3[0] = new ARRAY (descriptor, conn, values);
       }
     }
   /


And i`m getting from function get_tab error PLS-00306: wrong number or types of arguments x4..
Could you tell me what i`m making wrong?
Regards
Radomir
Re: How to return multivalues from Java to PL/SQL [message #591176 is a reply to message #590954] Thu, 25 July 2013 06:07 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
i have resolved my problem, but now i have other one. Why i can`t map java double to pl/sql double precision or float??
create or replace type my_record is object (
one varchar2(4),
two number,
three number,
four double precision)
/

and in java
...
         values[0]=new String("aaa");
	 values[1]=new Integer(11);
	 values[2]=new Integer(22);
	 values[3]=new Double(111.1);

gives error ORA-06502: PL/SQL: numeric or value error character to number conversion error. When i`m changing double to integer everything works.
i don`t understand.
Re: How to return multivalues from Java to PL/SQL [message #591192 is a reply to message #591176] Thu, 25 July 2013 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have resolved my problem


Please post a complete test that works (so with not double precision) and that does not work.

Regards
Michel
Re: How to return multivalues from Java to PL/SQL [message #591195 is a reply to message #591192] Thu, 25 July 2013 07:48 Go to previous messageGo to next message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
I have changed previous function get_tab to not operate on clobs (they are still as parameters)

Working solution
drop type STRING_ARRAY4;
drop type my_table4;
drop type my_record4;

create or replace type my_record4 is object (
      val1 varchar2(100),
      val2 varchar2(100),
      val3 number,
      val4 number(5));
    /
create or replace type my_table4 is table of my_record4;
    /

create or replace type STRING_ARRAY4 as table of VARCHAR2(255);
/
create or replace procedure BIOJAVACLOB4 (p1 clob, p2 clob, p3 out STRING_ARRAY4)
    is
      language java
      name 'demo4.start (oracle.sql.CLOB, oracle.sql.CLOB, oracle.sql.ARRAY[])';
    /
    create or replace and compile java source named "demo" as
      import java.io.*;
      import java.sql.*;
      import oracle.sql.*;
      import oracle.jdbc.driver.*;
  
      public class demo4 extends Object {
        public static void start (oracle.sql.CLOB    p1,
                                  oracle.sql.CLOB    p2,
                                 oracle.sql.ARRAY[] p3)
       throws SQLException, IOException {
         Reader   is1      = p1.getCharacterStream();
         Reader   is2      = p2.getCharacterStream();
         Object[] values   = new Object[4];

       values[0]=new String("aaaaaa");
       values[1]=new String("aaaaaa");
       values[2]=new Integer(100);
       values[3]=new Integer(101);
        
         is1.close();
         is2.close();
 
         Connection conn            = new OracleDriver().defaultConnection();
         ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor ("STRING_ARRAY4", conn);
         p3[0] = new ARRAY (descriptor, conn, values);
       }
     }
   /
   
    create or replace function get_tab4 (p_curs sys_refcursor)
    return my_table4
    pipelined
    is
      l_v1  clob;
      l_v2  clob;
      l_val string_array4 := string_array4();
      l_v3 double precision;
    begin
      loop
       fetch p_curs into l_v1, l_v2;
       exit when p_curs%notfound;
       BIOJAVACLOB4 (l_v1, l_v2, l_val);      
       pipe row (my_record4(l_val(1),l_val(2),l_val(3),l_val(4)));
    end loop;
   end;
   /

Doesn`t working (double)
drop type STRING_ARRAY4;
drop type my_table4;
drop type my_record4;

create or replace type my_record4 is object (
      val1 varchar2(100),
      val2 varchar2(100),
      val3 Double precision,
      val4 number(5));
    /
create or replace type my_table4 is table of my_record4;
    /

create or replace type STRING_ARRAY4 as table of VARCHAR2(255);
/
create or replace procedure BIOJAVACLOB4 (p1 clob, p2 clob, p3 out STRING_ARRAY4)
    is
      language java
      name 'demo4.start (oracle.sql.CLOB, oracle.sql.CLOB, oracle.sql.ARRAY[])';
    /
    create or replace and compile java source named "demo" as
      import java.io.*;
      import java.sql.*;
      import oracle.sql.*;
      import oracle.jdbc.driver.*;
  
      public class demo4 extends Object {
        public static void start (oracle.sql.CLOB    p1,
                                  oracle.sql.CLOB    p2,
                                 oracle.sql.ARRAY[] p3)
       throws SQLException, IOException {
         Reader   is1      = p1.getCharacterStream();
         Reader   is2      = p2.getCharacterStream();
         Object[] values   = new Object[4];

       values[0]=new String("aaaaaa");
       values[1]=new String("aaaaaa");
       values[2]=new Double(100.1);
       values[3]=new Integer(101);
        
         is1.close();
         is2.close();
 
         Connection conn            = new OracleDriver().defaultConnection();
         ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor ("STRING_ARRAY4", conn);
         p3[0] = new ARRAY (descriptor, conn, values);
       }
     }
   /
   
    create or replace function get_tab4 (p_curs sys_refcursor)
    return my_table4
    pipelined
    is
      l_v1  clob;
      l_v2  clob;
      l_val string_array4 := string_array4();
      l_v3 double precision;
    begin
      loop
       fetch p_curs into l_v1, l_v2;
       exit when p_curs%notfound;
       BIOJAVACLOB4 (l_v1, l_v2, l_val);      
       pipe row (my_record4(l_val(1),l_val(2),l_val(3),l_val(4)));
    end loop;
   end;
   /

And table with select
create table t5 (v1 clob,v2 clob);
insert into t5(v1,v2) values('aaaaaaaa','bbbbbbb');
select * from table(get_tab4(cursor(select v1, v1 from t5)));

I think i figured out what`s going on. From java i`m returning double with "." and pl/sql is writing doubles with "," so there is problem with converting. I don`t know how to resolve this.
Best regards
Radomir

[Updated on: Thu, 25 July 2013 07:52]

Report message to a moderator

Re: How to return multivalues from Java to PL/SQL [message #591270 is a reply to message #591195] Fri, 26 July 2013 02:12 Go to previous message
radomir.adamek
Messages: 15
Registered: July 2013
Junior Member
The problem is with STRING_ARRAY4 that is not different types only varchar2. How can i bypass that to return double,number and string in one array? Is that possible?
Previous Topic: The Problem of Java Time Synchronization
Next Topic: JDeveloper 10.1.2 java problems
Goto Forum:
  


Current Time: Thu Mar 28 12:49:09 CDT 2024