Home » Developer & Programmer » JDeveloper, Java & XML » paging in pl/sql with xml format.
paging in pl/sql with xml format. [message #466506] Mon, 19 July 2010 23:59
oracleplsqlunix
Messages: 1
Registered: June 2010
Location: india
Junior Member
hi

this is my select stmt in cursor... i wrote this stmt in xml formate... so here i want to do paging....
here i am unable to do paging can anyone help me please
my paging is..
WHERE ROWNUM < (( l_pageNumber*l_pageSize) + 1)
WHERE RN >= (((l_pageNumber-1)* l_pageSize)+1) ;
but here i wrote in xml....

thanks in advance..

SELECT XMLELEMENT("UsageRecords", XMLAGG(XMLELEMENT("UsageRecord",
XMLFOREST(DU.METER,
TO_CHAR(DU.DAILYTIMESTAMP, g_timeStampFormat) DAILYTIMESTAMP,
DU.TOTALENERGY,
DU.STATUSID,
DU.AVERAGEUSAGEDAYS,
DU.SPIKEPERCENT,
DECODE(DU.STATUSID, 3, 'Estimated', 'Calculated') "USAGETYPE",
DU.PEAKKW,
TO_CHAR(DU.PEAKKWTIMESTAMP, g_timeStampFormat) PEAKKWTIMESTAMP,
DU.PEAKKVA,
DU.PEAKKVAR,
DU.COINPOWERFACTOR,
DU.LOADFACTOR,
DU.TOTALGKW,
DU.TOTALGKVA,
DU.TOTALGKVAR,
DU.TOTALBLINKCOUNT,
DU.TOTALEVENTS,
DU.TOTALOUTAGES,
DU.TOTALPOSKWH,
DU.TOTALNEGKWH,
DU.NOTES NOTES,
DU.ENDREADING,
DU.USAGEID,
DU.LATESTREADINGID,
(SELECT ( XMLAGG(XMLELEMENT("Validation",XMLFOREST(ED.ERRORID, DECODE(ED.ERRORID, 10, 'Illegal Usage', 11, 'Usage Spike') "ERRORDESC")))) xe FROM ERROR_DETAIL ED WHERE ED.ERRORKEY = DU.USAGEID) "Validations"
)
)
)
).GETCLOBVAL()
FROM DAILY_USAGE DU LEFT OUTER JOIN ERROR_DETAIL ED ON DU.USAGEID = ED.ERRORKEY
-- WHERE DU.METER = cp_meter AND (DU.DAILYTIMESTAMP >= cp_usageFrom AND DU.DAILYTIMESTAMP <= cp_usageTo);
WHERE DU.METER = cp_meter AND (cp_usageFrom IS NULL OR (cp_usageFrom IS NOT NULL AND DU.DAILYTIMESTAMP >= cp_usageFrom))
AND (cp_usageTo IS NULL OR (cp_usageTo IS NOT NULL AND DU.DAILYTIMESTAMP <= cp_usageTo));
thanks...
Previous Topic: Proxy user connectivity with thin driver
Next Topic: OAF - iSupplier Portal - Current Supplier ID?
Goto Forum:
  


Current Time: Fri Aug 22 23:23:06 CDT 2014

Total time taken to generate the page: 0.10029 seconds