This discussion is archived
4 Replies Latest reply: Nov 6, 2008 4:46 AM by 428684 RSS

SQL Server migration object renamin

428684 Newbie
Currently Being Moderated
The situation I face is the migration of a database from SQL Server to 10gR2. The obstacle I'm trying to deal with is that many of the 900+ SQL Server stored procedure names exceed the Oracle 30-character limit. Many exceed it by 10-15 characters, and the last several characters are the part of the name that uniquely identify it and differentiate it from other procedures with similar names. If I perform the standard migration, many of the new names get shortened and have some arbitrary ending such that I can't tell which new migrated procedure corresponds to a particular source procedure.

Renaming the procedures in the SQL Server would be ideal, but MS indicates that it's a bad idea (nice, huh?) and I don't have sufficient privs on that schema to do it anyway, so that attractive option is unfortunately unavailable.

With the large number of procedures involved, I've tried automating the renaming process by using a java application (my own) to edit the names in the captured schema in the migration repository prior to generating the Oracle model. I process the MD_STORED_PROGRAMS table to change the NAME column and edit the CLOB in the NATIVE_SQL column, replacing the old names with the new ones. Unfortunately, when I then try to generate the Oracle model from this "corrected" schema, I get errors of the sort "Error creating procedure <proc_name>: unexpected end of subtree Line 0 column 0" for each proc whose data I altered. It seems that it's looking somewhere else and finding the original name and throwing an error when it finds it doesn't match the new name I gave it in the automated renaming process.

I'm hoping someone else knows what other table(s) I need to edit to correct the names, or of a different/better way to accomplish this task.

Thanks!
  • 1. Re: SQL Server migration object renamin
    wkobargs Journeyer
    Currently Being Moderated
    Hi,

    that looks like an "interesting" problem. I will try that out. I hope that I can find a solution or workaround for that problem.

    I'll come back to you as soon as possible.

    Best regards
    Wolfgang
  • 2. Re: SQL Server migration object renamin
    wkobargs Journeyer
    Currently Being Moderated
    Hi again,

    I created two stored procedures in my SQL Server database with names longer than 30 characters that were identical in the first 30 characters.

    I captured them and converted the model and saw how their names were changed to make them unique within the first 30 characters.

    I found the names in MD_STORED_PROGRAMS.
    It seems that it's looking somewhere else and finding the original name and throwing an error when it finds it doesn't match the new name I gave it in the automated renaming process.
    Yes. Have a look at columns ORIGINAL_IDENTIFIER and NEW_IDENTIFIER in the table MD_DERIVATIVES.

    You might want to try out how it works if your Java renaming program also manipulates the table MD_DERIVATIVES. I didn't yet try that out.

    Best regards
    Wolfgang
  • 3. Re: SQL Server migration object renamin
    428684 Newbie
    Currently Being Moderated
    Wolfgang,

    <em>Wunderbar! </em>That looks like the "secret hiding spot" I've been looking for<em>.</em> I'm modifying my Java classes to include a mod to that table, and I'll keep you posted as to the results.

    Thanks for the help and the fast response!

    Dave
  • 4. Re: SQL Server migration object renamin
    428684 Newbie
    Currently Being Moderated
    Wolfgang,

    Thanks for getting me pointed in the right direction. I've now created a Java application that edits the CLOB in the MD_STORED_PROGRAMS table after being converted to the Oracle model from the captured schema. I'm changing procedure names, inserting/correcting copyright notices, fixing missing references to tables in other schema, and a few other necessary steps that SQL Developer doesn't handle. I'm able to automate what would have been a horribly tedious process in 900+ stored procs.

    Thanks for the help!

    Dave

Legend

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