Oracle SQL Developer on Ubuntu 8.10

Like my earlier post on installing the Oracle 10g client on Ubuntu, when I came to install SQL Developer on 8.10 I couldn’t find a complete list of instructions so I thought I’d write my own.

First we need to get the rpm from Oracle:

http://www.oracle.com/technology/software/products/sql/index.html

Now we need a few things before we install it:

sudo apt-get install lsb sun-java6-jre alien

We’re ready now to build the .deb package and install:

sudo alien --scripts sqldeveloper.rpm
sudo dpkg -i sqldeveloper.deb

Finaly we need to tell SQL Developer where to find our JDK, mine went into /usr/lib/jvm/java-6-sun-1.6.0.10/ so:

echo "/usr/lib/jvm/java-6-sun-1.6.0.10/" > ~/.sqldeveloper/jdk

SQL Developer should now be installed in the Applications menu under Programing.

Creating Oracle Users From a CSV file

Hi All. Managing several Oracle Servers with large numbers of students can by time consuming. I’ve no desire to spend a couple of hours creating user accounts so insted I spent a couple of hours building a VBScript to do it for me. Fortunately I can export the data I need from the University’s student management system and create the csv file quite quickly. Anyway, here’s the code. It’s probably full of examples of how not to do things but as far as I am concerned it works. Any feedback would be appreciated!

'A script to create Oracle schema users
'NOTE: sqlplus must be installed and configured to connect to Oracle!
'Created by Rob Childs
'thisbrokenworld.wordpress.com
'=======================================
intArgs = WScript.Arguments.Count
If intArgs < 2 then
WScript.Echo "usage: cscript createOracleUsers.vbs [inputFile] [mode] [OracleSid] [OracleAdminUser]"
WScript.Echo "Creates Oracle Schema users from a specified csv file"
WScript.Echo " "
WScript.Echo "example: createOracleUsers.vbs users.csv 1 sid sys"
Wscript.Echo "Mode - 0 Create SQL Script only"
Wscript.Echo "       1 Create SQL Script and users"
WScript.Echo "       [OracleSid] and [OracleAdminUser] is required for modes 1 and 2"
WScript.Echo " "
WScript.Echo "Note that the CSV file must be in the following format with 2 fields and remember that"
WScript.Echo "Oracle user passwords can't start with a number!"
WScript.Echo "               USERNAME,PASSWORD"
WScript.Quit
end If

'set this to 'yes' to have the sql script quit sqlplus after completing
'Note that if you do this you'll have no way to tell if the script succeeded or not
autoQuit = "no"

'Default Oracle roles to assign to new users
roles = "CONNECT, RESOURCE"

'USERS Tablespace Quota in Mb
intQuota = 10

'grab the command line arguments
inputFile = WScript.Arguments.Item(0)
mode = WScript.Arguments.Item(1)

'the file name for the .sql script we're going to create
sqlScript = inputFile & ".sql"

if mode > 2 then
WScript.Echo "Not a valid mode"
WScript.Quit
End If

if intArgs = 4 then
oraAdmin = WScript.Arguments.Item(3)
oraConnect = oraAdmin & "@" & dbInst & " as sysdba"
end if

'Open the input file
Const ForReading = 1
Set objInputFSO = CreateObject("Scripting.FileSystemObject")
Set objInputFile = objinputFSO.OpenTextFile(inputfile, ForReading)

'create the sql file we're going to output to
WScript.Echo sqlScript
Set objOutputFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objOutputFSO.CreateTextFile(sqlScript)
objOutputFile.close

'add lines to our new sql script
intUsersToProcess = 0
Do Until objInputFile.AtEndOfStream
arrStr = split(objInputFile.ReadLine,",")
strUser = arrStr(0)
strPwd = arrStr(1)
intUsersToProcess = intUsersToProcess + 1
WScript.Echo "Processing " & strUser
strCreateUserLine = "CREATE USER " & strUser & " IDENTIFIED BY " & strPwd & ";"
strGrantLine = "GRANT " & roles & " TO " & strUser & ";"
strAlterUserLine = "ALTER USER " & strUser & " QUOTA " & intQuota & "M ON USERS;"
Set WriteFile = objOutputFSO.OpenTextFile(sqlScript, 8, True)
WriteFile.WriteLine(strCreateUserLine)
WriteFile.WriteLine(strGrantLine)
WriteFile.WriteLine(strAlterUserLine)
WriteFile.Close
loop
WScript.Echo intUsersToProcess & " Users Processed"

'if autoQuit is set to yes, add the quit command to the end of our sql script so that it exits.
'the problem with that is you'll miss any errors thown by sqlplus when running the script
if autoQuit = "yes" then
Const ForAppending = 8
Set objFile = objOutputFSO.OpenTextFile(sqlScript, ForAppending)
objFile.WriteLine("quit")
objFile.Close
end if

'if the autoCreateUsers is set to yes, run sqlplus and execute the sql script.
if mode > 0 then
Set WshShell = WScript.CreateObject("WScript.Shell")
WScript.Echo "connecting to sqlplus " & oraConnect & " @" & sqlScript
Return = WshShell.Run("sqlplus " & oraConnect & " @" & sqlScript, 1, true)
end if

Johnny Chung Lee

I Stumbled across this guy a few days ago. He’s done some amazing stuff with the Wii remote, most of it is pretty straight forward but the applications are amazing. It’s worth checking out some of the videos particularly the one on head-tracking. Wow, I hope that the next generation of Wii FPS games start using this, imagine being able to actually pear around a corner for real or dodge left and right by moving your head! I guess there’ll be some weird looking headgear to go with it but the results would be so worth the humiliation.

Oracle 10g Client Tools on Ubuntu 7.10

Hey all, Having been using Ubuntu on my home PC for about 18 months I’ve finally switched my work PC too, There’s a load of stuff I need from Windows so I have a VM of XP running too but I want as much as possible in the host Ubuntu system. I looked high and low for a how-to for the Oracle client tools to no avail so I adapted this how-to on installing the Oracle Database 10g on Ubuntu 5.10. Note that I’m using the 10g r2 Oracle tools as I’m only working on 10g r2 databases for the moment:

Activate the root account:

First off, as the installer and other steps are required to be done as root I suggest you unlock the root account. You can just use sudo but I found it easier this way

$ sudo passwd root

Install required packages:

You’ll need all of these to get the installer to work properly:

# apt-get install gcc make binutils lesstif2 libc6 libc6-dev rpm libmotif3 libaio libstdc++5 gawk alien libg++2.8.1.3-glibc2.2 ksh gcc-3.3 g++-3.3 libstdc++5

You’ll also need to add the /etc/redhat-release file to trick the installer into thinking that you’re running a supported OS.

echo "Red Hat Linux release 4" > /etc/redhat-release

Directories, users and groups:

create the ORACLE_BASE directory where you want the installation to go

# mkdir -p /u01/app/oracle

then create the oracle user, the dba and oinstall groups and change the owner of the target directory. We’ll also need to add the nobody group because Ubuntu sets the nobody user as a member of nogroup but the installer expects it to be a member of the nobody group.

# addgroup oinstall
# addgroup dba
# addgroup nobody
# useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
# usermod -g nobody nobody
# chown oracle:oinstall -R /u01/app/oracle

Simlnks:

The Oracle installer apparently uses absolute paths for some of the binaries which are stored in a different place in Ubuntu to where Oracle expects so we need to create simlinks so it can find them

# ln -s /usr/bin/awk /bin/awk
# ln -s /usr/bin/rpm /bin/rpm
# ln -s /usr/bin/basename /bin/basename

Run the installer:

Now, logged into gnome as the oracle user download the Oracle client tools and unpack to a staging directory then start the Oracle installer.

$ mkdir ~/stage
$ unzip 10201_client_linux32.zip ~/stage
$./runinstaller

Once the install is completed you need to add the appropriate environment variables, I added the following lines to /etc/profile so that all users on the system get them:
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/client_1
export ORACLE_SID=YOURSID
export PATH=$PATH:/u01/app/oracle/product/10.2.0/client_1/bin

and finally configure the $ORACLE_HOME/network/admin/tnsnames.ora file for your environment. If you want a different user other than oracle to use the tools you’ll need to add them to the oinstall and dba groups.