Prevent the JDeveloper XSL mapper from breaking

Who has worked with the Design view of the XSL mapper in Oracle JDeveloper, knows that it works nice for simple mappings and drawing lines from one end to the other. However, when adding more complicated transformations, the moment arrives one has to modify the source. This usually results in mapper errors, and the Design view will not be displayed.

Although it’s not entirely avoidable this will happen at some point, there are a few (more...)

CSV, XML and JSON parsing – a comparison over versions

Via Steven Feuerstein I was asked to try and give advice concerning fastest way to parse CSV data in PL/SQL. The case was that they had a different database that could be setup to deliver data as a webservice serving either CSV, XML or JSON, and they wished to let their APEX application use PL/SQL to retrieve data from that other database via such webservices. They were not yet on 12.1.0.2.0 (more...)

JSON_TABLE or XMLTABLE – comparison with geocoding

Previously I've demonstrated how to use function XMLTABLE to query Google maps routing directions. Now Oracle version 12.1.0.2.0 has function JSON_TABLE to do similar querying on JSON data rather than XML data. So let's try that out and spot some differences...

For this test we'll use the Google maps geocoding rather than routing directions and try to geocode the address of Oracle headquarters. If we call the Google api with instructions (more...)

Hierarchical XML from SQL

Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here.
It describes how to create an xml document as an XMLType with an Oracle SQL Query.

The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data (more...)

Run XQuery with XqlPlus

I started today with setting up an OSB training and figuring out how to run XQuery scripts outside of OSB. I looked into the (ok a little dated) book: Oracle Database 10g XML&SQL, that I have in my library. In that book there's also a chapter about XQuery. And it shows that the Oracle XDK has a XQuery processer, but also a commandline tool like SQLPLus: XQLPlus. It is in the xquery jar, but (more...)

Current date and time in XLST under OSB

For my current assignment I needed to build quite complex transformations in OSB. Mainly because of my experiences I choose to do that in XSLT.
Eclipse has a quite nice XQuery mapper, but it lacks an XSLT mapper.
Since JDeveloper has a nice XSL Mapper tool, I incorporated a JDeveloper (more...)

Oracle Dynamic SQL: generic search – crazy case

For the Friday evening I decided to dig out my example from the most recent of my published books (Expert PL/SQL Practices, APress, 2011) - it was a very fun project to work with: first, to come with with the idea of such multi-author book, second, to write (more...)

LPX-00209: PI names starting with XML are reserved

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. I don't know why it took me more than the necessary to understand where was the problem when I hit the following error. ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00209: PI names starting with XML are reserved At any rate, in my case the reason was

SQL Solution for the Third International NoCOUG SQL & NoSQL Challenge

SQL vs NoSQL: Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian
As published in the 102nd issue of the NoCOUG Journal

THE WICKED WITCH OF THE WEST NEEDS HELP

BE IT KNOWN BY THESE PRESENTS that the Wicked Witch of the West needs your help to create
a (more...)

How to simulate DML Operations on XML data using XU (XQuery Update)

The following SQL pattern can be used to simulate DML operations on XML data by utilizing XU (XQuery Update) functionalities.

This query will do the following XML data transformation by using XU insert/delete/rename/replace statements.

1) Convert all the XML Attributes to Elements.                ( XU Delete & Insert)

2) Change the  (more...)

How to validate XML data using XSD in Oracle XML DB

The following XML Schema can be used to validate the XML input data in the XML DB.

It enforces the following validation rules:

1) Parent_Tab/Parent_Row/dep_id is the (primary) key within Parent_Tab.
2) Parent_Tab/Parent_Row/dep_name should be one of the xs:enumeration list in DeptnameType and it is also unique within Parent_Tab.
3) (more...)

MS Exchange API for PL/SQL

Uncategorized
| May 26, 2012
As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!



Features


Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.

Prerequisites



How to do XML based data manipulation in SQL and XSLT

The following SQL pattern can be used to do XML based data manipulation and transformation by utilizing XSLT functionality.

This query will do the following data manipulation and transformation:

1) Rename the xml tag <FirstName> to <FN> when the position of the element node is
either the last one or (more...)

How to do table column based data manipulation in SQL and XQuery

The following SQL pattern can be used to stragg all the table column data together
The STRAGG(column data) is grouped together based on the table column names.
This SQL also manipulates comma-delimited strings using XQuery functionalities.


CREATE TABLE PERSON
(ID            NUMBER,
 FIRSTNAME     VARCHAR2(20 BYTE),
 MIDDLENAME    VARCHAR2(20 BYTE),
 LASTNAME      VARCHAR2(20 BYTE),
  (more...)

How to do character based data manipulation in SQL and XQuery

The following SQL pattern can be used to do character based data manipulation
by utilizing the XQuery functionalities.


COLUMN old_str  FORMAT  A28
COLUMN ordered_no_dup_chars FORMAT A23
COLUMN Reversed_chars FORMAT  A23



--------------------SQL Query-------------------


WITH data AS
(SELECT '3332221118888'   AS  old_str  FROM dual
  UNION ALL
  SELECT 'CCCBBBAAA'          FROM dual 
   UNION ALL
  SELECT  (more...)

How to convert an Internet HTML Table to CSV format in SQL

The following SQL pattern can be used to transform an HTML Table on the web to CSV format.


set long 10000;
variable url_input varchar2(500)
exec :url_input:='http://oraqa.com/2012/02/27/how-to-transform-a-csv-file-into-html-table-in-sql/'


-------------------------------------------------------------------SQL QUERY---------------------------------------------------------------------


WITH HTML_TAB AS
(SELECT CAST(REGEXP_REPLACE(SUBSTR(STR, 0, REGEXP_INSTR(str,'</table>', 1,1,1, 'i') ), '( ){2,}', ' ') 
         AS VARCHAR2(4000)) AS html_str
 FROM 
 (SELECT SUBSTR(str,  (more...)

How to transform a RefCursor query resultset into HTML table in SQL

The following SQL pattern can be used to tranform a RefCursor query resultset into data in HTML table format by utilizing the XSLT functionality. The query can handle NULL in the data columns. The SQL input string should be based on the following format:

SELECT Col AS name_x, Function() AS (more...)

How to convert a RefCursor query resultset to CSV format in SQL

The following SQL pattern can be used to convert a RefCursor query result set into a CSV format. This query can handle NULL in the data columns. The SQL input string should be based on the following format:

SELECT Col AS name_x, Function() AS name_y, Analytical_function( ) AS name_z FROM (more...)

How to transform a CSV file into HTML table in SQL

The following SQL pattern can be used to tranform the CSV file into a HTML table by utilizing the XQUERY functionality.


create or replace directory tmp as '/tmp';

create a word.csv file.

C:\tmp>more word.csv

ID,FIRSTNAME,MIDDLENAME,LASTNAME
1,Frank,,Zhou
2,Peter,B,Lee
3,John,C,Adam
4,Dave,,Washington


CREATE TABLE CSV_FILE (csv_str VARCHAR2(3000))
     ORGANIZATION EXTERNAL
     (
       TYPE  (more...)

JDeveloper Overview and (book) Review

Dear visitors, I will pretend I forgot that I'm about a year without posting anything and I will go straight to the subject that I owe to you: The review of the book "Processing XML documents with...

This is a summary only. Please, visit the blog for full content and (more...)