This discussion is archived
11 Replies Latest reply: Jan 29, 2013 2:36 PM by rp0428 RSS

Oracle to Java communication

SamFisher Explorer
Currently Being Moderated
Hello All,
Vesrion:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
A general question about Oracle to Java connection/communicate

I have a front end(Java) page where in a user gives an input.
Based on the input I should fetch the data from Oracle database and send/pass it back to front end.

How do I pass the input to Oracle DB &
how do I pass the output from back end to front end?

Going through few articles, I came to know that we can use
Refcursors.
Not sure about Jpub.

Can you please suggest me the possible ways here?
  • 2. Re: Oracle to Java communication
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You need to ask this in a Java forum. How to use JDBC and how to use Oracle java classes.

    This has very little to do with SQL and PL/SQL.
  • 3. Re: Oracle to Java communication
    SamFisher Explorer
    Currently Being Moderated
    Hello All,

    Here I'm trying to pass the collection object as an input and output param from the procedure.
    I'm not sure if I can do that.
    Gave a try.
    CREATE OR REPLACE TYPE department_type AS OBJECT (
       dno        NUMBER (10),
       name       VARCHAR2 (50),
       location   VARCHAR2 (50)
    );
    / 
     
    CREATE OR REPLACE TYPE dept_array AS TABLE OF department_type;
    / 
     
    CREATE OR REPLACE PACKAGE insert_object
    AS
       PROCEDURE insert_object_prc (d IN dept_array, d2 OUT dept_array);
    END;
    / 
    SHOW ERRORS
     
    CREATE OR REPLACE PACKAGE BODY insert_object
    AS
       PROCEDURE insert_object_prc (d IN dept_array, d2 OUT dept_array)
       IS
       BEGIN
          d2 := dept_array ();
          FOR j IN 1 .. d.COUNT
          LOOP
             d2.EXTEND;
             d2 (j) := department_type (d (j).dno, d (j).name, d (j).location);
             Dbms_output.put_line(d (j).dno||' '|| d (j).name||' '|| d (j).location);
          END LOOP;
       END insert_object_prc;
    END insert_object;
    / 
    This is where I'm struck. Don't know how to pass the collection as input or output params.
    DECLARE
    
    BEGIN
          insert_object_prc(?..);
    END;
    Can we pass the collection object as input/output param?
    If so, Can you please guide me.


    Thx
    Shank.
  • 4. Re: Oracle to Java communication
    Rahul_India Journeyer
    Currently Being Moderated
    this might help

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:712625135727
  • 5. Re: Oracle to Java communication
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    SamFisher wrote:

    Here I'm trying to pass the collection object as an input and output param from the procedure.
    I'm not sure if I can do that.
    Yes you can - but it is still now a PL/SQL or SQL question/issue.

    The client needs to make a call as follows:
    begin
      insert_object( :1, ;:2 );
    end;
    And bind two host variables. The issue is that the data type need to support the SQL custom type used by the procedure. And that means "setting up" the SQL custom types for use in Java. Which makes it a Java issue.

    There are Oracle guides for Java developers. Consult these? (http://tahiti.oracle.com)
  • 6. Re: Oracle to Java communication
    rp0428 Guru
    Currently Being Moderated
    >
    Here I'm trying to pass the collection object as an input and output param from the procedure.
    I'm not sure if I can do that.
    >
    That procedure doesn't do anything.

    Why don't you explain what you are really trying to do so we can suggest the best way to do it.

    First you mention ref cursors and Java but now you mention nested tables.

    Unless you provide a description of what you need to do and why we can only offer general advice.

    If you are working with Jave and need to use collections see the JDBC Developer Guide
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/toc.htm

    Chapter 15 Using Oracle Object References and Chapter 16 Working with Oracle Collections.

    But if you have a JDBC issue you should post in the JDBC forum.
    https://forums.oracle.com/forums/category.jspa?categoryID=288
  • 7. Re: Oracle to Java communication
    SamFisher Explorer
    Currently Being Moderated
    Sorry for the delayed response.

    Here's my requirement.
    I have a procedure which has 10 input parameters. I have to pass those input parameters to front end.
    I can declare all the input parameters as variables in the procedure but the problem is, if in future they want to add more input parameters then there will be a lot to change in the code(Code change).
    That is the reason why I was looking for passing a collection wherein I have all the input parameters.
    Please correct me if I am wrong.


    Thx,
    Shank.
  • 8. Re: Oracle to Java communication
    sb92075 Guru
    Currently Being Moderated
    SamFisher wrote:
    Sorry for the delayed response.

    Here's my requirement.
    I have a procedure which has 10 input parameters. I have to pass those input parameters to front end.
    I can declare all the input parameters as variables in the procedure but the problem is, if in future they want to add more input parameters then there will be a lot to change in the code(Code change).
    But if the procedure must process the new input parameters, a code change is still REQUIRED!;
    That is the reason why I was looking for passing a collection wherein I have all the input parameters.
    Please correct me if I am wrong.
    You are wrong.
  • 9. Re: Oracle to Java communication
    6363 Guru
    Currently Being Moderated
    SamFisher wrote:

    I have a procedure which has 10 input parameters. I have to pass those input parameters to front end.
    I can declare all the input parameters as variables in the procedure but the problem is, if in future they want to add more input parameters then there will be a lot to change in the code(Code change).
    I would think the smallest change would be the parameter declaration. Say they want to pass in a parameter p_debug for example, that if Y writes all the steps the procedure executes and the time taken to a log table (This is a very good idea BTW). I would think in that case code change is unavoidable, since there is a requirement change and a functionality change, and that adding the p_debug parameter would be the least thing to be trying to minimize.

    An object type or collection argument can be used so that multiple values can be passed in via the single argument with no change, conversely inside the procedure it will require more code changes to handle the additional information that needs to be extracted from the existing parameter.

    On the output side you would want to return a ref cursor if the output is a set of data consisting of multiple rows.
  • 10. Re: Oracle to Java communication
    Justin Cave Oracle ACE
    Currently Being Moderated
    SamFisher wrote:
    I have a procedure which has 10 input parameters. I have to pass those input parameters to front end.
    Do you mean "from the front end"? Or are you really saying that the PL/SQL stored procedure is trying to call the Java front end?
    I can declare all the input parameters as variables in the procedure but the problem is, if in future they want to add more input parameters then there will be a lot to change in the code(Code change).
    That is the reason why I was looking for passing a collection wherein I have all the input parameters.
    Please correct me if I am wrong.
    That doesn't seem to make sense unless there is a lot of background that you're leaving out...

    A collection makes sense when you are passing many instances of the same thing that you want to treat equally. For example, if you were to write a GET_EMPLOYEES method where you wanted to be able to pass in many employee ID's and get back data about those employees, it would make perfect sense to pass in a collection of employee_id's and return a collection of employee%rowtype's. It would not make sense to create a procedure that could take up to 10 employee_id parameters.

    On the other hand, if you are passing in 10 legitimately different parameters, it makes little sense to use a collection. If you want to add another input parameter, you're going to have to modify the front end to gather than information and pass it in. You're going to have to modify the back end to do something with the new parameter. The additional effort of modifying the procedure signature is minimal. The overhead of putting unrelated parameters into a collection and then unpacking those unrelated elements grossly outweighs the effort of modifying the procedure's signature in the future.

    If you find yourself trying to pass 10 parameters to a procedure, it is often the case that either you need to refactor your procedure so that it has a single task to perform rather than being a swiss-army knife that can do a bunch of different things. It is also potentially the case that you would be better served by organizing some of the parameters into an object type that can be passed in.

    Justin
  • 11. Re: Oracle to Java communication
    rp0428 Guru
    Currently Being Moderated
    >
    Here's my requirement.
    I have a procedure which has 10 input parameters. I have to pass those input parameters to front end.
    I can declare all the input parameters as variables in the procedure but the problem is, if in future they want to add more input parameters then there will be a lot to change in the code(Code change).
    That is the reason why I was looking for passing a collection wherein I have all the input parameters.
    Please correct me if I am wrong.
    >
    Ok - you are wrong. :D

    Seriously, though, that approach is wrong for several reasons.

    1. It extremely limits the ability of Oracle to provide compile-time checking of the parameters when the procedure is called by other PL/SQL code.

    2. It makes it difficult for YOU to use parameters of different datatypes. How will your collection support parameters of DATE, NUMBER, VARCHAR2, UDT and other datatypes?

    3. It makes it difficult for you to use default parameter values for some or all parameters especially if the parameters are of different datatypes.

    4. It makes it difficult for YOU to validate that the parameters that are passed are correct.

    5. It eliminates Oracles ability to provide information about the procedure to others. You can isse a 'desc myProcedure' to get information about the parameters that a procedure takes and from the information you can construct a proper call.

    6. Any change to the parameters requires a code change in the procedure anyway to make use of the new parameter. And if the new parameter does not have a DEFAULT value it will require a change to the calling code to call the procedure because the calling code will have to provide the new parameter.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points