Very Computer

My company has created a client-server application which makes
extensive use of Transact-SQL stored procedures in Microsoft SQL Server
7.0. We are looking to port the server application to Oracle using the
Oracle Migration Workbench. This would be implemented through Java
stored procedures.

Does anyone have experience in migrating from SQL Server to Oracle with
this tool,  especially in regards to stored procedures? What
difficulties have you experienced? Are there any types of client-side
code that had to be modified to accomodate these changes? How about
performance issues on Oracle versus SQL Server on the same NT box?

Thanks in advance for your answers,

David Lawrence
Database Architect
Incentive Systems, Inc.
Burlington, MA

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

I haven't used the Oracle Migration Workbench, and I don't know what
you mean by "Java stored procedures" in Oracle.  However, I've done a
fair amount of work in moving from Sybase/MS SQL Server to Oracle, and
here are the big stumbling blocks you're going to hit:

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.

2.  Stored procedures in Oracle cannot return result sets.  Any
procedures you use to return sets of data will probably have to be
re-thought.  Oracle passes all data into & and out of it through its
parameters.  Your client code will have to be changed to get procedure
output from the parameters.

3.  Errors are handled differently.  Oracle uses throw & catch logic

utility may be able to handle this issue adequately.

4.  (Not a stored procedure issue) Oracle triggers are for the most
part more versatile than Sybase/SQL Server triggers, since they have
both statement level (Sybase-style) and row-level triggers.  However,
the statement level triggers in Oracle have no concept of the
"inserted" and "deleted" tables - they're just "dumb" triggers that
fire with no information about the nature of the action.  Standard
operating procedure in Oracle is to put all your interesting logic in
the row-level triggers.  However, the problem with row-level triggers
is that the cannot affect other data in the same table, only the row in
question.  This means that any triggers that do things like updating
sibling rows, or inserting backup/audit rows into the same table will
not work.  The best way to get around this problem is to change these
triggers to stored procedures & alter the dependent code accordingly.

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

Hi,

If performance is an issue, I wouldn't use Java stored procedures. I've
heard (from reliable sources) that they are much slower than PL/SQL stored
procs.

Hope this helps,

Steve

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 ?

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

A copy of this was sent to smb_...@my-dejanews.com
(if that email address didn't require changing)

stored procedures in Oracle8i, release 8.1 may be written in Java, PL/SQL, or C.
Java is just another language in the database.

In Oracle8i, release 8.1 there are session based temporary tables (hold data for
the life of a session, when you disconnect -- your data is gone) and transaction
based temporary tables (when you commit the data is gone)

For example:

they are similar to temp tables in those databases the main exception being that
they are 'statically' defined.  You create them once per database, not once per
stored procedure in the database.  They always exist but appear empty until you
put data in them.  They may be SESSION based (data survives a commit but not a
disconnect/reconnect).  They may be TRANSACTION based (data disappears after a
commit).  Here is an example showing the behaviour of both.  I used the
scott.emp table as a template:

SQL> create global temporary table temp_table_session
  2  on commit preserve rows
  3  as
  4  select * from scott.emp where 1=0
  5  /
Table created.

the ON COMMIT PRESERVE ROWS makes this a session based temporary table.  rows
will stay in this table until a logoff.  Only I can see them though, no other
session will ever see 'my' rows even after I commit

SQL>
SQL>
SQL> create global temporary table temp_table_transaction
  2  on commit delete rows
  3  as
  4  select * from scott.emp where 1=0
  5  /
Table created.

the ON COMMIT DELETE ROWS makes this a transaction based temp table.  when you
commit -- the rows disappear.

SQL> insert into temp_table_session select * from scott.emp;
14 rows created.

SQL> insert into temp_table_transaction select * from temp_table_session;
14 rows created.

we've just put 14 rows into each temp table and this shows we can 'see' them:

SQL> select count(*) from temp_table_session
  2  /

  COUNT(*)
----------
        14

SQL> select count(*) from temp_table_transaction
  2  /

  COUNT(*)
----------
        14

SQL> commit;
Commit complete.

since we've committed, we'll see the session based rows but not the transaction
based rows:

SQL>
SQL> select count(*) from temp_table_session
  2  /

  COUNT(*)
----------
        14

SQL> select count(*) from temp_table_transaction
  2  /

  COUNT(*)
----------
         0

SQL>

SQL> connect tkyte/tkyte
Connected.
SQL>

since we've started a new session, we'll see no rows now:

SQL>
SQL> select count(*) from temp_table_session
  2  /

  COUNT(*)
----------
         0

SQL> select count(*) from temp_table_transaction
  2  /

  COUNT(*)
----------
         0

SQL>

If you really need the temp table to be created in the procedure itself,
Oracle8i release 8.1 makes this much easier to do as well.  Consider the
following example which uses plsql to create, insert into, fetch from and drop a
temporary table -- whose name is not known until run time.  Its almost as easy
as static sql is:

SQL> declare
  2      type mycur is ref cursor;
  3  
  4      l_tname     varchar2(30) default 'temp_table_' || userenv('sessionid');
  5      l_cursor    mycur;
  6      l_ename     scott.emp.ename%type;
  7  begin
  8      execute immediate 'create global temporary table ' ||
  9                         l_tname || ' on commit delete rows
 10                         as
 11                         select * from scott.emp where 1=0 ';
 12  
 13      execute immediate 'insert into ' || l_tname ||
 14                        ' select * from scott.emp';
 15  
 16      open l_cursor for
 17          'select ename from ' || l_tname || ' order by ename';
 18  
 19      loop
 20          fetch l_cursor into l_ename;
 21          exit when l_cursor%notfound;
 22          dbms_output.put_line( l_ename );
 23      end loop;
 24  
 25      close l_cursor;
 26      execute immediate 'drop table ' || l_tname;
 27  end;
 28  /
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

PL/SQL procedure successfully completed.

Since version 7.2 of the database, Oracle has the ability to return result sets.
See:

http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=465388583&fmt=text

for a description of how to do that in pro*c, java, and odbc.

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

Thomas Kyte
tk...@us.oracle.com
Oracle Service Industries
Reston, VA   USA

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

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.

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

(if that email address didn't require changing)

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.

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

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

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