Extract CSV file format in Oracle SQL

How to extract CSV file format data in Oracle SQL Plus.

---10G Solution. On SQL Plus session
alter session set nls_date_format='YYYY-MM-DD';

Session altered.

select regexp_replace(column_value,' *<[^>]*>[^>]*>’,';’)
from table(xmlsequence(cursor(select * from emp)));

=======================

-- Oracle 9i Solution. SQL Plus session.
-- Following sql fail to give EMPTY space for NULL values properly.
-- See PL/SQL solution to handle
SELECT XMLTRANSFORM
(COLUMN_VALUE,
XMLTYPE
('















'
)
) csv
FROM TABLE (XMLSEQUENCE (CURSOR (SELECT *
FROM emp)))
/
=======================================
---- Oracle 9i PL/SQL solution
SQL> VAR cur refcursor

SQL> DECLARE
ctx DBMS_XMLGEN.ctxhandle;
BEGIN
ctx := DBMS_XMLGEN.newcontext ('select * from emp');
DBMS_XMLGEN.setnullhandling (ctx, DBMS_XMLGEN.empty_tag);

OPEN :cur FOR
SELECT (SELECT RTRIM(XMLAGG (XMLELEMENT (c,VALUE (t2),',').EXTRACT('//text()')),',')
FROM TABLE (XMLSEQUENCE (VALUE (t1).EXTRACT ('ROW/*'))) t2) csv
FROM TABLE(XMLSEQUENCE(DBMS_XMLGEN.getxmltype (ctx).EXTRACT('ROWSET/ROW'))) t1;

DBMS_XMLGEN.closecontext(ctx);
END;
/
PL/SQL procedure successfully completed.

SQL> PRINT cur
==================================

New Articles to Read - Java

XML Parser Benchmarks: Part 1

JavaOne 2007, #2: Making Web Apps Easy
Summary
Today, JavaOne 2007 showcased a complete open source technology stack that lets you develop and deploy web applications quickly and easily, including JRuby, Rails, NetBeans, and Glassfish

Integrating Struts With Spring

Hibernate Interceptors - An Introduction

New Articles to Read - Java

XML Parser Benchmarks: Part 1

JavaOne 2007, #2: Making Web Apps Easy
   Summary
Today, JavaOne 2007 showcased a complete open source technology stack that lets you develop and deploy web applications quickly and easily, including JRuby, Rails, NetBeans, and Glassfish

Integrating Struts With Spring

Hibernate Interceptors - An Introduction

Build an Online Reporting Application Using Oracle XML Publisher

by Mark Rittman

Learn how to create an online reporting environment using XML Publisher technology, step by step.

Build an Online Reporting Application Using Oracle XML Publisher

by Mark Rittman

Learn how to create an online reporting environment using XML Publisher technology, step by step.

A Primer on Spring's Data Access Object (DAO) Framework

A Primer on Spring's Data Access Object (DAO) Framework by Dhrubojyoti Kayal -- This article introduces the Spring Data Access Object (DAO) framework. After examining the architectural needs for DAO, it describes the DAO pattern in general and how Spring simplifies the implementation.

Collection of JVM Options

I found link about Collection of JVM options compiled by Joseph D. Mocker (Sun Microsystems, Inc.)

Apache Axis2 - Nice Technical Articles

Today, I was looking at Jroller.com and found nice entry posted by Glen Mazza. Mazza posted Axis2 - Technical Articles. It is very good collection of articles about Apache Axis2. From his posting, I was directed to ws02.org site. This where I finally read Axis2 technical articles.

WSO2.org is Oxygen for Web Service Developers. It has got very nice detail articles and tutorials for Web Services projects related to Apache Open Source.

Thanks to Glen Mazza for his posting.

Spring Framework - Two Articles on Form Validation with DWR and AJAX

Spring Framework - Two Articles on Form Validation with DWR and AJAX
Submitted by Colin Sampaleanu on Thu, 2007-04-19 23:09. Technical Article
Using AJAX (via DWR) to perform client-side validation is a useful technique that allows the implementation of both client side and server side form validation, while actually using the same (server-side) validators for both task. Here are a couple of articles that describe this technique.
Generic Validator for Spring MVC and DWR by Jose Noheda, shows one approach. His validators work off annotations in the objects to be validated (this is somewhat orthogonal to the question of validating remotely).


ForwardSourceID:NT00009002

Incremental Compilation and Error Handling in XMLBeans

Incremental Compilation and Error Handling in XMLBeans by Hetal Shah -- Hetal Shah shows how to ease the maintenance of XMLBeans-based applications by using incremental compilation of schemas and custom error handling facilities. NT00008FFE

How to configure XML Bean

Configuring XMLBeans by Hetal Shah -- One of the most powerful features of XMLBeans Version 1.03 is the ability to customize its code generation through a configuration file. Configuration features of XMLBeans bring a number of benefits in terms of flexibility, reusability, simplified code, and XMLBeans"s readiness for enterprise environments.

Nice Oracle Analytic Article

Today, I found the following article by Jonathan Lewis was very nice and
interesting. The article is about Analytic functions, and is one that
Jonathan first published in the UKOUG journal earlier on last year.
This article published in Select (the journal of the IOUG) has won the
"SELECT Editors' Choice" award.There's going to be a presentation of
awards at Collaborate '07, which starts this week.

http://www.jlcomp.demon.co.uk/analytics.html

Single Sign On between WebLogic Server 9.2 and IIS

Single Sign On between WebLogic Server 9.2 and IIS using SAML by Alex Rykov -- Single sign on (SSO) is becoming increasingly important in enterprise environments. This tutorial by Alex Rykov describes a simple SAML SSO scenario between Microsoft Internet Information Services Server (IIS) and BEA WebLogic Server 9

Oracle 9i - How to add missing partitions

Assume, We need to insert missing partition as new partition with Oracle Range Partition.

Here is the simple example for that.

create table karth_test (a date,data char(10))
partition by range(a)
( partition p1 values less than (to_date('2007-01-01','YYYY-MM-DD')),
partition p2 values less than (to_date('2007-02-01','YYYY-MM-DD')),
partition p4 values less than (to_date('2007-04-01','YYYY-MM-DD'))
)

select partition_name pname, high_value from user_tab_partitions
where table_name ='KARTH_TEST' order by partition_name

alter table KARTH_TEST add partition p3 values less than (TO_DATE('2007-03-01','YYYY-MM-DD'))

-- Above statement raises ORA-14074: partition bound must collate higher than that of the last partition.

-- We can execute following statement to overcome above error

Alter table KARTH_TEST split partition p4 at (TO_DATE('2007-03-01','YYYY-MM-DD'))
into (partition p3, partition p4)

Drop table KARTH_TEST

Exploring the WebLogic Integration 8.1 RDBMS Event Generator

Nice DEV2DEV article. It is well explained in simple terms.

Exploring the WebLogic Integeration 8.1 RDBMS Event Generator
by Mahadevan Krishnan. Learn about an approach to integerating databases with enterprise applications through the use of WebLogic Integeration Event Generators.

Introduction to OpenTerracotta - From InfoQ

Orion Letizi posted nice article in www.infoq.com
Java applications are easiest to write and test when they run in a single JVM. However, the requirements to make applications scalable and highly available have forced Java applications to run on more than one JVM. In this article, we introduce OpenTerracotta, an enterprise-class, open-source JVM-level clustering solution. Read more

Web2.0, REST & Weblogic Workshop - Vinny Carpenter's blog

Found nice link at Vinny's blog about REST, Web 2.0 and BEA Workshop.

Dynamic DataSource Routing - Spring 2.0.1

Spring 2.0.1 introduced an AbstractRoutingDataSource. I believe that it deserves attention, since (based on frequent questions from clients) I have a hunch that there are quite a few 'home-grown' solutions to this problem floating around. Mark Fisher posted nice entry about this topic in his blog. Read More.

Pojos In Action - Sample Chapter

POJOs in Action by Chris Richardson -- In this book excerpt from POJOs in Action, Chris Richardson explores the benefits and drawbacks of using the Transaction Script pattern and describes how to implement this pattern using POJOs and the Spring framework

Hibernate Mapping XML to Oracle XMLTYPE

Oracle XMLTYPE attribute type can be mapped to a W3C Document type representation. Hibernate's UserType mechanism allows to create vendor specific data types. There are certain points to consider when mapping an object to an Oracle XMLType.
Please read more.