11 Replies Latest reply: Jan 29, 2013 4:36 PM by rp0428 RSS

    Oracle to Java communication

    SamFisher
      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
          Billy~Verreynne
          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
            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
              this might help

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:712625135727
              • 5. Re: Oracle to Java communication
                Billy~Verreynne
                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
                  >
                  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
                    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
                      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
                        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
                          JustinCave
                          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
                            >
                            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.