Monday, 28 November 2011

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.


Friday, 18 November 2011

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
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '

and when I login to sqlplus, I get this.
SQL*Plus: Release 11.2.0.2.0 Beta on Mon Nov 21 11:05:58 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta


GLOBAL_NAME
-----------------------------------------------------------------

barry@XE

barry@XE>

Obviously, in SQLDeveloper, this won't mean anything as prompts are not there, however, the variables you set and the column formats, titles, pagesizes etc, will be preserved.

For example, in our login2.sql  list in the preferences above, we set a couple of column settings and for fun, lets set the prompt variable too.


As shown above, we now have a login.sql defined in the preferences. When we make a connection, the login.sql will be run and any settings will be applied to the database.  We will also hold onto any SQL*Plus variables defined so they can be used in any worksheet that is started using this connection.


When you connect, you get the new worksheet, and the sql that was run produces any output in the messages.log.  The reason for this is you can connect to the database without spawning a worksheet and this lets you know of any output from that login script we put together earlier.


Now, we are connected, any worksheet created on that connection will have the context of the original script.



Lastly, you can also set your worksheet name to be a substitution variable as well


set worksheetname &gname

which will swap you default worksheet name to your connection credentials shown above.

Tuesday, 15 November 2011

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 &&, e.g., &&foo, it prompts for the value and then uses this values for every other occurrence of the variable. &foo on the other hand will prompt for the variable, use it and then discard the value so the next time it is seen, it will prompt again. So, now an example.  Lets assume we want to create a user db1 and db2 with some tables in each user. We can define a simple script for each one.

define db1_password=&&db1
define db2_password=&&db2
@@users.sql
@@db1.sql
@@db2.sql

Now, this script does three things.
  1. it defines two variables for the passwords of the two users we are going to create.
  2. @@users.sql sets up the users for us. Remember, in SQL*Plus, there is only ever one connection active, so whenever you have a connect statement in your script, that will be the user running the script until you change it. In this case above, we're starting with a privileged user to create the users.
  3. @@db1.sql and @@db2.sql create our users for us and we will create a table in each one. We'll see that script in a moment.

drop user user1 cascade;
prompt creating user user1
create user user1 identified by &&db1_password;
grant connect, resource to user1;
drop user user2 cascade;
prompt creating user user2
create user user2 identified by &&db2_password;
grant connect, resource to user2;

db1.sql and db2.sql are identical apart from user names.

connect user1/&&db1
create table table1 (id number, name varchar2(10));
insert into table1 values (1,'barry');

Substitution variables are passed to any subscript called from the main script.  The initial &&db1 and &&db2 are prompted for and set to the variables db1_password and db2_password
From then on, the subscripts use the variables to connect to each user.

Finally, the output from all of this is below.  I have VERIFY=ON here to show the substitutions going through, but for any real world scenario, you'll want to switch that off avoid printing the passwords.

user USER1 dropped.
creating user user1
old:create user user1 identified by &&db1_password
new:create user user1 identified by db1
user USER1 created.
grant succeeded.
user USER2 dropped.
creating user user2
old:create user user2 identified by &&db2_password
new:create user user2 identified by db2
user USER2 created.
grant succeeded.
old:connect user1/&&db1_password
new:connect user1/db1
Connected
table TABLE1 created.
1 rows inserted.
old:connect user2/&&db2_password
new:connect user2/db2
Connected
table TABLE2 created.
1 rows inserted.
Connection created by CONNECT script command disconnected

With verify off, you get cleaner output with no passwords.  If you need more ouput here, you can also use the prompt command to identify which script you are in and what is running.

user USER1 dropped.
creating user user1
user USER1 created.
grant succeeded.
user USER2 dropped.
creating user user2
user USER2 created.
grant succeeded.
Connected
table TABLE1 created.
1 rows inserted.
Connected
table TABLE2 created.
1 rows inserted.
Connection created by CONNECT script command disconnected

Monday, 14 November 2011

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 sqlplus are like this.

 sqlplus [LOGIN} @{FILENEMAME]

where LOGIN can be any of this.


 {[/][@] | / }
 [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]


So, our arguments will be

${sqldev.dbuser}/${promptl:label=Password} @${file.dir}/${file.name}

You can check the variables you can submit with the insert button.  I am choosing a labelled prompt for the password here for security.

Once we have all that done we can see our External tool in the list of tools.


Now, all we have to do is make sure our file is in focus and click the SQL*Plus button.  This will run the file with the current user of the connection we have on the file and prompt for the password.

My File looks like this
clear screen
select username from all_users where username like 'B%';

and running it with my new SQL*Plus button gives me this.

C:\oraclexe\app\oracle\product\11.2.0\server\bin>
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe barry/barry "@C:\Documents and Settings\bamcgill\Desktop/Untitled1.sql"

SQL*Plus: Release 11.2.0.2.0 Beta on Mon Nov 14 15:38:25 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

 

USERNAME
------------------------------
BARRY
BARRY2

SQL> 

Now, thats not so secure either, since you can see my password, so we want to switch off banner output too which will suppress the login string and the header.  You can do this by editing your external tools and adding it to the arguments like this.
-S ${sqldev.dbuser}/${promptl:label=Password} @${file.dir}/${file.name}

giving you what you want here.
C:\oraclexe\app\oracle\product\11.2.0\server\bin>
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe -S barry/barry "@C:\Documents and Settings\bamcgill\Desktop/Untitled1.sql"
 

USERNAME
------------------------------
BARRY
BARRY2