Home » SQL & PL/SQL » SQL & PL/SQL » Passing array from oracle procedure to javascript function (Oracle 9i)
Passing array from oracle procedure to javascript function [message #399494] Wed, 22 April 2009 08:24 Go to next message
souvik_roy_bec@yahoo.com
Messages: 6
Registered: April 2009
Junior Member
Hi,

I am preparing html page using oracle htp utility package. I have written one function in javascript:
function Redirect(i_name)
{
alert(i_name);
var arr = new Array[];
arr[0] = GUI_test.subcategory_list?i_category=i_name
}
I have procedure subcategory_list which is taking i_name as input parameter and returns varray as output.

could you please let me know how the array is returned from oracle to javascript array variable.

Thanks in advance,
Souvik
Re: Passing array from oracle procedure to javascript function [message #402397 is a reply to message #399494] Sat, 09 May 2009 17:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have done some of this before. Maybe it helps. In particular you may find value in #7. You are on your own though, I offer no support. 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 (this is not needed for external java)
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

CALL DBMS_JAVA.SET_OUTPUT(1000000);
set serveroutput on 
exec show_GettingRefcursorData
Re: Passing array from oracle procedure to javascript function [message #402413 is a reply to message #402397] Sun, 10 May 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kevin, thanks for this wrap-up.

Regards
Michel
Re: Passing array from oracle procedure to javascript function [message #402463 is a reply to message #402413] Sun, 10 May 2009 20:07 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
no sweat. I have found that this can save java developers weeks of trial and error.

Kevin
Previous Topic: Run query based on parameter
Next Topic: concert number to string like (1,one)
Goto Forum:
  


Current Time: Sun Dec 04 06:22:19 CST 2016

Total time taken to generate the page: 0.09679 seconds