1 2 3 Previous Next 33 Replies Latest reply: Feb 26, 2014 11:28 PM by 936666 RSS

    Java pl sql vs pl sql


      Difference in Java plsql(function or stored procedure ) and PL/SQL

      which is faster?

      when to use java plsql(function or stored procedure ), why ?

      when to use pl sql ,why?



        • 1. Re: Java pl sql vs pl sql

          I'm afraid your question doesn't make a whole lot of sense.


          Java is a language.  PL/SQL is a language.  Each can be used to write stored procedures in the Oracle database (as can .Net if you're on Windows). 


          What, exactly, does "Java + plsql" mean to you in this context?  Are you asking why someone might use Java stored procedures?



          • 2. Re: Java pl sql vs pl sql

            Yes java stored procedure or function.

            creating a java class and invoking it as java stored procedure or function and calling that stored procedure or function.

            • 3. Re: Java pl sql vs pl sql

              PL/SQL is used to defined named blocks like stored procedures, functions, triggers etc within the database and they will remain in the DB however you can use Java program to call such named blocks except triggers.

              which is faster?

              hard to say which one is faster. It depends on various things like how are you calling, against what object you are calling, objects involved, their statistics, volume of data and many more. On the top, as Justin has mentioned, are you asking if java and pl/sql can be used together or not and if you say Yes then yes you can do that.

              when to use java+ plsql, why ?

              when to use pl sql ,why?

              Java-pl/sql can be used when you wanted to call some function/procedure from a java program for data manipulation or something. PL/SQL can used when you wanted to call such named blocks from the database itself.


              On the top, please make sure to redefine your question. It would be much easier for others to comment.



              • 4. Re: Java pl sql vs pl sql

                In general, you use a Java stored procedure when it isn't practical to use a PL/SQL stored procedure.  For example, there are millions of Java APIs for doing things like getting lists of files from the file system, working with binary Excel files (.XLS), doing SFTP, etc.  When you find yourself wanting to do something that isn't easy to do in PL/SQL but which Java has a well-tested, production-quality library to handle, being able to leverage Java stored procedures is quite helpful.



                • 5. Re: Java pl sql vs pl sql

                  i had some bench marking of splitting up of large blob data into pieces based on the byte len in oracle it takes time where as when i tried the same blob in java it takes less time

                  Oracle BLOB  takes 2 min

                  java(alone) BLOB  takes 1.25 min(note pure java not plsql-java)

                  having an idea of implementing java code with plsql like CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED ""








                  CREATE OR REPLACE FUNCTION fn-name

                  RETURN VARCHAR2 AS

                  LANGUAGE JAVA NAME 'javaname.method return java.lang.String';

                  • 6. Re: Java pl sql vs pl sql

                    OK.  Is there a question there?


                    It's certainly possible that you could find something that is faster in Java than PL/SQL (particularly if you're comparing two non-equal alternatives).  I'd find it a bit odd that using the straight DBMS_LOB API was less efficient than writing your own in Java but without any code, it's hard to suggest why that might be.



                    • 7. Re: Java pl sql vs pl sql

                      my oracle code and java code is having some business logics so i cant put my code over here


                      oracle code


                      l_cursor := dbms_sql.open_cursor;


                      dbms_sql.parse( l_cursor,'colunm name ...................................N+1',dbms_sql.native );


                      FOR idx  IN 1..count LOOP

                      FOR i IN 1..max LOOP


                      dbms_sql.bind_variable( l_cursor, l_count, dp);


                      dbms_sql.bind_variable( l_cursor, l_count, nu);


                      dbms_sql.bind_variable( l_cursor, l_count, fl);

                      dbms_sql.execute( l_cursor );

                      end loop;


                      end loop;

                      java code






                        double a= (bytebufr.getDouble());










                      query="insert into "+Table_Name+" values("+str+")";

                      • 8. Re: Java pl sql vs pl sql

                        Again, is there a question?


                        I fail to see how your PL/SQL code has anything to do with parsing a BLOB.  It appears to relate to executing a dynamic SQL statement (though there are numerous syntax errors so it's hard to follow).



                        • 9. Re: Java pl sql vs pl sql

                          As i said i can past my code over here

                          i have displayed the logic what i have used in java and in oracle.

                          • 10. Re: Java pl sql vs pl sql

                            Are you asking a question or making a statement?


                            I've said that I can't fathom how the two pieces of code can be said to be doing the same task.  The PL/SQL you posted doesn't appear to be doing anything with a BLOB.


                            If you're not looking for help understanding how your code (particularly your PL/SQL code) could be made faster, maybe the thread is complete.  Otherwise, if you're asking a question that hasn't been fully answered yet, can you re-iterate what exactly that question might be?



                            • 11. Re: Java pl sql vs pl sql
                              create or replace Procedure MY_Proc
                              P_STATUS            OUT number,
                              P_OUTPUT            OUT sys_refcursor,
                              P_Input       NUMBER,
                              P_blob              BLOB    ,
                              p_count             NUMBER
                              l_current_state NUMBER(5);
                              l_row_num     NUMBER(5);
                              data_type     VARCHAR2(50);
                              vblob         BLOB;
                              vstart        NUMBER ;
                              vstartfull    NUMBER ;
                              bytelen       NUMBER ;
                              fullbytelen   NUMBER ;
                              len           NUMBER;
                              my_vr         RAW(50);
                              fl            FLOAT  ;
                              dp            Double precision;
                              var2          Double precision;
                              nu            NUMBER;
                              l_cursor      int;
                              l_status      int;
                              l_count       VARCHAR2(120);
                              errpos        BINARY_INTEGER;
                              l_max         int;
                              l_sum         int;
                              idx           number;
                              l_Seq         number;
                              in_TableName  VARCHAR2(50);
                              counter number(4) default 0;
                              TYPE varColtypelist is varray(1000) of NUMBER(20);
                              collist varColtypelist;
                              TYPE varColLenlist is varray(1000) of number(20);
                              byteLenList varColLenlist;
                              in_TableName :='Tab_'||P_Input;
                              Select Sum(BYTE_INFO) into l_sum from byte_info where Input=P_Input;
                              len := dbms_lob.getlength(P_blob);
                              vstartfull :=1;
                              fullbytelen :=l_sum;
                                      Select byte_info bulk collect into byteLenList from byte_info   where Input=P_Input;
                                       Select   CASE WHEN data_type ='BINARY_FLOAT' THEN 1
                                                WHEN data_type ='BINARY_DOUBLE' THEN 2
                                                WHEN data_type ='NUMBER' THEN 3 END  bulk collect into  collist
                                      from all_tab_columns where table_name=UPPER(in_TableName);
                              l_cursor := dbms_sql.open_cursor;
                              dbms_sql.parse( l_cursor,'insert into mytable (column names) values (..........) ,dbms_sql.native );
                              Select max(R_NUM) into l_max from byte_info where Input=P_Input;
                              Select max(R_NUM) into l_max from byte_info where Input=P_Input;
                              FOR idx  IN 1..p_count LOOP
                              vstart :=1;
                              FOR i IN 1..l_max LOOP
                                      l_count := '':''||i;
                                      data_type := collist(i);
                                      bytelen := byteLenList(i);
                                      if data_type =1 THEN /*4 byte */
                                      fl := utl_raw.cast_to_binary_float( my_vr ,2);
                                      dbms_sql.bind_variable( l_cursor, l_count, fl);
                                      end if;
                                      if data_type =2 THEN /*8 byte */
                                      dp := utl_raw.cast_to_binary_double( my_vr,2 );
                                      dbms_sql.bind_variable( l_cursor, l_count, dp);
                                      end if;
                                      if data_type =3 THEN /*4 byte */
                                      nu := utl_raw.CAST_to_BINARY_INTEGER( my_vr,2 );
                                      dbms_sql.bind_variable( l_cursor, l_count, nu);
                                      end if;
                              vstart :=vstart+ bytelen;
                              end loop;  /* MAX Close Loop*/
                              l_status := dbms_sql.execute( l_cursor );
                              vstartfull := vstartfull+l_sum;
                                  counter := counter +SQL%rowcount ;
                                  If counter >= 100 then
                                   counter := 0;
                                  end if;
                              end loop; /*p_Count Close Loop*/
                                      P_STATUS   :=0;
                                      open P_OUTPUT for
                                      Select 'Super' from dual;
                                 WHEN OTHERS
                                    errpos := DBMS_SQL.LAST_ERROR_POSITION;
                                    DBMS_OUTPUT.PUT_LINE (SQLERRM);
                                    DBMS_OUTPUT.PUT_LINE (SQLERRM || '' at pos '' || errpos);
                                  IF DBMS_SQL.IS_OPEN(l_cursor) THEN
                               END IF;


                              here goes the code this is the code i have used to split the BLOB

                              I will be given a blob and need to split the blob based on the byte size which i have stored in the byte_info table.

                              line 42 will give the blob size.

                              Based on the table name i will know for each column what data type is used line 46-50

                              from line 51 till end my logic starts

                              • 12. Re: Java pl sql vs pl sql

                                As Justin has repeatedly stated:


                                WHAT IS YOUR QUESTION OR PROBLEM!


                                You keep posting a bunch of statements/code but you aren't asking any question.

                                • 13. Re: Java pl sql vs pl sql
                                  Mike Kutz


                                  • you have multiple tables
                                  • of some indiscriminate format
                                  • that contain only numbers of type BINARY_FLOAT,BINARY_DOUBLE, and/or BINARY_INTEGER(or number)




                                  This sounds like you are trying to use Oracle as a "data dumping ground" not as a relational database.


                                  Note 1

                                  WHEN OTHERS without RAISE

                                  This is usually a big indication of a BUG.

                                  The section looks like a valid use of WHEN OTHERS (ie clearing resources) but you really need to RAISE the original error or use RAISE_APPLICATION_ERROR


                                  Note 2

                                  What is up with the "open P_OUTPUT for select 'super' from dual"?

                                  We now live in the 21st century.

                                  Please update your application design to reflect something that was available at the end of the 20th century.

                                  "try/catch" type coding construct has been available for handling error within in multiple languages (including Java and PERL) since the last millennium.

                                  Again, lookup RAISE_APPLICATION_ERROR() for throwing your own ORA-.... errors that contain the "position information" in the error string.


                                  Note 3

                                  This section is known to be a SLOW-BY-SLOW process.  Get rid of it.

                                  if counter >= 100 then

                                    counter := 0;


                                  end if;


                                  Note 4

                                  line 77:  l_status := sbms_sql.execute( l_cursor );


                                  Are you sure you can't use some sort of BULK PROCESSING instead


                                  1. build arrays of the values to be inserted
                                  2. bind the arrays
                                  3. dbms_sql.execute (ie don't loop over this line)

                                  (don't forget to lookup the LOG ERRORS INTO clause.)


                                  Note 5

                                  I've seen this type of code before.  parsing IEEE number data out of a BLOB/file

                                  This can be done much faster using EXTERNAL TABLES


                                  I'd revisit the whole workflow to see if I could use EXTERNAL TABLE for the data importing instead of BLOB processing.... or some other method.

                                  If it takes you 2min to process with the above code, using a workflow that allows the usage of well established techniques (like EXTERNAL TABLES) can drop your time down to <5s.


                                  yes, you'd need to create an EXTERNAL TABLE for each table.

                                  Again, this requirement seems to imply that you are using Oracle as a "data dumping ground", not as a relational database.


                                  My $0.02 worth



                                  • 14. Re: Java pl sql vs pl sql

                                    This sounds like you are trying to use Oracle as a "data dumping ground" not as a relational database.

                                    Excellent observation. This is mostly just a continuation of OPs original thread where that is EXACTLY what OP was doing.


                                    1 2 3 Previous Next