?
Solved

Code to invoke Oracle Package in Java

Posted on 2012-08-26
4
Medium Priority
?
414 Views
Last Modified: 2012-09-20
I want to following Oralce procedure to be called in Java.

TYPE param_obj_ty AS OBJECT
  ( P_P1     VARCHAR2(100 BYTE),
    P_P2     VARCHAR2(100 BYTE),
    P_P3     VARCHAR2(100 BYTE),
    P_P4     VARCHAR2(100 BYTE),
    P_P5     DATE
  );

create or replace
TYPE param_tab_ty AS TABLE OF param_obj_ty;


Create or replace package myPkg
IS
      PROCEDURE abc
      ( p_param IN param_tab_ty);
END myPkg;

Create or replace package Body myPkg
IS
      PROCEDURE abc
      ( p_param IN param_tab_ty)
      IS  
      BEGIN
            NULL;
      END abc;
END myProc;
0
Comment
Question by:gram77
  • 2
4 Comments
 
LVL 12

Expert Comment

by:basav_com
ID: 38335604
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 38335687
Hi!

Here is how you call a stored procedure that returns some data.

http://www.java2s.com/Code/Java/Database-SQL-JDBC/StoredprocedurewithInputOutputparmsandaResultSet.htm

This example needs to create a database connection though. ;)

Regards,
     Tomas Helgi
0
 

Author Comment

by:gram77
ID: 38336511
Please notice that my Oracle package is recieving a collection as a parameter.

The collection has the following structure.

TYPE param_obj_ty AS OBJECT
  ( P_P1     VARCHAR2(100 BYTE),
    P_P2     VARCHAR2(100 BYTE),
    P_P3     VARCHAR2(100 BYTE),
    P_P4     VARCHAR2(100 BYTE),
    P_P5     DATE
  );

create or replace
TYPE param_tab_ty AS TABLE OF param_obj_ty;


Create or replace package myPkg
IS
      PROCEDURE abc
      ( p_param IN param_tab_ty); <--collection passed to proc abc
END myPkg;


How do i pass a collection in java

Sample code:
CallableStatement cs = conn.prepareCall("{? = call proc (?,?,?,?,?,?,?)}");

I can't pass induvidual strings. I need to pass a collection
cs.setString(2, "");
cs.setString(3, "");
cs.setString(4, "123");
0
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 38336740
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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
In this post we will learn different types of Android Layout and some basics of an Android App.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
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
Course of the Month9 days, 23 hours left to enroll

571 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