Substr in unix/linux shell script

Linux: expr substr 1 3
Solaris: awk '{print substr($1, 1,3}' (the expr in solaris does not support substr)

Access windows file server from Linux

1. Use smbclient (a ftp-like client allows you put/get file)

smbclient -U Windows_Domain\\user //windows_file_server_name/dir

it prompts for your windows password

2. Mount the file server

mount -t cifs '//windows_file_server_name/dir' /mnt/mount_point -o username=Windows_Domain\\user,rw,iocharset=utf8

After typing your windows password, you can access the files on the windows server
cd /mnt/mount_point
ls -l
...

The above are tested in FC6

Sort tab delimited Japanese Data (unix)

sort -t ******** # push a tab key here, it wouldn't accept \t

in zsh, it will be a no-go because tab is assigned for auto-completion. You may need to create a shell file and put the command in.

the file would like

sort -t " " ......

In order to sort Japanese Data, you have to set LANG=jp_JP.UTF-8 for Linux
For Solaris LC_CTYPE=jp_JP.UTF-8 will do the thing.

Decimal to hex and vice versa

select to_char(134829, 'XXXXX') from dual;
select to_number('AFF0', 'XXXXXX') from dual;

Merge continuous ranges

SQL> select * from test;

ID S E
---------- ---------- ----------
1 1 5
1 5 7
1 8 12
1 12 19

SELECT ID, MIN (x), MAX (x)
FROM (SELECT CEIL (ROWNUM / 2) rn, ID, x
FROM ((SELECT ID, s x
FROM TEST
MINUS
SELECT ID, e x
FROM TEST)
UNION
(SELECT ID, e x
FROM TEST
MINUS
SELECT ID, s x
FROM TEST)))
GROUP BY ID, rn


ID MIN(X) MAX(X)
---------- ---------- ----------
1 8 19
1 1 7

SSH port forwarding on Unix/Linux

ssh -L your_client_ip_or_name:port1:destination_ip_or_name:22 proxy_ip_or_name -l username

your_client_id_or_name (The machine you want to start the connection to destination)
port1:whatever port in the client machine
destination_ip_or_name (the machine you want to connect to)
proxy_ip_or_name (The machine used as proxy, it is connectable to your_client and destination)

ssh -p port1 your_client_ip_or_name
this will connect you to destination

client -> client port 1 -> proxy -> destination

Converting Unix time_t to Oracle Date

select to_date(19700101, 'YYYYMMDD') + unix_time_t/(24*60*60) from dual;

dump & zip the dmp file on the fly

# create a named pipe
mknod exp.pipe p
# read the pipe - output to zip file in the background
gzip < exp.pipe > scott.exp.gz &
# feed the pipe
exp userid=scott/tiger file=exp.pipe ...

SQL Loader: Skip column

In the control file, you can specify a filler column so that it will be skipped.

SQL> desc dept
Name
-----------------
DEPTNO
DNAME
LOC

Control File

LOAD DATA
INFILE *
INTO TABLE dept
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(x filler integer, dname, loc)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND

It will allow you only load value for column dname and loc. The first column ID will be skipped.

One row goes to two groups

SQL> select * from tst;

C1 C2 C3
---------- ---------- ----------
1 150
1 1 80
1 2 90
1 1 130
1 3 120

Make sum on C2 based on group (c1, c2), the special requirement is
within the group of (1, null), it should include 10% of the sum of
other groups which c2 is not null. To achive this, one row will go
to 2 groups. one for itself and one maps to (c1, null).

The query will be

select
C1,
(CASE WHEN TYPE = 1 THEN C2
WHEN TYPE = 2 THEN NULL
END) C2,
SUM(CASE WHEN TYPE = 1 THEN C3
WHEN TYPE = 2 AND C2 IS NOT NULL THEN C3*0.1
ELSE NULL
END) C3_SUM
from
(
select c1, c2, sum(c3) c3
from tst
group by c1, c2
), (select 1 type from dual union all select 2 type from dual
)
group by
C1,
(CASE WHEN TYPE = 1 THEN C2
WHEN TYPE = 2 THEN NULL
END)

and result is

C1 C2 C3_SUM
---------- ---------- ----------
1 192
1 1 210
1 2 90
1 3 120

get database parameter

You do not need to have DBA priviledge to view all the database parameters

A simple script will do

set serveroutput on size 1000000

declare
x number;
y long;
d number;
begin
if ( dbms_utility.get_parameter_value( '&1', x, y ) = 1 )
then
dbms_output.put_line( y );
else
dbms_output.put_line( x );
end if;
end;
/

Unfortunately, the following parameters are not supported

shared_pool_size
sga_max_size
shared_pool_reserved_size
large_pool_size
java_pool_size
db_keep_cache_size
db_recycle_cache_size
db_2k_cache_size
db_4k_cache_size
db_8k_cache_size
db_16k_cache_size
db_32k_cache_size
db_cache_size
pga_aggregate_target

Join Methods

Abstracted from Oracle Online Document
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#56299

1. Nested Loop

The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.
The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.
The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian product; for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods when two independent row sources are joined together.

2. Hash Join

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:
A large amount of data needs to be joined.
A large fraction of the table needs to be joined.

(Note: The smaller one will be used to build the in-memory hash)

3. Sort Merge

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:
The join condition between two tables is not an equi-join.
OPTIMIZER_MODE is set to RULE.
HASH_JOIN_ENABLED is false.
Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
The optimizer thinks that the cost of a hash join is higher, based on the settings of HASH_AREA_SIZE and SORT_AREA_SIZE.

Note:
Oracle does not recommend using the HASH_AREA_SIZE and SORT_AREA_SIZE parameters unless the instance is configured with the shared server option. Oracle recommends instead that you enable automatic sizing of SQL work areas by setting PGA_AGGREGATE_TARGET. HASH_AREA_SIZE and SORT_AREA_SIZE are retained only for backward compatibility.

Reverse a multi-byte string

reverse() reverses a single byte string but it does not work well with multi-byte characters

SQL> select reverse('abc') from dual;
REVERS
------
cba

SQL> exec :mystr := '国中cba';
1 SELECT max(replace(SYS_CONNECT_BY_PATH (c, '/'), '/')) keep (dense_rank last order by level)
2 FROM (SELECT LEVEL lvl, SUBSTR (:mystr, LEVEL, 1) c
3 FROM (SELECT LENGTH (:mystr) len
4 FROM DUAL) x
5 CONNECT BY LEVEL <= x.len
6 ORDER BY 1 DESC)
7* CONNECT BY PRIOR lvl = lvl + 1

MAX(REPLACE(SYS_CONNECT_BY_PATH(C,'/'),'/'))KEEP(DENSE_RANKLASTORDERBYLEVEL)
----------------------------------------------------------------------------
abc中国

Find rows containing multi-byte character

select *
from
you_table
where length(your_column) <> lengthb(your_column)

Passing a select as Cursor to a Function

create or replace function CursorSum(cur SYS_REFCURSOR) return number
AS
ln_int NUMBER;
ln_sum NUMBER := 0;
BEGIN
loop
fetch cur into ln_int;
exit when cur%NOTFOUND;
ln_sum := ln_sum + ln_int;
end loop;
return ln_sum;
END;
/

select CursorSum(Cursor(select 10 from dual union all select 20 from dual)) as sum
from dual

SUM
-----
30

Select from a function

The following steps allow you select from a function return value just like a normal table.

1. Create a record type

Create TYPE MyRecType AS OBJECT
(c1 number, c2 varchar2(5));
/

2. Create a table type

Create Type MyTabType AS Table of MyRecType;
/

3. Create a function returning the Table Type

Create or Replace function MyFunc return MyTabType
AS
mytab MyTabType := MyTabType();
BEGIN
mytab.extend(2);
mytab(1) := MyRecType(1, 'abc');
mytab(2) := myRecType(2, 'efg');
return mytab;
END;
/

4. Do you select

SQL> select * from TABLE(MyFunc);

C1 C2
---------- --------------------
1 abc
2 efg

** Note **
You can make the function as PIPELINE (refer to Oracle Docs for details)

get one line randomly from a group

SQL> select c1, c2
2 from x;

C1 C2
----- ------------
1 a
1 b
1 c
2 a
2 c
3 f
4 c
5 a

SQL> select
2 c1,
3 substr(max(dbms_random.string('a', 1) || c2), 2) random_c2
4 from x
5 group by c1
6 /

C1 RANDOM_C2
----- --------------------
1 b
3 f
5 a
2 a
4 c

Or you can do it with a fancy way
1 select c1,
2 min(c2) keep (dense_rank first order by dbms_random.value) random_c2
3 from x
4 group by c1
5* order by 1

C1 RANDO
-------------------- -----
1 a
2 c
3 f
4 c
5 a

Transpose rows into columns

TABLE EMP_B
EMP_ID/EMP_NAME

111/TEST RARO/
112/ICHIGO MUJIKO
113/NORISHIMA ICHIRO
…

Table EMP_PHONE
(One employee may have up to 3 types of phone numbers, Type 1 – home, type2 – office and type 3 - cell)

EMP_ID/TYPE/PHONE NUMBER

111/1/037378877987
111/2/048880000078
112/1/034878887888
112/2/044545645878
112/3/090945564456
114/1/456777899889

With the following query, you can get all the phone number into one row for one employee


SELECT a.emp_id,
a. emp_name,
NVL(b.phone1, ‘NA’) phone_1,
NVL(b.phone2, ‘NA’) phone_2,
NVL(b.phone3, ‘NA’) phone_3
FROM
EMP_B a,
(SELECT EMP_ID
MIN(DECODE(TYPE, 1, PHONE_NUMBER, NULL)) PHONE1,
MIN(DECODE(TYPE, 2, PHONE_NUMBER, NULL)) PHONE2,
MIN(DECODE(TYPE, 3, PHONE_NUMBER, NULL)) PHONE3
FROM EMP_PHONE
GROUP BY EMP_ID) b
WHERE a.emp_id = b.emp_id (+)

The result will be

EMP_ID EMP_NAME PHONE_1 PHONE_2 PHONE_3

111 TEST_RARO 037378877987 048880000078 NA
112 ICHIGO_MUJIKO 034878887888 044545645878 90945564456
113 NORISHIMA ICHIRO NA NA NA

Strip out the number from a string

select result
from
(
select level, replace(sys_connect_by_path(x, '/'), '/') result
from
(select rownum lvl, x from (select level lvl, substr('XYZabcd1234.34efEFGgh', level, 1) x
from dual connect by 1 = 1 and level <= length('XYZabcd1234.34efEFGgh'))
where x between '0' and '9' or x = '.')
connect by prior lvl = lvl -1
start with lvl = 1
order by level desc
)
where rownum < 2

RESULT
---------
1234.34

Count String Occurance

(length(str1) - length(replace(str1, str2)))/length(str2)
gives how many times str2 appears in str1.

e.g.

select (length('xyzabc123abcefgh') - length(replace('xyzabc123abcefgh', 'abc')))/length('abc') from dual

OCCURANCE
----------
2

split a string into multiple lines by comma

SQL> select c2 from test;

C2
----------------------------------------
a,b,c,dd,efg
h,iii


select
decode(lvl, 1, substr(c2, 1, instr(c2, ',', 1) - 1),
length(c2) - length(replace(c2, ',')) + 1, substr(c2, instr(c2, ',', 1, lvl-1)+1),
substr(c2, instr(c2, ',', 1, lvl-1) + 1, instr(c2, ',', 1, lvl) - instr(c2, ',', 1, lvl-1) -1))
what_you_need
from
(
select level lvl from (select max(length(c2) - length(replace(c2, ','))) + 1 maxlvl from test)
connect by 1 = 1 and level <= maxlvl
),
test
where lvl < length(c2) - length(replace(c2, ',')) + 2
/

WHAT_YOU_NEED
-------------
a
b
c
dd
efg
h
iii

Group by continuous date range

SQL> select * from sales order by 1;

EVENT_TIME AMT
-------------------- ----------
06-01-18 62
06-01-19 43
06-01-20 18
06-01-22 5
06-01-23 94
06-01-24 34
06-01-25 88
06-01-26 57
06-01-27 5
06-01-30 72
06-01-31 96
06-02-01 5
06-02-04 43

SQL> with x as (
2 select * from sales order by 1 asc
3 )
4 select min(event_time) Start_date, max(event_time) End_date, sum(amt) Amount
5 from x
6 group by event_time - rownum
7 order by 1
8 /

START_DA END_DATE AMOUNT
-------- -------- ----------
06-01-18 06-01-20 123
06-01-22 06-01-27 283
06-01-30 06-02-01 173
06-02-04 06-02-04 43

Generate continuous data

See, if you have a sales table like

SDATE AMT
---------- ----------
02/01/2006 34
02/02/2006 60
02/04/2006 35
02/05/2006 53
02/07/2006 61
02/08/2006 23
02/09/2006 34
02/10/2006 34

The data is not continuous but your employer needs a continuous
report over the whole data range 02/01/2006 - 02/10/2006

Here comes the solution

SQL> r
1 with dates as (
2 select trunc(sysdate, 'MM') + level - 1 sdate
3 from dual connect by level <= 10)
4 select dates.sdate, nvl(sales.amt, 0) amt
5 from dates, sales
6* where sales.sdate (+) = dates.sdate

SDATE AMT
---------- ----------
02/01/2006 34
02/02/2006 60
02/03/2006 0
02/04/2006 35
02/05/2006 53
02/06/2006 0
02/07/2006 61
02/08/2006 23
02/09/2006 34
02/10/2006 34

Loop

No doubt, most important features in any computer languages are condition (if statement) and loop.

In SQL, we have decode/case to do conditional check. To do a loop, before Oracle 9i, it was a no-go; now, you can have something like

SQL> SELECT rownum FROM (SELECT * FROM DUAL CONNECT BY LEVEL <= 10);

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

Simple? Ok, let's have some fun

SQL> select * from x;

C1 C2
---------- ----------
11111 1
22222 2
33333 3
44444 4


SQL> create table y (c1 number);

Table created.

SQL> insert into y
2 select a.c1 from x a,
3 (
4 select rownum rn from (select max(c2) maxlevel from x) a connect by 1=1 and level <=
5 maxlevel) b
6 where a.c2 >= rn
7 order by c1
8 /

10 rows created.

SQL> select * from y;

C1
----------
11111
22222
22222
33333
33333
33333
44444
44444
44444
44444