We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
12 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
2
8 Comments
Connection conn = getJDBCConnection();
CallableStatement cstmt = conn.prepareCall( "{ ? = call my_func( ? ) }" );
cstmt.registerOutParameter
cstmt.setString( 2, "Hello world" );
cstmt.execute();
String returnval = cstmt.getString( 1 );
cstmt.close();
CallableStatement cstmt = conn.prepareCall( "{ ? = call my_func( ? ) }" );
cstmt.registerOutParameter
cstmt.setString( 2, "Hello world" );
cstmt.execute();
String returnval = cstmt.getString( 1 );
cstmt.close();
sorry, missed the bit about the PLSQL table...
Perhaps cast the function call, and just use SQL:
SELECT * FROM TABLE( my_func() );
Or you can use the Array of Object mechanism, using the SQLType interface (I've had problems with this!)
Failing that, you will probably have to use STRUCT array (I've had problems before using arrays of custom objects being returned from PL/SQL procedures, but that was because the objects being returned were of types owned by a different user in the database....)
Perhaps cast the function call, and just use SQL:
SELECT * FROM TABLE( my_func() );
Or you can use the Array of Object mechanism, using the SQLType interface (I've had problems with this!)
Failing that, you will probably have to use STRUCT array (I've had problems before using arrays of custom objects being returned from PL/SQL procedures, but that was because the objects being returned were of types owned by a different user in the database....)
Depending on what you want to do with the data, it may be worthwhile to create using the table command as previously defined. Alternatively, here's a step-by-step guide to actually return an Array:
The PL/SQL code required to set up the example:
CREATE OR REPLACE TYPE table_type IS TABLE OF VARCHAR2(8);
CREATE OR REPLACE FUNCTION get_test_array RETURN table_type AS
BEGIN
RETURN table_type('hello', 'from', 'the', 'array');
END;
And the java code:
cstmt = conn.prepareCall("{ ? = call get_test_array() }");
cstmt.registerOutParameter
cstmt.execute();
Array array = cstmt.getArray(1);
String[] values = (String[]) array.getArray();
for (int i = 0; i < values.length; i++) {
System.out.println("row " + i + " = '" + values[i] + "'");
}
The PL/SQL code required to set up the example:
CREATE OR REPLACE TYPE table_type IS TABLE OF VARCHAR2(8);
CREATE OR REPLACE FUNCTION get_test_array RETURN table_type AS
BEGIN
RETURN table_type('hello', 'from', 'the', 'array');
END;
And the java code:
cstmt = conn.prepareCall("{ ? = call get_test_array() }");
cstmt.registerOutParameter
cstmt.execute();
Array array = cstmt.getArray(1);
String[] values = (String[]) array.getArray();
for (int i = 0; i < values.length; i++) {
System.out.println("row " + i + " = '" + values[i] + "'");
}
grim_toaster's approach is what I had problems with before, with more complex objects (using the SQLType interface in Java), due to the actual type being owned by a different user than the connection's user (package accessed through synonym), which is why I needed to use STRUCT array, and inspect the types of attributes for each object being returned, and manually build the Java object to represent it.... a real pain!)
--> with more complex objects (using the SQLType interface in Java),
The SQLType interface???
Not had problems personally, provided the appropriate privileges have been granted to each user (directly), even when using arrays of Oracle objects (but yes, it is an absolute pain!!) What problems did you have, I'm intrigued...
The SQLType interface???
Not had problems personally, provided the appropriate privileges have been granted to each user (directly), even when using arrays of Oracle objects (but yes, it is an absolute pain!!) What problems did you have, I'm intrigued...
sorry, meant "SQLData" interface.
As I mentioned, I had problems when using a package that was owned by a different user (through a synonym) that was returning an array of objects which were also accessible through a synonym (their type was also owned by the other user). (The package was owned by a user that was created to have several DBA type roles, rather than granting those roles to every user (several instances of the application can be installed each with different normal schemas. Each instance would have a package created in this power user's schema (possibly different release versions of the product also, so different versions of package also - hence the synonym giving the package the same synonym name in each normal schema, but different real names (by version) in the "power" schema.
Now, the problem that was seen was when trying to extract the array of the Java implementation of these objects. Its been a while, but because it was quite a strange one, I actually put the Oracle error into a comment in the Java code!!!
java.sql.SQLException: ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SYS.DBMS_PICKLER", line 15
ORA-06512: at "SYS.DBMS_PICKLER", line 52
ORA-06512: at line 1
is the actual error, as far as I could figure out it was due to trying to automatically rebuild objects that are actually synonyms to their real types...
Tried several different ways of addressing the type, and the package, with no avail. Eventually I got around this by fetching a STRUCT[] with the following:
ARRAY array = ((OracleResultSet)resultSe
Object returnedArray = array.getArray();
STRUCT[] struts = null;
try {
struct = (STRUCT[])Arrays.asList( (Object[]) returnedArray ).toArray( new STRUCT[0] );
}
and then going through the STRUCT[], confirming the attributes (correct # and types), and manually building the Java object.... Way more painful than the SQLData interface I'll say!
Cheers,
C.
As I mentioned, I had problems when using a package that was owned by a different user (through a synonym) that was returning an array of objects which were also accessible through a synonym (their type was also owned by the other user). (The package was owned by a user that was created to have several DBA type roles, rather than granting those roles to every user (several instances of the application can be installed each with different normal schemas. Each instance would have a package created in this power user's schema (possibly different release versions of the product also, so different versions of package also - hence the synonym giving the package the same synonym name in each normal schema, but different real names (by version) in the "power" schema.
Now, the problem that was seen was when trying to extract the array of the Java implementation of these objects. Its been a while, but because it was quite a strange one, I actually put the Oracle error into a comment in the Java code!!!
java.sql.SQLException: ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SYS.DBMS_PICKLER", line 15
ORA-06512: at "SYS.DBMS_PICKLER", line 52
ORA-06512: at line 1
is the actual error, as far as I could figure out it was due to trying to automatically rebuild objects that are actually synonyms to their real types...
Tried several different ways of addressing the type, and the package, with no avail. Eventually I got around this by fetching a STRUCT[] with the following:
ARRAY array = ((OracleResultSet)resultSe
Object returnedArray = array.getArray();
STRUCT[] struts = null;
try {
struct = (STRUCT[])Arrays.asList( (Object[]) returnedArray ).toArray( new STRUCT[0] );
}
and then going through the STRUCT[], confirming the attributes (correct # and types), and manually building the Java object.... Way more painful than the SQLData interface I'll say!
Cheers,
C.
Featured Post
Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this post we will learn different types of Android Layout and some basics of an Android App.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it.
Define Error Message:
Line Numbers:
Type of Error:
Break Down…
Viewers will learn one way to get user input in Java.
Introduce the Scanner object:
Declare the variable that stores the user input:
An example prompting the user for input:
Methods you need to invoke in order to properly get user input:
Suggested Courses
Course of the Month12 days, 5 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.