The ADFdi add-in for Excel runs on Microsoft Windows inside Microsoft Excel

Where to download The ADFdi add-in for Excel runs on Microsoft Windows inside Microsoft Excel

https://<hostname>:<portnumber> /fscmUI/adfdiRemoteServlet?excel-addin-installer-all-users

Object Extension Types in Oracle HCM Cloud

 _B - base table. It holds the main details of the object in question and it implies that there is also a _TL table for it. You'd never find a _B table without a _TL table. the primary key is the id column.

_TL - translatable table. Holds the translated names/descriptions for base objects. will have one row per installed language. linked to the base table by the _id column. Doesn't always imply that there is a _B as its base data could also be in a _F table or a table without postfix. The primary key is the id column plus the language column.

_F - date effective table. Tends to exist only in HR and it holds objects that are date effective. It means the primary key is the combination of the ID column, effective start date and effective end date. there is only 1 row per ID for any date, but you can have multiple rows for the same id over time. Some _F columns come with a matching _TL column, too.

_VL - translatable view. It's not a table, but the view that represents the join of the base table and the TL table using the user's session language. You can assume that almost all _TL tables come paired with a _VL view.

_M - multiple changes per date table. Like a _F table, but it allows rows to start and end on the same day. There is only one such table per_all_assignments_m, and it has additionally the effective_sequence in the primary key.

_DN - denormalised table: usually just a simplification of another table that holds hierarchies or trees of some kind. The denormalised tables are populated by ESS processes and hold the flattened hierarchy that has direct links between all nodes and their distance. Those are used e.g. for security profiles based on the manager hierarchy.

source: oracle

APEX_JSON.WRITE Tips

• For null case

Use: APEX_JSON.write('P_INVOICE_TRACKING', '', true);

Result: "P_INVOICE_TRACKING":null 

• For Escaping "& /" character 

Use: APEX_JSON.write_raw('P_INVOICE_NUMBER', '"'||r_header.sppd_code||'"');

Result: "P_INVOICE_NUMBER":"0134/XYZ/084/RS/2021" 

How to get SOURCE SYSTEM OWNER list

 Here how to get SOURCE SYSTEM OWNER on HDL process

1. Go to Setup and Maintenance



2. Search "Manage Common Lookup"





3. Search "HRC_SOURCE_SYSTEM_OWNER"





4. By Query

SELECT *
FROM fnd_lookup_values
WHERE lookup_type = 'HRC_SOURCE_SYSTEM_OWNER'

 

Oracle APEX - Using Outlook 365 as outbound email

Here the steps to use SMTP Outlook 365 as email outbond from oracle APEX

• Setup SMTP outlook 365 relay register your APEX host and get the smtp server name and port. how to to it check this youtube
• Create oracle wallet on DBaaS/DB to *.outlook.com
• Set Instance Setting > Email values on Workspace Internal

• Test it using apex_email

  procedure send_email_test(
    p_from    varchar2 default 'info@yourdomain.com'
  , p_to      varchar2
  , p_subject varchar2 default 'Email test'
  )
  as
    l_body      clob;  
  begin
  
    l_body := 'Thank you for your interest in the APEX_MAIL package.'||utl_tcp.crlf;
    l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;
    l_body := l_body ||'  The APEX Dev Team'||utl_tcp.crlf;
    apex_mail.send(
        p_to       => p_to,   -- change to your email address
        p_from     => p_from, -- change to a real senders email address
        p_body     => l_body,
        p_subj     => p_subject
    );
        APEX_MAIL.PUSH_QUEUE; 
  end send_email_test;

Thats it and good luck :D

Install/Upgrade Oracle APEX 18.1 on Oracle Database Cloud Service (DBaaS)

Assumption

• You can connect to DBaaS through Putty and FileZilla
• Oracle Database 12c Release 2 install on plugable db: PDB1
• You have the required version of the cloud tooling

Steps

1. Log in to the compute node as the oracle user.
2. Log in to SQL Plus as the SYS user.
3. Upload Oracle Application Express 18.1 to the compute node: /home/oracle/tmp
4. Go to /home/oracle/tmp directory unzip to /u01/app/oracle/product/apex/18.1.0.00.45 directory:

unzip apex_18.1_en.zip
mv /home/oracle/tmp/apex/ /u01/app/oracle/product/apex/18.1.0.00.45/

5. Final directory preview

6. Go to /u01/app/oracle/product/apex/18.1.0.00.45/ directory
7. Make sure you can connect using ORDS_PUBLIC_USER, by default will have same password like sys,system on DBaaS creation.

sqlplus /nolog 
SQL> CONNECT ORDS_PUBLIC_USER 
Password: 
Connected. 
SQL>

8. Intall APEX 18.1
• Log in to SQL Plus as the SYS user and install

ALTER SESSION SET CONTAINER=PDB1; 
@dbcsins.sql SYSAUX SYSAUX TEMP /i/18.1.0.00.45/ <ORDS_PUBLIC_USER_PASSWORD> 

• Check the installation went fine by issuing the following command (Using the same sqlplus session or at lease issuing the statement to the same container you installed APEX into)



SELECT VERSION, STATUS, SCHEMA FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';

9. Create a link to the APEX images in ORDS' docroot by executing the below command as the oracle user



ln -sfn /u01/app/oracle/product/apex/18.1.0.00.45/images/ /u01/app/oracle/product/ords/conf/ords/standalone/doc_root/i/18.1.0.00.45 



10. Configure Oracle Application Express by executing the Oracle REST Data Services (ORDS) assistant:


11. Log in to the compute node as the opc user
12. Start a root-user shell:

$ sudo -s 

13. Change to the ords directory:

# cd /var/opt/oracle/ocde/assistants/ords

14. Execute the ORDS assistant:

# ./ords -out="/var/opt/oracle/ocde/res/ords.out" -ords_action="configure_apex"

15. Restart ORDS:

# /etc/init.d/ords restart

16. Exit the root-user shell and log out of the compute node.

Reference

• Upgrading from Oracle Application Express 5.1.0 or 5.1.3 to 5.1.4 for Oracle Database 12c and Oracle Database 18c
• SR support

Implementing PDF.js on Oracle APEX

Here step by step preparation :

• Install PDF.js under image prefix apex

• Prepare Download page helper
  • Page with alias : DOWNLOAD_PDF
  • Process : before header

  declare
    r_data EMP_PDF%rowtype;
  BEGIN
    select * into r_data
      from emp_pdf
     where empno = :P4_EMPNO;
      sys.htp.init;
      sys.owa_util.mime_header( r_data.mimetype, FALSE);
      sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( r_data.pdf_data ));
      sys.htp.p('Content-Disposition: inline; filename='||r_data.filename );
      -- tell the browser to cache for one hour, adjust as necessary
      sys.htp.p('Cache-Control: max-age=3600');
      sys.owa_util.http_header_close;
      
      sys.wpg_docload.download_file(r_data.pdf_data);
      apex_application.stop_apex_engine;
  exception when others then
      sys.htp.prn('error: '||sqlerrm);
      apex_application.stop_apex_engine;
  END;
  

Case 1 : On form

• Create Static Region

• Preview

Case 2 : On Tabular form

• Install plugin : Execute PL/SQL Code and Return Content
• Preview

Query APEX Metadata out of APEX Context

For example you need to query on sql developer or execute apex_email from oracle job

example:

Select *
  from APEX_MAIL_QUEUE;

result : no row selected
Solution : Create procedure to mimic APEX context

create or replace procedure set_apex_context (
  p_app_id in number 
) as 
begin
 FOR c1 IN (
   SELECT workspace_id
    FROM apex_applications
   WHERE application_id = p_app_id
  )
 LOOP
   apex_util.set_security_group_id(p_security_group_id => c1.workspace_id);
 END LOOP;
end set_apex_context;

How to use :
before execute query run the procedure:

execute set_apex_context (140); --i40 is app id 
Select *
  from APEX_MAIL_QUEUE;

APEX - Tomcat error : java.lang.OutOfMemoryError: Java heap space solution

Have you experienced the following error when using APEX-ords in Tomcat?

• Environment : Tomcat 8 Windows 10
• Symptom : java.lang.OutOfMemoryError: Java heap space when submit grid edit or array g_f02..g_fn
• Solution : Change initial memory pool & maximum memory pool

After searching for solutions on the internet, many suggest arranging memory through catalina.bat or setenv.bat like Morten's suggestion. but Tomcat on windows cannot recognize the environment.

The solution you just need to run Tomcat8w.exe at %TOMCAT_HOME%bin, and configure the memory via the GUI and then restart Tomcat.

How to relocate datafile from one drive to other without shutdown db

1. Login to SQL*Plus
2. Connect as SYS DBA with CONNECT / AS SYSDBA command
3. Make offline the affected tablespace

ALTER TABLESPACE tablespace_name OFFLINE;

4. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:

ALTER TABLESPACE tablespace_name
RENAME DATAFILE 'fully qualified path to original data file name' 
TO 'new or original fully qualified path to new or original data file name';

5. Bring the tablespace online again

ALTER TABLESPACE tablespace_name ONLINE;

6. If you experienced this error : ORA-01113, execute this syntax:

recover datafile 'new fully qualified path to original data file name'

source : otn

Can You Build Application and Go Live in Six Months?

The answer is Yes we can, if:

We have the Operations Team with the concept of mature business processes and understand the systems approach when transform a manual process into the IT system.

You are an experienced developer and willing to adopt the best standards.

You have the end-user militant to jointly perform data entry and testing of applications, and then use it in everyday transactions.

Full support of the company's management.

I post this to share how to build an aplication with the concept of RAD (Rapid Application Develoment) can be achieved. Four factors I described above should be synergized to get there, if you are in the position of the second factor (as developers) usually have a tendency of less expensive non-technical factors, can be guaranteed not to be. As well as any best software you have if no synergy of all four of these factors it is not unattainable.

Okay, to measure the success of an app I would narrow the parameters of what is called success :

• Applications have Go Live and in use in everyday transactions replace existing manual processes
• Maintaining business continuity between the actual processes and processes used in the IT application.

Process & Team

This is an example of a case when building an application "Tailor-Made" called IP-ICOFR CSA Online, I was fortunate enough to do so by a synergy of the four factors that I mentioned earlier.

I would normally ask for a team that includes: business analyst, I as a developer and other components depending on the needs will be called "Operation Team". This team will become one entity, no longer act as lonely developer, despite the fact that I am the only developer :D, but to the unity of IT application system will be talking about all the problems and decided by this team.

For myself, I do the following:

• Provide enough time to build the Blue-print business process applications, with interview and fairly intensive discussion, do not rush to the design phase of the application until the blue-print is obtained.
• To the business analyst I ask: "Do not let me do the assumptions of a process, give me certainty"
• Do not always accept an idea without giving an alternative that is better if there is one. Because the business user perspective they should also be viewed and considered how it goes in the IT system.
• Study the business processes in depth so that we will be able to predict how these applications will evolve in the future.
• Mutual open to understanding ideas and receive feedback about how the system they will be built.

From the Operation Team: Business Analyst side

• Giving a good presentation to the management about: the staging, the process, the planned schedule, and asked for support for every phase of which will be run.
• Understand that there are stages that will be achieved requires the process to be accommodated in a certain phase, it will not necessarily have to be adopted at that time.
• All demand/request for features will be through team and filter based on: the need, and readiness phase. I relayed: "I just listen to one side, namely through Operation Team".
• Always provide certainty of process, if still doubt be delayed until absolutely sure as to what it should be.

Finally, with hard work and synergies of these four factors we succeeded in realizing a Go Live application within 6 months, which has been implemented into the entire units of PT. Indonesia Power

• Kantor Pusat (Headquarters)
• Unit Jasa Pemeliharaan
• UJP Banten 1 Suralaya
• UJP Banten 2 Labuan
• UJP Banten 3 Lontar
• UJP Banten 4 Cilegon
• UJP Pelabuhan Ratu
• UJP Adipala
• UJP Pangkalan Susu
• UPJP Priok
• UP Suralaya
• UP Saguling
• UPJP Kamojang
• UP Mrica
• UP Semarang
• UP Perak Grati
• UPJP Bali

Rundown Phase

• 01 Nopember 2015 : Kick Off Project

27-29 Januari 2016 : Workshop/UAT (Operation Team Level) - Bandung

17-19 Februari 2016 : Workshop/UAT (User Admin Level) - Semarang

11-15 April 2016 : Workshop/UAT (End User Level) - Bali

21-23 April 2016 : Go Live (Indonesia Power's Annual Financial Forum 2016) – Jogjakarta

Technology

• Oracle Application Express (APEX) 5.0.3.00.03 with ORDS Listener 3.0.2.294.08.40
• Oracle Database Enterprise Edition Release 12.1.0.2.0 - 64bit (Linux Redhat)
• Oracle WebLogic Server 12.2.1.0.0
• Oracle BI Publisher 11.1.1.6.8

Features

• Flexible Setup : BPM & Hierachy Team
• Multiplatform browser
• Responsive Web Design
• Email notification & Subscription
• Interactive Reports
• Multi format Report
• Online Video tutorial

ICOFR- Control Self Assesment References

• Internal Control
• Sarbanes–Oxley Act

How to Utilize APEX Navigation List Into an Object Security

Long time ago before APEX 5 release, i am prefer using tree as main navigation. Now APEX 5 offer us more awesome style using list as main navigation.

Ok let me show you to adopt this APEX view as authorization scheme to view/access menu base on role granted by main navigation menu which is APEX_APPLICATION_LIST_ENTRIES

1. Display APEX_APPLICATION_LIST_ENTRIES as tree
• The Query

 select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
        end as status
      , level
      , entry_text as title
      , entry_image as icon
      , list_entry_id as value
      , null as tooltip
      , null as link
   from APEX_APPLICATION_LIST_ENTRIES
  where application_name = 'APPS_NAME'
    and list_name = 'List name'
  start with list_entry_parent_id is null   
connect by 
  prior list_entry_id = list_entry_parent_id
  order siblings by display_sequence

• Use same icon as List
change this : Region Attributes >> Settings >> Icon Type : fa
• Display List as Tree

2. Use on role entry
3. Create Authorization scheme base on role
4. Use Authorization on pages, list

Can you show us the prototype as soon as possible?

Usually after business process requirement, immediately user ask me such a question "Can you show us the prototype as soon as possible? ".

With power of Oracle APEX 5 using universal theme. You can provide quickly "Yes i will deliver presentation and prototype tomorow :D

Yes we can, with Universal Theme (Vista) responsive design and style from sample database application now you can create prototyype easily.

This is why Oracle APEX claiming as RAD methodology :D

Oracle APEX Evangelist at e-35 20 Years Reunion

Twenty years ago we had such a great memories as student of Electrical Engineering Sepuluh Nopember Institute of Technology (ITS). at Sativa Hotel Trawas for 3 days we meet again as professional in wide range field of Industries : Sales, IT, HR, Telco, Entrepreneur and etc.

Mr. Taufik Ardi Nugroho as Event Organizer gives me a chance to deliver presentation in IT session : Oracle Application Express as IT Solutions and Business Opportunities.

What a moment, i will miss you guys !!!

New Development Environment: Oracle 12c, APEX 4.2.6, APEX 5, ORDS 3.0 and Tomcat 8

APEX 5 now release into production with big impact and wow features, as part of continuous learning process, now I am preparing new development environment on my laptop (Windows 8.1 SL).
Oracle Database 12c enable to create separated environment with multiple APEX version in a single db instance. APEX 4.2.6 is my recent stable applications and APEX 5 as new learning development.

Dev Environment:

• Windows 8.1 SL 64bit
• OracleDatabase 12c Enterprise Edition Release
• OracleREST Data Services 3.0.0.65.09.31
• ApacheTomcat 8.0.21
• JavaJDK 1.8.0.40
• Oracle SQL Developer 4.1 EA2 (4.1.0.18.37)

Scenario:

• APEX 4.2.6 on PDB426
• APEX 5 on PDB500

Installation

• Create 2 PDB : PDB426 and PDB500
• Uninstall apex on CDB
• Install APEX 4.2.6 on PDB426 with images /i/
• Install APEX 5 on PDB500 with images /i5/
• Setup ORDS as APEX4 for APEX 4.2.6 container => change ords.war to apex4.war
• Setup ORDS as APEX5 for APEX 5 container => change ords.war to apex5.war

ORDS installation Tomcat 8 :

• Unzip Ords into temp directory : C:\temp\apex4 and C:\temp\apex5

• Create separated config directory for each apex version : C:\Tomcat\apex4-config and C:\Tomcat\apex5-config.

• Install ords for each version
• Copy apex4.war and apex5.war to \tomcat-home\webapps

• Copy APEX images to  \tomcat-home\webapps\ROOT don't forget to change images into i and i5 folder

• Run Tomcat and enjoy :D

Now you have 2 APEX version in single instance

• APEX 4.2.6 : http://localhost:8080/apex4/f?p=4550:1

• APEX 5 : http://localhost:8080/apex5/f?p=4550:1

How to use modal pop-up region

APEX telah menyediakan banyak contoh applikasi, salah satunya adalah Sample Dialog. Disitu kita akan belajar tentang bagaimana memakai dialog dengan teknik jQuery UI atau Custom Modal region untuk theme 25.

Namun untuk kasus modal region kita akan lihat bahwa modal dialog default adalah false. 

oh ya..saran saya jika kita memakai Theme 25 untuk membangun applikasi lebih baik kita bangun dialog dengan memakai region modal jangan memakai UI dialog biasa untuk pertimbangan estetika tampilan yg lebih oke. Untuk membuat modal dialog menjadi "true" maka tambahkan kode berikut saat memanggil regionnya.

function(){
  openModal('addDept');
  gBackground.off('click');
}

Untuk pemakaian dalam form process pop-up, nanti saya akan posting dengan kombinasi iFrame.

Note : Terima kasih buat bro Hoki yang telah men-debug dan mencarikan solusinya.

APEX 5 Tree Enhancement

APEX Early Adopter 3 telah release, kemaren telah terdaftar dan mencoba nya. Banyak sekali peningkatan yang signifikan dalam versi ini, yang paling ingin saya coba adalah universal theme.

Berikut beberapa ujicoba yang telah saya lakukan.

• Mencoba implementasi jstree terbaru ke dalam APEX. dalam hal ini kita memperoleh feature
• APEX dapat menerima file zip library dan otomatis di unzip kedalam Static Workspace Files.

• Kita tidak perlu merubah referensi apapun di file CSS, tinggal pakai seperti halnya kita melakukan zip di file explorer.

• Mencoba APEX tree.
• Ada jenis tree baru yaitu : APEX Tree

• APEX tree bisa memakai custom vector icon seperti font-awesome

• Contoh query

 select case 
          when connect_by_isleaf = 1 then 0
          when level = 1             then 1
          else                           -1
        end as status 
      , level
      , m.description as title
      , case 
          when m.status = 'X' then 'fa-home fa-lg'
          when m.status = 'M' then 'fa-folder-open-o fa-lg'
          when m.status = 'F' then 'fa-book fa-lg'
          when m.status = 'R' then 'fa-table fa-lg'
          when m.status = 'T' then 'fa-tasks fa-lg'
          when m.status = 'C' then 'fa-bar-chart fa-lg'
          when m.status = 'S' then 'fa-cubes fa-lg'
        end as icon
      , m.id as value
      , m.tooltip as tooltip
      , 'f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.::::P3_ID,P3_SELECTED:'||m.id||','||m.id as link 
   from my_menu m
  start with m.status = 'X'
connect by prior m.id = m.parent_id
  order siblings by m.SEQ

APEX AJAX and how to use apex.server.process

Pada kasus pemkaian dynamic biasanya kita telah terbiasa memakai fitur dari APEX yaitu Dynamic Action (DA). DA merupakan salah satu fitur terbaik yang dimiliki oleh APEX untuk melakukan proses AJAX dan hal lain dengan secara declarative dan bisa di gabunkan dengan kondisi dan sekuriti. Namun pada kasus-kasus khusus  nanti kita tetap perlu untuk membangun AJAX secara manual tanpa atau dikombinasikan dengan DA.

APEX telah menyediakan API untuk hal ini, namun sayang sangat ringkas sehingga perlu try-error untuk memahaminya. Tulisan Tom Petrus sangat mencerahkan dan detail untuk memahami hal ini. saya sangat merekomendasikan untuk membacanya untuk memahami gambaran besar APEX AJAX. 

Adapun hal yang harus diperhatikan adalah APEX AJAX adalah :

• Gunakan documented API dalam hal ini (apex.server.process) untuk pembuatan proses AJAX supaya dalam perkembangannya jika aplikasi di migrasi ke versi yang terbaru tidak muncul masalah.
• Dalam apex.server.process return default adalah JSON, jadi hati-hati karena meskipun kita telah membangun data dengan konstruksi JSON jika dilakukan lewat PL/SQL maka akan selalu menjadi string atau text, jadi untuk proses ini salalu gunakan opsi = dataType: "text". Setelah itu dalam proses JS kita bisa parsing kedalam JSON object memakai jQuery.parseJSON(pData)
• Opsi = success agak membingunkan sering diartikan bahwa disitu untuk hadle proses sukses saja, namun sebenarnya adalah untuk funsi callback yang dilakukan setelah memperoleh return data dari proses pl/sql.
• Cara melakukan passing parameter, item, value dan array.

Silahkan akses contoh pemakaiannya di demo ini disitu akan diperlihatkan contoh code javascript dan pl/sql yang digunakan. Selamat mencoba.

Miscellaneous Javascript APIs

• $x(pNd)
Adalah sebuah padanan dari getElementById(). Dengan pNd adalah id dari DOM atau string "id". returnnya adalah DOM element atau false jika tidak ada.
contoh : $x(P43_TEXT) atau $x("P43_TEXT").
Pemakaian untuk get: $x("P43_TEXT").value, $x("P43_TEXT").name
Pemakaian untuk set: $x("P43_TEXT").value="Test", $x("P43_TEXT").name="test"
Namun tidak semua jenis item dapat dilakukan operasi tersebut seperti checkbox, selectlist dan shuttle.
• $v(pNd), $v2(pNd), apex.item(pNd).getValue(),$f_SelectValue(pNd) dan $("#pNd").val()
Semuanya pada dasarnya adalah untuk mendapatkan value dari sebuah item, namun memiliki beberapa perbedaan. Pada test kasus item berikut :
• Hidden, text, text area, date picker, popup lov (basis input) semua berhasil memperoleh value, khusus untuk display only $("#pNd").val() memakai $("#pNd").text()
• selectlist, checkbox, radiogroup, list manager hanya $v(pNd) $v2(pNd) apex.item(pNd).getValue() dan $f_SelectValue(pNd) yang bisa memperoleh value
• $v2(pNd) adalah shortcut untuk apex.item(pNd).getValue()
• untuk kasus item multiple value perbedaan dari $v(pNd) dan $v2(pNd) adalah $v(pNd) mendapatkan nilai persis string dari item sedangkan $v2(pNd) dalam bentuk array
Kesimpulan yang dapat diambil adalah selalu gunakan APEX APIs $v(pNd) atau $v2(pNd) untuk mengambil nilai item.
• $s(pNd, pValue, pDisplayValue, pSuppressChangeEvent), apex.item( pNd ).setValue(pValue, pDisplayValue, pSuppressChangeEvent), $x_Value(pNd,pValue) dan $("#pNd").val("pValue")
Sama halnya dengan kasus get selalu gunakan APEX APIs, $s(pNd, pValue, pDisplayValue, pSuppressChangeEvent) adalah shortcut untuk apex.item( pNd ).setValue(pValue, pDisplayValue, pSuppressChangeEvent). Kelebihan dari $s( pNd,pValue, pDisplayValue, pSuppressChangeEvent) adlah kita bisa melakukan pada semua jenis item dengan bisa mengatur pSuppressChangeEvent property ini berguna dalam urusan Dynamic Actions
• Silahkan coba fungsi2 diatas pada page demo berikut

Get Yahoo Exchange Rate using PL/SQL

Setelah mencari api yang disediakan oleh Bank Indonesia atau bank-bank swasta/nasional indonesia tidak tersedia. maka dengan terpaksa kebutuhan exchange rate real time saya ambil dari yahoo api.

Melalui Yahoo Query Language kita dapat peroleh URI untuk exchange rate USD-IDR :

http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.xchange where pair in ("USDIDR")&env=store://datatables.org/alltableswithkeys

Lalu kemudian kita bangun fungsi didalam oracle untuk bisa dipakai dalam operasi sql pl/sql atau di APEX.

create or replace function get_yahoo_rate 
return number 
is

/******************************************************************************
   NAME:       get_yahoo_rate
   PURPOSE:    get latest USD-IDR rate from Yahoo finance exchange

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        10/10/2014   Lutfi Hedir     1. Created this function.

******************************************************************************/
  l_xml xmltype;
  l_yahoo_uri constant varchar2(500) := 'http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20(%22USDIDR%22)&env=store://datatables.org/alltableswithkeys';
  l_rate      varchar2(500);
  l_rate_n    number(10);
begin
  -- get xml data
  l_xml := xmltype(utl_http.request(l_yahoo_uri));
  -- extract rate value from xml data  
  select extractvalue(l_xml, '/query/results/rate/Rate/text()') into l_rate
    from dual;  
  -- convert to number   
  l_rate_n := to_number(REGEXP_SUBSTR(l_rate,'[0-9]+'));
  
  return l_rate_n;
  
end get_yahoo_rate;
/

Catatan :

• Jangan lupa setting ACL untuk 11g supaya bisaakses internet.
• Jika akses SSL buatlah wallet terlebih dahulu
• Jika akses proxy modifikasi utl_http.request dengan signature proxy dan wallet