Forum Stats

  • 3,824,982 Users
  • 2,260,448 Discussions
  • 7,896,369 Comments

Discussions

run procedure insert table

ömer faruk akyüzlü
ömer faruk akyüzlü Member Posts: 216 Bronze Badge
edited Apr 5, 2022 10:53AM in SQL & PL/SQL

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;

Answers

  • Alex Nuijten
    Alex Nuijten Member Posts: 242 Silver Badge

    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
    Frank Kulash Member, Moderator Posts: 42,030 Red Diamond

    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.

This discussion has been closed.