This discussion is archived
8 Replies Latest reply: Jan 25, 2013 7:38 AM by 666153 RSS

Error converting SQL Server DDL and views to Oracle

666153 Newbie
Currently Being Moderated
I am trying to convert SQL Serve 2008 on windows to Oracle 11g using SQL Developer 3.2.20.09. I am using the jtds-1.2.7 drivers to connect to SQL Server.

I have made several attempts to run the conversion and am getting the tables, indexes, and triggers converted with no issue. However, my Procedures and views are not coming across. All of them are giving similar errors as:
"Failed To Convert Stored Procedure get_compvision > esp3.dbo.get_compvision:unexpected end of subtree: Line 0 Column 0"

When I log into the source SQL Server environment using SQL Developer, I get an empty SQL Editor window with "null" when I try to {right-click -> "Open"} on the Procedure.

Logging into SQL Server Administrator as the same user I can see the source code, so the issue appears to be related to SQL Developer and not my user privleges. I am a dbo alias in SQL Server.

In addition, if I click on a view, I can see the data and metadata, but not the SQL that defines the view. I see tabs for column, data, triggers, and details.

In addition, if I go to the "capture" for my Migration project and drill down to procedures, I can see them listed, but each and every procedure gives me a "/* DDL not accesible */" error. I get teh same results for views, triggers and indexes.

Can someone help me? I have been googling for days!

Edited by: user4714217 on Jan 24, 2013 12:54 PM
  • 1. Re: Error converting SQL Server DDL and views to Oracle
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    Have you seen our supplementary documentation on how to increase you odds of success for SQL Server migrations?
    http://docs.oracle.com/cd/E35137_01/doc.32/e18462/trig_stored_proc.htm#BABGCJBJ

    Searching this forum, I am able to find only one reply to a "Failed to convert" question:
    Re: Unknown Object:unexpected AST node: EXEC+PARAM_LIST:Line -1 Column -1

    and a couple of others regarding the "unexpected end of subtree":
    Re: ExtStringTemplate Warning while Converting a SQLServer DB to Oracle 10g
    SQL Server migration object renamin

    I will ping Dermot on this. In case your situation is something new and unexpected, it might help if you could post some samples of the procedure / view DDL that fail.

    Regards,
    Gary
    SQL Developer Team
  • 2. Re: Error converting SQL Server DDL and views to Oracle
    666153 Newbie
    Currently Being Moderated
    SELECT v.vnd_c, CASE v.use_parent_addr_flg WHEN 0 THEN a.str_1 ELSE pa.str_1 END AS str_1,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.str_2 ELSE pa.str_2 END AS str_2,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.str_3 ELSE pa.str_3 END AS str_3,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.city ELSE pa.city END AS city,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.state ELSE pa.state END AS state,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.zipcode ELSE pa.zipcode END AS zipcode,
    CAST((CASE v.use_parent_addr_flg WHEN 0 THEN (CASE a.foreign_flg WHEN 1 THEN 'Y' ELSE 'N' END)
    ELSE (CASE pa.foreign_flg WHEN 1 THEN 'Y' ELSE 'N' END) END) AS char(1)) AS foreign_flg,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.country ELSE pa.country END AS country,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.cre_d ELSE pa.cre_d END AS cre_d,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.mod_d ELSE pa.mod_d END AS mod_d,
    CASE v.use_parent_addr_flg WHEN 0 THEN a.mod_by ELSE pa.mod_by END AS mod_by
    FROM dbo.om_vendor AS v LEFT OUTER JOIN
    dbo.om_address AS a ON a.vnd_id = v.vnd_id AND a.addr_c = 1 INNER JOIN
    dbo.om_owner AS o ON v.owr_id = o.owr_id INNER JOIN
    dbo.om_address AS pa INNER JOIN
    dbo.primary_vendors AS pv ON pa.vnd_id = pv.vnd_id ON o.part_id = pv.part_id
    WHERE (pa.addr_c = 1)




    WHICH TRANSLATES TO



    SELECT v.vnd_c ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.str_1
    ELSE pa.str_1
    END str_1 ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.str_2
    ELSE pa.str_2
    END str_2 ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.str_3
    ELSE pa.str_3
    END str_3 ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.city
    ELSE pa.city
    END city ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.STATE
    ELSE pa.STATE
    END STATE ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.zipcode
    ELSE pa.zipcode
    END zipcode ,
    (CASE v.use_parent_addr_flg
    WHEN 0 THEN (CASE a.foreign_flg
    WHEN 1 THEN 'Y'
    ELSE 'N'
    END)
    ELSE (CASE pa.foreign_flg
    WHEN 1 THEN 'Y'
    ELSE 'N'
    END)
    END) foreign_flg ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.country
    ELSE pa.country
    END country ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.cre_d
    ELSE pa.cre_d
    END cre_d ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.mod_d
    ELSE pa.mod_d
    END mod_d ,
    CASE v.use_parent_addr_flg
    WHEN 0 THEN a.mod_by
    ELSE pa.mod_by
    END mod_by

    FROM om_vendor v
    LEFT JOIN om_address a
    ON a.vnd_id = v.vnd_id
    AND a.addr_c = 1
    JOIN om_owner o
    ON v.owr_id = o.owr_id
    JOIN om_address pa
    JOIN primary_vendors pv
    ON pa.vnd_id = pv.vnd_id
    ON o.part_id = pv.part_id
    WHERE ( pa.addr_c = 1 );

    Edited by: user4714217 on Jan 24, 2013 1:43 PM
  • 3. Re: Error converting SQL Server DDL and views to Oracle
    666153 Newbie
    Currently Being Moderated
    The above code was cut from the admin tool on SQL Server, copied to the "scratch editor" and translated with no errors.

    I think the issue is that I cannot get the code using SQL Developer, not the translation itself. I copied a procedure over as well, and it translated succesfully also.

    I found the links you supplied but they did not answer my question.

    Edited by: user4714217 on Jan 24, 2013 1:41 PM
  • 4. Re: Error converting SQL Server DDL and views to Oracle
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    Well, perhaps the underlying issue has something to do with privileges or (at least) the way SQL Developer either uses or does not use them. Searching for "DDL not accessible" I found the following, but it seems some private emailing occurred so we don't know how that poster's issue got resolved:
    Re: SQL Server 2005 Migration Fails part of the way through

    Let's hope Dermot can get back to you this week.

    -Gary-
  • 5. Re: Error converting SQL Server DDL and views to Oracle
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi,

    Just to test the connections permissions.

    1) Open your SQL Server connection. Right Click on the database you want to migrate and choose "Select Default Database"
    2) Open a worksheet for your SQL Server connection.
    3) Run the following query (run statement, for the grid result)
    select o.name, c.encrypted,c.compressed,c.text from syscomments c,sysobjects o where c.id =o.id

    If your connection has enough privileges and the text is not compressed or encrypted, then you should see the procedure/view definitions in plain text in the result window.
    This should give us a clue as to the problem.

    Just two things that may help
    A) Use JTDS version 1.2 . This is an older version. But it is the version we use here and what we recommend.
    B) SQL Developer supports a second way of capturing your database. Offline Capture. If there is some problem with online (standard) capture, then this mechanism may help you capture your database.

    Regards,
    Dermot
    SQL Developer Team
  • 6. Re: Error converting SQL Server DDL and views to Oracle
    666153 Newbie
    Currently Being Moderated
    OK, I did as you proposed and I get no rows back. I logged into the admin screen on the host server and executed the same query and I did get rows back, and in clear text. It appears to not be my privilege but some interface with my ID and SQL Developer?

    I will prusue this, but do you have any suggestions?
  • 7. Re: Error converting SQL Server DDL and views to Oracle
    666153 Newbie
    Currently Being Moderated
    There is one difference. When I login using the SQL Server GUI, I am using domain based login. When I login using SQL Developer, I have to use password, so it appears they are not identical. I will investigate this further.

    Edited by: user4714217 on Jan 25, 2013 9:08 AM
  • 8. Re: Error converting SQL Server DDL and views to Oracle
    666153 Newbie
    Currently Being Moderated
    O...M...G

    It appears that my SQL Server DBA did nto set me up correctly or consistently. He created a new local login and it had no privilege. When I logged in directly, I was using a domain based login, and was a sysadmin. I have just verified this. I am going to run this again and see if it works. I anticipate it will.

    I had assumed my DBA knew what he was doing when he set me up! I'll let you know if I am OK.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points