Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access stored procedures via JDBC

Posted on 1998-09-28
10
Medium Priority
?
350 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
[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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Industry Leaders: We Want Your Opinion!

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!

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

722 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