Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access stored procedures via JDBC

Posted on 1998-09-28
10
Medium Priority
?
352 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 300 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

926 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