migration syscolumns from SQL-Server to ORACLE

Coming from MS-Sql-Server I was looking for some time for the equivalent of mircrosofts system table syscolumns.

The simple part is to find out that columns of tables and views go to all_tablecolumns, dba_table_columns and user_table_columns. But MS-Sql constains as well the parameters of stored procedures. And that corresponds in ORACLE to ALL_ARGUMENTS. Even knowing that there must be a view in the data dictionary I needed nearly half a year to find this out. Well I was looking for name with colum or parameter or the like. Now I can happily forget about DBMS DESCRIBE and it's nasty interface. Google search didn't help here very much. They focused on the simple half.

By the way this blog and project is not abandoned, only til the midth of the year I'm occupied by some personal changes.

SET SERVEROUTPUT ON SIZE UNLIMITED

This is one off the really nice features of release 10. Even better you need release 10 only on the client side. I was really surprised when I connect to a 9.2 server and there it worked as well, I would had bet that it depended on the server too.

PS.: I'm using now the Firefox 2.0 for spell checking.

A minimal sqlplus clone in IronPython

Hier folgt der Code für ein einfaches IronPython Programm, welches interaktiv die Eingabe von Sql -Statements und das Ausführen auf Oracle Datenbanken ermöglicht. Ich verwende den ODP, offenbar ist er bei Oracle 10g XE dabei, denn ich habe ihn nicht extra installiert. Außerdem beziehe ich mich auf hr das bekannte demo Schema.

Nach dem Start könnte eine Beispielsession wie folgt aussehen:

C:\D\FePy\sqldb>ipy uifs_oda_public.py
Data Source: xe
Username: hr
password: hr

ServerVersion: 10.2.0.1.0

Connection state: Open
hr@xe -->>
select table_name
from user_tables

> select table_name
from user_tables



TABLE_NAME REGIONS

TABLE_NAME LOCATIONS

TABLE_NAME DEPARTMENTS



und nun der Python Code:

------------------------- uifs_oda_public.py -----------------------------------------

'''
Bernd Kriszio: 2006-11-05
'''
import clr
import sys
clr.AddReference("System.Data")
clr.AddReference("Oracle.DataAccess")

import System.Data
import Oracle.DataAccess.Client

class ConnectionProperties :
def set(self, string):
self._dict = {}
for i in string.split(';'):
k, v = i.split('=')
self._dict[k.lower()] = v
def __getitem__(self, key):
return self._dict[key]

def n2onoff(val):
if val:
return 'ON'
else:
return 'OFF'

class Connection:
def __init__(self, ConnectString = ''):
self._echo = 0
self._feedback = 0
self._prompt = '>'
if not ConnectString:
ConnectString = self.queryConnectInfo()
self.Open(ConnectString)

def queryConnectInfo(self):
dataSource = raw_input('Data Source: ')
user = raw_input('Username: ')
password = raw_input('password: ')
return 'Data Source=%s;User ID=%s;Password=%s' % (dataSource, user, password)


def Set(self, OptionString):
''' some resamblance to SQL*PLUS
Tablemode on|off|auto
'''
s = OptionString.upper().strip()
if s == 'ECHO ON':
self._echo = 1
elif s == 'ECHO OFF':
self._echo = 0
elif s == 'FEEDBACK ON':
self._feedback = 1
elif s == 'FEEDBACK OFF':
self._feedback = 0

def Show(self, OptionString):
''' when there are more options i'll work it out, meanwhile SHOW ALL
'''
print 'ECHO is ' + n2onoff(self._echo)
print 'FEEDBACK is ' + n2onoff(self._echo)


def Open(self, ConnectString):
self._ConnectString = ConnectString
self._ConnectionProperties = ConnectionProperties()
self._ConnectionProperties.set(self._ConnectString)
self._Connection = Oracle.DataAccess.Client.OracleConnection(self._ConnectString)
self._Connection.Open()

print '\nServerVersion: ' + self._Connection.ServerVersion
print '\nConnection state: %s' % self._Connection.State.ToString()

def Close(self):
self._Connection.Close()

## def getConnection(self):
## return self._Connection

def setCommand(self, query):
self._Command = Oracle.DataAccess.Client.OracleCommand(query, self._Connection)

def ExecuteReader(self):
self._Reader = self._Command.ExecuteReader()
## bei SELECT immer -1
## if self._feedback == 1:
## print self._Reader.RecordsAffected

def ExecuteNonQuery(self):
self._count = self._Command.ExecuteNonQuery()
if self._feedback == 1:
print '%d rows affected' % self._count

def ExecSql(self, query):
if self._echo:
print '> ' + query
try:
## if 1:
if query.strip()[:6].upper() == 'SELECT':
self.setCommand(query)
self.ExecuteReader()
self.print_Reader()
elif query.strip()[:5].upper() == 'EXEC ':
statement = query[5:]
print 'statement %s' % statement
proc = statement.split(' ')[0]
print 'proc %s' % proc
elif query.strip()[:5].upper() == 'DESC ':
## print '--> desc'
statement = query[5:]
self.desc(statement.upper().strip())
else:
## print '--> else'
self.setCommand(query)
self.ExecuteNonQuery()
## if 0:
except StandardError, e:
if self._echo == 0:
print query
print "Fehler: ", e


def print_Reader (self):
rdr = self._Reader
if rdr:
anz = rdr.FieldCount
print
cnt = 0
while rdr.Read():
cnt += 1
for i in range(anz):
## print '%-30s %-30s%s' % (rdr.GetName(i), rdr.GetFieldType(i), rdr[i])
print '%-30s %s' % (rdr.GetName(i), rdr[i])
print
self._count = cnt
if self._feedback == 1:
print '%d rows affected' % self._count
rdr.Close()
rdr.Dispose()
self._Reader = None


def prompt1(self):
return '%s@%s -->>' % (self._ConnectionProperties['user id'], self._ConnectionProperties['data source'])

def ufi2(self):
''' multi line commands
I would like to use cancel ^C for cancel,
but KeyboardInterrupt works somewhat different in IronPython
'''
cmd = ''
print self.prompt1()
while 1:
a = raw_input()
try:
a_lc = a.lstrip().lower().split()[0]
except:
a_lc = ''
## work around. I would like to cancel input with ^C
if a_lc == 'cancel':
print 'input canceld'
cmd = ''
print self.prompt1()
elif a_lc == 'set':
self.Set(' '.join(a.lstrip().split()[1:]))
elif a_lc == 'show':
self.Show(' '.join(a.lstrip().split()[1:]))
elif a_lc == 'connect':
## print 'a:', a
p = ' '.join(a.lstrip().split()[1:])
## print 'p: ', p
try:
(up, dataSource) = p.split('@', 1)
except:
up = p
dataSource = self._ConnectionProperties['data source']

print up, dataSource

try:
user, password = up.split('/',1)
except:
user = up
password = raw_input('password: ')
connectString = 'Data Source=%s;User ID=%s;Password=%s' % (dataSource, user, password)
print '--> connect %s' % connectString
self.Close()
self.Open(connectString)
else:
cmd += a + '\n'
if cmd.lower() == 'quit\n':
break
if a == '' and cmd.strip():
## print 'calling: %s' % cmd
self.ExecSql(cmd)
else:
continue
cmd = ''
print self.prompt1()

## ----- Self test call -------------------------------------------------------------------------
if __name__=='__main__':

conn = Connection()
conn.Set('Echo on')
conn.Set('Feedback on')
conn.ufi2()
conn.Close()

begin IronPython; end;

Hello IronPython world - viele Grüße aus Deutschland

I plan to post here some notes, code examples and links concerning IronPython, databases as Oracle and SqlServer and their procedural extensions PL-SQL und T-SQL.

One idee is to build a minimal clone of SQL*PLUS in IronPython.

Why just SQL*PLUS? At the positive side it is rather dynamic

Var r ref cursor
exec something_vital(:r)
print r

till mow, I found no simple way to display the returned resultsets in neither TOAD nor sql-developer.
And it displays cursor expressions in Select statements directly. Try the following using the hr schema:

select DEPARTMENT_NAME, cursor (select first_name, last_name from employees e where e.department_id = d.department_id) from departments d;

Are they parsing this statement by themself.
Sql-Developer throws an error and my atempts via Ironpython and Oracle.DataAccess.Client aren't better.

At the other side, there are some handicaps with this tool, lets try to ignore them and do it better.