Dermot's Tech Tips

Dynamic SQL can be tricky to translate. Imagine the following T-SQL EXECUTE("SELECT TOP 10 " + @selectList + "FROM table1,"+@newTableName + " WHERE "+@condition1); Gets Translated to BEGIN EXECUTE IMMEDIATE 'SELECT TOP 10 ' || v_selectList || 'FROM table1,' || v_newTableName || ' WHERE ' || v_condition1; END; Our translator is designed to recognize and translate valid T-SQL. But the SQL within the strings that make up the execute statement is not complete, therefore its not valid SQL that the translator can work with. How should it translate "SELECT TOP 10" ? As a whole the Execute statement is valid, but in the eyes of the translator it cant do much with it. So the translator does not translate dynamic SQL. This has to be manually performed. But there is help! Take the Dynamic SQL String and create a valid, complete SQL Statement from it. I added in "dummy" values to make up for the missing pieces. SELECT TOP...

Sometimes a procedure, function, trigger or view will fail to convert. An error will popup in the migration log "Failed to convert blah blah". The case maybe that the object is hundreds of lines long but one line is causing the translator to fail. Heres how I go about identifying that problematic clause so you can continue the migration. Copy the ddl into the Translation Scratch Editor (Migrations> Translation Scratch Editor)(TSE) Select the correct translator from the TSE toolbar. Translate it If it translates , then you can use the converted PL/SQL as the starting point. Paste it into your converted model, report the bug and continue with your migration. If it fails, then I comment out half of the statements (still leaving valid T-SQL, dont cut words or statements in half :) ) Keep commenting out parts of the objects ddl until you it translates and you have comented out a particular statement or clause. You can then paste the generated PL/SQL into your converted model...

Sybase and SQL Server have an option to called "is quoted identifier on". When ON, double quotes can be used to reference identifiers (tables,columns,....). When OFF, double quotes means string literals ("Hello World"). In the past this caused no end of problems when translating to PL/SQL , as our translator had to guess if a double quote was referencing a identifier or a string. For Example what does this mean ? SELECT "col1" from table1 Is "col1" a column in table1 or is it string? In PL/SQL strings are defined using single quotes and identifiers are convert so that they are case insensitive (without double quotes). Pre 1.5 the translator would look at the context the double quotes where used and make a best guess. In 1.5 we changed this so that the translator can be more accurate. There is a new preference By Default (like the SQL Server and Sybase) it is on. So double quotes are recognized in T-SQL as identifiers. If the database your migrat...