Re: Converting SQL Server Stored Procedures to Oracle using Migration Workbench

Yeah, you can use this package, but the problem with it is that any tables you create using dbms_sql can only be accessed from statements also using dbms_sql, since the table in question is not visible to the compiler. It can get ugly. Also, dbms_sql statements are not verified, so you can run into run-time errors. I use this package only as a last resort, or to do some tricky dynamic SQL where I want to construct a custom statement. Also, I've run into some strange problems in Oracle 7.3.4 having to do with permissions: some actions which should be allowed are not allowed. For example, my user is able to drop & create public synonyms, but for some reason calling "drop public synonym fu" using dbms_sql produced a permissions error. This was never resolved by Oracle tech support.

In article <7ht4vj$ekp$1_at_front4m.grolier.fr>,   "Arlette BROSSARD" <abray_at_club-internet.fr> wrote:
> With DBMS_SQL (Dynamic SQL) you can create and drop tables on the fly
in
> stored procedures.
> I use it to create tablespaces, users, tables, indexes ... all DDLs !
> (DBMS_SQL.OPEN_CURSOR, DBMS_SQL.PARSE and DBMS_SQL.CLOSE_CURSOR).
> I'm not sure it's a good way to replace temporary tables (I use
permanent
> tables with a SessionD column)
> but perhaps it can be a useful to translate SQL Server stored
procedures ?
>
> >1. There are no temp tables in Oracle like you have in SQL Server.
> >Furthermore, you can't create & drop tables on the fly in your
stored
> >procedures either, so you'll have to change any logic which uses
temp
> >tables to logic which uses a permanent table with a "SessionId"
> >column that separates the data used by other connections.
> >
> >
> >--== Sent via Deja.com http://www.deja.com/ ==--
> >---Share what you know. Learn what you don't.---
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 19 1999 - 09:13:11 CDT