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.

isWeekend

Oracle:
select case when mod(TO_CHAR(sysdate), 'J' in (5, 6) then 'T' else 'F' end from DUAL;

Ms-SQL:
select case when Datepart(dw, getdate()) + @@datefirst in (7, 8, 14) then 'T' else 'F' end

Are there better ways to escape from NLS hell ?

Oracle - Microsoft (1:1) here.

How to set up IronPython in an Intranet

For general instructions first consult CodePlex. I'll just stress the fact that the minimal prerequisites are the Microsoft .NET Framework Version 2.0 Redistributable Package (x86).

Though there are people around who cry for .msi installers, I'm very happy that we do not need those and I hope you 'll never depend on installers for IronPython. It is very nice just to unpack some files, using different releases at the same time, to get rid of them by just deleting one directory and in an Intranet you just need to know where the local guru maintains the current IronPython Files.

Just to save some typing of full pathnames it is a nice thing to have a special directory C:\bin for example (note my UNIX background) and add this to the PATH variable.

Than after unpacking some IronPython Releases to their the default locations
C:\IronPython-1.0.1
and
C:\IronPython-1.0

and putting the following two command-files into the C:\bin directory

------ ipy.bat --- this will always customized to refer to the newest release
@echo OFF
C:\IronPython-1.0.1\ipy.exe -X:TabCompletion %*

------ ipy1.0.bat ---
@echo OFF
C:\IronPython-1.0\ipy.exe -X:TabCompletion %*

and having installed CmdHere, starting any IronPython Script say myscript.py
is as simple as

• locate the script using the Explorer
• Open a cmd-shell using CMDHere
  
• and type: ipy myscript.py

To compare the behavior with the last but one release just type

• ipy1.0 myscript.py
  

Im lokalen Intranet machen wir das natürlich noch eine Spur einfacher. Alle die mitspielen wollen, installieren das Microsoft .NET Framework Version 2.0 auf ihren Kisten und richten sich ein Verzeichnis für Command-Skripte ein (Vorschlag: C:\bin), welches dem Pfad hinzugefügt wird.
Die aktuelle IronPython Version steht im Netzwerk auf einem intern bekannten Verzeichnis zur Verfügung.[hier im blog steht der Pfad definitiv nicht] Einige lokaler Erweiterungen sind dort unter lib abgelegt und können somit direkt importiert werden.
Der einzelne muß nur noch seine Kopie von ipy.bat in sein C:\bin bringen und auf das zentrale IronPython Verzeichnis angepassen.

Für ServiceRechner außerhalb des Intranets auf denen der jeweils benötigte Datenbankzugriff sowie das Framework 2.0 installiert ist, muß für Oracle eventuell noch der Databaseprovider installiert werden. Sonst ist lediglich das IronPython Verzeichnis auf den Rechner zu kopieren und wenn man den kompletten Pfad zur ipy.exe ausschreibt, dann braucht man nicht einmal Pfade zu verändern.

Danach können wir mit IronPython Skripten die Aufgaben lösen, bei denen uns die üblichen Bordmittel und auch die üblichen Zusatztools im Stich lassen. Typisches Besispiel sind hier die üblichen Längenbeschränkungen die einem die Ausgabe von langen Strings abschneiden.
Zu den Programmen die ihrer Aufgabe unvollkommen erledigen gehören besonders bcp, dts, SQL*Loader und natürlich mein besonderer Liebling SQL*PLUS.

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.

Yet Chrismas ?

# -*- coding: cp1252 -*-


allowed in IronPython v1.0.1 and no word in the v1.0.1 Release Notes. This was one off the major annoyances when trying to reuse old Python scripts. I only discovered it by chance when I was collecting material for my planned pitfall section.

C:\D\_svn\trunk\examples\python>ipy simple_text_demo.py
228
u'abc\xe4'
u'abc\xe4'
abcä

C:\D\_svn\trunk\examples\python>ipy10 simple_text_demo.py
Traceback (most recent call last):
File mscorlib, line unknown, in .ctor
File mscorlib, line unknown, in .ctor
TypeError: Value cannot be null.
Parameter name: encoding

C:\D\_svn\trunk\examples\python>

You find the example here. Many thanks to the IronPython developers. This saves me a lot of time.

Comming soon

Abstract:

• something about the Python scripts I've written in the past
  
• the advantages of switching to IronPython
• initial problems when applying IronPython to real tasks
  
• the audiance I will reach with this blog

Zusammenfassung:

• Ich erkläre kurz die Art von C-Python Skripten, die ich bisher geschrieben habe
  
• warum mir IronPython eine Fülle neuer Möglichkeiten eröffnet
• welche Anfangsprobleme beim Umstieg auf IronPython auftraten
• wen ich in diesem Blog ansprechen möchte

Most of the python I've done in the past just was the combination of Filesystem-Access and a lot of fiddling with regular expression (module re). Most of these scripts process SQL-Server scripts and produce some resturctured, beautified and well -wrapped scripts as output. A second kind helps me transforming scripts to Oracle.

I'm not using any sql-grammars, only heuristic aproches. When the sql is written in suitable format this aproch is goes rather far.

My standard editor for python is IDLE.

Speaking german most python scripts will include strings containing our german Umlaute (äöüßÄÖÜ) and some other characters as (§ µ °) which are not includes in the 7-bit Ascii half.

So I had to chose between starting the python-scripts with either

# -*- coding: cp1252 -*-

or using lots of unreadable escape sequences.

The sql scripts I have to process use a nice mixtures of OEM 850, cp1252 , utf8 and some were corupted by editing them using the wrong characterset. [That's just live].

Unicode or not can be detected automaticly. And for the rest one can take a pragmatic approch. Only a few additional character with ord() > 127 are in commen use in the german local and the sets of these codes from the two chartersets do not overlap.

Did you notice that I told nothing of database access from python. Well I just didn't try.

Database access is a fundamental part of my every days work. Anything from DAO ( help me forget all about VB3), ADO (VB6) und finally ADO.NET (C#). But without IronPython database access needs some additional components and I get no one else to use it.

IronPython = Python + integrated Database access + Windows.Forms

With IronPython I use exactly the same components as the C# devellopers.

The framework 2.0 is installed or comes any case, all what is missing is the download, unpacking and copying of the IronPython directory and they can start.

Ok I assume you have your databases allready working and if you are using Oracle you may have to get the ODP Database Provider for.Net.

In short: I intend to use IronPython mainly for scripting of .NET. I know about the way to access the python modules from a current C-Phython installation and I know about the FePython people, but I don't think I'll need their modules soon.

My intendet audiance are primarily DBAs of Oracle and SQL-Server Databases and of corse all python fans who are interessted in this topic.

In coming post I intend

• a minimal Installation HowTo (in german) for DBAs in .Net environments, which just need to add IronPython to their toolbox
  
• some small talk about the pitfalls I met switching from Python to IronPython. Most dealing with charactersets, unicode and €.
  
• explore the posibilities of accessing the databases by .Net (presumably a never ending story). Let's dive into ADO.NET
  
• some little wrappers around sql*plus and query analyzer so that I can use them for things they are suited for (and redirect their nasty error messages)
  

Meiki

Seem's to be rather usual to use one's pets for testing uploading of pictures.
Perherps I ought reduce the resolution of the picture.

So that's Meiki one of our two cats. Both knows exactly about keyboard and mice and where to sit to get attention.

Did by chance oneone find my svn ?
At python examples
is a C-Python module for reading Text Files stressing charset features and some test text files.
The transition to IronPython and some description follows soon.