run procedure insert table

ömer faruk akyüzlü

hello,

I created a procedure.

my procedure name is DOVIZ_KUR_GUNCELLE.

how can I run, TAGE0002 table is null why?

create or replace PROCEDURE DOVIZ_KUR_GUNCELLE

IS

MerBank_url HTTPURITYPE;

MerBank_xml XMLTYPE;

BEGIN

MerBank_url :=

httpuritype.createuri('http://www.tcmb.gov.tr/kurlar/today.xml');

MerBank_xml := MerBank_url.getxml ();

FOR rec_

IN (SELECT EXTRACTVALUE (VALUE (x), '/Tarih_Date/@Tarih') Tarih,

EXTRACTVALUE (VALUE (p), '/Currency/@CurrencyCode') Dovkod,

EXTRACTVALUE (VALUE (p), '/Currency/ForexBuying') Doval,

EXTRACTVALUE (VALUE (p), '/Currency/ForexSelling') Dovsat,

EXTRACTVALUE (VALUE (p), '/Currency/BanknoteBuying') Efal,

EXTRACTVALUE (VALUE (p), '/Currency/BanknoteSelling') Efsat

FROM TABLE (XMLSEQUENCE (EXTRACT (MerBank_xml, '/Tarih_Date'))) x,

TABLE (XMLSEQUENCE (EXTRACT (VALUE (x), '/Tarih_Date/Currency'))) p

WHERE EXTRACTVALUE (VALUE (p), '/Currency/@CurrencyCode') IN

('USD', 'EUR'))

LOOP

BEGIN

INSERT INTO TAGE0002 (DOVKURKOD,

DOVKOD,

KURTAR,

DOVAL,

DOVSAT,

EFAL,

EFSAT)

SELECT 1,

TO_CHAR (rec_.Dovkod),

rec_.Tarih,

NVL (REPLACE (rec_.Doval, '.', ','), 0),

NVL (REPLACE (rec_.Dovsat, '.', ','), 0),

NVL (REPLACE (rec_.Efal, '.', ','), 0),

NVL (REPLACE (rec_.Efsat, '.', ','), 0)

FROM DUAL;

COMMIT;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

UPDATE TAGE0002 SET DOVAL = NVL (REPLACE (rec_.Doval, '.', ','), 0),

DOVSAT = NVL (REPLACE (rec_.Dovsat, '.', ','), 0),

EFAL = NVL (REPLACE (rec_.Efal, '.', ','), 0),

EFSAT = NVL (REPLACE (rec_.Efsat, '.', ','), 0)

WHERE DOVKURKOD = 1 AND DOVKOD = TO_CHAR (rec_.Dovkod) AND KURTAR = rec_.Tarih ;

COMMIT;

WHEN OTHERS

THEN

NULL;

END;

END LOOP;

END;

Alex Nuijten

Does this query give you any results? If there are no results, it doesn't go into the LOOP

SELECT EXTRACTVALUE (VALUE (x), '/Tarih_Date/@Tarih') Tarih,

EXTRACTVALUE (VALUE (p), '/Currency/@CurrencyCode') Dovkod,

EXTRACTVALUE (VALUE (p), '/Currency/ForexBuying') Doval,

EXTRACTVALUE (VALUE (p), '/Currency/ForexSelling') Dovsat,

EXTRACTVALUE (VALUE (p), '/Currency/BanknoteBuying') Efal,

EXTRACTVALUE (VALUE (p), '/Currency/BanknoteSelling') Efsat

FROM TABLE (XMLSEQUENCE (EXTRACT (MerBank_xml, '/Tarih_Date'))) x,

TABLE (XMLSEQUENCE (EXTRACT (VALUE (x), '/Tarih_Date/Currency'))) p

WHERE EXTRACTVALUE (VALUE (p), '/Currency/@CurrencyCode') IN

('USD', 'EUR')

Remove the following, there might be an error which you're suppressing:

WHEN OTHERS

THEN

NULL;

Frank Kulash

Hi, @ömer faruk akyüzlü

This looks like a duplicate of

run procedure insert table — oracle-tech

Please post each question only once.

The other thread has miore replies, so I'll close this one.