Satyaki De - SQL Exploration

I'm a software engineer. I love my Oracle more than me. And, i'm senior forum member in OTN from Dec 2006 and completed my OCA. This blog mainly deals with Oracle & it's relevant technologies from useful perspective. Currently, I'm working as Teradata & Informatica ETL Solution designer. And, also take part in Teradata forum discussion.

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.


0 comments: