Monthly Archives: January 2012

screen and screenrc

Posted on January 18, 2012 by timwarnock

I would like to use GNU screen as a window manager. By default screen seems like little more than a persistent shell (that you can resume even after logging out). By itself, this is incredibly useful if you wish to … Continue reading →

Posted in bash, shell tips | 1 Comment

nvl, ifnull, nullif, isnull, coalesce

Posted on January 17, 2012 by timwarnock

I would like to programmatically handle NULL values in a database. If I add or multiply a known value to a NULL, the result is still NULL. This is an appropriate result as a NULL value in a database schema … Continue reading →

Posted in data architecture, mysql, oracle | Leave a comment

oracle, limit results and pagination

Posted on January 11, 2012 by timwarnock

I would like to limit query results in oracle to support pagination. In postgres, mysql, and sqlite you can use the LIMIT and OFFSET modifier, e.g., mysql> SELECT login FROM users ORDER BY login LIMIT 2 OFFSET 70001; +-------------+ | … Continue reading →

Posted in data architecture, mysql, oracle | Leave a comment

sqlplus, edit stored procedures

Posted on January 11, 2012 by timwarnock

Previously, we discussed viewing stored procedures in sqlplus. It came to my attention in an Ask Tom article of a very handy way to edit a package in sqlplus. Set your $SQLPATH environment variable to wherever you wish to store … Continue reading →

Posted in oracle | Leave a comment

sqlplus, view stored procedures

Posted on January 11, 2012 by timwarnock

I would like to view stored procedures and packages in sqlplus. All stored procedures, functions, and packages can be found in the user_source, dba_source, or all_source (depending on your access). Let's examine user_source. foo_owner@FOO> desc user_source Name Null? Type ----------------------------------------- … Continue reading →

Posted in oracle | Leave a comment

sqlplus explain plan

Posted on January 11, 2012 by timwarnock

I would like to view explain plan output in sqlplus. One easy approach is to set autotrace. You can set autotrace on to show query statistics as well as an exection plan. You can also view only the execution plan, … Continue reading →

Posted in oracle | Leave a comment

sqlplus, exploring schemas and data

Posted on January 11, 2012 by timwarnock

I would like to get information about different schemas and tables in sqlplus. I would like this to be as easy as mysql, it's less intuitive but almost as easy. To get a list of schemas (similar to mysql "show … Continue reading →

Posted in oracle | Leave a comment

sqlplus pagesize and linesize

Posted on January 11, 2012 by timwarnock

I would like sqlplus output to be more readable. Fortunately, you can adjust the pagesize and linesize variables on the fly, e.g., foo_owner@FOO> set pagesize 50000 foo_owner@FOO> set linesize 120 You can set pagesize to 0, which is very useful … Continue reading →

Posted in oracle, shell tips | Leave a comment

sqlplus command prompt (sqlprompt)

Posted on January 11, 2012 by timwarnock

I would like to change the sqlplus command-prompt to something more useful than SQL> You can modify the sqlprompt variable as follows, SQL> SQL> set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> " FOO_OWNER@FOO> You can also use variables such as _PRIVILEGE and _DATE, although … Continue reading →

Posted in oracle, shell tips | Leave a comment