Solved

Convert Sybase scripts to Oracle scripts

Posted on 2004-08-27
8
1,252 Views
Last Modified: 2011-10-03
Hello,
I've got problem converting a sybase scripts to oracle scripts. Here is the Sybase script:

if exists (select name from sysobjects
                where name = "parameter" and type = "U")
  begin
      print "Dropping table:  parameter - for replacement"
     drop table parameter
  end
go

How shoud it be converted to Oracle scripts? The difficulties include:
1) PL/SQL does not allow DROP TABLE
2) I dont know the equivalences for sysobjects, type in Oracle
Would appreciate very much if you can provide the complete conversion for the above script.
Thanks,
Do
0
Comment
Question by:dttai
  • 3
  • 3
  • 2
8 Comments
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
1) PL/SQL does not allow DROP TABLE

use "execute immediate 'drop table <table_name>" instead.
2) I dont know the equivalences for sysobjects, type in Oracle

sysobjects===dba_objects

type===object_type

what's U for in your script?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Beyond figuring out exactly how to do thing in Oracle just as they are done in SQL Server, the bigger question is: are you sure that you want to do things in Oracle the same way they are done in SQL Server?  Some things that work well in one of these databases may not work the same way, or as well in the other.  Droppoing and/or creating objects via stored procedures is just one of these.  Some others are: how nulls are handled; how dates are handled; how record-locking is handled; how temporary tables are used; etc.

If you are converting an application from SQL Server to Oracle, you may have more issues than just how to convert some scripts.  You may want to re-write some scripts for performance and/or functionality reasons, since the simplest translation may not give you the best performance or the same results.
0
 

Author Comment

by:dttai
Comment Utility
@seazodiac: the U is for user.
@markgeer: thank you for the thoughtful comments. In fact, I've tried to search around for migration applications. I found some of them such as Oracle Migration Workbench, SwissSQL from AdvenNet, SQLWay from Ispirer. It seemed that OMW and SQLWay can convert a Sybase db to an Oracle db, even though I am not sure about their accuracy. SwissSQL can convert Sybase scripts to Oracle scripts. Again, SwissSQL's results look suspected too. I hence decided that I will try manually do the migration.
To give you all the overall picture of what I am dealing it, I'll give more details of the problem in this paragraph. We have an application currently running on Sybase. The Sybase is used for a number of purposes: 1) it has a database X_db to store parameter setting of the application; 2) it has a Y_db to store the query results; and 3) it has a Z_db to store the index of the raw files. We now wish to migrate the app. to Oracle. In Sybase, before starting up the application, we run a number of Sybase scripts to create necessary tables for X_db, Y_db and Z_db. The scripts also populate some of the predefined settings in X_db. All three dbs are also updated when app. is running. My approach is to convert these Sybase scripts to Oracle scripts to create similar tables in three dbs.
I do agree with markgeer that this task will go beyon simply converting some scripts. However, for now I just have to stick with my chosen approach for a while, see how things turn out. And one more thing, performance is not my worry for the time being, getting the system run correctly is the first priority.
I expect that I will have more questions for you folks in the next few days. I double the points, and will award them among people, who provide educational answers or lead me to solve a specific question.
Thanks all,
Do
0
 

Author Comment

by:dttai
Comment Utility
@seazodiac:
Could you provide the entire Oracle scripts for the above Sybase scripts. I guess if you dont want to provide a word-by-word translation for the above script, then please tell me how to:
1) check if a table "parameter" exists in my current database
2) if the table exists, drop that table
Thanks,
Do
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 150 total points
Comment Utility
ok, here is a try:

set serveroutput on size 1000000
declare
isFound number;
begin
select count(*) into isFound from all_tables where table_name='PARAMETER';
if isFound <>0 then
 execute immediate 'drop table PARAMETER';
end if
end;
/
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 150 total points
Comment Utility
That "drop table" example will only work in Oracle if the table is owned by (in the schema of) the user who is running the procedure.  If you only have one schema you are concerned about, then this should work.  If you need to check multiple schemas, you will need to run this from a DBA account and use a cursor loop instead and select the table owner, then: 'drop table [owner].PARAMETER';

Here is an example:
create or replace procedure drop_table (v_table in varchar2) as
  cursor c1 is select owner from all_tables
    where table_name = v_table;
  v_owner varchar2(30);
begin
  open c1;
  loop
    fetch c1 into v_owner;
    exit when c1%notfound;
    execute immediate 'drop table '||v_owner||'.'||v_table;
  end loop;
  close c1;
end;
/

To drop any/all tables named: PARAMETER, call it like this:
exec drop_table('PARAMETER');

Be aware that this is a very dangerous type of procedure!  If it is every used maliciously, or a wrong value is entered, the results could be disastrous!

I do not recommend dropping tables via PL\SQL procedures.
0
 

Author Comment

by:dttai
Comment Utility
@seazodiac and markgeer: I'll try your suggested scripts. Thanks for your fast responses.
@markgeer: " I do not recommend dropping tables via PL\SQL procedures." So you are suggesting to drop tables only by running at the command line of SQL*Plus?
Thanks,
Do
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Yes.  Keep in mind that in applications developed for Oracle, it is extremely rare to drop tables in a production database.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now