Re: procedures in dba studio

Glad to help.

The reason migrated SQL Server code is garbage is that the underlying architecture is different. In SQL Server you use lots of temporary tables. In Oracle, while it has them, they are hardly ever used. The locking is different. In Oracle reads and writes don't block so code written for SQL Server where they do is a workaround for what is unnecessary. And as SQL Server has no multiversioning and no SHARED POOL code written for that environment is generally highly serialized and does not take advantage of Oracle's strongest features. And, as you mention, the exception handling. But I doubt and migration tool will create custom exceptions, PRAGMA EXCEPTION_INIT, PRAGMA AUTONOUMOUS_TRANSACTION, or packages.

If you are happy with what you got ... great. But someone reworking it to take advantage of Oracle could probably improve performance by 500-1000% while greatly increasing scalability.

Daniel Morgan

Peter K wrote:

> Hello Daniel,
> yes Sql*Plus seems to be the better way. Thanks for your support.
>
> Why do you think that migrated (Migration workbench) code is usually garbage
> ?
>
> The most eye-catching (sorry for my english) thing for me has been
> code-blowing-up due to exeception handling.
> Ok, transaction logic must be proofed carefully and some constructs are not
> allowed in PL/SQL like selects in select lists.
> But on the whole I'm not unhappy with the migration (maybe my stored procs.
> are not too complex).
>
> Regards,
> Peter
>
> "Daniel A. Morgan" <damorgan_at_exesolutions.com> schrieb im Newsbeitrag
> news:3C444706.79BC1B6E_at_exesolutions.com...
> > Can't help you there as I don't like DBA Studio. But I can help you in two
> > other ways.
> >
> > 1. Log on in SQL*Plus and type the following at the SQL> command prompt:
> >
> > alter procedure <proc_name> compile;
> >
> > it will either compile successfully for indicate that errors exist. To
> view the
> > errors and their associated line numbers type:
> >
> > SHO ERR
> >
> > 2. SQL Server code migrated to PL/SQL is usually garbage. I strongly urge
> you
> > to use what you have as a starting point and rewrite it to take advantage
> of
> > (not sabotage) Oracle's shared pool.
> >
> > Daniel Morgan
> >
> >
> >
> >
> > Peter K wrote:
> >
> > > Hi,
> > > after autom. migration sqlserver -> oracle 8, i want to view and test
> some
> > > migrated stored procedures that have status invalid.
> > > i use dba-studio -> <db> -> schema -> <owner> -> procedures and i am
> > > connected as sysdba. On the right side i can see the source and buttons
> > > "compile" and "show errors". status is invalid but "show errors" is
> grayed.
> > > how can i modify, recompile and especially view errors ?
> > >
> > > regards
> > > peter
> >
Received on Thu Jan 17 2002 - 11:57:30 CST