Oracle Consulting and Training
For Better, Faster, Smarter
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.
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 scheduled Oracle training 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
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.
Learn how to develop your Oracle systems better, faster and smarter with high quality Oracle training from our top
consultants . See the Oracle training page for more info or click here for details of our scheduled Oracle courses .