4 Replies Latest reply: Jun 25, 2013 10:20 AM by odie_63 RSS

    ORA-00939: too many arguments for function using Timezones in xquery

    pl_sequel

      Running on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

       

       

       

      CREATE TABLE "ORT"."SAMPLE"
         ( "THEDATE" DATE,
      "THETIMESTAMP" TIMESTAMP (6),
      "STARTTIMESTAMP" TIMESTAMP (6) WITH LOCAL TIME ZONE,
      "ENDTIMESTAMP" TIMESTAMP (6) WITH LOCAL TIME ZONE
         );
      
      
      
      

       

       

       

      REM INSERTING into SAMPLE
      SET DEFINE OFF;
      Insert into SAMPLE (THEDATE,THETIMESTAMP,STARTTIMESTAMP,ENDTIMESTAMP) values (to_date('13-06-10 14:07:52','RR-MM-DD HH24:MI:SS'),to_timestamp('13-06-19 14:27:52.000000000','RR-MM-DD HH24:MI:SS.FF'),to_timestamp('13-06-19 10:34:04.586000000','RR-MM-DD HH24:MI:SS.FF'),to_timestamp('13-06-19 15:05:38.805000000','RR-MM-DD HH24:MI:SS.FF'));
      
      
      
      

       

      following query raises ora-00939

       

      SELECT XMLQUERY('for $v in fn:collection("oradb:/ORT/SAMPLE")
      let $date1 := $v/ROW/STARTTIMESTAMP/text()
      let $date2 := $v/ROW/ENDTIMESTAMP/text()
      return if ($date1 < $date2) then (concat($date1," date is less than ", $date2)) else (concat($date1," date is greater than ", $date2)) ' returning content) from dual;
      
      

       

      ORA-00939: too many arguments for function

      00939. 00000 -  "too many arguments for function"

      *Cause: 

      *Action:

       

       

      any ideas?