Tech and travel

Selecting random rows

2007-08-01

If you want to select random rows from a table, you can order the rows using the dbms_random.value function : SELECT column FROM table ORDER BY dbms_random.value To get 20 random rows from the table, we can add a condition on rownum. This selects the first 20 rows : SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum < 21 With thanks to this site.

Recompiling database objects

2007-07-26

The DBMS_UTILITY package has a function called compile_schema that compiles all procedures, functions, packages, and triggers. EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT'); You can also use the UTL_RECOMP package, but I haven’t tried that one yet. With thanks to this ORACLE-BASE article.

Jerusalem

2007-07-23

A day trip to Jerusalem while in Israel for work is a no-brainer.

Korn shell way of checking if environment variable is set

2007-07-04

Sometimes you have to check if a variable is set and exit if it’s not. The following code does it in a nice and simple way : error_mess="This environment variable should be set" : ${ORACLE_HOME:?$error_mess} The output looks like this : ./script.sh[3]: ORACLE_HOME: This environment variable should be set The script exits after checking ORACLE_HOME, none of the following commands get executed. I found this in the sample chapter of the The Korn Shell: Unix & Linux Programming Manual, 3rd Edition book.

Statspack summary