This discussion is archived
4 Replies Latest reply: Jun 25, 2013 8:20 AM by odie_63 RSS

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

pl_sequel Newbie
Currently Being Moderated

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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points