Solved

Convert Sybase scripts to Oracle scripts

Posted on 2004-08-27
8
1,282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 11915445
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 35

Expert Comment

by:Mark Geerlings
ID: 11915541
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
ID: 11945615
@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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:dttai
ID: 11999803
@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
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 150 total points
ID: 11999840
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 35

Accepted Solution

by:
Mark Geerlings earned 150 total points
ID: 12000343
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
ID: 12000990
@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 35

Expert Comment

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

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

717 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