Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
run procedure insert table

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
-
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;
-
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.