0

How to refresh the URI type data and reload to table , whenever the website url got refresh.

My complete code:

   Created table with URI TYPE 

create table vignesh3(url URIType)

   loaded data from the url using geturi function.

INSERT INTO adw.vignesh3 VALUES
  (SYS.URIFACTORY.getURI('http://www.floatrates.com/daily/nzd.xml'));

Loading the columns as a table using xmltable option. 

select e.url.getXML() from adw.vignesh3 e

SELECT xt.*
FROM   adw.vignesh3 x,
       XMLTABLE('/channel/item'
         PASSING x.url.getXML()
         COLUMNS 
           title     VARCHAR2(100)  PATH 'title',
           link     VARCHAR2(100) PATH 'link',
           DESCRIPTION       varchar2(100)  PATH 'description',
           PUBDATE  varchar2(100)  PATH 'pubDate',
           BASECURRENCY varchar2(60) PATH 'baseCurrency',
           BASENAME  varchar2(60) PATH 'baseName',
           TARGETCURRENCY varchar2(60)  PATH 'targetCurrency',
           TARGETNAME  varchar2(60) PATH 'targetName',
           EXCHANGERATE  varchar2(60)  PATH 'exchangeRate',
           INVERSERATE  varchar2(60)  PATH 'inverseRate',
           INVERSEDESCRIPTION  varchar2(60)  PATH 'inverseDescription'           
         ) XT;

I need to refresh this data whenever the url updates the rates.

  • This is required to load currency rate to the temp table and required refresh of rates on hourly basis – suresh vignesh Aug 9 at 12:30
  • Are you looking for DBMS_SCHEDULER? – mustaccio Aug 9 at 14:50
  • Sorry for the delayed response - Yes looking for the schedule to refresh the data. – suresh vignesh Aug 13 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.