Oracle Consulting and Training
For Better, Faster, Smarter
Make Your Oracle Systems Your Most Valuable Asset
Subscribe to our ezine and every month we'll send you the tips and tricks you need to make your Oracle
systems better, faster and smarter and make sure you send us your questions and feedback.
Questions about Oracle 10g
How to use NOT EXISTS
Use of rownum greater than
See the resources page for book recommendations as well as links to the Oracle Technology Network and
other useful site or see here for more Oracle tips and
From: Heba el Leithy - I am studying computer
science at the American University in Cairo. I am
taking a database course and I am intreseted to
know more about the new release of oracle 10g. I
would like to know what is meant by grid
techonology as well as what are the new feautures
provided by this oracle 10g. My last question is that
who are the users of 10g or who will be most
benefited by 10g.
These are all very good questions which is why we
answered them in our series of articles on the Oracle 10g
From: Hegde, Santhosh - I am using a delete query using “NOT EXISTS” clause. The query is:
delete from giant_prices where not exists ( select orderable_product
from product_master_tbl , giant_prices where orderable_product=sku )
But this will not delete even single row even there are rows in giant_prices table having sku not
exists in product_master_tbl.orderable_product. Please advice me why this query is not working.
NOT EXISTS is used for correlated sub queries (see the Oracle and SQL tutorials on our web site). For
this to work the inner query has to be correlated with the outer query. In this case there is no correlation
between the inner and outer queries, to make the query work you would have to correlate the inner query
with the outer delete or change not exists to not in.
DELETE FROM giant_prices WHERE NOT EXISTS
( SELECT orderable_product FROM product_master_tbl
From: Sanu Sanjel - how can I use the sql command for the greater than a row number like
select * from purchase where rownum>5?
The short answer is you can’t! Rownum is a pseudo-column and is generated as rows are returned by the
query (starting at 1) so the where clause can only filter rows up to the limit.
To only keep data from the 5th row onwards you would have to do it programmatically using PL/SQL or
another programming language, for example:
i PLS_INTEGER := 0;
FOR sal_recs IN (SELECT * FROM purchase) LOOP
i := i + 1;
IF i >5 THEN <do whatever is needed> END IF;
Note that without an ORDER BY clause you are not guaranteed to get the rows in the same order every
time or indeed in any particular order. You could also fetch the data into a PL/SQL collection using the
BULK COLLECT INTO clause and then just ignore the first 5 rows of the PL/SQL table.
Looking to sky-rocket productivity, save time and reduce costs?
Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-
led training in Oracle and related technologies on or off site in cities across the UK as well as self-study
online training. See our scheduled Oracle training courses, or let us know your requirements.
Oracle tips and tricks
Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make
your systems better, faster and smarter.
Smartsoft Computing Ltd, Bristol, England
Tel: 0845 003 1320
Need help? Contact Us
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.