Dynamic SQL
Dynamic SQL can be tricky to translate. Imagine the following T-SQL
Gets Translated to
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.
Pop it in the Translation Scratch Editor and translate.
I would used q'# .... #' to define the string.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements003.htm#i11223
This just allows me to use single quotes within string literal, so I don't have to escape each one. In this example its not required.
This is a small example so you may be quicker just translating the Dynamic SQL by hand. The process gets handy when you have a larger Dynamic SQL Statement.
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 10 dummycol/*@selectList*/ FROM table1 , dummytable/*@newTableName*/ WHERE dummycondition = true/*@condition1*/
Pop it in the Translation Scratch Editor and translate.
SELECT dummycol
/*@selectList*/FROM table1 ,
dummytable
/*@newTableName*/WHERE dummycodition = TRUE/*@condition1*/ AND ROWNUM <= 10;
I would used q'# .... #' to define the string.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements003.htm#i11223
This just allows me to use single quotes within string literal, so I don't have to escape each one. In this example its not required.
BEGIN
EXECUTE IMMEDIATE 'SELECT'||v_selectList ||'FROM table1 ,'|| v_newTableName || 'WHERE '||v_condition1 || ' AND ROWNUM <= 10';
END;
This is a small example so you may be quicker just translating the Dynamic SQL by hand. The process gets handy when you have a larger Dynamic SQL Statement.