2 Replies Latest reply: Jun 10, 2013 5:56 AM by rt15_france RSS

    SimpleDateFormat not working inside a java stored procedure

    rt15_france
      Although it is working outside Oracle, I cannot manage to parse a date inside a java stored procedure using SimpleDateFormat.
      Here is the java code in MyTestClass.java:
      import java.text.ParsePosition;
      import java.text.SimpleDateFormat;
      import java.util.Date;
      import java.util.Locale;
      
      public class MyTestClass
      {
           public static String myTest()
           {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss", Locale.ENGLISH);
      
                sdf.setLenient(false);
                ParsePosition parsePosition = new ParsePosition(0);
                Date date = sdf.parse("20130606174509", parsePosition);
      
                String str;
                if (date == null)
                     str = "Error at index " + parsePosition.getErrorIndex();
                else
                     str = date.toString();
      
                return str;
           }
      }
      Then I compile this code:
      javac -g MyTestClass.java
      Then I upload the class:
      loadjava -user <user>/<pwd> MyTestClass.class
      Then I open sqlplus, connect as <user>/<pwd> and create the procedure:
      CREATE or replace FUNCTION toto RETURN VARCHAR2
      AS LANGUAGE JAVA
      NAME 'MyTestClass.myTest() return java.lang.String';
      /
      Then I use the procedure:
      select toto from dual;
      And the result is :
      TOTO
      ---------------------------
      Error at index 0
      I tried to debug using jpda, but I can only debug my code as I don't know which implementation of SimpleDateFormat is used in Oracle (11.2.0). A jdk 1.5.0_17 is provided with Oracle database but stored procedure are using a different implementation. As a consequence, there is a gap between what I put in the ide (source of jdk 1.5.0_17) and what is inside database. So breakpoints are not working.

      Please help
        • 1. Re: SimpleDateFormat not working inside a java stored procedure
          Marcus Rangel
          I did it in a different way and it seems to have worked. Database version 11.2.0.2.
          SQL> create or replace and compile java source named mytestclass as
            2  import java.text.ParsePosition;
            3  import java.text.SimpleDateFormat;
            4  import java.util.Date;
            5  import java.util.Locale;
            6  
            7  public class MyTestClass
            8  {
            9       public static String myTest()
           10       {
           11            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss", Locale.ENGLISH);
           12  
           13            sdf.setLenient(false);
           14            ParsePosition parsePosition = new ParsePosition(0);
           15            Date date = sdf.parse("20130606174509", parsePosition);
           16  
           17            String str;
           18            if (date == null)
           19                 str = "Error at index " + parsePosition.getErrorIndex();
           20            else
           21                 str = date.toString();
           22  
           23            return str;
           24       }
           25  }
           26  /
           
          Java created
           
          SQL> 
          SQL> CREATE or replace FUNCTION toto RETURN VARCHAR2
            2  AS LANGUAGE JAVA
            3  NAME 'MyTestClass.myTest() return java.lang.String';
            4  /
           
          Function created
           
          SQL> select toto from dual;
           
          TOTO
          --------------------------------------------------------------------------------
          Thu Jun 06 17:45:09 GMT-03:00 2013
           
          SQL> 
          • 2. Re: SimpleDateFormat not working inside a java stored procedure
            rt15_france

            Hi,

             

            Thanks for the try.

             

            I spent one full day on this issue. Before killing myself, I have reinstalled my Oracle database.

            And it is now working.

             

            This issue will probably remain a strangeness for me forever.