| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Converting SQL Server Stored Procedures to Oracle using Migration Workbench
A copy of this was sent to smb_slb_at_my-dejanews.com
(if that email address didn't require changing)
On Tue, 18 May 1999 22:20:52 GMT, you wrote:
>In article <7hpvah$t6$1_at_nnrp1.deja.com>,
> david617_at_my-dejanews.com wrote:
>> 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?
>>
>
>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:
>
stored procedures in Oracle8i, release 8.1 may be written in Java, PL/SQL, or C. Java is just another language in the database.
>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.
>
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_transaction2 on commit delete rows
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;
>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.
>
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.
>3. Errors are handled differently. Oracle uses throw & catch logic
>like C++, as opposed to checking @@error value. An automated migration
>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.---
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 - 06:53:23 CDT
 |
 |