Solved

Access stored procedures via JDBC

Posted on 1998-09-28
10
330 Views
Last Modified: 2012-08-13
I've written PL/SQL stored procedures and want to
execute them from within a Java programm.

In the stored procedures I use a datatype
"type s_array is table of varchar2(200)
index by binary_integer;"
as a parameter.

On the Java side I use an array of strings. How do I map
the (SQL) table of varchar into a (Java) array of strings?
There are methods to map simple datatypes and there are
the methods getObject and setObject. Do I have to use them?
But how?

A simple source code example would be nice!
Thanks!
0
Comment
Question by:steffi
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 8

Expert Comment

by:diakov
ID: 1225107
Which Oracle Java driver are you using?
There shoud be indicated what mappings are possible, because the standart JDBC is poor on more complex types.
I think array of strings is not possible with the standart JDBC.
0
 

Author Comment

by:steffi
ID: 1225108
The version of the JDBC driver I use is 7.3.3.1.3 beta-release.

The HTML online docs state that it supports the following standard Oracle sql-types:
CHAR, VARCHAR2,  LONG, RAW, LONG RAW, NUMBER, DATE
and in addition to that
ROWID, REFCURSOR, BLOB, CLOB, BFILE.

But the 's_array' which I use in the example above is not a standard Oracle datatype.
How can I map such a self-defined datatype to access its values in my Java program?
0
 

Expert Comment

by:mka
ID: 1225109
Hello steffi,

How about this?

plSqlText = "DECLARE  " +
                   "BaseItemTable NEW_WIZARDOLDCPY.string_tabtype;  " +
                "BEGIN  ";
    for (int i=0; i < baseItemArr[0].size(); i++) {
         plSqlText += "BaseItemTable(" + i + ")  := " + "'" + baseItemArr[0].elementAt(i) + "'" + ";  " ;

I took the help of one of my expert colleague to get this.

Thanks.
Kaleem.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Expert Comment

by:mka
ID: 1225110
Oops! forgot to check "Check here if you'd like an email notification whenever this question is updated" to true. Doing it now, so I be updated ...
0
 

Author Comment

by:steffi
ID: 1225111
hi mka,

what is the meaning of "NEW_WIZARDOLDCPY.string_tabtype" ?
Is it the same as my "s_array"?

Hmm, plSqlText contains pl/sql code that creates a sql-table and fills it with the
values contained in the Java array, right?
I think something like 'END' is needed to close the block ...

How do I execute this sql-code?

0
 

Expert Comment

by:mka
ID: 1225112
Hello steffi,

Yeah, the array contents go into the table rows. It is same as s_array.

You are right, I did not go upto the END of the block ... because it is a small part of the entire block I cut it from.

Use CallableStatement and pass the plSqlText as the string for execution.

Thanks.
Kaleem.
0
 
LVL 2

Expert Comment

by:aziz061097
ID: 1225113
0
 
LVL 3

Accepted Solution

by:
mjenkins earned 150 total points
ID: 1225114
Create a REF CURSOR in PL/SQL and return it from the procedure.
In your Java code:
            statement.registerOutParameter(1, OracleTypes.CURSOR );
            statement.executeQuery();
   
            // The result set is an Oracle cursor (actually REFCURSOR)
            ResultSet rset = (ResultSet)statement.getObject(1);

hen just walk through the result set as normal. This works very well for me in my apps.
0
 

Author Comment

by:steffi
ID: 1225115
mjenkins,
sorry for being so late with my answer.
Does this mean I have to touch every stored procedure and make it return a
REF CURSOR?

0
 
LVL 3

Expert Comment

by:mjenkins
ID: 1225116
Read the examples in the Oracle JDBC documentation. It shows you the best way to deal with complex data types.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Windows 10 IE Certificate Issue 10 45
web application structure 18 76
iterator/ListIterator approach 17 26
Convert from a json string array to a Java object 3 31
For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question