Solved

Access stored procedures via JDBC

Posted on 1998-09-28
10
335 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hibernate example using maven 12 56
Selenium docs api java index 3 48
Java 8 to Java 6 8 20
Java Eclipse Loop 3 20
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
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…
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 video teaches viewers about errors in exception handling.

856 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