Skip to main content

Tips for running queries on Oracle

r-onlineinformation.blogspot.com

Tips for running queries on Oracle


We encourage the use of PostgreSQL for the labs, to expose students to an excellent open source database.  However, if your syllabus forces you to use Oracle (and all participants at your remote centre are also from universities where they are forced to use Oracle), you can do assignments on Oracle.  Our model solutions will often give syntax errors on Oracle due to non-support for some SQL features in Oracle.  Below are some tips on how you can rewrite the queries to work on Oracle.
Oracle SQL Tips: non-standard features, and limitations
The following issues are for Oracle 10, and may or may not apply to later versions of Oracle.

  1. Oracle does not support the as clause, except as part of the "with" statement. In all other cases, just use the same syntax without the "as" keyword, and things should work.
  2. To use a single quote in a string, use two consecutive single quotes, e.g. 'D''Souza', the usual backslash escape (e.g. 'D'Souza') does not work.
  3. Oracle supports the with clause, but there are some syntax differences: the query defining a temporary view should be enclosed in parenthesis, and if there are multiple views, separate them by commas, instead of repeating the with keyword. Also column renaming is not supported, you can only give a name for the temporary view. The following example illustrates the Oracle with clause syntax:

    with foo as (select * from bar),
                baz as (select * from zap, zit where P)
    select *
    from foo, baz
    where P
  4. The with clause cannot be used in update queries
  5. Oracle does not support tuple comparisons; thus, (r.A,r.B) < (s.A, s.B) will not work in Oracle; instead you have to use ((r.A < s.A) OR (r.A=s.A and r.B < s.B))
  6. Oracle does not allow giving an alias to (renaming) a join expression such as (r natural join s), using FROM (r natural join s) T does not work. You can use from (select * from r natural join s) T, or even define T using a with clause.
  7. If you use the expression "r join s using (A, B)", you cannot refer to r.A or r.B, you have to refer to them as just A and B; however, if you have another common attribute C, you can use r.C and s.C. For the case of "r natural join s", all attributes can be accessed using only their name, you cannot prefix them with "r." or "s."
  8. Oracle allows correlation variables (table aliases) to be used in an immediate subquery, but not in a subquery of a subquery. This caused a lot of grief in some queries, where the obvious formulation required the variable to be used two levels down. 
  9. Instead of the keyword lateral, use the keyword table
  10. Oracle supports a weird non-standard SQL syntax which you should NOT use:
    select max(count(*)) from student group by dept_name
reference: iitb.ac.in/moodle
 
 

Comments

Post a Comment

Popular posts from this blog

Peer to Peer (P2P) Search Engine

r-onlineinformation.blogspot.com World Wide Web (WWW) is emerging as a source of online information at a very faster rate.It’ s content is considerably more diverse and certainly much larger than what is commonly understood. Information content in WWW is growing at a rate of 200% annually. The sheer volume of information available makes searching for specific information quite a daunting task. Search engines are efficient tools used for finding relevant information in the rapidly growing and highly dynamic web. There are quite a number of search engines available today. Every search engine consists of three major components: crawler, indexed repository and search software. The web crawler fetches web pages (documents) in a recursive manner according to a predefined importance metric for web pages. Some example metrics are back link count of the page, forward link count, location, page rank etc. The Indexer parses these pages to build an inverted index that is then used by the search s ...
Post a Comment
Read more

Book Your Tata Nano Online

r-onlineinformation.blogspot.com Booking starts today and will close on April 25 If you are one of those many waiting to book the Tata Nano, your wait is over. You can now book the world's cheapest car by logging on to tatanano.com. The booking starts today and will last up to April 25. The website also gives you details on the price list and the booking locator. The online booking form will cost you Rs. 200, while the forms available at Titan and Tata Indicom stores will cost you Rs. 300. These forms can be submitted at Tata Motors dealerships, SBI, Croma and Westside outlets. However, the payment for the application form and booking amount cannot be paid by credit or debit cards but has to be done via netbanking from banks mentioned on the website. The website also answers FAQs like 'Will Any preference be given to the Tata Group or Tata Motors employees?' and 'How do I make the payment of booking amount in case of Online Form Submission?' So go ahead and book...
Post a Comment
Read more

Prevent Conficker on your Windows machine

To prevent Conficker virus from infecting your Windows computer, you first need to disable the AutoRun feature. This can be done by downloading and installing certain updates manually for Windows XP or Windows 2000 . Windows Vista users can skip this step as this is taken care of via automatic updates . Now click Start , Run and enter gpedit.msc and click OK . Under Computer Configuration , expand Administrative Templates . Now for Windows XP, click System and for Windows Vista, click Windows Components then Autoplay Policies . In the Settings pane, right-click Turn off Autoplay , and then click Properties . Click Enabled , and then select All drives in the Turn off Autoplay box to disable Autorun on all drives. Click OK and restart the computer.
Post a Comment
Read more