UKOUG 2011

I'm speaking next week at the UK Oracle User Group at the ICC in Birmingham. The topic will be one I've posted several times about which is Tuning, Refactoring and Instrumentation.   Have a look at the agenda, and if you are in town, come along.  You can click on the image to go to the conference site and check out the agenda.

login.sql, SQLPROMPT and worksheets

SQLDeveloper has had support for a login.sql for several releases now.  You can set this in the preferences at

Tools -> Prefernces -> Database

You can set your login.sql here.

Now, when SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed.
Thereafter, SQL*Plus will try to find a local login script called login.sql in the directory where you start sqlplus from, alternatively the directories listed in the SQLPATH environment variable. When found, sqlplus will execute it.  Here's my login.sql for SQL*Plus

Substitution Variables in SQL*Plus

Working through security issues uncovers some interesting things. Anyone who has developed scripts for building out schemas for an application will have had the issues of passing variables to subscripts or managing password visibility when creating users, building objects or granting permissions

SQLDeveloper and SQL*Plus have substitution variables to solve this problem.  Basically, there are two types of substitution variables,  & and &&.  &foo is used to refer to the variable foo.  &&foo is also used to refer to the variable foo.  The main difference between the two variables is that first time SQL*Plus comes across a variable defined with (more...)

SQLPLus as an external tool in SQLDeveloper

@thatjeffsmith asked me today about running SQL*Plus from SQLDeveloper for his current sql file.  This has been shown before, but there is a simple way to add it and to get it to run your file under SQL*Plus.

The only caveate on this example, is that we are using the bequeath adapter to connect to a local XE database. You can amend this to add a service after the username

The main steps are to point the program executable to your SQLPlus, which will populate the executable and the Run directory.  Next you need to populate the arguments, which for (more...)

Proxy Connections

While looking at some issues with specific connection upgrades, I've been playing with proxy connections in SQL Developer, and while easy to do, can be interesting to get your head around. There are a number of things to do which are important. So, Lets start with a proxy user called proxy and a target user called target. (Nice and original)
drop user proxy cascade;
drop user target cascade;
create user proxy identified by proxy;
create user target identified by target;
alter user target grant connect through proxy;
grant create session to proxy;
grant connect, resource to target;
connect target/target;
create (more...)