Erkenntnisse aus der MySQL -> Oracle migration

[Donald]

Hallo,

ich habe mich nun eine ganze Weile mit der Migration einer PHP Anwendung von MySQL auf Oracle beschäftigt. Ich möchte hier meine Erfahrung aufschreiben, damit jemand anderem evtl. geholfen wird. Einen Anspruch auf Vollständigkeit der Themen gibt es natürlich nicht.

Hier die wesentlichen Punkte:
---
Oracle kennt kein NOW(). Ein Ersatz mit CURRENT_TIMESTAMP hat hier geholfen.
---
Die Datumsangaben werden, im Gegensatz zu MySQL, nicht sehr frei interpretiert. Sie müssen
A) mit TO_DATE() übergeben werden. Dabei ist das komplette Format der Datumsangabe zu übergeben.
B) durch Anpassen von NLS_DATE_FORMAT umgestaltet werden. Voraussetzung ist, dass man in seiner Anwendung immer im selben Format übergibt. Dazu gehört auch die Uhrzeit, die man bei entsprechendem Format dann immer angeben muss.
Doch Achtung: Wenn man OCI verwendet, kann man die Oracle-Parameter verändern wie man möchte. Es zeigt keine Wirkung (also einfach lassen). Es hilft nur, bei jedem Query vorher die Session mit ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' SCOPE = SPFILE; auf das gewünschte Format zu bringen. Dann klappt es. Performance-Einschränkung habe ich gemessen und kam auf 0,0005 Sekunden mehr für einen Query mit Anpassung der Session (10'000 Querys ausgeführt).
---
Eine ON DUPLICATE KEY Funtion gibt es in Oracle nicht. Das kann man nur mit komplexeren IF THEN Anweisungen oder einer MERGE WHEN NOT MATCHED Funktion erreichen. Diese ist aber recht komplex. In diesen Fällen habe ich mir lieber einen SELECT vorneweg gegönnt und entscheide dann im Code ob ich ein INSERT oder ein UPDATE mache.
---
Oracle kennt keine INSERTs im Stil von INSERT INTO table SET Field1=Value1, Field2=Value2 etc. Das geht nur mit INSERT INTO table(Field1, Field2) VALUES(Value1, Value2). Ich habe mir einen kleinen Parser gebaut, der die INSERTS zur Laufzeit umbaut. Ansonsten hätte ich bergeweise INSERTS umbauen müssen. Ausserdem hat mir die Code-Übersichtlichkeit in der SET Variante mehr zugesagt. Code ist weiter unten im Thread...
---
Einen vergleichbaren Ersatz für LIMIT gibt es unter Oracle nicht. Stattdessen muss man den Query in zwei übergeordnete Query's einschachteln um das zu simulieren. Auch hier habe ich mir einen Parser zur Laufzeit gebaut, der die bestehenden LIMIT-Angaben umwandelt (nicht in Sub-Selects). Code ist weiter unten im Thread...
---
Die TO_DAYS() Funktion aus MySQL kann man möglicherweise in Oracle einfach durch TRUNC() ersetzen. Das liefert auch die Tageszahl.
---
Die MONTH(Date) Funktion aus MySQL kann mit TO_NUMBER(TO_CHAR(Date,'mm')) übersetzt werden.
---
Die YEAR(Date) Funktion aus MySQL kann mit TO_NUMBER(TO_CHAR(Date,'yyyy')) übersetzt werden.
---
Vor allem die Oracle XE hat per Default nur 10 Prozesse eingestellt. Das führt bei schnellen Datenbank-Zugriffen zu Fehlermeldungen (TNS:listener). Dem kann man mit ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE; abhelfen (danach DB restarten).
---
Statt CONCAT() fügt man in Oracle Strings übrigens mit dem || Operator zusammen.
---
AutoID Spalten kennt Oracle nicht. Dazu muss man mit Sequenzen und Triggern arbeiten. Das Internet ist voll mit Beispielen dazu.
---
LIKE-Suchen sind bei Oracle immer Case-Sensitiv! Bei MySQL ist das nicht der Fall. Nun hat man evtl. ein Problem zB bei Nutzer-Logins etc. Hier kann es helfen, die Abfrage so zu gestalten (Beispielsuche nach Usernamen):
$SQL = "SELECT * FROM tbluser WHERE UPPER(USERNAME) LIKE '%" . strtoupper($Username) . "%' ORDER BY USERID";
Leider benutzt Oracle nun nicht mehr einen auf USERNAME stehenden Index. Da gibt es aber Abhilfe, indem man den Index folgend anlegt:
CREATE INDEX idx_Userame ON tbluser(UPPER(USERNAME));
---
GROUP BY kann bei Oracle nicht mit Aliasen arbeiten. Man sollte dann den eigentlichen (im Query angegebenen) Ausdruck erneut zur Sortierung heranziehen.

Ich hoffe es hilft jemandem...

Grüße,

Donald

[nikosch]

Danke für die Arbeit!

Kannst Du viell. hierzu noch ein paar erklärende Worte verlieren?

Einen vergleichbaren Ersatz für LIMIT gibt es unter Oracle nicht. Stattdessen muss man den Query in zwei übergeordnete Query's einschachteln um das zu simulieren. Auch hier habe ich mir einen Parser zur Laufzeit gebaut, der die bestehenden LIMIT-Angaben umwandelt (nicht in Sub-Selects).

[thomas_w]

Oracle kennt keine INSERTs im Stil von INSERT INTO table SET Field1=Value1, Field2=Value2 etc. Das geht nur mit INSERT INTO table(Field1, Field2) VALUES(Value1, Value2).

In MySQL werden eine Menge "sinnvoller" Funktionen angeboten, die nicht im SQL-Standard sind (Beispielweise NOW() oder diese INSERT Technik. Das SET gehört eher zum UPDATE). Dann klemmt es beim Wechseln zu einer anderen Datenbank mal eben mehr oder weniger. Auch ORACLE baut da vieles am Standard vorbei. Die IBM DB2 ist meiner Meinung nach am nächsten am SQL-Standard, wobei man sofort definieren müsste, welchen SQL-Standard man denn meint. Nur der SQL-92 wird von den meisten Datenbanken nahezu vollständig unterstützt. Aber dies ist ein Thema für lange Winterabende...

Schön dass Du die Probleme gelöst hast!

Grüße
Thomas

[Chriz]

Sehr schön.

Ich kann morgen vom Geschäft mal mein Skript posten, das ein CREATE TABLE Statement von MySQL in ORACLE transfomiert (Trigger, Fremdschlüssel, etc.), allerdings sehr rudimentär und ziemlich star.

[Donald]

MySQL LIMIT -> ORACLE

Hier die Routine, welche eine MySQL LIMIT Funktion in das passende Oracle Konstrukt wandelt. Damit kann der Query so bleiben wie er für MySQL gemacht war. Also gut für eine Anwendung welche mit beiden Datenbanken funktionieren soll.
Einschränkung: Funktioniert nicht in Sub-Querys!

// converts a given MySQL LIMIT() clause to the oracle pendant! 
// Attention: this works only with values up to 999999!
function ConvertOracleLimit($SQL) {
    // find LIMIT position
    $Pos = strripos($SQL, "limit ");
    if ($Pos === FALSE) { 
        return $SQL; // no LIMIT clause found, no need to do something
    }
    
    // find a following comma position (max. 8 chars later)
    $PosComma = stripos($SQL, ",", $Pos);
    if ($PosComma > $Pos + 6 + 8) {
        $PosComma = FALSE; // too far away
    }
    
    // get min and count values
    if ($PosComma === FALSE) {
        // using no offset
        $MinRow = 1;
        $Count = intval(substr($SQL, $Pos + 5, 6));
        
        $PosRestBlank = stripos($SQL, " ", $Pos + 7);
        if ($PosRestBlank === FALSE) { $PosRestBlank = strlen($SQL); }
        $PosRestLF = stripos($SQL, " ", $Pos + 7);
        if ($PosRestLF === FALSE) { $PosRestLF = strlen($SQL); }

    } else {
        // using ffset
        $MinRow = intval(substr($SQL, $Pos + 5, $PosComma - $Pos - 5));
        $Count = intval(substr($SQL, $PosComma + 1, 5));
        
        $PosRestBlank = stripos($SQL, " ", $PosComma + 2);
        if ($PosRestBlank === FALSE) { $PosRestBlank = strlen($SQL); }
        $PosRestLF = stripos($SQL, " ", $PosComma + 2);
        if ($PosRestLF === FALSE) { $PosRestLF = strlen($SQL); }

    }
    
    // choose nearest end of limit (linefeed or a blank char)
    if ($PosRestBlank < $PosRestLF) {
        $PosRest = $PosRestBlank;
    } else {
        $PosRest = $PosRestLF;
    }

    // remove original limit clause
    $SQL = substr($SQL, 0, $Pos) . substr($SQL, $PosRest);
    
    // cover SQL by oracle specific limit variant
    $limit_sql = "SELECT z2.* FROM ( 
                        SELECT ROWNUM AS db_rownum, z1.* FROM ( 
                        $SQL
                        ) z1 
                  ) z2 
                  WHERE z2.db_rownum BETWEEN " . ($MinRow) . " AND " . ($MinRow+$Count); 
    return $limit_sql;
} 

Beispiel:
Ein solcher Query:

SELECT * FROM tblTest 
WHERE ID > 10 
ORDER BY ID 
LIMIT 10, 8

wird umgewandelt zu

SELECT z2.* FROM ( 
  SELECT ROWNUM AS db_rownum, z1.* FROM ( 
    SELECT * FROM tblTest 
    WHERE ID > 10 
    ORDER BY ID
  ) z1 
) z2 
WHERE z2.db_rownum BETWEEN 10 AND 18

Grüße,

Donald

[Donald]

MySQL INSERT mit SET -> ORACLE INSERT mit VALUES

[EDIT] Leider hatte die Routine ein Problem mit Kommas in Strings. Das ist jetzt behoben.

Da Oracle die Form INSERT INTO tabelle SET Spalte=Wert nicht versteht, habe ich mir diese Funktion zum Umbauen erstellt. Die Schreibweise der von MySQL unterstützen SET Methode gefällt mir im Code deutlich besser.

// converts a given mysql insert in the following format:
// INSERT INTO tablename SET Field=Value, Field=Value
// into the oracle preferred format:
// INSERT INTO tablename(Field, Field) VALUES(Value, Value)
function ConvertOracleInsert($SQL) {
    if (substr($SQL,0, 12) != "INSERT INTO ") {
        return $SQL; // no insert query
    }
    if (stripos($SQL, "VALUES(") > 12) {
        return $SQL; // already INSERT INTO() VALUES() formated
    }
    
    $SetPos = stripos($SQL, "SET", 12) + 3;
    if ($SetPos < 15) { return $SQL; }
    $ValueString = substr($SQL, $SetPos);
    $Assigns = array();
    for ($i=0;$i<=strlen($ValueString);$i++){
        $c = substr($ValueString, $i, 1);
        if ($c == "'" AND $InString == TRUE) {
            $InString = FALSE;
        } else {
            if ($c == "'" AND $InString == FALSE) {
                $InString = TRUE;
                // try a faster way through the strings
                $NextPos = stripos($ValueString, "'", $i + 1); // get position of the next '
                if ($NextPos > $i) {
                    $Part .= substr($ValueString, $i, $NextPos - $i);
                    $c = "";
                    $i = $NextPos - 1; // to allow the last ' to get found
                }
            }
        }
        if ($c == "," AND $InString == FALSE) {
            $Assigns[] = $Part;
            $Part = "";   
        } else {
            $Part .= $c;
        }
    }
    if ($Part != "") {
        $Assigns[] = $Part; // add the last part
    }
    
    foreach($Assigns AS $Assign) {
        $Row = explode("=", $Assign);
        $Fields .= trim($Row[0]) . ", ";
        $Values .= trim($Row[1]) . ", ";
    }
    if (substr($Fields, -2) == ", ") { $Fields = substr($Fields, 0, -2); }
    if (substr($Values, -2) == ", ") { $Values = substr($Values, 0, -2); }
    
    $Result = substr($SQL, 0, $SetPos - 4); // Beginning
    $Result .= "($Fields) VALUES($Values)";
    return $Result;
} 

Beispiel:
Ein Query wie dieser

INSERT INTO tblTest SET UserID=10, Username='Klaus, Meier'

wird umgewandelt zu:

INSERT INTO tblTest(UserID, Username) VALUES(10, 'Klaus, Meier')

Grüße,

Donald

[Donald]

Spaltennamen in MYSQL Tabelle in Großbuchstaben wandeln

Wenn man eine Anwendung auf den Betrieb mit mehreren Datenbanken auslegt, dann merkt man schnell, dass Spaltennamen am besten immer in Großbuchstaben angelegt werden. Hat man das initial aber nicht (weil MySQL das kann), dann hat man bald ein Problem (Oracle kann das nämlich nicht so ohne weiteres).
Hier eine kleine Routine, welche mir alle bestehenden MySQL Tabellen auf Großschreibung umgebaut hat. Diese Routine verwendet allerdings ein paar Funktionen aus einem Include, die hier nicht abgebildet sind:
Klasse classOpenDB() -> erlaubt den gekapselten Zugriff. Kann man leicht ersetzen.
GetOneSQLValue() -> liefert mir die erste Zeile eines Query als Array.
ExecuteSQL() -> führt einen Query einfach nur aus.
Diese Routinen kann ich leider nicht veröffentlichen, aber sind sicher schnell nachprogrammiert oder ersetzt. Es geht eher um die Technik dahinter um die Spalten umzubenennen.

// rename all available MySQL columns to uppercase names!
// alrerady uppercase columns will get skipped and not changed!

// get all tables that are needed to convert
$SQL = "SELECT TABLE_NAME FROM information_schema.tables 
        WHERE TABLE_NAME LIKE 'tbl%'
        AND TABLE_SCHEMA='mySchemaName'";

$db = new classOpenDB($SQL);
while($row = $db->fetchArray()) {
    // processing each table
    $TableName = $row["TABLE_NAME"];
    echo "<b><u>converting table $TableName...</u></b><br>";
    $Result = GetOneSQLValue("SHOW CREATE TABLE $TableName");
    $Create = $Result["Create Table"];
    $Lines  = explode(chr(10), $Create);
    foreach($Lines AS $Line) {
        $Line = trim($Line);
        if (substr($Line, 0, 1) == "`") {
            if (substr($Line, -1) == ",") {
                // remove last comma
                $Line = substr($Line, 0, -1);
            } 
            echo "<b>processing:</b> " . $Line . ". ";
            $Elements = explode("`", $Line);
            if ($Elements[1] != strtoupper($Elements[1])) {
                // need to process
                $FieldName = "`" . $Elements[1] . "`";
                $New = strtoupper($Line);
                $Alter = "ALTER TABLE $TableName CHANGE COLUMN $FieldName $New";
                $Result = ExecuteSQL($Alter);
                if ($Result) {
                    echo "<span style='color: green;'>-&gt; OK</span>";
                } else {
                    echo "<span style='color: red;'>-&gt; ERROR</span>";
                }
                usleep(50000);
            } else {
                // skip
                echo "<span style='color: blue;'>-&gt; SKIP</span>";
            }
            echo "<br>";
        }
    }
    echo "<hr>";
    flush();
}
echo "<b>FINISHED!</b>"; 

Wichtig: Im ersten Query oben ($SQL = "...") muss man den so anpassen, dass nur die Tabellen der eigenen Anwendung bearbeitet werden!

Grüße,

Donald

[lstegelitz]

Früher (mein letzter Stand ist Oracle konnte Oracle keine "multiple inserts" der Form

INSERT INTO <table> (field1, field2, field3) VALUES (
    (1, 1, 1),
    (2, 2, 2)
)

Musste man in mehreren Einzel-INSERTs machen, das hat mich seinerzeit wahnsinnig gemacht... ist das immer noch so?

[nikosch]

Oracle 8 sollte das heißen (an alle Boardneulinge).

[Donald]

Musste man in mehreren Einzel-INSERTs machen, das hat mich seinerzeit wahnsinnig gemacht... ist das immer noch so?

Ja, das ist bei 10G (Oracle XE) noch immer so. Meines Wissens auch bei 11G.

Donald