6 Replies Latest reply on Feb 26, 2017 12:45 PM by Peter de Vaal

    SQLDev 4.2ea2: Bug in creation of Java Stored Procedure?

    Peter de Vaal

      @ product development:

       

      I have a script that creates a Java Stored Procedure and a PL/SQL procedure calling the Java.

      The script runs well on SQLDev 4.1.x, but does not run on 4.2ea2. If I click the RUN button nothing happens.

      Another Java stored procedure script runs ok, but only if I run the individual statements.

       

      My current workaround is to use version 4..1.5 to run the script (or SQL*Plus).

        • 1. Re: SQLDev 4.2ea2: Bug in creation of Java Stored Procedure?

          Thanks for sharing.

           

          But if you actually want help you have to SHOW US:

           

          1. WHAT you do

          2. HOW you do it

          3. WHAT results you get

           

          We can't see your screen or see how Sql Dev responds to whatever it is you are doing.

          • 2. Re: SQLDev 4.2ea2: Bug in creation of Java Stored Procedure?
            Gary Graham-Oracle

            Yes, posting a test case that we could try against the latest 4.2 internally would help.  EA2 is from December and we are getting closer to 4.2 production, so time is of the essence.

            • 3. Re: SQLDev 4.2ea2: Bug in creation of Java Stored Procedure?
              JodyMooney-Oracle

              Please untag - Oracle Eloqua Internal Product Community from this post. It's not relevant.

               

              Thank you.

              • 4. Re: SQLDev 4.2ea2: Bug in creation of Java Stored Procedure?
                Peter de Vaal

                The test case is a very simple procedure that gets a list of files in a directory on the DB server and puts it in a global temporary table.

                The code is the following:

                 

                create or replace and compile java source named "DirList"

                as

                import java.io.*;

                import java.sql.*;

                 

                 

                public class DirList

                {

                   public static void getList(String directory)

                                      throws SQLException

                   {

                      File path = new File( directory );

                     

                     

                      FileFilter filter = new FileFilter() {

                        @Override

                        public boolean accept(File pathname) {

                          return pathname.canRead();

                        }

                      };

                     

                     

                      File[] files = path.listFiles(filter);

                      String element;

                   

                      for(int i = 0; i < files.length; i++)

                      {

                        element = files[i].getName();

                        #sql { INSERT INTO DIR_LIST (FILENAME)

                                   VALUES (:element) };

                      }

                   }

                }

                /

                • 5. Re: SQLDev 4.2ea2: Bug in creation of Java Stored Procedure?

                  Thanks for the additional info.

                   

                  Should have mentioned before but can you clarify EXACTLY what the problem is?

                   

                  You said your script doesn't run in the EA release. What does that mean? Nothing in the script EVER executes? Or does it mean it runs but you don't get the same results?

                   

                  What you posted is NOT a 'script' - it is a SQLJ program. So it would need to be processed first by the SQLJ translator

                  https://docs.oracle.com/cd/B28359_01/java.111/b31227/overview.htm#i1005826

                  SQLJ Translation Steps

                  After you have written your .sqlj file, you must run SQLJ to process the files.

                  So I'm assuming that the 'script' doesn't really include what you posted but includes the commands to execute the sqlj translator.

                   

                  Then the resulting class and other files would need to be loaded into the DB.

                   

                  So please explain whether the script is the problem or the code you posted is the problem. What are the steps in the script? Do ANY of them work in the EA release? Do ANY scripts work for you in EA?

                  • 6. Re: SQLDev 4.2ea2: Bug in creation of Java Stored Procedure?
                    Peter de Vaal

                    The script that I use to create the Java Stored procedure is exactly what I posted, except that it is followed by the statement to create the PL/SQL wrapper procedure, which is:

                     

                    create or replace procedure get_dir_list(p_dir varchar2)

                    as language java

                    name 'DirList.getList( java.lang.String )';

                    /

                     

                    The first statement, starting with:

                        create or replace and compile java source named "DirList" as ..

                    should to my humble opinion create the java in the database (and it indeed always did in SQLDev 4.1.5 and earlier.

                     

                    However, this statement is causing the problem in 4.2. When I open the script and click the Run button nothing happens.

                    It is not typical to Java sources, because another one does run. Here is the code of that one:

                     

                    -- SCRIPT START --

                    create or replace and compile java source named "makeFileReadable"

                    as

                    import java.io.*;

                    import java.sql.*;

                     

                    public class makeFileReadable

                    {

                    public static void setReadable(String filename)

                                       throws SQLException

                    {

                       File path = new File( filename );

                       path.setReadable(true,false);

                    }

                    }

                    /

                     

                    create or replace procedure make_file_readable(p_dir varchar2)

                    as language java

                    name 'makeFileReadable.setReadable( java.lang.String )';

                    /

                    -- SCRIPT END --

                     

                    I think the difference with the 2 sources is that the one that does not run contains an annotation (@Override). However, 4.1.5 did not have a problem with it.