473,372 Members | 1,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Returning Oracle PLSQL Array to Java code ( incorrect Array type code)

Hi,

I have been trying to run the below example to get a Oracle Array as an output from a Java code. This is an example I have found on the web. But, the expected result is that the code should return me Array element type code 1, but it is returning me type code 12 and the array in a junk or unreadable format . Our environment is JDK 1.4, ojdbc14.jar and Oracle 8i

Not able to find out what they problem is and am not able to change the type code so that it returns the data in correct format. I suspect it is either the problem with either the JDBC library (ojdbc14.jar ) or the oracle 8i installation/options. We have also tested the above code with a 8i, jdk1.2, classes12.zip environment, but it still returns code 12.

Can you please guide as to what are the areas to be looked and how do we resolve for returning type code 1.

---- Oracle PLSQL function to return an array ----

** Type declaration **

CREATE OR REPLACE TYPE SimpleArray AS TABLE OF VARCHAR2(30)
/


** Oracle PLSQL package and function **

CREATE OR REPLACE PACKAGE BODY Test_Pkg_Fp IS

FUNCTION getSimpleArray RETURN SIMPLEARRAY
AS
l_data SIMPLEARRAY := SIMPLEARRAY();
BEGIN
FOR i IN 1 .. 10 LOOP
l_data.EXTEND;
l_data(l_data.COUNT) := 'entry ' || i;
END LOOP;
RETURN l_data;
END;

END Test_Pkg_Fp;
/


----- Java Code calling the PLSQL function to return an array -----

import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class Array
{

public static void main(String args[]) throws Exception
{

DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@ccd ev3:1521:CAM2D3","hst","hst");

OracleCallableStatement stmt =
(OracleCallableStatement)conn.prepareCall( "begin ? := TEST_PKG_FP.getSimpleArray; end;" );

// The name we use below, SIMPLEARRAY, matches the name of our
// type we defined in SQL above
stmt.registerOutParameter( 1, OracleTypes.ARRAY, "SIMPLEARRAY" );
stmt.executeUpdate();

// Now we can get the array object back and print out some meta data about it
ARRAY simpleArray = stmt.getARRAY(1);

System.out.println ("Array is of type name " + simpleArray.getBaseTypeName());

System.out.println ("Array is of type " + simpleArray.getSQLTypeName());

System.out.println ("Array element is of type code " + simpleArray.getBaseType());

System.out.println ("Array is of length " + simpleArray.length());

// Lastly, we'll print out the entire contents of our array
String[] values = (String[])simpleArray.getArray();

for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" + values[i] +
"'" );

stmt.close();
conn.close();
}
}

--- Actual Result ----

Array is of type name VARCHAR
Array is of type HST.SIMPLEARRAY
Array element is of type code 12
Array is of length 10
row 0 = '???'
row 1 = '???'
row 2 = '???'
row 3 = '???'
row 4 = '???'
row 5 = '???'
row 6 = '???'
row 7 = '???'
row 8 = '???'
row 9 = '???'

------- Expected Result ---

Array is of type name CHAR
Array is of type HST.SIMPLEARRAY
Array element is of type code 1
Array is of length 10
row 0 = 'entry 0'
row 1 = 'entry 1'
row 2 = 'entry 2'
row 3 = 'entry 3'
row 4 = 'entry 4'
row 5 = 'entry 5'
row 6 = 'entry 6'
row 7 = 'entry 7'
row 8 = 'entry 8'
row 9 = 'entry 9'

Thanks and Regards,

Anup
Feb 11 '08 #1
0 4064

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: John MacIntyre | last post by:
Hi, Can anybody give me a hint as to how to convert a javascript array into a vbscript array? BTW-it only needs to work in IE5 & 6 Thanks in advance, John MacIntyre VC++ / VB / ASP /...
8
by: Peter B. Steiger | last post by:
The latest project in my ongoing quest to evolve my brain from Pascal to C is a simple word game that involves stringing together random lists of words. In the Pascal version the whole array was...
10
by: Adam Warner | last post by:
Hi all, With this structure that records the length of an array of pointers as its first member: struct array { ptrdiff_t length; void *ptr; };
2
by: andrea_ | last post by:
I've got a problem: I must use an int java array in a C code. Using JNI i wrote this code: jsize len = (*env)->GetArrayLength(env, payload); //payload is the name of java array jint *body =...
2
by: Dick Swager | last post by:
The following code is from a solution with a C++ project and a C# project. The C++ project creates a managed array and the C# project tries to use it. But I am having a 'System.ValueType' to...
12
by: wenmang | last post by:
Hi, I am using following Oracle Proc-C compiler: Pro*C/C++: Release 8.1.7.0.0 - Production on Thu Jun 15 15:57:32 2006 (c) Copyright 2000 Oracle Corporation. All rights reserved. I like to...
5
by: benyboy | last post by:
hi, after hours of staring at my computer screen, and browsing through various internet forums i am totally stuck! I have created the following array which reads from an input file the following...
1
by: jenninnifer | last post by:
This doesn't seem to be working... I am trying to send an array to my jscript, but am having problems. I query my db, populate the array (which is working), convert using wddx and try to get the...
2
by: Barry | last post by:
The problem brought by one earlier post from comp.lang.c++ http://groups.google.com/group/comp.lang.c++/browse_thread/thread/bf636c48b84957b/ I take part of the question and reproduce the code to...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.