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.
