Solved

Access stored procedures via JDBC

Posted on 1998-09-28
10
311 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now