After a longer pause because of vacation and general busyness/laziness I present you a standalone simple command line Oracle client.
It's not standalone as in independent on Oracle code (it comes with 106 megabytes of Oracle dll's), but it doesn't need any installation, doesn't modify registry and it doesn't need any privileges, just unpack it and run. Yeah I know, you're thinking "106 megabytes for dll that connects to database it's enormous". Truth to be told, I have no idea, what is Oracle doing in so much code.
The client itself, it's written in C# and it's pretty basic. Reads SQL statements from standard input, executes them in DB, sends output to standard output and errors to standard error. Nothing really spectacular.
using System;
using System.IO;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using Oracle.DataAccess.Client;
class SimpleOracleClient
{
OracleConnection con;
OracleCommand cmd;
TextWriter output;
TextWriter error;
bool headers = true;
string separator = ";\t";
SimpleOracleClient(string connectionString)
{
output = Console.Out;
error = Console.Error;
con = new OracleConnection(connectionString);
con.Open();
Console.Error.WriteLine("Connected to Oracle " + con.ServerVersion);
}
~SimpleOracleClient()
{
if (output != Console.Out)
output.Close();
if (error != Console.Error)
error.Close();
con.Close();
con.Dispose();
}
void Execute(string command)
{
cmd = new OracleCommand(command, con);
OracleDataReader myReader;
try {
myReader = cmd.ExecuteReader();
if (headers)
{
for (int i = 0; i < myReader.FieldCount; i++)
{
error.Write(myReader.GetName(i) + separator);
}
error.WriteLine();
error.WriteLine();
}
while(myReader.Read())
{
for (int i = 0; i < myReader.FieldCount; i++)
{
output.Write(myReader.GetValue(i).ToString() + separator);
}
output.WriteLine();
}
if (headers)
{
for (int i = 0; i < myReader.FieldCount; i++)
{
error.Write(myReader.GetName(i) + separator);
}
error.WriteLine();
}
output.Flush();
error.Flush();
myReader.Close();
}
catch (OracleException e)
{
Console.Error.WriteLine(e.Message);
}
}
static string getConnectionString(string[] args)
{
Dictionary<string, string> options = new Dictionary<string, string>();
options.Add("host", "localhost");
options.Add("port", "1521");
options.Add("service", "ora");
options.Add("user", "Administrator");
options.Add("password", "Administrator");
string connectionString;
if (args.Length == 0)
{
connectionString = Console.ReadLine();
}
else if (args.Length == 1)
{
connectionString = args[0];
}
else
{
for (int i = 0; i < args.Length; i++)
{
string name = args[i].Substring(1).ToLower();
if (options.ContainsKey(name))
{
options[name] = args[i+1];
}
}
connectionString = "Data Source=(DESCRIPTION="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)"
+ "(HOST=" + options["host"] + ")"
+ "(PORT=" + options["port"] + ")))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)"
+ "(SERVICE_NAME=" + options["service"] + ")));"
+ "User Id=" + options["user"] + ";"
+ "Password=" + options["password"] + ";";
}
return connectionString;
}
static int Main(string[] args)
{
Console.Error.WriteLine("Simple Oracle Client 0.1");
SimpleOracleClient s = new SimpleOracleClient(getConnectionString(args));
Dictionary<string, string> helper = new Dictionary<string, string>();
helper.Add(".tables", "SELECT table_name FROM all_tables");
helper.Add(".databases", "SELECT * FROM user_tablespaces");
string line = "";
while ((line += Console.ReadLine()) != null)
{
if (line.Length == 0)
continue;
string[] command = Regex.Split(line.Trim().ToLower(), " +");
if (command[0] == ".quit" || command[0] == ".exit")
break;
bool processed = true;
switch (command[0])
{
case ".headers":
case ".header":
if (command.Length > 1 && Regex.Match(command[1], "(on)|1|(true)").Success)
{
s.headers = true;
}
else if (command.Length > 1 && Regex.Match(command[1], "(off)|0|(false)").Success)
{
s.headers = false;
}
else
{
Console.Error.WriteLine("Headers " + (s.headers ? "on" : "off"));
}
break;
case ".output":
if (command[1] == "stdout")
{
if (s.output != Console.Out)
{
s.output.Close();
s.output = Console.Out;
s.error = Console.Error;
}
}
else
{
if (s.output != Console.Out)
{
s.output.Close();
}
s.output = new StreamWriter(command[1]);
s.error = s.output;
}
break;
case ".separator":
if (command.Length > 1)
{
s.separator = command[1];
}
else
{
Console.Error.WriteLine("Separator is '{0}'", s.separator);
}
break;
case ".help":
Console.Error.WriteLine("Simple Oracle Client 0.1");
Console.Error.WriteLine("For updates, please see http://www.gettingclever.com");
Console.Error.WriteLine("");
Console.Error.WriteLine(".exit - exits the program");
Console.Error.WriteLine(".headers [on|off] - turn headers on");
Console.Error.WriteLine(".output FILENAME - sends output to FILENAME");
Console.Error.WriteLine(".output stdout - sends output to stdout");
Console.Error.WriteLine(".quit - quits the program");
Console.Error.WriteLine(".separator STRING - uses STRING as column separator");
Console.Error.WriteLine("");
break;
default:
processed = false;
break;
}
if (!processed)
{
if (line[line.Length-1] == '\\')
{
line = line.Substring(0, line.Length-1);
continue;
}
else
{
if (helper.ContainsKey(line))
{
s.Execute(helper[line]);
}
else
{
s.Execute(line);
}
}
}
line = "";
}
return 0;
}
}
Connecting to the database
There are three different ways to establish a connection to the DB.-
Specify the connection string on the command line
Notice the double quotes surrounding the whole connection string, they are needed, so that the whole connection string is considered as a whole.
- Specify connection details on the command line
- Type in the connection string as first line from standard input - this option is probably the most useful for scripting and at the same time most secure, since it doesn't display the password in process list.
Working with the database
When connected to the database, the work is pretty straightforward, you issue SQL commands and the results get displayed. Inspired by sqlite I implemented couple of useful dot-commands.| .databases | Lists available databases |
| .exit | Exits the application |
| .headers [on|off] | Switches displaying of column headers on and off |
| .help | Displays help screen |
| .output FILENAME | Sends all output to FILENAME |
| .output stdout | Sends all output to standard output |
| .quit | Quits the application |
| .separator [NEW_SEPARATOR_STRING] | Sets new separator string |
| .tables | Lists available tables |
Things to keep in mind
This is just a very basic client. There is no support for multiple SQL statements.Following statement will fail
SELECT * FROM table1; SELECT * FROM table2;Because it's up to the client application to separate individual SQL statements.
Not only that, but even this statement will fail because of the trailing semicolon.
SELECT * FROM table1;
This is a work in progress, there are quirks, which I'm aware of and bugs, which I'm not. I'll keep on updating this client, but keep in mind, that it was written in one afternoon, while travelling back from work. Use at your own risk. I use it on production databases, which doesn't mean you should.
Link to the code and binaries oracle.0.1.7z - 20 MB, hosted on MediaFire.
It's 7zipped for minimal download size. Standard zip yielded results somewhere about 37 megabytes.
Planned enhancements
- Interactive password query
- SQL parsing - possibility to enter multiple SQL statements, comments etc
- Plain file import
- History of commands
Tested with Oracle 10.2.0.3 and 11.1.0.6
Please note, that the oracle client library creates %USERPROFILE%\Oracle folder, which is safe to delete.
Obligatory thanks to Mark James for creating the amazing Silk icons.
Update [15th September 2008]: Fixed the download link, I apologize for not using a direct link. I must buy some hosting.
3 comments:
Thank you for this helpful little program. :)
I discovered last week, that you can also use the so called "lite" version of the runtime library provided by the oracle instant client, which is only about 27MB.
(oraociicus11.dll and oraocci11.dll). That reduces the inital loading time of your client by 3/4. :)
The file doesn`t exist on MediaFire anymore. Could you please reupload it? That tiny client could be the answer to my prayers :) Regards, Mordazy.
Post a Comment