Home » SQL & PL/SQL » SQL & PL/SQL » CAST VARRAY TO CURSOR (oracle 9i)
CAST VARRAY TO CURSOR [message #381172] Thu, 15 January 2009 09:19 Go to next message
nicky
Messages: 5
Registered: January 2009
Junior Member
Hi all.
I am fairly new to pl/sql and I need to do the following:
I need to create an object containing 3 attributes(eg. no,name,size). I then need to store these objects in a VARRAY. I then want to convert the VARRAY into a cursor and return it.
Firstly, is it possible to convert a VARRAY to a CURSOR?
Secondly, would Java be able to read a VARRAY if i returned just that.
Thirdly, Is there a different approach that I should be taking on my above problem?
Any help would be greatly appreciated.
Thanks in advance.
Re: CAST VARRAY TO CURSOR [message #381173 is a reply to message #381172] Thu, 15 January 2009 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.


Re: CAST VARRAY TO CURSOR [message #381191 is a reply to message #381172] Thu, 15 January 2009 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thirdly, Is there a different approach that I should be taking on my above problem?

This is the first point: you should explain what is your problem.

Regards
Michel
Re: CAST VARRAY TO CURSOR [message #381214 is a reply to message #381191] Thu, 15 January 2009 19:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Quote:
#1 a little googling and you can find how to create a type with your attributes.

#2 a little googling and you can find how to create a type varray using your other type.

#3 a little googling and you can find how to create a table that stores a varray or your type as a column.

#4 a little googling and you can find how to create pack elements into a varray and unpack them back into a rowset for selecting from.


These are well documented. Java interaction is another matter. I make an assumption here you are talking about java. But .NET or any other 3gl can use the same plsql as long as you map the right datatypes on the 3gl side. Since I have a java example I'll use it here.

I find integrating java with oracle sometimes easy and sometimes not so. With respect to your specific question, it will not be so easy to return a varray of complex records (more than one column). It is possible, and there are examples on the web, but you will need to construct a java class that maps to your oracle varray. This can be done by hand or you can use jdeveloper to create it for you (preferred method it seems but I am not a java guy at heart). Then you make the right oracle java calls and it works.

But I would suggest forget the varray passed back from a refcursor to java. At some point the data must be converted to its elements for use anyway so in most situations you are better off just unpacking your varray into a rowset inside your plsql procedure and then use a refcursor on the now unpacked varray as a set of rows.

With that in mind, 99% of all data passing between oracle and java can be done with basic datatypes. Here is an example of passing back several different datatypes to java. This should satisfy just about anything you need with respect to passing back data to java on a refcursor. This is done on a 10g database.

One last caution. As I said, I am not a java guy. I learn only what I need to survive in the java world as my passion is Oracle. How interesting it is that I a database man, have to show java guys how to do their thing. Oh well. Bottom line is, a real java expert may find fault with this material; may even have a much better way some of the parts. I hope that if one such person reads this thread, they provide us these better methods.

Good luck, Kevin

/*

This code shows how to retrieve various common datatypes off an Oraxle REFCURSOR.

We show how to get the following datatypes off a refcursor

1) number
2) string
3) date
4) clob
5) refcursor (yes, nested refcursors (this makes getting data way easy))
6) xml
7) simple array (we use string array, but number/date (maybe even clob) are also possible)

Some things to keep in mind:

1) java result sets are cast as OracleResultSet
2) there are several ways to deal with arrays, we show only one, converting them to a result set
3) complex (or STRUCTURED) datatypes (ie. complex collections) are not shown here, only simple types and single column arrays of simple types
4) numbers are interesting, as oracles default traslation isn BigDecimal in java, but this is very java expensive
5) dates require getTimstamp to keep the seconds, as getDate will trunc the seconds (notice our use of date formatting for printing)
6) when working with clobs you sometimes must convert it to a string, but java has a max string size (32000 I think)
7) our java is compiled and executed inside oracle, yours will be outside so connection syntax is a little different for you

You see below the following:

1) a table type, we create an oracle object that is essentially a string array definition (nested table of varchar2(4000))
2) we create a function that returns a refcursor
3) this refcursor returns two rows each of which has data of the types mentioned above
4) we can test our function for correctness easily from sqlplus
5) then comes our oracle java routine that calls the function and prints the data returned
6) a plsql wrapper is needed to make the oracle java package executable via normal plsql/sql syntax
7) finally we can turn output on for sqlplus java and run the routine to see the data

*/

create or replace type c_varchar2_4000 is table of varchar2(4000)
/

create or replace package pkg_latha_kevin_xml_test1 is

   function get_some_data return sys_refcursor; 

end; 
/ 
show errors 

create or replace package body pkg_latha_kevin_xml_test1 is 

   function get_some_data return sys_refcursor is 
      rc1_v sys_refcursor; 
   begin 
      open rc1_v for 
                      select 
                               rownum anumber
                             , 'xyz' astring
                             , sysdate adate
                             , to_clob('lots of words') aclob
                             , cursor(select * from dual) anested_rc
                             , dbms_xmlgen.getxmltype('select * from dual') axml
                             , c_varchar2_4000('abc','def','ghi') astringarray
                      from dual
                      connect by level <= 2
      ; 
      return (rc1_v); 
   end; 

end; 
/ 
show errors 

select pkg_latha_kevin_xml_test1 .get_some_data from dual 
/ 

create or replace and compile java source named "JavaGettingRefcursorData" as 

   import oracle.jdbc.driver.*; 
   import oracle.xdb.XMLType;
   import java.sql.*; 
   import java.text.DateFormat;
   import java.text.SimpleDateFormat;

   public class GettingRefcursorData { 

     public static void Refcursordata() throws SQLException { 

        String query = "begin ? := pkg_latha_kevin_xml_test1.get_some_data; end;"; 
	DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

        System.out.println(" query = " + query); 

        Connection conn = new OracleDriver().defaultConnection(); 

        CallableStatement cstmt = conn.prepareCall(query); 
        cstmt.registerOutParameter(1, OracleTypes.CURSOR); 
        cstmt.execute(); 
        OracleResultSet orset = (OracleResultSet)cstmt.getObject(1);

        for (int i = 0; orset.next(); i++) {
            System.out.println("number = " + orset.getInt(1));
//            System.out.println("number = " + orset.getBigDecimal(1));
//            System.out.println("number = " + orset.getFloat(1));
//            System.out.println("number = " + orset.getDouble(1));
            System.out.println("string = " + orset.getString(2));
            System.out.println("date = " + dateFormat.format(orset.getTimestamp(3)));
            System.out.println("clob = " + orset.getClob(4).getSubString(1,4000));
//            System.out.println("clob = " + orset.getString(4));
            OracleResultSet orset2 = (OracleResultSet)orset.getObject(5); 
            for (int j = 0; orset2.next(); j++) {
                System.out.println("nested refcursor column 1 = " + orset2.getString(1));
            }
            orset2.close();
            System.out.println(XMLType.createXML(orset.getOPAQUE(6)).getStringVal());
            orset2 = (OracleResultSet)orset.getARRAY(7).getResultSet();
            for (int j = 0; orset2.next(); j++) {
                System.out.println("array element (index,value) = (" + orset2.getInt(1) + "," + orset2.getString(2) + ")");
            }
            orset2.close();
            System.out.println("  --------");
        }
        ;
        orset.close();
     } 
   } 
/ 
show errors 

create or replace 
procedure show_GettingRefcursorData
as language java 
name 'GettingRefcursorData.Refcursordata()'; 
/ 

CALL DBMS_JAVA.SET_OUTPUT(1000000);
set serveroutput on 
exec show_GettingRefcursorData

Re: CAST VARRAY TO CURSOR [message #381252 is a reply to message #381214] Fri, 16 January 2009 00:39 Go to previous messageGo to next message
nicky
Messages: 5
Registered: January 2009
Junior Member
Thank you for your response.
below is my code with stuff that I need to do, commented out.

create or replace package body pkg_vit_abc_test is

           

function getMembersInfo(             
                p_vit_policy_no       in number,   
                pr_array              out member_array,
                pr_errMessage         out varchar2,
                pr_errCode            out number
            ) is
   
my_member member_object; --member_object defined already-has 3 attributes
       
type member_array is varray(10) of member_object;    
my_arr member_array; 

--temp variables
e_no number :=0;
firstName varchar2(20);
lastName varchar2(20);  

begin  
-- null;       
   e_no:=1002354;
   firstName:='John';
   lastName:='Doe';
   
   my_member.entity_number:=e_no;
   my_member.f_name:=firstName;
   my_member.l_name:=lastName;
   

my_arr(0):=my_member; -I will create more objects and add to varray


--I need to cast this varray to a cursor and return it.

 
end;

                                  
end pkg_vit_abc_test;

Re: CAST VARRAY TO CURSOR [message #381254 is a reply to message #381252] Fri, 16 January 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a pipelined function.

Regards
Michel
Re: CAST VARRAY TO CURSOR [message #381339 is a reply to message #381254] Fri, 16 January 2009 07:24 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You need to do some reading (as I indicated initially).

Do a google for the following:

oracle create type
oracle table cast
oracle cast multiset
oracle table functions
oracle varray

These will lead you to writings that give you the syntax of manipulating data in oracle. Here are some examples, but you need to do your own reading.

create or replace type myobject is object (a number,b number,c number)
/
create or replace type mycollection is varray(10) of myobject
/

declare
--
-- pack a varray of records
--
   varray_v mycollection := mycollection(myobject(1,2,3),myobject(2,3,4),myobject(3,4,5));
   av number;
   bv number;
   cv number;
begin null;
--
-- unpack a varray into is original parts
--
   for r1 in (
               select a,b,c
               from table(cast(varray_v as mycollection)) x
             ) loop
      av := r1.a;
      bv := r1.b;
      cv := r1.c;
   end loop;
end;
/

--
-- both at the same time in sql
--
select *
from table(cast(
                 mycollection(myobject(1,2,3),myobject(2,3,4),myobject(3,4,5))
                as mycollection
               )
          )
/


Good luck, Kevin
Previous Topic: Oracle Queues
Next Topic: sql
Goto Forum:
  


Current Time: Fri Dec 02 12:03:35 CST 2016

Total time taken to generate the page: 0.10599 seconds