Tired of typing to_char(datefield,'DD-MM-YYYY HH24:MI:SS') when you want to display the time component of the DATE field?
Tools|Preferences|Database|NLS
Change Date Format to: DD-MON-RR HH24:MI:SS
This is an example of the use of the CURSOR function within a Query Web Service call to generate subnodes in de XML output. I stumbled on this link looking into the DBMS_XMLGEN functionality. So let’s see wat that does within a Query Web Service Call.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="SQL">
<![CDATA[
select department_id, department_name,
cursor(select first_name, last_name
from employees e
where e.department_id = d.department_id) emps
from departments d
]]>
</oraw:query_text>
<oraw:pretty_print>false</oraw:pretty_print>
<oraw:rowset_tag>DEPARTMENTS</oraw:rowset_tag>
<oraw:row_tag>DEPARTMENT</oraw:row_tag>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<DEPARTMENTS>
<DEPARTMENT>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<EMPS>
<EMPS_ROW>
<FIRST_NAME>Jennifer</FIRST_NAME>
<LAST_NAME>Whalen</LAST_NAME>
</EMPS_ROW>
</EMPS>
</DEPARTMENT>
<DEPARTMENT>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<EMPS>
<EMPS_ROW>
<FIRST_NAME>Michael</FIRST_NAME>
<LAST_NAME>Hartstein</LAST_NAME>
</EMPS_ROW>
<EMPS_ROW>
<FIRST_NAME>Pat</FIRST_NAME>
<LAST_NAME>Fay</LAST_NAME>
</EMPS_ROW>
</EMPS>
</DEPARTMENT>
<DEPARTMENT>
<DEPARTMENT_ID>30</DEPARTMENT_ID>
<DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>
<EMPS>
<EMPS_ROW>
<FIRST_NAME>Den</FIRST_NAME>
<LAST_NAME>Raphaely</LAST_NAME>
</EMPS_ROW>
<EMPS_ROW>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Khoo</LAST_NAME>
</EMPS_ROW>
<EMPS_ROW>
<FIRST_NAME>Shelli</FIRST_NAME>
<LAST_NAME>Baida</LAST_NAME>
</EMPS_ROW>
<EMPS_ROW>
<FIRST_NAME>Sigal</FIRST_NAME>
<LAST_NAME>Tobias</LAST_NAME>
</EMPS_ROW>
<EMPS_ROW>
<FIRST_NAME>Guy</FIRST_NAME>
<LAST_NAME>Himuro</LAST_NAME>
</EMPS_ROW>
<EMPS_ROW>
<FIRST_NAME>Karen</FIRST_NAME>
<LAST_NAME>Colmenares</LAST_NAME>
</EMPS_ROW>
</EMPS>
</DEPARTMENT>
</DEPARTMENTS>
</queryOut>
</soap:Body>
</soap:Envelope> |
Now that’s pretty cool.
The query_text part of the XML Schema for Database Queries to be processed by web service contains the text of the query. Attribute type specifies the type of your query: either SQL or XQUERY.
I’m going to give an example of an XQUERY using relational data.
XQuery Expressions
XQuery expressions are case-sensitive. The expressions include the following:
primary expression – literal, variable, or function application. A variable name starts with a dollar-sign ($) – for example, $foo. Literals include numerals, strings, and character or entity references.
XPath expression – Any XPath expression. The developing XPath 2.0 standard will be a subset of XQuery. XPath 1.0 is currently a subset, although XQuery uses a richer type system.
FLWOR expression – The most important XQuery expression, composed of the following, in order, from which FLWOR takes its name: for, let, where , order by, return.
XQuery sequence – The comma (,) constructor creates sequences. Sequence-manipulating functions such as union and intersect are also available. All XQuery sequences are effectively flat: a nested sequence is treated as its flattened equivalent. Thus, for instance, (1, 2, (3, 4, (5), 6), 7) is treated as (1, 2, 3, 4, 5, 6, 7). A singleton sequence, such as (42), acts the same in most XQuery contexts as does its single item, 42. Remember that the result of any XQuery expression is a sequence.
Direct (literal) constructions – XML element and attribute syntax automatically constructs elements and attributes: what you see is what you get. For example, the XQuery expression 33 constructs the XML element 33.
Computed (dynamic) constructions – You can construct XML data at runtime using computed values. For example, the following XQuery expression constructs this XML data:
In this example, element foo is a direct construction; the other constructions are computed. In practice, the arguments to computed constructors are not literals (such as toto and “tata”), but expressions to be evaluated (such as 2+3). Both the name and the value arguments of an element or attribute constructor can be computed. Braces ({, }) are used to mark off an XQuery expression to be evaluated.
Conditional expression – As usual, but remember that each part of the expression is itself an arbitrary expression. For instance, in this conditional expression, each of these subexpressions can be any XQuery expression: something, somethingElse, expression1, and expression2.
if (something < somethingElse) then expression1 else expression2
Arithmetic, relational expression – As usual, but remember that each relational expression returns a (BooleanFoot 1 ) value. Examples:
2 + 3
42 < $a + 5
(1, 4) = (1, 2)
5 > 3 eq true()
Quantifier expression – Universal (every) and existential (some) quantifier functions provide shortcuts to using a FLWOR expression in some cases. Examples:
every $foo in doc(”bar.xml”)//Whatever satisfies $foo/@bar > 42
some $toto in (42, 5), $titi in (”xyz12″, “abc”, 5) satisfies $toto = $titi
Regular expression – XQuery regexes are based on XML Schema 1.0 and Perl. (See Support for XQuery Functions and Operators.)
Type expression – An XQuery expression that represents an XQuery type. Examples: item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string.Foot 2
Type expressions can have occurrence indicators: ? (optional: zero or one), * (zero or more), + (one or more). Examples: document-node(element())*, item()+, attribute()?.
XQuery also provides operators for working with types. These include cast as, castable as, treat as, instance of, typeswitch, and validate. For example, “42″ cast as xs:integer is an expression whose value is the integer 2. (It is not, strictly speaking, a type expression, because its value does not represent a type.)
1 | query_text type="XQUERY"> |
XQUERY statement created in SQL Developer
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | select xmlquery(
'<regions>
{for $r in ora:view("REGIONS")
let $reg_name := $r/ROW/REGION_NAME/text()
,$reg_id := $r/ROW/REGION_ID/text()
order by $reg_id
return
<region id="{$reg_id}">
<name>{$reg_name}</name>
<countries>
{for $c in ora:view("COUNTRIES")
let $c_name := $c/ROW/COUNTRY_NAME/text()
,$c_country_id := $c/ROW/COUNTRY_ID/text()
,$c_reg_id := $c/ROW/REGION_ID/text()
where $c_reg_id = $reg_id
order by $c_country_id
return
<country id="{$c_country_id}">
<name>{$c_name}</name>
</country>
}
</countries>
</region>
}
</regions>' returning content)
from dual; |
Input XML Document for XQUERY Using Query Web Service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="XQUERY">
<![CDATA[
<regions>
{for $r in ora:view("REGIONS")
let $reg_name := $r/ROW/REGION_NAME/text()
,$reg_id := $r/ROW/REGION_ID/text()
order by $reg_id
return
<region id="{$reg_id}">
<name>{$reg_name}</name>
<countries>
{for $c in ora:view("COUNTRIES")
let $c_name := $c/ROW/COUNTRY_NAME/text()
,$c_country_id := $c/ROW/COUNTRY_ID/text()
,$c_reg_id := $c/ROW/REGION_ID/text()
where $c_reg_id = $reg_id
order by $c_country_id
return
<country id="{$c_country_id}">
<name>{$c_name}</name>
</country>
}
</countries>
</region>
}
</regions>
]]>
</oraw:query_text>
<oraw:pretty_print>false</oraw:pretty_print>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope> |
Output XML Document for SQL Query Using Query Web Service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<ROWSET>
<ROW>
<COLUMN_VALUE>
<regions>
<region id="1">
<name>Europe</name>
<countries>
<country id="BE">
<name>Belgium</name>
</country>
<country id="CH">
<name>Switzerland</name>
</country>
</countries>
</region>
<region id="2">
<name>Americas</name>
<countries>
<country id="AR">
<name>Argentina</name>
</country>
<country id="BR">
<name>Brazil</name>
</country>
</countries>
</region>
<region id="3">
<name>Asia</name>
<countries>
<country id="AU">
<name>Australia</name>
</country>
<country id="CN">
<name>China</name>
</country>
</countries>
</region>
<region id="4">
<name>Middle East and Africa</name>
<countries>
<country id="EG">
<name>Egypt</name>
</country>
<country id="IL">
<name>Israel</name>
</country>
</countries>
</region>
</regions>
</COLUMN_VALUE>
</ROW>
</ROWSET>
</queryOut>
</soap:Body>
</soap:Envelope> |
And one just for the fun of IT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="XQUERY">
<![CDATA[
(1, 2 + 3, "a",100 to 110, <A>33</A>)
]]>
</oraw:query_text>
<oraw:pretty_print>false</oraw:pretty_print>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<ROWSET>
<ROW>
<COLUMN_VALUE>1</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>5</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>a</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>100</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>101</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>102</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>103</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>104</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>105</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>106</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>107</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>108</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>109</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>110</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>
<A>33</A>
</COLUMN_VALUE>
</ROW>
</ROWSET>
</queryOut>
</soap:Body>
</soap:Envelope> |
Web Service Mapping Between XML and SQL Data Types
| SQL Data Type | XML Schema Data Type |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
PL/SQL |
|
|
Object types |
|
An object type is represented in XML as a complex-type element named the same as the object type. The object attributes are represented as children of this element.
Create Object Type Person
1 2 3 4 5 6 7 8 | create or replace type PersonObj as OBJECT ( name varchar2(100) ,date_of_birth date ,member function getAge RETURN NUMBER ,member function getDateOfBirth RETURN DATE ,member function getName RETURN VARCHAR2 ) / |
Implement Person’s methods
1 2 3 4 5 6 7 8 9 10 | create or replace type VehicleObj as OBJECT ( vehicle_name varchar2(50) ,vehicle_build_date date ,owner PersonObj ,member function getAge RETURN NUMBER ,member function getName RETURN VARCHAR2 ,member function getVehicleBuildDate RETURN DATE ,member function getOwner RETURN PersonObj ) / |
Create Object Type Vehicle
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE OR REPLACE TYPE BODY VehicleObj AS
MEMBER FUNCTION getAge RETURN NUMBER AS
BEGIN
RETURN Trunc(Months_Between(Sysdate, vehicle_build_date)/12);
END getAge;
MEMBER FUNCTION getVehicleBuildDate RETURN DATE AS
BEGIN
RETURN vehicle_build_date;
END getVehicleBuildDate;
MEMBER FUNCTION getName RETURN VARCHAR2 AS
BEGIN
RETURN vehicle_name;
END getName;
MEMBER FUNCTION getOwner RETURN PersonObj AS
BEGIN
RETURN owner;
END getOwner;
END;
/ |
Implement vehicle’s methods
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE OR REPLACE TYPE BODY PersonObj AS
MEMBER FUNCTION getAge RETURN NUMBER AS
BEGIN
RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
END getAge;
MEMBER FUNCTION getName RETURN VARCHAR2 AS
BEGIN
RETURN name;
END getName;
MEMBER FUNCTION getDateOfBirth RETURN DATE AS
BEGIN
RETURN date_of_birth;
END getDateOfBirth;
END;
/ |
Create the corresponding tables
1 2 3 4 5 6 7 | create table owner (id number(10) NOT NULL ,person PersonObj); create table vehicles (id number(10) NOT NULL ,vehicle VehicleObj); |
Populate the tables
Insert data using the VehicleObj() and PersonObj() constructors.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | DECLARE
v_vehicle VehicleObj;
v_person PersonObj;
BEGIN
v_person := PersonObj('Rob',TO_DATE('27/06/1966','DD/MM/YYYY'));
v_vehicle := VehicleObj('BMW', TO_DATE('31/12/2001','DD/MM/YYYY'),v_person);
INSERT INTO owner VALUES(1,v_person);
INSERT INTO vehicles VALUES (4, v_vehicle);
COMMIT;
END;
/
DECLARE
v_vehicle VehicleObj;
v_person PersonObj;
BEGIN
v_person := PersonObj('Rob',TO_DATE('27/06/1966','DD/MM/YYYY'));
v_vehicle := VehicleObj('Mercedes', TO_DATE('01/12/1988','DD/MM/YYYY'),v_person);
INSERT INTO vehicles VALUES (5, v_vehicle);
COMMIT;
END;
/
DECLARE
v_vehicle VehicleObj;
v_person PersonObj;
BEGIN
v_person := PersonObj('Richard',TO_DATE('31/12/1974','DD/MM/YYYY'));
v_vehicle := VehicleObj('Hyundai Pony'
,TO_DATE('01/02/1985','DD/MM/YYYY'),v_person);
INSERT INTO owner VALUES (2, v_person);
INSERT INTO vehicles VALUES (6, v_vehicle);
COMMIT;
END;
/ |
Definition of PL/SQL Function Used for Web-Service Access
1 2 3 4 5 6 7 8 9 | create or replace PROCEDURE get_vehicles ( p_id IN vehicles.id%TYPE, p_vehicles OUT VehicleObj) AS BEGIN select vehicle into p_vehicles from vehicles where id = p_id; END; |
Input XML Document for PL/SQL Query Using Web Service
1 2 3 4 5 6 7 8 9 10 | <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:get="http://xmlns.oracle.com/orawsv/TEST/GET_VEHICLES">
<soapenv:Header/>
<soapenv:Body>
<get:GET_VEHICLESInput>
<get:P_VEHICLES-VEHICLEOBJ-COUT/>
<get:P_ID-NUMBER-IN>6</get:P_ID-NUMBER-IN>
</get:GET_VEHICLESInput>
</soapenv:Body>
</soapenv:Envelope> |
Output XML Document for PL/SQL Query Using Web Service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GET_VEHICLESOutput xmlns="http://xmlns.oracle.com/orawsv/TEST/GET_VEHICLES">
<P_VEHICLES>
<VEHICLEOBJ>
<VEHICLE_NAME>Hyundai Pony</VEHICLE_NAME>
<VEHICLE_BUILD_DATE>01-FEB-85</VEHICLE_BUILD_DATE>
<OWNER>
<NAME>Richard</NAME>
<DATE_OF_BIRTH>31-DEC-74</DATE_OF_BIRTH>
</OWNER>
</VEHICLEOBJ>
</P_VEHICLES>
</GET_VEHICLESOutput>
</soap:Body>
</soap:Envelope> |
Input XML Document for SQL Query Using Query Web Service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="SQL">
<![CDATA[SELECT v.*,v.vehicle.getAge(),v.vehicle.owner.getAge()
FROM vehicles v WHERE id = :e]]>
</oraw:query_text>
<oraw:bind name="e">6</oraw:bind>
<oraw:pretty_print>false</oraw:pretty_print>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope> |
Output XML Document for SQL Query Using Query Web Service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | lope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<ROWSET>
<ROW>
<ID>6</ID>
<VEHICLE>
<VEHICLE_NAME>Hyundai Pony</VEHICLE_NAME>
<VEHICLE_BUILD_DATE>01-FEB-85</VEHICLE_BUILD_DATE>
<OWNER>
<NAME>Richard</NAME>
<DATE_OF_BIRTH>31-DEC-74</DATE_OF_BIRTH>
</OWNER>
</VEHICLE>
<V.VEHICLE.GETAGE_x0028__x0029_>23</V.VEHICLE.GETAGE_x0028__x0029_>
<V.VEHICLE.OWNER.GETAGE_x0028__x0029_>33</V.VEHICLE.OWNER.GETAGE_x0028__x0029_>
</ROW>
</ROWSET>
</queryOut>
</soap:Body>
</soap:Envelope |
Overview of Native Oracle XML DB Web Services Part 1
Web services provide a standard way for applications to exchange information over the Internet and access services that implement business logic. Your applications can access Oracle Database using
native Oracle XML DB Web services.
- One available service lets you issue SQL and XQuery queries and receive results as XML data.
- Another service provides access to all PL/SQL stored functions and procedures.
The WSDL is automatically generated by the native database Web services engine.
Configuring and Enabling Web Services for Oracle XML DB
For security reasons, Oracle XML DB is not preconfigured with the native Web services enabled. To make native Oracle XML DB Web services available, you must have the Oracle XML DB HTTP server up and running, and you must explicitly add Web service configuration. Then, to allow specific users to use Web services, you must grant them appropriate roles.
Add Web Service Configuration Servlet
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DECLARE
SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
DBMS_XDB.deleteServletMapping(SERVLET_NAME);
DBMS_XDB.deleteServlet(SERVLET_NAME);
DBMS_XDB.addServlet(NAME => SERVLET_NAME,
LANGUAGE => 'C',
DISPNAME => 'Oracle Query Web Service',
DESCRIPT => 'Servlet for issuing queries as a Web Service',
SCHEMA => 'XDB');
DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
ROLENAME => 'XDB_WEBSERVICES',
ROLELINK => 'XDB_WEBSERVICES');
DBMS_XDB.addServletMapping(PATTERN => '/orawsv/*',
NAME => SERVLET_NAME);
END;
/
XQUERY declare default element namespace "http://xmlns.oracle.com/xdb/xdbconfig.xsd";
for $doc in fn:doc("/xdbconfig.xml")/xdbconfig/sysconfig/protocolconfig/httpconfig/
webappconfig/servletconfig/servlet-list/servlet[servlet-name='orawsv']
return $doc
/ |
Generated output
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | PL/SQL procedure successfully completed.
<servlet xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
<servlet-name>orawsv</servlet-name>
<servlet-language>C</servlet-language>
<display-name>Oracle Query Web Service</display-name>
<description>Servlet for issuing queries as a Web Service</description>
<servlet-schema>XDB</servlet-schema>
<security-role-ref>
<description/>
<role-name>XDB_WEBSERVICES</role-name>
<role-link>XDB_WEBSERVICES</role-link>
</security-role-ref>
</servlet> |
To enable Web services for user, log on as user SYS and grant the roles XDB_WEBSERVICES and XDB_WEBSERVICES_OVER_HTTP to the user. This roles enable use of Web services.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | connect system create user test identified by password default tablespace users temporary tablespace temp; grant connect , resource to test; GRANT XDB_WEBSERVICES TO test; GRANT XDB_WEBSERVICES_OVER_HTTP TO test; connect test create table vehicle (id number , description varchar2(10) , constraint vehicle_pk PRIMARY KEY (id) ); insert into vehicle values (1,'car'); insert into vehicle values (2,'bicycle'); insert into vehicle values (3,'benenwagen'); CREATE OR REPLACE PROCEDURE get_vehicle ( p_id IN vehicle.id%TYPE, p_description OUT vehicle.description%TYPE) AS BEGIN select description into p_description from vehicle where id = p_id; END; / |
The web services require authentication. If you use this URL in a browser you will be prompted for a username and password. In this case you would enter the schema credentials (test/password). Alternatively, you can include the authentication details in the URL (Note : this gives an error in MicroSoft Exploder).
1 2 3 4 5 6 7 | # Format with password. # Webservice call function or procedure http://username:password@host:port/orawsv/DBSCHEMA/FN_OR_PROC?wsdl # Webservice call function or procedure within package http://username:password@host:port/orawsv/DBSCHEMA/PACKAGE/FN_OR_PROC?wsdl # Webservice for database queries http://username:password@host:port/orawsv?wsdl |
Querying Oracle XML DB Using a Web Service
The Oracle XML DB Web service for database queries is located at URL http://host:port/orawsv, where host and port are the host and HTTP(S) port properties of your database. This Web service has a WSDL document associated with it that specifies the formats of the incoming and outgoing documents using XML Schema.
This WSDL document is located at URL http://host:port/orawsv?wsdl.
XML Schema for Database Queries To Be Processed by Web Service
The important parts of incoming query documents are as follows:
- query_text – The text of your query. Attribute type specifies the type of your query: either SQL or XQUERY.
- bind – A scalar bind-variable value. Attribute name names the variable.
- bindXML – An XMLType bind-variable value.
- null_handling – How NULL values returned by the query are to be treated:
- DROP_NULLS – Put nothing in the output (no element). This is the default behavior.
- NULL_ATTR – Use an empty element for NULL-value output. Use attribute xsi:nil=”true” in the element.
- EMPTY_TAG – Use an empty element for NULL-value output, without a nil attribute.
- max_rows – The maximum number of rows to output for the query. By default, all rows are returned.
- skip_rows – The number of query output rows to skip, before including rows in the data returned in the SOAP message. You can use this in connection with max_rows to provide paginated output. The default value is zero (0).
- pretty_print – Whether the output document should be formatted for pretty-printing. The default value is true, meaning that the document will be pretty-printed. When the value is false, no pretty-printing is done, and output rows are not broken with newline characters.
- indentation_width – The number of characters to indent nested elements that start a new line. The default value is one (1).
- rowset_tag – Name of the root element of the output document.
- row_tag – Name of the element whose value is a single row of query output.
- item_tags_for_coll – Whether to generate collection elements with name collection_name_item, where collection_name is the name of the collection.
Input XML Document for SQL Query Using Query Web Service
1 2 3 4 5 6 7 8 9 10 11 12 13 | <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="SQL">
<![CDATA[SELECT * FROM vehicle WHERE id = :e]]>
</oraw:query_text>
<oraw:bind name="e">1</oraw:bind>
<oraw:pretty_print>false</oraw:pretty_print>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope> |
Output XML Document for SQL Query Using Query Web Service
1 2 3 4 5 6 7 8 9 10 11 12 | <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<ROWSET>
<ROW>
<ID>1</ID>
<DESCRIPTION>car</DESCRIPTION>
</ROW>
</ROWSET>
</queryOut>
</soap:Body>
</soap:Envelope> |
Accessing PL/SQL Stored Procedures Using a Web Service
The Oracle XML DB Web service for accessing PL/SQL stored functions and procedures is located at URL http://host:port/orawsv/dbschema/package/fn_or_proc or, for a function or procedure that is not in a package (standalone), http://host:port/orawsv/dbschema/fn_or_proc. Here, host and port are the
host and HTTP(S) port properties of your database, fn_or_proc is the stored function or procedure name, package is the package it is in, and dbschema is the database schema owning that package. The input XML document must contain the inputs needed by the function or procedure.
The output XML document contains the values of all OUT variables, as well as the return value.
WSDL Document Corresponding to a Stored PL/SQL Function
Input XML Document for PL/SQL Query Using Web Service
1 2 3 4 5 6 7 8 9 10 | <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:get="http://xmlns.oracle.com/orawsv/TEST/GET_VEHICLE">
<soapenv:Header/>
<soapenv:Body>
<get:GET_VEHICLEInput>
<get:P_ID-NUMBER-IN>1</get:P_ID-NUMBER-IN>
<get:P_DESCRIPTION-VARCHAR2-OUT/>
</get:GET_VEHICLEInput>
</soapenv:Body>
</soapenv:Envelope> |
Output XML Document for PL/SQL Query Using Web Service
1 2 3 4 5 6 7 | <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GET_VEHICLEOutput xmlns="http://xmlns.oracle.com/orawsv/TEST/GET_VEHICLE">
<P_DESCRIPTION>car</P_DESCRIPTION>
</GET_VEHICLEOutput>
</soap:Body>
</soap:Envelope> |
When connecting to a database from Java, it’s very handy to tag your connections. When tracking down performance issues and monitoring at a database level, being able to seperate out your program’s connections (which could come from a variety of machines). Most database drivers allow you to specify a program name when creating your connection. The defaults for osuser,terminal and
machine are usually correct. By querying the database’s v$session dictionary view we can see each connection from our application.
Java Code snippet
1 2 3 4 5 6 7 8 9 | java.util.Properties props = new java.util.Properties();
props.setProperty("password","mypassword");
props.setProperty("user","myusername");
props.put("v$session.osuser", System.getProperty("user.name").toString());
props.put("v$session.machine", InetAddress.getLocalHost().getCanonicalHostName());
props.put("v$session.program", "My Program Name");
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn=
DriverManager.getConnection("jdbc:oracle:thin:@myhostname:1521:mysid",props); |
Check v$session table
1 2 3 4 5 6 7 8 | SQL> select username,osuser,program,machine from v$session where username = 'ROB'; USERNAME OSUSER PROGRAM MACHINE --------- ----------- ------------------ ----------- ROB rlaarhoven My Program Name dto.tud.tudelft.nl |
SQL injection is one of the main software security vulnerabilities.
Using this technique, a hacker can pass string input into an
application. The vulnerability is present when user input is incorrectly
checked before concatenating it into a SQL statement.
I’m going to demonstrate this technique with code used by a Login
page. The same technique can be used anywhere where user input is
concatenated directly into a SQL statement.
Create a table containing username and password
combinations
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> create table users (username varchar2(100),password varchar2(100));
Table created.
SQL> insert into users values ('rob','geheim';);
1 row created.
SQL> insert into users values ('''hans','geheim');
1 row created.
SQL> select username,password from users;
USERNAME PASSWORD
---------- ----------
rob geheim
'hans geheim |
Create a method to check if a user has access to the system. The user
input is concatenated directly into the SQL statement.
1 2 3 4 5 6 7 8 9 10 11 | public boolean login(String username, String password) throws SQLException {
String sql = "select * from users where username = ' "
+ username + "' and password = ' " + password + " ' ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
return true;
} else {
return false;
}
} |
Test the method:
1 | System.out.println(login("rob","geheim")); |
returns true as expected
Try another string for username
1 | System.out.println((login("rob' or '1' = '1", "")); |
Returns true
This is not what we expected.
Take a look at the SQL statement send to the database:
1 | select * from users where username = 'rob' or '1' = '1' and password = '' |
Adding or ‘1′ = ‘1′ to the expresssion always results in
true. We only need to know the username to login.
Try to login as user ‘hans
1 2 | System.out.println(login.login(" 'hans", "geheim"));
java.sql.SQLException: ORA-00933: SQL command not properly ended |
Take a look at the SQL statement send to the database:
1 | select * from users where username = ''hans' and password = 'geheim' |
This statement contains a syntax error
The solution : Use prepared statements
Edit the login method
1 2 3 4 5 6 7 8 9 10 11 12 | public boolean login(String username, String password) throws SQLException {
String sql = "select * from users where username = ? and password = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,username);
stmt.setString(2,password);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
return true;
} else {
return false;
}
} |
Test the method:
1 | System.out.println(login("rob","geheim")); |
Returns true as expected
Try another string for username
1 | System.out.println(login("rob' or '1' = '1", "")); |
Returns false
This is what we expected. Let’s take a look at the SQL statement
send to the database:
1 | select * from users where username = ? and password = ? |
The user input is bound to the variable not concatenated to the
statement.
Try to login as user ‘hans
1 | System.out.println(login("'hans", "geheim")); |
Returns true
This resolves the problem with quotes
Conclusion
Using prepared statements:
- Resolves problems with nasty characters
- Prevents SQL Injection
- Results in better Performance & Scalability (trust me on this one for now)
