Oracle SQL tips and tricks + Unix/Linux Work Log

simplest cgo program

2012-01-23T11:17:00.000-08:00

Have been struggling to make a simple cgo sample to get familiar on how to link c code and go library.

Finally made it working.

cgo_test.go

-------------------------------

package cgo_test

#include

void out() {

printf("Hello world from c\n");

}

import "C"

func Out() {

C.out()

}

--------------------------------------------------------

main.go

--------------------------------------------------------

package main

import "cgo_test"

func main() {

cgo_test.Out()

}

--------------------------------------------------------

Makefile

--------------------------------------------------------

include ../go/src/Make.inc

TARG=cgo_test

CGOFILES=cgo_test.go

CLEANFILES+=main

include ../go/src/Make.pkg

main: install main.go

$(GC) main.go

$(LD) -o $@ main.$O

The most annoy error message I got is something like:

gomake main

main.go:6: cannot refer to unexported name cgo_test.out

main.go:6: undefined: cgo_test.out

It seems that GO only export Initcap names by default.

VirtualBox Scientific Linux Issues and Resolution

2012-01-21T21:27:00.000-08:00

* Change the default screen resolution 1024x768

Resolution:

Install VboxAddtions
Define the new resolution with xrandra & cvt
Add the new mode into /etc/gdm/Init/Default to make it permanent

xrandr --newmode "1440x900" 106.50 1440 1528 1672 1904 900 903 909 934 -hsync +vsync

xrandr --addmode VBOX0 1440x900

xrandr --output VBOX0 --mode 1440x900

Details can be found at: http://www.ubuntugeek.com/how-change-display-resolution-settings-using-xrandr.html

* The New IME ibus

For RH5.*, scim is the default IME. Now Ibus takes its position. The problem is that the Ibus is not setup correctly after installation. I've go "No input windows" for all the applications:

Resolution:

Enable the ibus daemon from "System" -> "Preference" -> "Startup Applications", add an entry with command "/usr/bin/ibus-daemon -d"
Add 3 lines into your bash profile (/etc/profile or .bashrc or .bash_profile)

export GTK_IM_MODULE=ibus

export XMODIFIERS=@im=ibus

export QT_IM_MODULE=ibus

Chacterset issue with SQL loader and external table

2010-12-10T11:32:00.001-08:00

Just had an issue with different contents for tables loaded with sqlldr and external table for a same input data file.

The issue is mainly caused by the different default behavior on the characterset of these 2 methods.

For external table, if character set is not specified, oracle assumes the data file has the same character set with the database. The DB characterset can be found with the following query:

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

For sqlldr, if characterset is not specified, it will try to use the value found in your client side NLS_LANG. If NLS_LANG is not set, it uses the default value, which is AMERICAN_AMERICA.US7ASCII .

Step by step Installation guide: Linkedin Azkaban as a webapp on redhat linux tomcat

2010-10-08T17:01:00.000-07:00

Before you start, make sure you have tomcat installed and running with "service status tomcat5".

download the latest azkaban tarball to /opt at http://github.com/downloads/azkaban/azkaban/azkaban-0.04.tar.gz
tar -xvf azkaban-0.04.tar.gz
copy azkaban-0.04/dist/war/azkaban.war /var/lib/tomcat5/webapps
stop tomcat service: service tomcat5 stop
append "AZKABAN_HOME="/opt/azkaban-0.04" into /etc/sysconfig/tomcat5
start tomcat service: service tomcat

wait for a couple of minus, you should get azkaban running at http://your_host_name:port/azkaban/

Query to get tablespace contiguous spaces

2010-09-14T21:34:00.000-07:00

Ever getting ORA-01659: unable to allocate MINEXTENTS beyond 8 in tablespace XYZ?

I got this last weekend when creating a table with several GB level partitions.

The error indicates that it can not find contiguous spaces to create initial extents * minextents.

How do we know how many contiguous spaces we have on the tablespace, I tried to search the web but only got lengthy complex PL/SQL scripts. It ends with that I have to write my own. I post it here hoping someday it may help somebody.

SELECT
tablespace_name,
file_id,
round(sum(bytes)/1024/1024/1024, 3) contiguous_free_space_gb,
sum(blocks) no_of_blocks,
min(block_id) as starting_block,
round((sum(sum(bytes)) over ())/1024/1024/1024, 3) total_ts_free_space_gb
FROM
(
SELECT
tablespace_name,
file_id,
block_id,
bytes,
blocks,
nvl(block_id - min(block_id) over (partition by file_id) - sum(blocks) over (partition by file_id order by block_id rows between unbounded preceding and 1 preceding), 0) gap_blocks
FROM dba_free_space
WHERE tablespace_name = '&ts_name'
)
GROUP BY gap_blocks, tablespace_name, file_id
ORDER BY sum(bytes) desc

やられた

2010-04-01T23:18:00.000-07:00

It was 5:00PM and I was asked to write a function which needs to parse a comma delimited string. I was lazy and trying to avoid the lengthy substr/instr loop. I remember I read somewhere that there is builtin function exactly does the same thing.
After a search, I found out the function dbms_utility.comma_to_table and that's when the nightmare started.

After I finished the function and tested with 'abc,efg', it works well.
When I put it into real use
This is what I got:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at "SYS.DBMS_UTILITY", line 160
ORA-06512: at "SYS.DBMS_UTILITY", line 202
ORA-06512: at line 7
00931. 00000 - "missing identifier"

http://www.dbforums.com/oracle/887432-dbms_utility-comma_to_table-giving-problem.html
"Unfortunately dbms_utility.comma_to_table is NOT a general purpose utility as its name would suggest. It only works with comma-separated names that are valid database object names, i.e. up to 30 chars beginning with a letter"

Postgresql 8.3 on OpenSolaris 2008-11

2008-12-23T19:21:00.000-08:00

Here are the steps to enable postgresql 8.3 on OpenSolaris 2008-11.

* Install all the 8.3 relate package with Package Manager.
* Enable the service:
svcadm enable postgresql_83:default_32bit
* Connect to database:
psql -U postgres postgres

List Service Status
svcs -a | grep postgres

ABS - Advanced Bash Scripting

2008-11-08T20:34:00.000-08:00

If you are serious about shell programming, you are gonna read this:

http://personal.riverusers.com/~thegrendel/abs-guide.pdf

Bash ANSI-C quoting

2008-10-23T16:55:00.000-07:00

The syntax of ANSI-C quoting is something like
$'\0x009' or $'\t'.

This is useful when you want to input a tab on the command line as an input parameter (tab key is mapped to auto-completion in bash).

for example:
psql -A -F $'\t' ...
will set the field separator to tab.

This is documented in http://www.faqs.org/docs/bashman/bashref_12.html as:
"Words of the form $'string' are treated specially. The word expands to string, with backslash-escaped characters replaced as specified by the ANSI C standard. Backslash escape sequences."

For tab, you can also input a CTRL-I (type CTRL-V-I).

IFILE for oracle parameter files

2008-09-16T09:49:00.000-07:00

Use IFILE to embed another parameter file within the current parameter file. (Maximum level 3)

It works for any oracle parameter files including tnsnames.ora.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams081.htm#REFRN10070

Credit goes to my friend Matthew!

Clean up the old oracle installation

2008-06-26T11:04:00.000-07:00

A short memo on how to clean up your previous installation on Windows:

0). Stop all the Oracle Related Service if you have the database installed
Control Panel -> Adminstrative Tools -> Services

1). Remove all the Oracle related ODBC Data Sources.
Control Panel -> Adminstrative Tools -> Data Sources (ODBC)
Remove all Oracle related User DSN/System DSN

ODBC driver hooks up with the oracle dlls which blocks you to remove Oracle components

2). Reboot

3). Run the oracle installer to remove whatever can be removed.

4). Remove the Oracle registration key with regedit.exe
HK_LOCAL_MACHINE/Software/Oracle
HK_LOCAL_MACHINE/System/Services (if you have the database installed)

5). Reboot

6). Remove the Oracle Folders
Typically c:\oracle or c:\orant & C:\Program Files\Oracle

Now you are clean and ready for a reinstallation.

Direct Oracle Connection without Tnsnames.ora

2007-12-18T17:23:00.000-08:00

sqlplus username/xxxxx@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=machine_name)(Port=1521))(CONNECT_DATA=(SID=service_name)))

or

sqlplus username/xxxxx@machine_name:port/sid

Convert a stats input date to Edward date_sid

2007-10-03T16:14:00.000-07:00

Old way of doing it:
select to_char(to_date('09/01/2007', 'MM/DD/YYYY'), 'YYYYMMDD') from dual;

New way with regexp
select regexp_replace('09/01/2007', '(\d{2})/(\d{2})/(\d{4})', '\3\2\1') from dual

reduced function call to 1

Oracle 11g password and Toad (connection issue)

2007-08-20T17:18:00.000-07:00

One of the new features of 11g is case-sensitive password.

It causes problem for Toad because Toad makes the password captial silently.

If you are using lower case password, you will get
ORA-01017: invalid username/password; logon denied.

How to disable this new featuer:
alter system set sec_case_sensitive_logon=FALSE;

A new column PASSWORD_VERSIONS has been added in dba_users may be related to this.
Not quite sure on this.
The values (10G 11G) remine same before/after changing the system parameter sec_case_sensitive_logon.

Unix Shell Integer Comparison

2007-05-25T15:56:00.000-07:00

In Unix shell,

Use -ne -eq -lt -gt to compare numbers.

== and != are used for string comparison.

Perl does the things on the opposite.

Initcap

2007-05-22T16:43:00.000-07:00

An interesting function:

SQL> select initcap('abc XYZ') from dual;

INITCAP('ABCXY
--------------
Abc Xyz

Scalar query and parallel query

2007-01-27T04:45:00.000-08:00

If you have a scalar query in the select list, the whole query will not work in parallel mode even if you give a parallel hint

(Unix/Linux Shell) How many lines in a file?

2007-01-18T07:14:00.000-08:00

Linux (FC6)

wc -l tx.txt | cut -f 1 -d " "

Solaris

wc -l tx.txt | sed -e 's/^ *//' | cut -f 1 -d " "
OR
wc -l txt.txt | awk '{print $1}'

Substr in unix/linux shell script

2006-12-28T01:00:00.000-08:00

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

2006-12-26T01:12:00.000-08:00

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)

2006-12-11T17:53:00.000-08:00

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

2006-11-22T01:30:00.000-08:00

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

Merge continuous ranges

2006-08-21T23:03:00.000-07:00

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

2006-08-11T01:04:00.000-07:00

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

2006-08-11T01:02:00.000-07:00

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

dump & zip the dmp file on the fly

2006-07-10T07:15:00.000-07:00

# 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

2006-05-09T19:13:00.000-07:00

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

2006-05-01T23:50:00.000-07:00

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

2006-04-20T22:08:00.000-07:00

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

2006-04-13T00:59:00.000-07:00

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

2006-04-03T18:08:00.000-07:00

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

2006-03-29T16:23:00.000-08:00

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

Passing a select as Cursor to a Function

2006-03-27T17:03:00.000-08:00

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

2006-03-27T16:34:00.000-08:00

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

2006-03-27T16:15:00.000-08:00

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

2006-02-13T21:47:00.000-08:00

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

2006-02-12T23:14:00.000-08:00

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

2006-02-12T23:01:00.000-08:00

(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

2006-02-12T22:57:00.000-08:00

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

2006-02-12T22:51:00.000-08:00

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

2006-02-11T07:48:00.000-08:00

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

2006-02-10T06:42:00.000-08:00

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