LISTAGG to a CLOB, avoid 4000 chr limit

Thanks to twitter I found this post by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.
ORA-01489: result of string concatenation is too long

I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search (more...)

SQL Server XQuery

I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a marvel table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.

You can query a sequence with xquery as follows:

DECLARE @x xml;
SET @x = N'';
SELECT @x.query( (more...)

Insert into XML Column

Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:

  • An XML Schema Collection type.
  • A table that uses an XML Schema Collection as a column’s data type.
  • An example on how you can transfer the contents of a table into the XML Schema Collection.

This post assumes you (more...)

Working with XML files and APEX – Part 3: Detail elements in a row with OUTER JOIN

After I described how to successfully import XML files into an APEX application and gave some basic information about the analyzing of xml data.
It's time for another example. This time we want to get all detail elements of a master element in one row.
Example XML data:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>  
<data>
<stadium>
<team name="SG Dynamo Dresden" league="3. Liga"/>
<event name="Konzert"/>
<event name="Stadionf├╝hrung"/>
</stadium>
<stadium>
<team name="RB Leipzig" league="2. Liga"/>
<event (more...)

checking suspicious bind variables in v$sql_monitor

In my current company we try to stabilize the performance of our Peoplesoft application. So on a more or less regular base I get a call to investigate as "it" is "slow" now.
During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - (more...)

Access Oracle GoldenGate JAgent XML from browser

There are many different ways of monitoirng Oracle GoldenGate; I have posted about many of these in earlier blog posts.  Additionally, I have talked about the different ways of monitoring Oracle GoldenGate at a few conferences as well.  (The slides can be found on my slideshare site if wanted).  In both my blog and presentations I highlight many different approaches; yet I forgot one that I think is really cool!  This one was shown to (more...)

Structured XML output from Oracle SQL query

Got an interesting question today: There are tables TABLE1, TABLE2 and a junction table that joins them called JUNCTABLE. Need the following output as XML


  
  


  
  

I know the output could be aggregated using XMLAGG, but I have never looked into how to format the tag names and attributed in the output like requested.

Data in my very simplified sample tables:

SQL> select * from table1;

        ID
----------
         1
         2
         3

SQL> select * from table2;

         (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...)

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

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