Oracle Training - 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. Also be sure to send us your questions and/or feedback.

Contents

• 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 tricks. 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 new features. --------- 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, so either correlate the inner query with the outer delete or change not exists to not in.  For example: DELETE FROM giant_prices WHERE NOT EXISTS (SELECT orderable_product FROM product_master_tbl   WHERE orderable_product=sku    AND giant_prices.<primary_key >= product_master_tbl.<foreign_key>) --------- 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: DECLARE     i PLS_INTEGER := 0; BEGIN   FOR sal_recs IN (SELECT * FROM purchase) LOOP       i := i + 1;       IF i >5 THEN  <do something> END IF;   END LOOP; END; 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 Oracle training schedule, or tell us 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  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 linked to individuals. View woopra privacy policy.    View our privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.