This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,967 Users
  • 2,269,775 Discussions
  • 7,916,823 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 Boston, USAMember, Moderator Posts: 43,002 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.