Satyaki De - SQL Exploration

Posted by Satyaki De

Wednesday, July 7, 2021

comments (0)

Hello Team,

For exciting new Topics on Cloud, Python, Streaming & many more - do visit the newly decorated site ->

https://satyakide.com/

And, share your feedback.

I'll wait for your participation.

Regards.

Satyaki De

Blog's New Address

Posted by Satyaki De

Tuesday, April 8, 2014

comments (0)

Hi Guys,

I'm now moving out my blog activities from this site to another site.

You can find all the latest here -

http://satyakide.wordpress.com/

Really, appreciate your valuable inputs.

See you over there.

Bye!

Regular Expression on Teradata 14.0

Posted by Satyaki De

Sunday, February 23, 2014

comments (1)

I've been working for more than 8 years in Oracle 10g, 11g & worked significant queries on Regular expressions in various scenario using SQL. It is real handy if you know how to use it & can reduce lots of pain with single SQL. And, the performance will be better compared to the total effort to achieve the same functionalists by using multiple SQL queries or PL/SQL Procedures.

Last couple of years, I'm working on Teradata. And, on some occasion - I was expecting features like these, where I can easily manipulate data with regular expression. I'm pretty excited when I heard that Teradata also introduced Regular Expression from Version 14.0.

As a result, I tried all those features that I think can be handy & useful for various scenarios & followings are the successful queries that I get. There are two occasion, where Teradata partially able to manipulate those strings. I've checked the latest Teradata Manual. However, unable to find those solution. So, I'm expecting other forum members can contribute here in order to make this thread useful for every one of us. And, I'll post here as soon as I get some answers on these partial conversions.

For better understanding, I've provided the actual column value & after transformation value of that column in the output. That will help us to grasp it easily - I guess. :)

Case 1,

SELECT regexp_replace('SatyakiDe','([[:lower:]]{1,})([[:upper:]]{1,})','\1 \2') AS COL_VAL;
 
COLA             COL_VAL
---------------- ----------------------------------------
SatyakiDe        Satyaki De

Case 2,

select regexp_replace('919047242526','^([[:digit:]]{2})([[:digit:]]{10})','+\1 \2') COL_VAL;
 
COLA         COL_VAL
------------ ---------------
919047255555 +91 9047255555

Case 3,

select regexp_replace('+++C','^([[:punct:]]{2})([[:punct:]]{1})(.*)$','\1\3') COL_VAL;
 
COLA COL_VAL
---- -----
+++C ++C

Case 4,

select initcap(regexp_replace(regexp_substr(' satyaki.de@mail.com','[^@]+'),'(.*)(\.)(.*)','\1 \3')) COL_VAL;
 
COLA                             COL_VAL
-------------------------------- --------------------------------------------------
satyaki.de@mail.com              Satyaki De

Case 5,

select regexp_replace('100011001','([[:digit:]]{3})([[:digit:]]{2})([[:digit:]]{4})','XXX-XX-\3') as COL_VAL;
 
COLA             COL_VAL
---------------- --------------------
100011001        XXX-XX-1001

Case 6,

select regexp_replace('123456789','([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{3})','\3.\2.\1') as COL_VAL;
 
COLA      COL_VAL
--------- ---------------
123456789 789.456.123

Case 7,

SELECT regexp_replace('satyaki9de0loves3to8work2on2sql0and2bi6tools1','[^0-9]+','',1,0,'i') AS DER_VAL;

COLA                                            DER_VAL
---------------------------------------------   ----------
satyaki1de0loves3to8work2on2sql0and2bi4tools1   1038220241

As you can see, all the characters have filtered out from the string & only numbers are kept here. These sorts of queries are very useful in lots of different business scenarios as well.

So, any extra space may not produce desired result. And, needs to pay attention into these small details.

And, I've tested all these queries in the following two versions -

select * from dbcinfo;
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  14.10.00.02
2   RELEASE  14.10.00.02
3   LANGUAGE SUPPORT MODE   Standard
 
 
select * from dbcinfo; 
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  14.10.01.05
2   RELEASE  14.10.01.04
3   LANGUAGE SUPPORT MODE   Standard

Hope, this will give you much more clarity. :)

One more thing, I would like to clarify here - my intention is to describe more features about these regexp_(similar/substr/instr/replace) functions.

I've received one question whether these regexp functions available in TD 13 or not in Teradata forum while posting the same article over there.

And, here is my answer to that question -

Regarding version 13,

Let us check whether they have these regexp functions or not -

select * from dbcinfo;
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  13.00.00.15
2   RELEASE  13.00.00.15
3   LANGUAGE SUPPORT MODE   Standard
 
 
select * from dbcinfo; 
 
    InfoKey  InfoData
    -------- ------------------------
1   VERSION  13.10.07.12
2   RELEASE  13.10.07.12
3   LANGUAGE SUPPORT MODE   Standard

select regexp_replace('SatyakiDe','^(.*)([[:upper:]]{1,})(.*) $','\1 \2\3') AS COL_VAL;
 
select regexp_replace('SatyakiDe','^(.*)([[:upper:]]{1,})(.*) $','\1 \2\3') AS COL_VAL;
 
select regexp_replace('SatyakiDe','^(.*)([[:upper:]]{1,})(.*) $','\1 \2\3') AS COL_VAL;
                                   $
  *** Failure 3706 Syntax error:  expected something between '(' and the string 'S' keyword.
                      Statement# 1, Info =35
 *** Total elapsed time was 1 second.

Hope this will give adequate clarity to the answer of that above question.

Now, Lets see some other functionality.

REGEXP_SIMILAR has similar functionality like REGEXP_LIKE in Oracle.

Let's see couple of such cases -

Lets prepare the table with some dummy data -

SELECT * FROM dbc.dbcinfo;
 
    InfoKey  InfoData
    -------- -----------------------
1   VERSION  14.10.01.05
2   RELEASE  14.10.01.04
3   LANGUAGE SUPPORT MODE   Standard
 
 
CREATE MULTISET VOLATILE TABLE TEST_T1
  (
            COL1  VARCHAR(10)
  ) 
ON COMMIT 
PRESERVE ROWS;
 
  INSERT INTO TEST_T1 VALUES('456')
  ;INSERT INTO TEST_T1 VALUES('123x')
  ;INSERT INTO TEST_T1 VALUES('x123')
  ;INSERT INTO TEST_T1 VALUES('y')
  ;INSERT INTO TEST_T1 VALUES('+789')
  ;INSERT INTO TEST_T1 VALUES('-789')
  ;INSERT INTO TEST_T1 VALUES('159-')
  ;INSERT INTO TEST_T1 VALUES('-1-');

Lets check the data now -

SELECT *
FROM TEST_T1;
 
    COL1
1   123x
2   456
3   x123
4   +789
5   -789
6   y
7   159-
8   -1-

Let's look into the various scenarios now -

Case 1 (Returns Mixed Numbers, Signed Numbers & Non Numbers),

SELECT *
 FROM TEST_T1
 WHERE REGEXP_SIMILAR(COL1,'^[0-9]+$','c')=0;
 
    COL1
    -----
1   123x
2   x123
3   +789
4   -789
5   y
6   159-
7   -1-

Case 2 (Returns Only Unsigned Positive Numbers),

SELECT *
 FROM TEST_T1
 WHERE REGEXP_SIMILAR(COL1,'^[0-9]+$','c')=1;
 
COL1
-----
456

Case 3 (Returns All Numbers including Positive, Negative & unsigned),

SELECT *
FROM TEST_T1
WHERE REGEXP_SIMILAR(COL1,'^[+-]?[0-9]+[+-]?$','c')=1;
 
COL1
-----
456
+789
-789
159-
-1-

Case 4 (Returns Only Non Numbers i.e. Characters),

SELECT *
FROM TEST_T1
WHERE REGEXP_SIMILAR(COL1,'[^0-9]+','c')=1;

COL1
----
y

Hope this will give you some additional idea. :)

My objective is to provide basic information to my friends. So, that they can write better SQL in TD while migrating from other popular databases or new developer in TD can get a flavor of this powerful feature & exploit them in all the positive aspect & apply them properly. :D

Really appreciate your time to read this post.

Regards.

Satyaki De.

Labels: 14, analytic function, code, data warehouse, pattern matching, regexp_replace, regexp_similar, regexp_substr, regular expression, snippet, sql, Teradata

Zoho BI Reports

Posted by Satyaki De

Sunday, August 14, 2011

comments (0)

Here is one sample Zoho Bi Reports which directly connects with my local Oracle DB. And, as soon as I'm changing the data - it will directly reflecting the same here.

I'm sure you are really want to know the secret behind this.

Explaining the same very soon.

Bye.

Labels: BI Reports, Online Bi Reports

How to publish live table data into your blog

Posted by Satyaki De

comments (0)

With the help of Zoho Reports, We can easily import our very own table data from Oracle DB & can publish the live data anywhere in the net including my own blog. This is the best example of how easily we can create Dash board using our own application.

I'm going to demonstrate the same very soon.

Till then kindly wait for some time.

Bye.

Few Useful Object Implementation Snippet

Posted by Satyaki De

Monday, January 18, 2010

comments (7)

Hi Friends,

Today i'm going to discuss few useful object implementation snippets with you. How, you can use objects in your Oracle programming and also demonstrate some object oriented approaches implement in Oracle using Objects.

Let's concentrate on our first case.

Type - 1,

test_m@ORCL>
test_m@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>create or replace type address_type as object
  2    (
  3      street_code     varchar2(10),
  4      street_name     varchar2(50)
  5    );
  6  /

Type created.

Elapsed: 00:00:00.10
test_m@ORCL>
test_m@ORCL>create table test_obj
  2    (
  3       empno    number(4),
  4       ename    varchar2(40),
  5       address  address_type,
  6       city     varchar2(30),
  7       constraints pk_empno primary key(empno)
  8    );

Table created.

Elapsed: 00:00:02.40
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city');
Enter value for eno: 1001
Enter value for enm: SATYAKI
Enter value for st_code: 700010
Enter value for st_name: BELEGHATA
Enter value for city: KOLKATA
old   1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new   1: insert into test_obj values(1001,'SATYAKI',address_type('700010','BELEGHATA'),'KOLKATA')

1 row created.

Elapsed: 00:00:00.01
test_m@ORCL>/
Enter value for eno: 1002
Enter value for enm: ATANU
Enter value for st_code: 700100
Enter value for st_name: DLF
Enter value for city: KOLKATA
old   1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new   1: insert into test_obj values(1002,'ATANU',address_type('700100','DLF'),'KOLKATA')

1 row created.

Elapsed: 00:00:00.01
test_m@ORCL>/
Enter value for eno: 1003
Enter value for enm: PRANAB
Enter value for st_code: 700079
Enter value for st_name: VIP ROAD
Enter value for city: KOLKATA
old   1: insert into test_obj values(&eno,'&enm',address_type('&st_code','&st_name'),'&city')
new   1: insert into test_obj values(1003,'PRANAB',address_type('700079','VIP ROAD'),'KOLKATA')

1 row created.

Elapsed: 00:00:00.01
test_m@ORCL>
test_m@ORCL>commit;

Commit complete.

Elapsed: 00:00:00.01
test_m@ORCL>
test_m@ORCL>select e.empno,
  2         e.ename,
  3         cast(e.address.street_code as varchar2(10)) street_code,
  4         cast(e.address.street_name as varchar2(50)) street_name,
  5         e.city
  6  from test_obj e;

     EMPNO ENAME                                    STREET_COD STREET_NAME                                CITY
---------- ---------------------------------------- ---------- -------------------------------------------------- ------------------------------
      1001 SATYAKI                                  700010     BELEGHATA                                  KOLKATA
      1002 ATANU                                    700100     DLF                                        KOLKATA
      1003 PRANAB                                   700079     VIP ROAD                                   KOLKATA

Elapsed: 00:00:00.03
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>

Lets concentrate on our second ventures where we will look into other way of accessing these various objects -

Type - 2 (Overloading),

test_m@ORCL>
test_m@ORCL>create or replace package test_overloadng
  2  is
  3    x      number(5);
  4    procedure test_a(
  5                      a  in number,
  6                      b  in   number,
  7                      c  out number
  8                     );
  9
 10    procedure test_a(
 11                      a  in varchar2,
 12                      b  in  varchar2,
 13                      c   out  varchar2
 14                    );
 15  end;
 16  /

Package created.

Elapsed: 00:00:00.05
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>create or replace package body test_overloadng
  2  is
  3    procedure test_a(
  4                      a  in number,
  5                      b  in   number,
  6                      c  out number
  7                    )
  8    is
  9      x   number(10);
 10    begin
 11      x := a + b;
 12      c := x;
 13    end;
 14    procedure test_a(
 15                      a  in varchar2,
 16                      b  in  varchar2,
 17                      c   out  varchar2
 18                    )
 19    is
 20      x    varchar2(300);
 21    begin
 22      x :=  a||b;
 23      c := x;
 24    end;
 25  end;
 26  /

Package body created.

Elapsed: 00:00:00.04
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>set serveroutput on
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>declare
  2    v    number(10);
  3    w    varchar2(300);
  4  begin
  5    test_overloadng.test_a(10,20,v);
  6    dbms_output.put_line( 'Value of V : ' ||v);
  7    test_overloadng.test_a( 'Satyaki ' , 'De' ,w);
  8    dbms_output.put_line( 'Value of W : ' ||w);
  9  end;
 10  /
Value of V : 30
Value of W : Satyaki De

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
test_m@ORCL>
test_m@ORCL>

Type - 3 (Polymorphism),

test_m@ORCL>
test_m@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_ST AS OBJECT(
  2                                  ID NUMBER ,
  3                                  MEMBER PROCEDURE THIS_PROC
  4                               )
  5  NOT FINAL NOT INSTANTIABLE
  6  /

Type created.

Elapsed: 00:00:00.42
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_T1 UNDER DEMO_ST(
  2                                     OVERRIDING MEMBER PROCEDURE THIS_PROC
  3                                   )
  4  /

Type created.

Elapsed: 00:00:00.36
test_m@ORCL>
test_m@ORCL>CREATE TYPE DEMO_T2 UNDER DEMO_ST(
  2                                     OVERRIDING MEMBER PROCEDURE THIS_PROC
  3                                   )
  4  /

Type created.

Elapsed: 00:00:00.18
test_m@ORCL>
test_m@ORCL>CREATE TYPE BODY DEMO_T1
  2  AS
  3    OVERRIDING MEMBER PROCEDURE THIS_PROC
  4    AS
  5    BEGIN
  6      DBMS_OUTPUT.PUT_LINE('DEMO_T1');
  7    END;
  8  END;
  9  /

Type body created.

Elapsed: 00:00:00.09
test_m@ORCL>
test_m@ORCL>CREATE TYPE BODY DEMO_T2
  2  AS
  3    OVERRIDING MEMBER PROCEDURE THIS_PROC
  4    AS
  5    BEGIN
  6      DBMS_OUTPUT.PUT_LINE('DEMO_T2');
  7    END;
  8  END;
  9  /

Type body created.

Elapsed: 00:00:00.03
test_m@ORCL>
test_m@ORCL>CREATE OR REPLACE PROCEDURE DEMO_PRC(
  2                                        P_OBJ DEMO_ST
  3                                      )
  4  AS
  5    V_OBJ DEMO_ST := P_OBJ ;
  6  BEGIN
  7    V_OBJ.THIS_PROC;
  8  END;
  9  /

Procedure created.

Elapsed: 00:00:00.04
test_m@ORCL>
-- 
-- TAKES AN OBJECT, NOT A PROCEDURE PER SE 
-- BUT ALLOWS THE PROCEDURE TO BE PASSED WRAPPED IN AN 
-- OBJECT 
-- 
test_m@ORCL>
test_m@ORCL>BEGIN
  2    DEMO_PRC(DEMO_T1(1));
  3    DEMO_PRC(DEMO_T2(1));
  4  END;
  5  /
DEMO_T1
DEMO_T2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
test_m@ORCL>
test_m@ORCL>
test_m@ORCL>

Type - 4 (Use Of Member Function),

scott@ORCL>
scott@ORCL>create table employees
  2    (
  3       id number primary key,
  4       name varchar2(30) not null,
  5       emp_status varchar2(1) not null
  6    );

Table created.

Elapsed: 00:00:00.19
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace type employee as object
  2   (
  3     id number,
  4     name varchar2(30),
  5     emp_status varchar2(1),
  6     member function exempt return varchar2
  7   );
  8  /

Type created.

Elapsed: 00:00:00.15
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace type body employee
  2  is
  3    member function exempt return varchar2
  4    is
  5    begin
  6        return  self.name||' '||self.emp_status;
  7    end;
  8  end;
  9  /

Type body created.

Elapsed: 00:00:00.11
scott@ORCL>

Now, to create a view called employees_view (Which will be shown later in this thread) we need the following privileges given by sys as follows -

In Sys,

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

sys@ORCL>grant create any view to scott, test_m;

Grant succeeded.

sys@ORCL>
sys@ORCL>
sys@ORCL>

Now, lets move into our user -

In Scott,

scott@ORCL>
scott@ORCL>create or replace view employees_view of employee
  2  with object identifier(id)
  3  as
  4    select id,
  5           name,
  6           emp_status
  7    from employees;

View created.

Elapsed: 00:00:00.31
scott@ORCL>
scott@ORCL>
scott@ORCL>select id,
  2         name,
  3         emp_status,
  4         o.exempt()
  5  from employees_view o;

no rows selected

Elapsed: 00:00:00.04
scott@ORCL>
scott@ORCL>
scott@ORCL>declare
  2     l_var varchar2(100);
  3     l_obj employee := employee(1, 'E1', 'a');
  4  begin
  5    l_var := l_obj.exempt(); -- works
  6    l_var := l_obj.exempt;   -- works with out parentheses too
  7    dbms_output.put_line(l_var);
  8  end;
  9  /
E1 a

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
scott@ORCL>
scott@ORCL>

Type - 5 (Use Of Member Procedure),

scott@ORCL>create or replace type TMyType is object(  
2          some_value      number,  
3  
4          member function Display return varchar2,  
5          static procedure MyProc( n number )  
6  );  
7  / 

Type created. 

scott@ORCL> 
scott@ORCL> create or replace type body TMyType as  
2  
3          member function Display return varchar2 is  
4          begin  
5                  return(  
6                          TO_CHAR( self.some_value,  '999,999,999,990.00' )  
7                        );  
8          end;  
9 
10          static procedure MyProc( n number ) is 
11          begin 
12                  DBMS_OUTPUT.put_line( 
13                                        TO_CHAR( n, '999,999,999,990.00' ) 
14                                      ); 
15          end; 
16  end; 
17  / 

Type body created. 

scott@ORCL> 
scott@ORCL> 
scott@ORCL> set serveroutput on 
scott@ORCL> begin  
2             TMyType.MyProc( 123456789.12 );  
3           end;  
4  / 
123,456,789.12 

PL/SQL procedure successfully completed. 
scott@ORCL>

Hope this thread will give some wise ways of implementing Oracle Objects.

Keep following - very soon i'll be coming back with another topic here. Till then - Bye.

Satyaki.

Labels: create or replace type, member function, member procedure, objects, overloading, polymorphism

Oracle procedure using Java

Posted by Satyaki De

Thursday, January 14, 2010

comments (0)

Today, i'm going to discuss another powerful feature of Oracle. That is embedding your Java code inside Oracle Procedures. This gives a lot of flexibility & power to Oracle and certainly you can do plenty of things which generally are very difficult to implement directly.

In this purpose i cannot restrict myself to explanation made by Bulusu Lakshman and that is -

From Oracle 9i a new environments are taking place where Java and PL/SQL can interact as two major database languages. There are many advantages to using both languages -

PL/SQL Advantage:

Intensive Database Access - It is faster than Java.
Oracle Specific Functionality that has no equivalent in Java such as using dbms_lock & dbms_alert.
Using the same data types and language construct as SQL providing seamless access to the database.

JAVA Advantage:

Automatic garbage collection, polymorphism, inheritance, multi-threading
Access to system resources outside of the database such as OS commands, files, sockets
Functionality not avialable in PL/SQL such as OS Commands, fine-grained security policies, image generation, easy sending of e-mails with attachements using JavaMail.

But, i dis-agree with him in case of fine grained security policies as Oracle has drastically improves it and introduces security policies like - VPDB (Virtual Private Database) & Database Vault. Anyway, we'll discuss these topics on some other day.

For better understanding i'm follow categories and we will explore them one by one. Hope you get some basic idea on this powerful feature by Oracle.

Before proceed we have to know the basics of the main ingredients called dbms_java .

We've to prepare the environment.

In Sys,

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
sys@ORCL>
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','writeFileDescriptor','');

PL/SQL procedure successfully completed.

Elapsed: 00:00:53.54
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','readFileDescriptor','');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
sys@ORCL>
sys@ORCL>exec dbms_java.grant_permission('SCOTT','SYS:java.io.FilePermission','D:\Java_Output\*.*','read,write,execute,delete');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
sys@ORCL>

Let's concentrate on our test cases.

In Scott,

Type: 1

scott@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:02.77
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace and compile java source named "Print_Hello"
  2  as
  3    import java.io.*;
  4    public class Print_Hello
  5      {
  6         public static void dislay()
  7           {
  8             System.out.println("Hello World...... In Java Through Oracle....... ");
  9           }
 10      };
 11  /

Java created.

Elapsed: 00:00:44.17
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure java_print
  2  as
  3    language java name 'Print_Hello.dislay()';
  4  /

Procedure created.

Elapsed: 00:00:01.39
scott@ORCL>
scott@ORCL>call dbms_java.set_output(1000000);

Call completed.

Elapsed: 00:00:00.34
scott@ORCL>
scott@ORCL>set serveroutput on size 1000000;
scott@ORCL>
scott@ORCL>exec java_print;
Hello World...... In Java Through Oracle.......

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22
scott@ORCL>

Type: 2 (Returning Value from JAVA)

scott@ORCL>
scott@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.13
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace and resolve java source named "ReturnVal"
  2  as
  3    import java.io.*;
  4
  5    public class ReturnVal extends Object
  6      {
  7          public static String Display()
  8          throws IOException
  9          {
 10             return "Hello World";
 11          }
 12     };
 13  /

Java created.

Elapsed: 00:00:00.22
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace function ReturnVal
  2  return varchar2
  3  is
  4     language java
  5     name 'ReturnVal.Display() return String';
  6  /

Function created.

Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(1000000);

Call completed.

Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>column ReturnVal format a15
scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>select ReturnVal from dual;

RETURNVAL
---------------
Hello World

Elapsed: 00:00:00.12
scott@ORCL>
scott@ORCL>

So, you can return the value from the compiled Java source, too.

Type: 3 (Reading console value into JAVA)

scott@ORCL>ed
Wrote file C:\OracleSpoolBuf\BUF.SQL

  1  create or replace java source named "ConsoleRead"
  2  as
  3    import java.io.*;
  4    class ConsoleRead
  5      {
  6        public static void RDisplay(String Det)
  7          {
  8             String dd = Det;
  9             System.out.println("Value Passed In Java Is: " + dd);
 10             System.out.println("Exiting from the Java .....");
 11          }
 12*     };
 13  /

Java created.

scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure java_UserInput(InputStr in varchar2)
  2     as
  3       language java
  4       name 'ConsoleRead.RDisplay(java.lang.String)';
  5  /

Procedure created.

scott@ORCL>

scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);

Call completed.

scott@ORCL>
scott@ORCL>
scott@ORCL>set serveroutput on size 100000
scott@ORCL>
scott@ORCL>exec java_UserInput('Satyaki');
Value Passed In Java Is: Satyaki
Exiting from the Java .....

PL/SQL procedure successfully completed.

scott@ORCL>

Type: 4 (Reading file from OS directory using JAVA)

scott@ORCL>ed
Wrote file C:\OracleSpoolBuf\BUF.SQL

  1  create or replace java source named "ReadTextFile"
  2  as
  3    import java.io.*;
  4    class ReadTextFile
  5  {
  6     public static void Process(String FileName) throws IOException
  7       {
  8          int i;
  9          FileInputStream fin;
 10          try
 11           {
 12             fin = new FileInputStream(FileName);
 13           }
 14         catch(FileNotFoundException e)
 15           {
 16              System.out.println("File Not Found....");
 17              return;
 18           }
 19          catch(ArrayIndexOutOfBoundsException e)
 20           {
 21              System.out.println("Usage: showFile File");
 22              return;
 23           }
 24         do
 25          {
 26             i = fin.read();
 27             if(i != 1)
 28               System.out.println((char) i);
 29          }while(i != 1);
 30         fin.close();
 31       }
 32*  };
 33  /

Java created.

scott@ORCL>
scott@ORCL>create or replace procedure Java_ReadTextFile(FileNameWithPath in varchar2)
  2  as
  3    language java
  4    name 'ReadTextFile.Process(java.lang.String)';
  5  /

Procedure created.

scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);

Call completed.

scott@ORCL>
scott@ORCL>
scott@ORCL>exec Java_ReadTextFile('D:\Java_Output\Trial.txt');

Type: 4 (Writing file in OS directory using JAVA)

In Scott,

scott@ORCL>
scott@ORCL>create or replace java source named "DynWriteTextFile"
  2  as
  3    import java.io.*;
  4  class DynWriteTextFile
  5  {
  6    public static void proc(String ctent,String FlNameWithPath) throws IOException
  7      {
  8        int i,j;
  9        String FileNm = FlNameWithPath;
 10        RandomAccessFile rFile;
 11
 12        try
 13         {
 14           rFile = new RandomAccessFile(FileNm,"rw");
 15         }
 16        catch(FileNotFoundException e)
 17         {
 18            System.out.println("Error Writing Output File....");
 19            return;
 20         }
 21
 22        try
 23         {
 24           int ch;
 25
 26           System.out.println("Processing starts...");
 27
 28           ch = ctent.length();
 29
 30           rFile.seek(rFile.length());
 31           for(int k=0; k<ch; k=k+ctent.length())
 32            {
 33               rFile.writeBytes(ctent);
 34            }
 35         }
 36        catch(IOException e)
 37         {
 38           System.out.println("File Error....");
 39         }
 40        finally
 41     {
 42               try
 43        {
 44                  System.out.println("Successfully file generated....");
 45                  rFile.close();
 46        }
 47          catch(IOException oe)
 48      {
 49                System.out.println("Exception in the catch block of finally is: " +oe);
 50                System.exit(0);
 51      }
 52        }
 53     }
 54  };
 55  /

Java created.

Elapsed: 00:00:00.17
scott@ORCL>
scott@ORCL>
scott@ORCL>create or replace procedure JavaDyn_WriteTextFile(para in varchar2,FileNameWithPath in varchar2)
  2  as
  3    language JAVA
  4    name 'DynWriteTextFile.proc(java.lang.String, java.lang.String)';
  5  /

Procedure created.

Elapsed: 00:00:00.15
scott@ORCL>

In Sys,

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

sys@ORCL>set timi on
sys@ORCL>
sys@ORCL>
sys@ORCL>create or replace public synonym dbms_dwrite_file for scott.JavaDyn_WriteTextFile;

Synonym created.

Elapsed: 00:00:00.08
sys@ORCL>
sys@ORCL>grant execute on dbms_dwrite_file to scott;

Grant succeeded.

Elapsed: 00:00:00.18
sys@ORCL>

In Scott,

scott@ORCL>
scott@ORCL>create or replace procedure DWrite_Content(dt in date,FileNmWithPath in varchar2)
  2  is
  3    cursor c1
  4    is
  5      select empno,ename,sal
  6      from emp
  7      where hiredate = dt;
  8    r1 c1%rowtype;
  9
 10    str  varchar2(500);
 11  begin
 12     str:= replace(FileNmWithPath,'\','\\');
 13     dbms_dwrite_file('Employee No'||' '||'First Name'||' '||'Salary',str);
 14     dbms_dwrite_file(chr(10),str);
 15     dbms_dwrite_file('---------------------------------------------------',str);
 16     dbms_dwrite_file(chr(10),str);
 17     for r1 in c1
 18     loop
 19       dbms_dwrite_file(r1.empno||' '||r1.ename||' '||r1.sal,str);
 20       dbms_dwrite_file(chr(10),str);
 21     end loop;
 22  exception
 23      when others then
 24        dbms_output.put_line(sqlerrm);
 25  end;
 26  /

Procedure created.

Elapsed: 00:00:00.43
scott@ORCL>
scott@ORCL>
scott@ORCL>call dbms_java.set_output(100000);

Call completed.

Elapsed: 00:00:00.02
scott@ORCL>
scott@ORCL>exec DWrite_Content(to_date('21-JUN-1999','DD-MON-YYYY'),'D:\Java_Output\satyaki.txt');
Processing starts...
Successfully file generated....

PL/SQL procedure successfully completed.

Hope, this thread will give you some basic idea about using your Java code with Oracle PL/SQL.

I'll discuss another topic very soon. Till then - Keep following. ;)

Regards.

Labels: call, clob, dbms_dwrite_file, dbms_java, extends, filenotfoundexception, filepermission, grant_permission, ioexception, java, objects, operating system, println, serveroutput, vpdb

How to store data from XML to Tables

Posted by Satyaki De

Friday, January 8, 2010

comments (0)

In the previous post we have discussed about generating an XML file using Oracle SQL XML functions. Today we will do that in reverse manner. That means we will load the data generated by that query in the database tables.

At the end of this post (Which is a continue of the previous post) - you will be successfully generate an XML file from Oracle Tables & also able to load the data from XML on that same structured tables. So, that will complete the full life cycle of XML in oracle(Obviously concentrate on some basics).

Lets see -

Our main ingredients for this class - is the XML file named - emp_oracle.xml

And, it looks like -

<?xml version="1.0" encoding="UTF-8"?>
<EmployeeList>
  <Emp>
      <Employee_ID>200</Employee_ID>
      <First>Whalen</First>
      <Sal>4400</Sal>
      <HireDate>1987-09-17</HireDate>
  </Emp>
  <Emp>
      <Employee_ID>201</Employee_ID>
      <First>Billy</First>
      <Sal>4500</Sal>
      <HireDate>1985-06-10</HireDate>
  </Emp>
  <Emp>
      <Employee_ID>202</Employee_ID>
      <First>Bireswar</First>
      <Sal>9000</Sal>
      <HireDate>1978-06-10</HireDate>
  </Emp>
</EmployeeList>

We need to create one Oracle Directories to map with the Operating System directories in the following manner ->

sys@ORCL>
sys@ORCL>select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
sys@ORCL>
sys@ORCL>
sys@ORCL>CREATE OR REPLACE DIRECTORY SATY_DIR AS 'D:\XML_Output'
  2  /

Directory created.

Elapsed: 00:00:00.23
sys@ORCL>
sys@ORCL>GRANT READ, WRITE ON DIRECTORY SATY_DIR TO SCOTT, HR;

Grant succeeded.

Elapsed: 00:00:00.08
sys@ORCL>

Once you have created the directory successfully and give the proper privileges to the users like Scott or Hr - you have completed one important component of today's test. Still we are far to go. Now the second part is -

scott@ORCL>
scott@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>CREATE TABLE t
  2   (
  3     serialNo  NUMBER(10),
  4     fileName  VARCHAR2(100),
  5     xml       XMLTYPE,
  6     constraints pk_serialNo primary key(serialNo)
  7   );

Table created.

Elapsed: 00:00:04.13
scott@ORCL>
scott@ORCL>
scott@ORCL>CREATE SEQUENCE x_seq
  2  START WITH 1
  3  INCREMENT BY 1;

Sequence created.

Elapsed: 00:00:00.31
scott@ORCL>
scott@ORCL>CREATE OR REPLACE PROCEDURE load_xml(
  2                                        p_dir       IN  VARCHAR2,
  3                                        p_filename  IN  VARCHAR2
  4                                      )
  5  IS
  6    l_bfile  BFILE := BFILENAME(p_dir, p_filename);
  7    l_clob   CLOB;
  8  BEGIN
  9    DBMS_LOB.createtemporary (l_clob, TRUE);
 10
 11    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
 12    DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
 13    DBMS_LOB.fileclose(l_bfile);
 14
 15    INSERT INTO t(
 16                   serialNo,
 17                   fileName,
 18                   xml
 19                 )
 20    VALUES (
 21             x_seq.NEXTVAL,
 22             p_filename,
 23             XMLTYPE.createXML(l_clob)
 24           );
 25
 26    COMMIT;
 27
 28    DBMS_LOB.freetemporary(l_clob);
 29  END;
 30  /

Procedure created.

Elapsed: 00:00:00.88
scott@ORCL>
scott@ORCL>EXEC load_xml(p_dir => 'SATY_DIR', p_filename => 'emp_oracle.xml');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
scott@ORCL>
scott@ORCL>set long 5000
scott@ORCL>
scott@ORCL>set pagesize 0
scott@ORCL>
scott@ORCL>select xml from t;
<?xml version="1.0" encoding="UTF-8"?>
<EmployeeList>
  <Emp>
      <Employee_ID>200</Employee_ID>
      <First>Whalen</First>
      <Sal>4400</Sal>
      <HireDate>1987-09-17</HireDate>
  </Emp>
  <Emp>
      <Employee_ID>201</Employee_ID>
      <First>Billy</First>
      <Sal>4500</Sal>
      <HireDate>1985-06-10</HireDate>
  </Emp>
  <Emp>
      <Employee_ID>202</Employee_ID>
      <First>Bireswar</First>
      <Sal>9000</Sal>
      <HireDate>1978-06-10</HireDate>
  </Emp>
</EmployeeList>


Elapsed: 00:00:00.10
scott@ORCL>

Ok. So, we've initially load the data into the temp table t. But, we need to load the data from this temp table t to our target table revive_xml which will look like -

scott@ORCL>create table revive_xml
  2   (
  3     rev_emp_id   number(4),
  4     rev_f_name   varchar2(40),
  5     rev_salary   number(10,2),
  6     rev_jn_dt    date,
  7     constraints pk_rev_emp_id primary key(rev_emp_id)
  8   );

Table created.

Elapsed: 00:00:00.40
scott@ORCL>

Ok. So, we have done another important part of our job. Let's concentrate on our final mission -

scott@ORCL>insert into revive_xml(
  2                          rev_emp_id,
  3                          rev_f_name,
  4                          rev_salary,
  5                          rev_jn_dt
  6                        )
  7  select cast(t1.EmployeeId as number(4)) EmployeeId,
  8         t2.FirstName,
  9         cast(t3.Salary as number(10,2)) Salary,
 10         to_date(t4.JoiningDt,'YYYY-MM-DD') JoiningDt
 11  from (
 12          select rownum rn1,
 13                  extractValue(value(EmployeeId),'/Emp/Employee_ID') EmployeeId
 14          from t,
 15               table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) EmployeeId
 16       ) t1,
 17       (
 18          select rownum rn2,
 19                  extractValue(value(FirstName),'/Emp/First') FirstName
 20          from t,
 21               table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) FirstName
 22       ) t2,
 23       (
 24          select rownum rn3,
 25                  extractValue(value(Salary),'/Emp/Sal') Salary
 26          from t,
 27               table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) Salary
 28       ) t3,
 29       (
 30          select rownum rn4,
 31                  extractValue(value(HireDate),'/Emp/HireDate') JoiningDt
 32          from t,
 33               table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) HireDate
 34       ) t4
 35  where t1.rn1 = t2.rn2
 36  and   t1.rn1 = t3.rn3
 37  and   t1.rn1 = t4.rn4;

3 rows created.

Elapsed: 00:00:00.16
scott@ORCL>
scott@ORCL>commit;

Commit complete.

Elapsed: 00:00:00.22
scott@ORCL>
scott@ORCL>
scott@ORCL>select * from revive_xml;

REV_EMP_ID REV_F_NAME                               REV_SALARY REV_JN_DT
---------- ---------------------------------------- ---------- ---------
       200 Whalen                                         4400 17-SEP-87
       201 Billy                                          4500 10-JUN-85
       202 Bireswar                                       9000 10-JUN-78

scott@ORCL>

So, you have done it finally.

You can do it another way but that is limited to single record parsing -

scott@ORCL>with t
  2  as (
  3      select xmlType('
  4      <EmployeeList>
  5        <Emp>
  6        <Employee_ID>200</Employee_ID>
  7        <First>Whalen</First>
  8        <Sal>4400</Sal>
  9        <HireDate>1987-09-17</HireDate>
 10        </Emp>
 11      </EmployeeList>') xml from dual
 12    )
 13  SELECT rownum rn,
 14         a.EmployeeId,
 15         a.FirstName,
 16         a.Salary,
 17         a.JoiningDt
 18  FROM t,
 19       XMLTABLE('/EmployeeList'
 20       PASSING t.xml
 21       COLUMNS
 22          EmployeeId varchar2(10) PATH '/EmployeeList/Emp/Employee_ID',
 23          FirstName varchar2(20) PATH '/EmployeeList/Emp/First',
 24          Salary number(10) PATH '/EmployeeList/Emp/Sal',
 25          JoiningDt date PATH '/EmployeeList/Emp/HireDate'
 26        ) a;

        RN EMPLOYEEID FIRSTNAME                SALARY JOININGDT
---------- ---------- -------------------- ---------- ---------
         1 200        Whalen                     4400 17-SEP-87

scott@ORCL>
scott@ORCL>

Hope this will solve your purpose.

Also you can refer to the following XML In Oracle link.

Regards.

Labels: create or replace type, grant, hr, on, operating system, oracle directories, read, scott, write

Generating XML from Oracle

Posted by Satyaki De

Tuesday, January 5, 2010

comments (4)

Hi friends, today we will see one of few useful case where we'll prepare XML from Oracle. From 9i, oracle has incorporated all major functionality which helped us to generate/parse XMLs. For details -> you can refer documents which describe these Oracle XML Functions .

You can also visit the official site which contains all the essential ingredients to learn the basics.

1 - Using XQuery

2 - Generating XML

Let us concentrate on our small test case, now.

We need to produce one XML and initial input will be two dates as date range and also the department number. Date should look in this format -> YYYY-MM-DD

Date should enclose within tag and employee details should enclosed within tag. Each record inside this would be enclosed as tag.

So, the final XML look like this ->

<?xml version="1.0" encoding="UTF-8"?>
<period>
  <START_DT>format of YYYY-MM-DD</START_DT>
  <END_DT>format of YYYY-MM-DD</END_DT>
</period>
<EmployeeList>
  <Emp>
    .... Employee Details ....
  </Emp>
</EmployeeList>

Step - 1

Save the following script in a file called emp_oracle.sql

set feedback off
set verify off
set trimspool on
set long 100000
set heading off
set serveroutput on
set timi off

spool d:\emp_oracle.xml
SELECT XMLROOT (
                   XMLELEMENT("period", XMLFOREST(e.start_dt, e.end_dt))
               ) AS "XMLROOT"
FROM (
          select to_date('&&1','DD-MON-YYYY') start_dt,
                 to_date('&&2','DD-MON-YYYY') end_dt
          from dual
     ) e
union all
SELECT XMLELEMENT(
                    "EmployeeList",
                    XMLAGG(
                            XMLELEMENT(
                                        "Emp",
                                        XMLFOREST(
                                                    e.employee_id as "Employee_ID", 
                                                    e.last_name as "First", 
                                                    e.salary as "Sal", 
                                                    e.hire_date as "HireDate"
                                                 )
                                    )
                          )
                  )
FROM employees e
WHERE department_id = &&3;

spool off

set feedback on
set verify on
set trimspool off
set heading on
set timi on

Step - 2

Execute the above query.

hr@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
hr@ORCL>
hr@ORCL>
hr@ORCL>@D:\emp_oracle.sql
Enter value for 1: 01-JAN-1979
Enter value for 2: 05-JAN-2010
Enter value for 3: 10

Step -3

Check the output and it will look like -

<?xml version="1.0" encoding="UTF-8"?>
<period>
  <START_DT>1979-01-01</START_DT>
  <END_DT>2010-01-05</END_DT>
</period>
<EmployeeList>
  <Emp>
   <Employee_ID>200</Employee_ID>
   <First>Whalen</First>
   <Sal>4400</Sal>
   <HireDate>1987-09-17</HireDate>
  </Emp>
</EmployeeList>

Hope this basic & simple simulations will give you initial idea on this topic.

Regards.

Labels: xml, XMLAGG, XMLELEMENT, XMLFOREST, XMLROOT

Magic SQL

Posted by Satyaki De

comments (0)

Few years before in OTN one of the user is looking for a solutions, which we think might not possible to provide in a single SQL solution. At that time Michael came to rescue that and for the first time he showed some interesting XML Kung-Fu to all of us and earned a great reputation for providing magic solution to others. I personally love to call them as Magic SQL.

The following SQL calculates number of rows in each table in a specific schema without visiting any dba views. This particular script is my 2nd personal favourite.

scott@ORCL>
scott@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.01
scott@ORCL>
scott@ORCL>
scott@ORCL>select table_name,
  2         DBMS_XMLGEN.getxmltype(
  3                                 'select count(*) c from '||table_name
  4                               ).extract('//text()').getnumberval() tot_rows
  5  from user_tables
  6  where iot_type is null
  7  or iot_type != 'IOT_OVERFLOW';

TABLE_NAME                       TOT_ROWS
------------------------------ ----------
DEPT                                    4
EMP                                    14
BONUS                                   0
SALGRADE                                5
EMP_DETAILS                             3
T_EMP                                   0
AUDIT_T                                 0
C_C                                     4
TRAIN_DET                               2
TEST_COM_IMP                            2
TIME_P                                  1

TABLE_NAME                       TOT_ROWS
------------------------------ ----------
PRI_UQ                                  4
TEST_CHK                                0
ANSHUMANSAHAISGENIOUS                   1
XEUROPE                                 2
D_D                                     8
PUBLICTRANSPORT                         4
XASIA                                   2
TF1                                     0
TEST_HH                                14
TEST_SWAP                               4
XGMT                                    1

TABLE_NAME                       TOT_ROWS
------------------------------ ----------
CUSTOMER_DET                            1
FOURWHEELER                             2
SPOOL_LOG                              13
CITYTRANSPORT                           8
T1                                      2
T2                                      2
A_A                                     1
B_B                                     2
AUTOMOBILE                              1
XDUAL                                   1
S_TEMP                                  0

33 rows selected.

Elapsed: 00:00:00.42
scott@ORCL>

But, this particular script has some limitations on Index-Organized-Table. But, overall this will work brilliantly without even touching all the dba views.

Grouped information in comma-separated values.

scott@ORCL>with tt
  2  as
  3    (
  4      select 1 id, 'saty' name from dual
  5      union all
  6      select 1, 'anup' from dual
  7      union all
  8      select 1, 'sas' from dual
  9      union all
 10      select 2, 'rajib' from dual
 11      union all
 12      select 2, 'amit' from dual
 13    )
 14  select id,
 15         cast(wmsys.wm_concat(name) as varchar2(100)) src
 16  from tt
 17  group by id;

        ID SRC
---------- ------------------------------------------------------
         1 saty,anup,sas
         2 rajib,amit

Elapsed: 00:00:01.62
scott@ORCL>
scott@ORCL>

Function wm_concat is undocumented. So, you cannot use it in production environment. Even if you use - you won't get any technical support from oracle if you have any production issue due to this. So, better not to use this function. But, certainly this reduces lots of our effort and provides a solution using single SQL. I'm still waiting to see it as documented function by Oracle. Till then, you have to go for your custom tailored solution.

The following sqls parse comma-separated values into rows. But, this cannot be applicable into any column of existing tables.

scott@ORCL>
scott@ORCL>SELECT cast(column_value as varchar2(40)) res
  2  FROM TABLE(SYS.dbms_debug_vc2coll (24, 34, 25));

RES
----------------------------------------
24
34
25

Elapsed: 00:00:00.03
scott@ORCL>
scott@ORCL>
scott@ORCL>SELECT cast(column_value as varchar2(40)) res
  2  FROM TABLE(SYS.dbms_debug_vc2coll ('A', 'B', 'C'));

RES
----------------------------------------
A
B
C

Elapsed: 00:00:00.05
scott@ORCL>
scott@ORCL>SELECT cast(column_value as varchar2(40)) res
  2  FROM TABLE(SYS.dbms_debug_vc2coll (24, 'B', '@'));

RES
----------------------------------------
24
B
@

Elapsed: 00:00:00.14
scott@ORCL>

This can handle alpha numeric data type without declaring any custom type for it.

Following SQL is which i prefer most as one of the brilliant features introduced in Oracle and can surprise many developer. Write a select query to retrieve any column information without using the select clause. I know - it sounds crazy. But, you really can do that.

That is what i consider is the leading contender of my Magic SQL category.

scott@ORCL>
scott@ORCL>
scott@ORCL>xquery for $i in ora:view("emp")/ROW/ENAME return $i/text()
  2  /

Result Sequence
----------------------------------------------------------------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

Result Sequence
----------------------------------------------------------------------------
JAMES
FORD
MILLER

14 item(s) selected.

Elapsed: 00:00:00.14
scott@ORCL>
scott@ORCL>

Hope you liked this edition.

Regards.

Labels: cast, comma-separated, dbms_debug_vc2coll, dbms_xmlgen, getxmltype, return, v$version, wm_concat, xquery

Satyaki De - SQL Exploration

Oracle Certified

Me

About Me

Readers

Visitors

Search This Blog

Blog Archive

Labels

Blog's New Address

Regular Expression on Teradata 14.0

Zoho BI Reports

How to publish live table data into your blog

Few Useful Object Implementation Snippet

Oracle procedure using Java

How to store data from XML to Tables

Generating XML from Oracle

Magic SQL