|
|
sqb2ora.pl
#############################################################
# #
# PERL script to convert a SQL (Gupta SqlBase) #
# UNLOAD file into Oracle- ... #
# #
# #
# #
# J. Pissarek #
# #
# Version : 1.1 #
# Stand : 30.11.1998 #
# #
# #
#############################################################
use Env qw(SqlDatabase SqlUser SqlPassword);
open(hFileSqlSchema, ">sql\\ora_pool.sql"); # Datenbanktabellen
open(hFileSqlDrop, ">sql\\ora_drop.sql"); #
open(hFileSqlGrantUsr, ">sql\\grant_usr.sql"); #
open(hFileSqlGrantTbl, ">sql\\grant_tbl.sql"); # Nutzer - Tabellenrechte
open(hFileSqlIndex, ">sql\\ora_idx.sql"); #
open(hFileSqlRefInt, ">sql\\RefInt.sql"); #
open(hFileCTLbat, ">bat\\startctl.bat"); #
open(hFileNoOra, ">log\\no_ora.log"); #
open(hFileSqlSC, ">sql\\sc.sql"); # stored commands
open(hFileLongData, ">sql\\longs.sql"); #
print hFileCTLbat "\@echo off\n";
@SqlDatabase = split(/:/, $SqlDatabase);
@SqlUser = split(/:/, $SqlUser);
@SqlPassword = split(/:/, $SqlPassword);
print hFileLongData "CONNECT $SqlDatabase $SqlUser/$SqlPassword\n/\n";
#============================================================
# Hauptleseschleife
#============================================================
$/="\n/\n";
while ($buf=<STDIN>){
$buf =~ s/\@UPPER/UPPER/g;
printf("\r");
#============================================================
# Ausgabe der "stored commands"
#============================================================
if( $buf =~ /^STORE / ){
$buf =~ s/^STORE //;
@strName=split(/ /,$buf);
$buf =~ s/$strName[0]//;
$buf =~ s/\///g;
$buf =~ s/\n/ /g;
# print hFileSqlSC "INSERT INTO SYSADM.SQB_SC (NAME, STATEMENT) VALUES ('$strName[0]','$buf')\n\/\n";
print hFileSqlSC "INSERT INTO SYSADM.SQB_SC (NAME, STATEMENT) VALUES ('$strName[0]','...')\n\/\n";
}else{
#============================================================
# Ausgabe der Daten in Oracle "CTL" - Dateien
# (keine long varchar - Daten)
#============================================================
if( $buf =~ /INSERT INTO/ ){
if(!$LongDaten){
printf(" Daten \n");
$buf =~ s/~ANAANAK~//g;
$buf =~ s/~ANAKANAK~//g;
$buf =~ s/~ANAK~//g;
$buf =~ s/~LD~/ü/g;
$buf =~ s/~ME~/Ä/g;
$buf =~ s/~NG~/Ö/g;
$buf =~ s/~KH~/§/g;
$buf =~ s/~NM~/Ü/g;
$buf =~ s/~JK~/Ü/g;
$buf =~ s/~NP~/ß/g;
$buf =~ s/~OE~/ä/g;
$buf =~ s/~OJ~/é/g;
$buf =~ s/~OH~/ç/g;
$buf =~ s/~OEOE~/ää/g;
$buf =~ s/~OENP~/äß/g;
$buf =~ s/~OL~/ë/g;
$buf =~ s/~ON~//g;
$buf =~ s/~OB~/á/g;
$buf =~ s/~OI~/è/g;
$buf =~ s/~PE~/ô/g;
$buf =~ s/~PF~/ä/g;
$buf =~ s/~PGNP~/öß/g;
$buf =~ s/~PM~/ü/g;
$buf =~ s/~PMPM~/üü/g;
$buf =~ s/~PG~/ö/g;
$buf =~ s/~PMOH~/üç/g;
$buf =~ s/~PD~/ó/g;
$buf =~ s/~PGPG~/öö/g;
$buf =~ s/~PMNP~/üß/g;
$buf =~ s/~KHKH~/§§/g;
$buf =~ s/~NPOE~/ßä/g;
$buf =~ s/~NPPG~/ßö/g;
$buf =~ s/~NF~//g;
$buf =~ s/~NPOE~/ßä/g;
$buf =~ s/~NPPM~/ßü/g;
$buf =~ s/~LGPMLG~/ü/g;
$buf =~ s/~LGLGNM~/Ü/g;
$buf =~ s/~LGLGLGLGLGLGLGLGLGLGLGLGLGLGLGLGLG~//g;
$buf =~ s/~HO~//g;
$buf =~ s/~HP~//g;
$buf =~ s/~IC~//g;
$buf =~ s/~IO~//g;
$buf =~ s/~JJ~/Ö/g;
$buf =~ s/~JK~//g;
$buf =~ s/~LG~//g;
$buf =~ s/~LC~//g;
$buf =~ s/~LE~//g;
$buf =~ s/~LF~//g;
$buf =~ s/~CP~/-/g;
$buf =~ s/select\n/select /g;
$buf =~ s/INSERT INTO [^)]+\)//;
$buf =~ s/\\\n//;
$buf =~ s/\$DATATYPES.*?\n//;
$buf =~ s/\/$//;
$buf =~ s/(\n$)+//;
$buf =~ s/(\d\d\d\d-\d\d-\d\d-\d\d\.\d\d\.\d\d)\.\d\d\d\d\d\d/$1/g; # TIMESTAMP / DATETIME
$buf =~ s/(\d\d\.\d\d\.\d\d)\.\d\d\d\d\d\d/$1/g; # TIME
#long:
$buf =~ s/~~\n//g;
$buf =~ s/~\n\/\//"/g;
$buf =~ s/\n\/\//"/g;
$buf =~ s/\n"\n/"\n/g;
$buf =~ s/,\$long,\n/,"/g;
$buf =~ s/\n\n/\n/g;
$buf =~ s/~AN~\n/ /g;
$buf =~ s/~ANAN~\n/ /g;
# if( $buf =~ /~\w~/ ){
# print "\n->$&>-\n";
# }
print hFileCTL $buf;
close(hFileCTL);
}else{
#============================================================
# Ausgabe der Daten in eine (SqlTalk) "SQL" - Datei
# (long varchar - Daten)
#============================================================
print hFileLongData $buf;
}
}else{
#============================================================
# Ausgabe INDEX
#
#============================================================
if( $buf =~ /(CREATE INDEX|CREATE UNIQUE INDEX)/ ){
if( $buf =~ /(UPPER\@LEFT|\@LOWER|SYSADM.LINK_LEFK|SYSADM.LINK_ORTFK|SYSADM.LINK_PERSFK)/ ){
print hFileNoOra $buf;
}else{
print hFileSqlIndex $buf;
}
}else{
#============================================================
# Ausgabe Nutzer
#
#============================================================
if( $buf =~ /(GRANT CONNECT TO|GRANT RESOURCE TO)/ ){
print hFileSqlGrantUsr $buf;
@GrantStatement=split(/ /,$buf);
@GrantStatement[6] =~ s/\n\///;
print hFileSqlGrantUsr "ALTER USER ".@GrantStatement[3]." IDENTIFIED BY ".@GrantStatement[6]." DEFAULT TABLESPACE \"USER_DATA\" TEMPORARY TABLESPACE \"TEMPORARY_DATA\"\n/\n";
}else{
#============================================================
# Ausgabe der Tabellenrechte der Nutzer
#
#============================================================
if( $buf =~ /GRANT/ ){
@GrantStatement = split(/ /,$buf);
@OwnerTable = split(/\./,@GrantStatement[3]);
if( $OldOwner !~ /@OwnerTable[0]/ ){
print hFileSqlGrantTbl "CONNECT ".@OwnerTable[0]."/".@OwnerTable[0]."\n/\n";
$OldOwner = @OwnerTable[0];
}
print hFileSqlGrantTbl $buf;
}else{
#============================================================
# Ausgabe ref. Int.
#
#============================================================
if( $buf =~ /ALTER TABLE/ ){
if( $buf =~ /ON DELETE SET NULL/){
$buf =~ s/\n//g;
@AlterStatement = split(/ /,$buf);
$ForeignKeyName = $buf;
$ForeignKeyName =~ s/([^(]+)\(//;
$ForeignKeyName =~ s/[^)]+$//;
$ForeignKeyName =~ s/\)//g;
$ForeignKeyName =~ s/ //g;
$PKey = $PKeyTab{$AlterStatement[10]};
$PKey =~ s/\)\///;
print hFileSqlRefInt "CREATE TRIGGER SN_$ForeignKeyName\n".
"AFTER DELETE OR UPDATE OF $PKey ON $AlterStatement[10]\n".
"FOR EACH ROW\n".
"BEGIN\n".
" IF UPDATING AND :OLD.$PKey != :NEW.$PKey OR DELETING THEN\n".
" UPDATE $AlterStatement[2] SET $ForeignKeyName = NULL\n".
" WHERE $ForeignKeyName = :OLD.$PKey;\n".
" END IF;\n".
"END;\n".
"/\n";
}else{
$buf =~ s/ FOREIGN KEY [^ ]+ / ADD FOREIGN KEY /;
$buf =~ s/ PRIMARY KEY / ADD PRIMARY KEY /;
print hFileSqlRefInt $buf;
}
if($buf =~ /PRIMARY KEY/){
$buf =~ s/\n//g;
@AlterStatement = split(/ /,$buf);
@OwnerTable = split(/\./,$AlterStatement[2]);
$PKeyTab{$AlterStatement[2]} = $AlterStatement[8];
if( $OldOwner !~ /@OwnerTable[0]/ ){
print hFileSqlRefInt "CONNECT ".@OwnerTable[0]."/".@OwnerTable[0]."\n/\n";
$OldOwner = @OwnerTable[0];
}
print hFileSqlRefInt "GRANT REFERENCES ON ".@AlterStatement[2]." TO PUBLIC\n/\n";
}
}else{
#============================================================
# Ausgabe der Nicht- Oracle konformen Anweisungen
#
#============================================================
if( $buf =~
/(\@LEFT|\@LOWER| TO SYSADM| TO SYSREP|SYSADM.LINK_LEFK|SYSADM.LINK_ORTFK|SYSADM.LINK_PERSFK|SET REFINTCHECK|SYSSQL|VIEW )/ ){
print hFileNoOra $buf;
}else{
#============================================================
# Ausgabe der Oracle konformen Anweisungen
# anlegen Datenbankschema
#============================================================
$buf =~ s/WITH DEFAULT//g;
$buf =~ s/SYSADM.FILE/SYSADM.DATEI/g;
$buf =~ s/SYNONYM FILE/SYNONYM DATEI/g;
$buf =~ s/SYSADM.\$PSEUDO_LE/SYSADM.DPSEUDO_LE/g;
if( $buf =~ /^CREATE TABLE/ ){
if( $buf =~ /LONG VARCHAR/ ){
$LongDaten = 1;
}else{
$LongDaten = 0;
}
$strH = $buf;
@CreateStatement=split(/ /,$strH);
print hFileCTLbat "echo Lade : ".@CreateStatement[2]."\nsqlldr73 control=CTL\\".@CreateStatement[2].".CTL userid=%SqlUser%/%SqlPassword% log=LOG\\".@CreateStatement[2].">screen\n";
printf(" Tab:\t\t %s ",@CreateStatement[2]);
$strFelder=$strH;
open(hFileCTL,">CTL\\@CreateStatement[2].CTL");
print hFileCTL "OPTIONS(SILENT=(FEEDBACK),DIRECT=TRUE)\nLOAD DATA\nINFILE *\nBADFILE 'BAD\\".@CreateStatement[2]."'\nINTO TABLE ".@CreateStatement[2]."\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\n".&SelField($strFelder)."\nBEGINDATA";
}
$buf =~ s/DATETIME/DATE/g;
$buf =~ s/TIMESTAMP/DATE/g;
$buf =~ s/TIME/DATE/g;
print hFileSqlSchema $buf;
#============================================================
# Ausgabe des Schema- Lösch- Skripts
#
#============================================================
if( $buf =~ /(^CREATE TABLE|^CREATE PUBLIC)/ ){
@CreateStatement=split(/ /,$buf);
if( @CreateStatement[1] =~ /PUBLIC/ ){
print hFileSqlDrop "DROP @CreateStatement[1] @CreateStatement[2] @CreateStatement[3]\n/\n";
}else{
print hFileSqlDrop "DROP @CreateStatement[1] @CreateStatement[2] CASCADE CONSTRAINTS\n/\n";
}
}
#============================================================
}}}}}
}
}
}
#============================================================
# Ende der Hauptleseschleife
#============================================================
print hFileSqlDropOra "\nEXIT\n";
print hFileSqlGrantUsr "\nEXIT\n";
print hFileSqlGrantTbl "\nEXIT\n";
print hFileSqlIndex "\nEXIT\n";
print hFileSqlRefInt "\nEXIT\n";
print hFileSqlSC "\nEXIT\n";
print hFileLongData "\nEXIT\n/";
close(hFileSqlDropOra);
close(hFileSqlGrantUsr);
close(hFileSqlGrantTbl);
close(hFileSqlIndex);
close(hFileSqlRefInt);
close(hFileSqlSC);
close(hFileNoOra);
close(hFileCTLbat);
close(hFileLongData);
#============================================================
# Unterprogramm zur Feldselektion (Spaltenbezeichner)
#
#============================================================
sub SelField
{
$strHelp = $_[0];
$strFieldList="(";
$strHelp =~ s/\n//g;
$strHelp =~ s/(.*?)\(//;
@strLines=split(/,/,$strHelp);
foreach $strLine (@strLines){
$strLine =~ s/^ +//g;
@strFields=split(/ /,$strLine);
if(@strFields[1] =~ /TIMESTAMP/ ){
$strFieldList=$strFieldList.@strFields[0]." DATE(19) 'yyyy-mm-dd-HH24.MI.SS',";
}else{
if(@strFields[1] =~ /TIME/ ){
$strFieldList=$strFieldList.@strFields[0]." DATE(8) 'HH24.MI.SS',";
}else{
if(@strFields[1] =~ /DATE/ ){
$strFieldList=$strFieldList.@strFields[0]." DATE(10) 'yyyy-mm-dd',";
}else{
$strFieldList=$strFieldList.@strFields[0].",";
}}}
}
$strFieldList=~ s/,$//;
$strFieldList=$strFieldList.")";
$strFieldList=~ s/$,//;
$_[0]=$strFieldList;
}
|
|
