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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 19 May 1999 15:29:22 GMT
Message-ID: <3746d7c9.16339695_at_newshost.us.oracle.com>

A copy of this was sent to smb_slb_at_my-dejanews.com (if that email address didn't require changing) On Wed, 19 May 1999 14:13:11 GMT, you wrote:

>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.

that one is easy to resolve actually:

roles are never enabled during the execution of a procedure. In Oracle8i, release 8.1 using Invokers rights roles may be enabled but when using definers rights or in v8.0 and before roles are never enabled.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

also, in Oracle8i, release 8.1, using dynamic tables is trivial in plsql, for example:

SQL> declare

  2          type rc is ref cursor;
  3          c1       rc;
  4          rnum number;

  5
  6 begin
  7 execute immediate 'create table t ( x int )';   8
  9 execute immediate 'insert into t select rownum from all_users';  10
 11 execute immediate 'update t set x = x+5';  12

 13          open c1 for 'select * from t';
 14          loop
 15                  fetch c1 into rnum;
 16                  exit when c1%notfound;
 17                  dbms_output.put_line( rnum );
 18          end loop;
 19          close c1;
 20  
 21          execute immediate 'drop table t';

 22 end;
 23 /

6
7
...
40
41

PL/SQL procedure successfully completed.

Also -- there are temp tables in 8.1 as well.

>
>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.---

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Wed May 19 1999 - 17:29:22 CEST