Oracle Tips and Tricks To Make Your

Oracle Systems Better, Faster & Smarter

We aim to provide the best on-line Oracle training and here we answer a few of the questions which have been sent in. Use the contact form to send us your questions and feedback, or to subscribe just click here. The resources page has recommendations for further reading to enhance your knowledge and contains links to information on OTN and other places or see here for more Oracle tips and tricks. We also offer a variety of training courses for developers and dbas both on site or off site.

Contents

• PL/SQL procedures vs. functions • how to optimize joins • restoring a database from the backup From: niti joshi - In Oracle, How can I make a Procedure work like a Function? Perhaps I'm missing something, here, but the short answer is "You can't!".  A function must return a value. Trying to exit a function without providing a return value causes an exception "ORA-6503: PL/SQL: Function returned without value". As a function always returns a value, the function call can be used wherever an expression can be used, which means that it must be part of an executable PL/SQL statement or a SQL statement. eg.  declare x pls_integer; begin x :=my_func(x); end; or SELECT my_func(col1) FROM my_table; Procedures, on the other hand, are not obliged to return a value (via a parameter), so a procedure call can't be used in an expression in SQL or PL/SQL or be part of a PL/SQL statement (the procedure call has to be a statement in itself), eg. declare x pls_integer; begin my_proc(x,y,z);  end; --------- From: Waqas Chaudhry - Today I got chance to read your article about optimizing joins which was quite informative for me. Actually we have one query that we need to run quite often in our database and it takes up all the resources of our system. I am sending you that query. I shall be very thankful to you, if you can help me in optimizing joins used in that query. There are 13 tables we are accessing in that query. I am also sending you info about number of rows in that table.   The first thing that strikes us about this query is its sheer complexity which makes it difficult to read and therefore difficult to understand. If Faced with a query like this, my instinct would be to break it up into views and/or PL/SQL functions. Both of these techniques would enable you to hide some of the complexity of this query, however using PL/SQL functions would enable you to have full control over the way the query operates and in Oracle 9i and above you can make a function look like a table by making use of the PIPELINED facility. This means the function can return rows of data rather than single elements and thus be queried exactly the same as if it were a table.  The next thing that struck us was the number of sums being performed in the query. One way to avoid this is to create summary/intermediate tables which can be updated as required. This would avoid the need to calculate the sums every time the query is run. If you're using Oracle8i or above, materialized views do the same thing. They also have the advantage that you can specify how often and when these materialised views are refreshed and the refresh will be performed automatically by the database. --------- From: Raj Masih (Programmer), Christian Medical College & Hospital, Ludhiana (India). - Sir, recently we've upgraded our network from foxpro to VB n Oracle n we're not much fluent in Oracle. So, I would like to ask that how do we restore the backup data files in Oracle 9i in Unix Platform n DOS as well. Thanx. No mention has been made as to what sort of backup was taken, so let’s assume you made a full cold backup, which means that the database was shut down normally (that is by one of the commands "shutdown normal", "shutdown immediate" or "shutdown transanctional" and not due to an instance failure) before the files were backed up. If this is the case, then as long as the backup includes all the data files, all the control files, all the redo logs and init.ora/spfile.ora (the parameter file) then you will be able to restore the database by copying the files back to their original locations (which should be accomplished automatically by your backup software). Then you can restart the database by running sql*plus (or svrmgr if you're using Oracle 7 or 8), connecting to the target database, and entering "startup". After a short delay the database will be ready for use. For more Oracle help, see the many free articles and tutorials we have on our site. Start from http://www.smart-soft.co.uk/Oracle/oracle-tips-and- tricks.htm and just follow the links.  --------------------------------------- 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.