Categories
sql

CONNECT BY NOCYCLE

the idea of this post was largely inspired by nuke_y on developpez.net (in French)

I will try to rephrase it in a hierarchical way.

Let’s take all managers of Adams in EMP :
SELECT empno, ename, mgr
FROM emp
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING

So far so good. Let’s imagine SCOTT has two managers, JONES and PAUL.

create table lsc_t AS
(SELECT ename, empno, mgr
FROM emp
UNION ALL
SELECT 'SCOTT', 7788, 9999
FROM DUAL
UNION ALL
SELECT 'PAUL', 9999, NULL
FROM DUAL);
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999
9999 PAUL

EMP is not supposed to allow this (primary key on empno), so I created a table LSC_T.

So far still fine, Paul is in my list of managers.

Let’s imagine Scott is the manager of Paul. In 9i and below, this would result in an ORA-0146 CONNECT BY loop in user data, but in 10g, and according to the doc The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data.

Should I try ???

UPDATE lsc_t
SET mgr = 7788
WHERE ename = 'PAUL';
COMMIT ;
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999

Due to a loop in user data, PAUL is no longer returned. I have no explanation so far.

Categories
dba unix

How to reuse connection in shell

It is Friday, I wanted to give my readers some stuff for the week-end 😉

Imagine that piece of code :

countlines() {
c=$(sqlplus -s /nolog <

I can run this

time ./script1
there are 14 lines in EMP
there are 4 lines in DEPT

real 0m0.46s
user 0m0.06s
sys 0m0.09s

Sounds like a regular shell script. How could we optimize the countlines function? Well, we could create the connection only once and use coprocess pipe (with |& that is pipe ampersand)

sqlplus -s /nolog |&

print -p "connect scott/tiger"

read -p line
if [ $line != Connected. ]
then
exit 1
fi

print -p "set feed off head off"

countlines() {
print -p "select count(*) from $1;"
read -p c
echo "there is $c lines in $1"
}

countlines EMP
countlines DEPT

print -p disconnect

A two-ways pipe is opened with sqlplus. There is only one connect, and one disconnect.

Let's check the performance :

$ time ./script2
there is 14 lines in EMP
there is 4 lines in DEPT

real 0m0.23s
user 0m0.00s
sys 0m0.01s

About twice as fast! Note the "Connected" output may not exist in recent version of sqlplus in silent mode. If you have a script that generates hundreds of connections, or which create a connection every 5 seconds or so, think about it 🙂

Enjoy your week-end

Categories
dba linux unix

return code before grep

In my previous post hide-password-from-ps-output-sql-loader I mentioned a way to pass the password to the loader thru a parameter file. As correctly suggested by Brian Tkatch, the password could be passed as standard input

sqlldr control=x.ctl silent=header,feedback <

The Username: prompt is displayed 🙁   🙁

How do we get rid of this ?

sqlldr control=x.ctl silent=header,feedback <

There is no output. But what's the error code
echo $?
1

The return code is 1 🙁

This is not the error code from sqlldr, but the error code from grep !

Ok, here is the trick, a bit cryptic if you are not familiar with file descriptors

( ( (sqlldr control=x <&3) |grep -v "^Username:" >&4 ) 3>&1 |(read x;exit $x) )4>&1
scott/tiger
EOF
echo $?
0

The return code is 0 🙂

Categories
dba

Hide password from ps output : sql loader

By reporting the process status with ps, any Unix user will see the command line arguments

ps -ef
UID PID PPID C STIME TTY TIME CMD
lsc 13837 13825 0 May 11 pts/17 0:01 -ksh
oracle 4698 6294 0 12:00:40 ? 0:00 sqlplus -s system/manager
appluser 4229 4062 0 12:00:03 ? 0:00 sqlldr scott/tiger
applrun0 28445 28281 0 11:54:03 ? 0:00 imp king/gold full=y
...

What you see here above is definitely a security issue. For sqlplus, the trick is to use sqlplus /nolog and then pass connect system/manager as input or script.

For sqlldr (and exp/imp etc…), the trick is to use a parameter file.

To make it as safe as possible, the file must be unique, readable only for owner and removed after usage.

Ex:
umask 0077
TMPFILE=$(mktemp)
echo "userid=scott/tiger" >$TMPFILE
sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback
rm $TMPFILE

mktemp is an Unix utility that creates temp files with unique names.

Categories
license

future of MySQL

a google search on oracle and mysql will returns concerns about the future of MySQL. When SUN bought mysql, the enthousiasm was also not unanimous in the opensource community. But now it really need repositioning.

I was just reading : http://www.mysql.com/products/enterprise/unlimited.html
Deploy an unlimited number of MySQL Enterprise Servers for the price of
a single CPU of Oracle Enterprise Edition

Today, there are over 5,000 organizations that spend more than $1Million on
License fees to Oracle, Microsoft, Sybase and IBM. In many cases, that’s money
that doesn’t need to be spent. Not only does open source save money, it
provides an architecture that is more scalable for modern web-based applications.
No wonder open source software has become a strategic initiative for CIOs & CFOs
in many organizations.

Clearly this need repositioning. (For Oracle, money need to be spent 😈 )

On the redundant offering MySQL and Oracle Database Express Edition, I could imagine Oracle will get rid of the free edition. Oracle seems to put effort on not releasing any database patch or security patch and the amount of features as well as the cpu/memory limitation are just way to annoying.

This is why I believe MySQL will survive XE.

Pure guess of course…