Tuesday, May 25, 2010 6:41 PM
I am relatively new to the migration tool SSMA and I would be grateul if anyone could help me in understanding this issue. I am trying to migrate from Oracle 10g to SQL Server 2008 and when I tried to convert my stored procedures in Oracle, I was given this error message
SSMA error messages:
O2SS0157: The OPEN...FOR statement will be converted, but the dynamic string must be converted manually.
Could anyone explain me to on what I need to do to fix this issue with the help of an example.
Wednesday, May 26, 2010 2:01 PM
This is more warning than error.
IIRC, you would OPEN <some cursor variable name> FOR <some select statement>. The select statement may be something dynamic that is not known until runtime. For example:
DECLARE TYPE MyCursorType IS REF CURSOR; myCursor01 MyCursorType; myDynamicQuery VARCHAR2(150); myDynamicQuery ='SELECT * FROM emp' OPEN myCursor01 FOR myDynamicQuery LOOP FETCH myCursor01 INTO........... ........ ........
It's been a while since I had to write PL/SQL so I expect there are bugs in the script but the point is, you can have queries that may be assembled on the fly during runtime. In the partial code sample above, the "myDynamicQuery" variable could very well be built by concatenating a few strings together to build the SELECT statement. In such cases, there's no way to figure out what that query might be when you analyze the sprocs in the DB with SSMA.
You'll need to look into how that dynamic query is built and address it at the source.
Of course, you could also take the opportunity to convert the cursor code into set based queries to get better performance and use less resources..... :-)
No great genius has ever existed without some touch of madness. - Aristotle
- Marked As Answer by sanooj82 Tuesday, June 01, 2010 5:18 PM