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!!!!
- SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name
- 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')
- 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)
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.
By kylemcna in forum ASP.NET
Last Post: 04-13-2007, 03:23 AM
By Xion in forum Database
Last Post: 02-06-2002, 12:07 PM
By Xzhang in forum Database
Last Post: 12-06-2001, 07:11 AM
By bigbastard4 in forum Database
Last Post: 05-16-2001, 07:24 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL