ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
(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...)

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

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

The X (Path) File

by Eduardo Rodrigues This week I came across one of those mysterious problems where I had some test cases that needed to verify the content of some DOM trees to guarantee that the test went fine....

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

Exposing Web Services as Database Views

In recent years, Web Services have become the defacto standard in passing  data between applications.   They make communication between disparate computer languages and architectures relatively simple.  For the most part, we tend to think of web services in terms of traditional application development (Java, .NET, Ruby, PHP, etc …) and inter-process communication.  What if we looked at them from another perspective?  What if we could easily integrate web services into the database?  Make them consumable by Business Intelligence tools, and Data Mining engines WITHOUT having to change the traditional manner in which these tools access (more...)

PL/SQL Web Service Utility: Accessing Web Services with PL/SQL is Simple!

This entry showcases a PL/SQL package called WebServiceUtils that makes accessing a web service from with the Oracle database very simple.  The package makes use of Tim Hall’s SOAP_API PL/SQL package (updated by me to get the SOAP response as a CLOB instead of a VARCHAR2) to do the SOAP encoding and processing.  The WebServiceUtils package puts a user friendly layer over the SOAP_API.    The WebServiceUtils package (here after known as the “utils” package) provides three structures (2 record types and 1 collection).  The two record types are:

  • service_definition_type – Contains attributes related to the service (more...)