Help me to convert SQL queries to Oracle!!!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Help me to convert SQL queries to Oracle!!!

  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Question Help me to convert SQL queries to Oracle!!!

    HI, I a a student and as a part of my academic project I had to change some queres from SQL to Oracle but I couldn't convert these queries beacuse they use some system tables in SQL that I don't know the equivalant Oracle tables. Following are SQL Queries would you pliz some one helppppp meeee!!!!
    1. SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name

    1. SELECT tS.name FROM sysobjects AS tS WHERE (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'U') AND xtype ='U') OR (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'V') AND xtype ='V')

    1. SELECT o.name as TableName, c.name as FieldName, c.colid as Field_Ordinal, t.name as FieldType, c.length as FieldLength, c.prec as FieldPrecision, c.scale as FieldScale, c.isnullable, c.iscomputed, CASE WHEN c.status & 0x80 > 0 THEN 1 ELSE 0 END AS isidentity, columnproperty(o.id, c.name, 'IsRowGuidCol') as isrowguidcol FROM (sysobjects o JOIN syscolumns c ON o.id = c.id) JOIN systypes t On c.xtype = t.xtype WHERE o.xtype IN ('U', 'V') AND (t.xtype = t.xusertype)

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,262
    You will find that most people perfer not to do someone's homework for them. Where I would start is by finding out what info the system tables are providing and how it is used in the query. Next step would be to see if Oracle has similar system tables or perhaps a function that can return similar info. Then rework the criteria to produce a similar result as what the criteria against the system tables does. The rest of the conversion would be the same at what you have already done in converting your other queries.
    Usualy system tables are there to provide structural information about the database, like table names, field names and their data types. Things like this so if you find out what type of info, like table names, or field names, etc... Are being returned when you filter the records by xtype being only 'U' or 'V' then you can find out how you get the same info from an Oracle database. Then change the query to get the same info using Oracle's method and then you can start appliing the other critera to procude the same results.

Similar Threads

  1. setting up asp.net server
    By kylemcna in forum ASP.NET
    Replies: 5
    Last Post: 04-13-2007, 02:23 AM
  2. Replies: 0
    Last Post: 02-06-2002, 11:07 AM
  3. Convert Oracle PL/SQL to SQL Server
    By Xzhang in forum Database
    Replies: 1
    Last Post: 12-06-2001, 06:11 AM
  4. SQL Tutorial (Answer Q's & post your reply)
    By bigbastard4 in forum Database
    Replies: 2
    Last Post: 05-16-2001, 06:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center