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

Create Custom ScreenTips for Shapes (MSOffice-word-2007)

r-onlineinformation.blogspot.com Create Custom ScreenTips for Shapes Shape ScreenTips in Microsoft Office Visio are the boxed text that appears when you point to a shape. Some Visio shapes have built-in ScreenTips—usually the name of the shape (such as the name of a workflow step). However, you can edit existing ScreenTip text or create new ScreenTip text for shapes that don’t have a built-in ScreenTip. To create or edit a ScreenTip: 1. Select the shape. 2. On the Insert menu, click either Shape ScreenTip or Edit Shape ScreenTip, as applicable. 3. In the Shape ScreenTip dialog box, type or edit the ScreenTip text. 4. Click OK.
1 comment
Read more

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