Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
OPEN...FOR Conversion Issues

Answered OPEN...FOR Conversion Issues

  • Tuesday, May 25, 2010 6:41 PM
     
     
    Sign In to Vote
    0

    Hello,

    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.

    Thank you,

    Sanooj

     

All Replies

  • Wednesday, May 26, 2010 2:01 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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
    •