Oracle: Generating XML Using XMLFOREST

XMLForest produces forest of XML elements from its arguments, which are expressions to be evaluated, with optional aliases.
Each of the value expressions is converted to XML.

Syntax:      XMLFOREST ( value_expr AS c_alias)

To understand more about XMLForest in a clear way, Let us see one simple example to create your XML document using a SQL query that could give you some sense to it.

Following example creates a XML document per each employee record without using XMLFOREST:
NOTE: Casting to XMLTYPE is optional here:

SELECT XMLTYPE
 (XMLELEMENT ("ROOT",
         XMLELEMENT ("REC",
         XMLELEMENT ("EMPNO", empno),
         XMLELEMENT ("ENAME", ename),
         XMLELEMENT ("JOB", job)
             )
 ).getclobval () ) AS "RESULT"
FROM EMP
WHERE ROWNUM<3

Output:
Row1:

<ROOT>

<REC >

<EMPNO>7369</EMPNO>

<ENAME>SMITH</ENAME>

<JOB>CLERK</JOB>

</REC>

</ROOT>

Row2:

<ROOT>

<REC EMPNO="7499">

<EMPNO>7499</EMPNO>

<ENAME>ALLEN</ENAME>

<JOB>SALESMAN</JOB>

</REC>

</ROOT>

Achieve the same above result by simply using XMLFOREST: Instead of creating XMLELEMENT for each coulmn you could simply use XMLFOREST to do the job.

SELECT XMLElement("ROOT",
       XMLForest(empno, ename, job, mgr, hiredate, sal, comm, deptno)
   ) AS "RESULT"
FROM EMP 

Output:
Row1:

<ROOT>

<EMPNO>7369</EMPNO>

<ENAME>SMITH</ENAME>

<JOB>CLERK</JOB>

<MGR>7902</MGR>

<HIREDATE>1980-12-17</HIREDATE>

<SAL>800</SAL>

<DEPTNO>20</DEPTNO>

</ROOT>

Row2:

<ROOT>

<EMPNO>7499</EMPNO>

<ENAME>ALLEN</ENAME>

<JOB>SALESMAN</JOB>

<MGR>7698</MGR>

<HIREDATE>1981-02-20</HIREDATE>

<SAL>1600</SAL>

<COMM>300</COMM>

<DEPTNO>30</DEPTNO>

</ROOT>

An alternate Query by using column alias:
SELECT XMLELEMENT ("ROOT",
       XMLFOREST (empno AS "EMP",
                  ename AS "NAME",
                  job AS "JOB",
                  mgr AS "MANAGER",
                  hiredate AS "HIRE_DATE",
                  sal AS "SALARY",
                  comm AS "COMMISSION",
                  deptno AS "DEPR_NO"
                 ) ) AS "RESULT"
FROM emp
WHERE ROWNUM < 3

Oracle: Generating XML Using DBMS_XMLGEN

Package DBMS_XMLGEN now supports hierarchical queries. PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results.
It retrieves an XML document as a CLOB or XMLType value.

Converts the query results from the passed in SQL query string to XML format, and returns the XML as a CLOB.

Syntax:
FUNCTION DBMS_XMLGEN.getXML
(
     sqlQuery IN VARCHAR2,
     dtdOrSchema IN NUMBER := NONE
) RETURN CLOB;

Converts the query results from the passed in SQL query string to XML format, and returns the XML as a XMLTYPE.

 Syntax:
FUNCTION DBMS_XMLGEN.getXMLType
(
     sqlQuery IN VARCHAR2,
     dtdOrSchema IN NUMBER := NONE
) RETURN XMLType;

Package DBMS_XMLGEN also provides options for changing tag names for ROW, ROWSET, and so on.
setRowTag()         : Sets the name of the element separating all the rows. The default name is ROW.
setRowSetTag()     : Sets the name of the document root element. The default name is ROWSET

Example1:
SELECT dbms_xmlgen.getxml('select * from emp where rownum<6') data FROM dual --RETURNS CLOB

Example2:
SELECT dbms_xmlgen.getxmltype ('select * from emp where rownum<6') data FROM dual --RETURNS XMLTYPE

Output:
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-Dec-1980</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>20-Feb-1981</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
</ROWSET>

For More Info : DBMS_XMLGEN

Oracle: Bind Variables

What are Bind Variables?

Bind variables are so called SQL query performance improvement catalysts. The simple definition of a Bind variable is "Passing a value by reference". Bind variables are 'substitution' variables that are used in place of literals.
Using bind variable is your dynamic queries could potentially improve your SQL query performance by reusing the execution plan that the statement is previously used.

NOTE: Important thing to remember is that you can't substitute object names (tables, views, columns etc) with bind variables. You could only substitute literals with the bind variables.

NOTE: "Using Duplicate Placeholders":
Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. So, if the same placeholder appears two or more times in the SQL statement, each appearance must correspond to a bind argument in the USING clause.

For example: following query is defined using the identical names to bind variables, but all the placeholder are being replaced with a proper value.


DECALRE
v_empno number := 100;
v_name varchar2(100) := 'srinivas sreeramoju';
v_sal     number := 5000;
BEGIN
v_sql := 'INSERT INTO emp (empno,fname,lname,sal) VALUES (:x, :y, :y, :x)';

EXECUTE IMMEDIATE v_sql USING v_empno, v_name, v_name, v_sal;
END;

How will they work?

Just analyze the following example:
SELECT f_name, l_name, sal FROM customers WHERE empno = 100;
SELECT f_name, l_name, sal FROM customers WHERE empno = 101;
SELECT f_name, l_name, sal FROM customers WHERE empno = 102;
SELECT f_name, l_name, sal FROM customers WHERE empno = 103;

Each time the query is submitted, Oracle first checks for a matching statement in the shared pool. if found, the execution plan that this statement previously used is retrieved, and the SQL is executed.

If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed.

In the above example, the "empno" column predicate value changes to each query, so you'll never get a match, and every statement you submit will need to be hard parsed.

So the best way to get Oracle to reuse the execution plans for these statements is to use bind variables.

SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;

Oracle: Creating Nested PL/SQL Subprograms

You can declare subprograms in any PL/SQL block.
Subprograms must go at the end of the declarative section (i.e. between IS and BEGIN statements)
The Scope of Subprogram is always within the main Program.

FUNCTION GROSSPAY (input NUMBER) RETURN NUMBER
IS
iBonus NUMBER;

/**BEGIN : Local program units **
The Scope of CalcBonus function is within the GROSSPAY function.
It cannot be directly accessed outside of GROSSPAY function.
NOTE : All the nested function must be created within IS and BEGIN statements.
*/
FUNCTION CalcBonus RETURN NUMBER; --Optional declarative section

FUNCTION CalcBonus RETURN NUMBER
IS
dBonus NUMBER ;
BEGINCASE
  WHEN input BETWEEN 2000 and 4999 THEN
   dBonus := (input * 10)/100;
  WHEN input BETWEEN 5000 and 6999 THEN
   dBonus := (input * 15)/100;
  WHEN input BETWEEN 7000 and 1000 THEN
   dBonus := (input * 20)/100;
  WHEN input > 7000 THEN
   dBonus := (input * 25)/100;
END CASE;
RETURN dbonus;
END CalcBonus; /**END : Local program units ***/
BEGIN
   iBonus := input + CalcBonus ; RETURN iBonus;END GrossPay;

Oracle: How to read UNIX ASCII file contents with a SQL Query

Following example illustrate the way to read the UNIX system ASCII files with a Single SQL Query.
With the function, now you do not have hazels to login to your UNIX box to view the contents of your ASCII log files.

Create the following function and compile it. Then use the following SQL query to read your log file.

SQL> : SELECT read_file(‘/home/export/auditlog.txt’,'DIR_ALIAS’) AS data FROM DUAL;

NOTE: Return value would be a CLOB datatype. use TO_CHAR to cnvert CLOB data to a VARCHAR2 type.

CREATE FUNCTION read_file (pFileName VARCHAR2 , pDirAlias VARCHAR2) RETURN CLOB
IS
oBFile BFILE;
oCLob CLOB;
v_file_exists NUMBER;
bValue BOOLEAN := FALSE;
v_error VARCHAR2(4000);

BEGIN
-- Purpose: Reads the UNIX Log files
-- SRINIVAS Sreeramoju 02/21/2010 Initial Creation
-- --------- ------ ------------------------------------------
DBMS_LOB.CREATETEMPORARY(oCLob,true);
oBFile := BFILENAME(pDirAlias,pFileName);
v_file_exists := DBMS_LOB.fileexists(oBFile);

IF v_file_exists = 1 THEN
-- Open the file
  DBMS_LOB.fileOpen(oBFile,DBMS_LOB.file_readonly);
 DBMS_LOB.loadFromFile
  (  dest_lob => oCLob,
     src_lob => oBFile,
     amount => DBMS_LOB.getLength(oBFile)
 );

IF DBMS_LOB.ISOPEN(oBFile) = 1 THEN
  DBMS_LOB.fileclose(oBFile); --Close file
END IF ;

IF oCLob IS NULL THEN
  RETURN ' ';
ELSE
  RETURN oCLob;
END IF;

END IF;

EXCEPTION

WHEN others THEN
   v_error := SQLCODE '-' SQLERRM ;
   DBMS_OUTPUT.Put_Line( v_error );

END read_file;

Oracle: How to Invoke a Java class in PL/SQL

The advantage of using Java class for accessing systems files is for richer set of file IO Capabilities.
Oracle's UTL_FILE package provides a limited functionality for accessing system files.
However, Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on.

The following example illustrates some simple steps to invoke a Java class in Oracle PL/SQL.

Objective of this example is to get list of all files under a specified directory and insert the data into an Oracle Table.

Step 1#. Create a Java Class in Oracle which could access system files.--Following Java class is used to read through a directory for Files
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "javaReadFolder"AS import java.io.*;
import java.sql.*;

  public static void getList(String pdirectory)
  throws SQLException{
--pdirectory : Full physical path of the directory
File path = new File( pdirectory );
String[] list = path.list();
String element;
int jobrunid;
jobrunid = 0;
--EXECUTE a SQL statement: Get runid
#sql { SELECT seq_job_runid.NEXTVAL into :jobrunid from dual};
--Loops through all the files from the specified directory and insert the file rows in the table.

for(int i = 0; i < list.length; i++)
{
  element = list[i];
  --EXECUTE a SQL statement
  #sql { INSERT INTO tb_load_files (runid, filename,processed)
  VALUES (:jobrunid, :element,'Y') };
}

}
}
/
Using a Java stored procedure it is possible to manipulate operating system files from PL/SQL: Get the Java File Handling class :
http://www.oracle-base.com/articles/8i/FileHandlingFromPLSQL.php

Step 2#. Create Oracle PL/SQL Procedure wrapper which could invoke the Java Class
PROCEDURE GET_DIR_LIST( p_directory in VARCHAR2) AS LANGUAGE JAVA
NAME 'javaReadFolder.getList(java.lang.String)' ;

Step 3#. Execute the Oracle Proceduer
EXEC GET_DIR_LIST('/home/xml/export')public class javaReadFolder
{

Oracle: Creating a XML File

Following example illustrate a simple way to create a XML file on your UNIX box.
The UTL_FILE package can be used to read or write file from operating system. The UTL_FILE package has different subprograms which will help to read and write file from/to OS. The first and foremost step is to create a Directory path for your XML files folder. Follow the steps to create a directory alias.

CREATE
DIRECTORY:

In Oracle, the list of accessible directories as will be configured in ALL_DIRECTORIES view. Together, the file location and name must represent a legal filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also accessible; it too must be specified using a complete path name matching an ALL_DIRECTORIES object.
CREATE DIRECTORY "XML_DIR_ALIAS" AS '/xml/export';
GRANT READ ON DIRECTORY "XML_DIR_ALIAS" TO <USER>;


Note that XML_DIR_ALIAS name is the alias of the physically existing directory of '/xml/export'. So there must exists export directory. And to create directory the user must have DBA role or create directory privilege.





CREATE OR REPLACE PROCEDURE CreateXMLFile

IS

v_filename VARCHAR2(255);

v_xml_file UTL_FILE.file_type;

v_emp_no VARCHAR2(255);

v_fname VARCHAR2(255);

v_lname VARCHAR2(255);

v_sal NUMBER ;

v_dob DATE ;



CURSOR emp_cursor is
SELECT t.emp_no, t.fname, t.lname, t.sal , t.dob

FROM emp t;

BEGIN

v_filename := TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.xml';

v_xml_file := UTL_FILE.fopen('XML_DIR_ALIAS', v_filename, 'W'); --XML_DIR_ALIAS is a Directory alias


--Add Version Tag

UTL_FILE.put_line(v_xml_file, '<?xml version="1.0" encoding="UTF-8"?>');



--Add top level node

UTL_FILE.put_line(v_xml_file, '<EMP>');



--Open the EMP cursor

OPEN emp_cursor;



--Loop through the emp cursor.

LOOP
FETCH emp_cursor

INTO v_emp_no, v_fname, v_lname, v_sal , v_dob;



EXIT WHEN emp_cursor%NOTFOUND;



UTL_FILE.put_line(v_xml_file, ' <EMP_RECORD >');

UTL_FILE.put_line(v_xml_file, ' <EMP_NO>' || v_emp_no || '</EMP_NO>');

UTL_FILE.put_line(v_xml_file, ' <FIRST_NAME>' || v_fname || '</FIRST_NAME>');

UTL_FILE.put_line(v_xml_file, ' <LAST_NAME>' || v_order_mode || '</LAST_NAME>');

UTL_FILE.put_line(v_xml_file, ' <SALARY>' || v_order_total || '</SALARY>');

UTL_FILE.put_line(v_xml_file, ' <DOB>' || TO_CHAR (v_dob,'YYYY-MM-DD') || '</DOB>');

UTL_FILE.put_line(v_xml_file, ' </EMP_ RECORD>');

END LOOP

--Close the EMP cursor

CLOSE emp_cursor;



UTL_FILE.put_line(v_xml_file, '</EMP>');

UTL_FILE.fclose(v_xml_file);



EXCEPTION
WHEN OTHERS THEN

raise_application_error(-22300,'Filed to Open :' || v_filename ||', Error:' || sqlcode ||':' || sqlerrm);
END CreateXMLFile;

UTL_FILE Package Exceptions:

Exception Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The requested file rename operation failed.

Oracle: SPLIT Function

There is NO direct SPLIT function Exists in Oracle as of today. You have to create your own custom split function in order to achieve your goal.
There are many ways you could create your own custom SPLIT function depending on your requirement.
An advantage of returning a TABLE Collection object is you could directly reference the function in your SQL query as a TABLE.


Following example illustrate a simple customized SPLIT function which returns TABLE Collection object:

--Create a TABLE Collection Object
CREATE TYPE tbl_array AS table OF VARCHAR2(32000);
/
--Create a SPLIT Fucntion which return Table Collection
CREATE OR REPLACE FUNCTION SPLIT(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
RETURN tbl_array PIPELINED PARALLEL_ENABLE
AS

v_cnt NUMBER ;
idx NUMBER ;
v_string VARCHAR2(32000);
v_start NUMBER := 0;
v_end NUMBER := 0;

BEGIN

-- Get Number of occurrences
v_cnt := LENGTH(p_string) - LENGTH(REPLACE(p_string,p_delimiter,'')) ;
FOR idx IN 1..v_cnt LOOP
v_end := INSTR(p_string,p_delimiter,1, idx);
v_string := SUBSTR (p_string, v_start + 1 , v_end - v_start - 1);
v_start := v_end ;
PIPE ROW(TO_CHAR(v_string));
END LOOP;

--Last split
v_string := SUBSTR (p_string, - (LENGTH(p_string) - v_end));
PIPE ROW(TRIM(v_string));

RETURN;
END SPLIT
/
--Testing
SELECT * FROM TABLE(SPLIT('Sriniavs,Sreeramoju,New York,USA'));
/

Another example illustrates a simple customized SPLIT query:

WITH tbl_split AS
(SELECT 'Sriniavs,Sreeramoju,New York,USA' val from dual )
SELECT TO_CHAR( SUBSTR (val, (DECODE (LEVEL, 1, 0, INSTR (val, ',', 1, LEVEL - 1)) + 1),
(DECODE (INSTR (val, ',', 1, LEVEL) - 1,-1, LENGTH (val),INSTR (val, ',', 1, LEVEL) - 1))
- (DECODE (LEVEL, 1, 0, INSTR (val, ',', 1, LEVEL - 1)) + 1)+ 1
)) a
FROM tbl_split
CONNECT BY LEVEL <=
(SELECT (LENGTH (val) - LENGTH (REPLACE (val, ',', NULL)))
FROM tbl_split) + 1


/
Another Example :

create or replace type myTableType as table of Varchar2(255);


create or replace function str2tbl
      (p_str in varchar2,
       p_delim in varchar2 default '.')    return myTableType
as
l_str  long default p_str || p_delim;
l_n number;
l_data myTableType := myTabletype();

begin
loop
     l_n := instr( l_str, p_delim );
     exit when (nvl(l_n,0) = 0);
     l_data.extend;
     l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
     l_str := substr( l_str, l_n+length(p_delim) );
end loop;

return l_data;
end;

SELECT * FROM TABLE (CAST (str2tbl ('10.01.03.04.234') AS mytabletype));

Oracle: Consuming a .NET Web Service

UTL_HTTP allows your PL/SQL program to consume a web service in a simplest way.

UTL_HTTP.request:The simplest way to call UTL_HTTP.request is to send a URL and get back the page.
As long as the page was 2,000 bytes or less, this would work well.

Syntax : UTL_HTTP.request(url,proxy,wallet_path,wallet_password);

DECLARE
v_pagecontent LONG;l_return LONG;_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);
iCnt NUMBER := 1;
BEGIN

v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';
--Add the parametersv_url := v_url || '?param1='|| p_param1 ||'&param2='|| p_param2 ||'';
--Call the WebServicev_pagecontent := UTL_HTTP.request( v_url );
LOOP l_return := SUBSTR( v_pagecontent, iCnt, 255 )
DBMS_OUTPUT.put_line( l_return);
iCnt = iCnt + 255
EXIT WHEN l_return IS NULL OR l_return = ' ' ;
END LOOP ; 
EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;

UTL_HTTP.request_pieces
For larger pages, we need to use REQUEST_PIECES as follows which returns Array of UTL_HTTP.HTML_PIECES.

Syntax : UTL_HTTP.request_pieces(url,max_pieces,proxy,wallet_path,wallet_password)

DECLARE
pieces UTL_HTTP.HTML_PIECES;
v_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);

 BEGIN
v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';--Add the parameters
v_url := v_url || '?param1='|| p_param1 ||'&param2='|| p_param2 ||'';

--Call the WebService
pieces := UTL_HTTP.REQUEST_PIECES (v_url);

FOR I IN 1 .. pieces.COUNT
LOOPDBMS_OUTPUT.put_line(SUBSTR ('Value of pieces=' || pieces (I), 1, 255));END LOOP;
EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;

UTL_HTTP.begin_request

DECLARE
v_url VARCHAR2(2000);
p_param1 VARCHAR2(2000);
p_param2 VARCHAR2(2000);
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
BEGIN
v_url := 'http://YourWebsite.com/YourWebservice.asmx/WebserviceMethod';
--Add the parameters
v_url := v_url || '?param1='|| p_param1 ||'&param2='|| p_param2 ||'';

--Call the WebService
http_req := UTL_HTTP.begin_request(v_url, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
UTL_HTTP.end_request(http_req);

--Response from WebService
http_resp := UTL_HTTP.get_response(http_req);
DBMS_OUTPUT.put_line('Response Received');
DBMS_OUTPUT.put_line('--------------------------');
DBMS_OUTPUT.put_line ( 'Status code: ' || http_resp.status_code );
DBMS_OUTPUT.put_line ( 'Reason phrase: ' || http_resp.reason_phrase );
UTL_HTTP.end_response(http_resp);

EXCEPTION
WHEN Utl_Http.request_failed THEN
DBMS_OUTPUT.put_line ('Request_Failed: ' || Utl_Http.get_detailed_sqlerrm);
WHEN Utl_Http.http_server_error THEN
DBMS_OUTPUT.put_line ('Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm);
END ;

---
Otherway of Consuming a Web Service in Oracle :

Source : http://www.oracle-base.com/articles/10g/utl_dbws10g.php

In Oracle 10g the UTL_DBWS package is loaded by default. In Oracle9i the package must be loaded using the specification and body provided in the zip file.

The function below uses the UTL_DBWS package to access a web services from PL/SQL. The URL of the WDSL file describing the web service is shown here (http://www.oracle-base.com/webservices/server.php?wsdl). The web service accepts two number parameters and returns the sum of those values.

CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER,

                                        p_int_2 IN NUMBER)

  RETURN NUMBER

AS

  l_service          UTL_DBWS.service;

  l_call             UTL_DBWS.call;

  l_wsdl_url         VARCHAR2(32767);

  l_namespace        VARCHAR2(32767);

  l_service_qname    UTL_DBWS.qname;

  l_port_qname       UTL_DBWS.qname;

  l_operation_qname  UTL_DBWS.qname;

  l_xmltype_in       SYS.XMLTYPE;

  l_xmltype_out      SYS.XMLTYPE;

  l_return           NUMBER;

BEGIN

  l_wsdl_url        := 'http://www.oracle-base.com/webservices/server.php?wsdl';

  l_namespace       := 'http://www.oracle-base.com/webservices/';

  l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'Calculator');

  l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'CalculatorPort');

  l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ws_add');

  l_service := UTL_DBWS.create_service (

    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),

    service_name           => l_service_qname);

  l_call := UTL_DBWS.create_call (

    service_handle => l_service,

    port_name      => l_port_qname,

    operation_name => l_operation_qname);

  l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>

    <ws_add xmlns="' || l_namespace || '">

      <int1>' || p_int_1 || '</int1>

      <int2>' || p_int_2 || '</int2>

    </ws_add>');

  l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,

                                   request     => l_xmltype_in);

  UTL_DBWS.release_call (call_handle => l_call);

  UTL_DBWS.release_service (service_handle => l_service);

  l_return := l_xmltype_out.extract('//return/text()').getNumberVal();

  RETURN l_return;

END;

/

The output below shows the function in action.

SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)

----------------

               6

SQL>

SELECT add_numbers(10, 15) FROM dual;

ADD_NUMBERS(10,15)

------------------

                25

SQL>