Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert Sybase scripts to Oracle scripts

Posted on 2004-08-27
8
Medium Priority
?
1,306 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

610 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