SQL and PL/SQL FAQ
This is the official SQL and PL/SQL FAQ Thread. It is a locked thread. I will update it as I see commonly asked questions.
Re: 1. Where can I find Oracle Documentation?
All oracle documentation is located on the following website:
(just select your version and then search)
New to Oracle SQL and PL/SQL?, which Documentation to read first?
Start with the "Concepts" manual.
"Application Developer's Guide - Fundamentals"
"PL/SQL User's Guide and Reference"
as appropriate for your interest.
Firstly, have you checked the documentation or done a search on the internet? Members don't appreciate people asking questions that are easily found just by looking in the documentation. Frequently asked questions can be found in the below posts.
1) New Thread
Click the "Post New Thread" link. (Note: do not post your new question in answer to someone elses thread; that's called "hijacking")
2) Thread Subject line
Give your thread a meaningful subject, not just "help please", "Query help" or "SQL". This is the SQL and PL/SQL forum. We know your question is going to be about those things, make it meaningful to the type of question so that people with the right sort of knowledge can pick it up and those without can ignore it. Never, EVER, mark your subject as "URGENT" or "ASAP". This forum is manned by volunteers giving their own time to help and your question is never urgent or more important than their own work or than other people's questions. It may be urgent to you, but that's not forum members issue. By marking your question as urgent you are actually less likely to get a good response, as members will choose to ignore such questions or respond infavorably.
3) SMS/Txt Spk
Avoid using "SMS spk" or "txt spk" in your posts. This is a professional forum and the primary language is English, though this is not everyone's native language. Using txt spk makes it hard for people to read and understand your requirements. Spelling mistakes, errors and language differences accepted. Abbreviations are ok, in moderation.
4) Ask nicely
Typing your subject line or your post IN CAPITALS is considered shouting. It's considered rude to SHOUT at people, so please use mixed case unless you are emphasising a word for a particular reason. Consider the internet's rules of netiquette (http://www.ietf.org/rfc/rfc1855.txt) in your posts.
5) Database Version
Ensure you provide your database version number e.g. 18.104.22.168 so that we know what features we can use when answering.
If you're not sure what it is you can do the following:
select * from v$version;
in an SQL*Plus session and paste the results.
Provide us with table structures and indexes where necessary. Use the DESC command in SQL*Plus or your CREATE statements if you have them.
7) Sample Data
Provide us with sample data to help recreate the issue
You can do this using subquery factoring (a "WITH" statement) e.g.
with t as (select 1 as id, to_date('01/01/2009','DD/MM/YYYY') as date_created, 50 as val from dual union all
select 2, to_date('02/02/2009','DD/MM/YYYY'), 25 from dual union all
select 3, to_date('03/02/2009','DD/MM/YYYY'), 30 from dual)
,x as (select 1 as id, 'Fred' as name from dual union all
select 2, 'Bob' from dual union all
select 3, 'Tim' from dual)
or by providing create table and insert statements e.g.
create table t (id number,
create table x (id number,
insert into t values (1, to_date('01/01/2009','DD/MM/YYYY'), 50);
insert into t values (2, to_date('02/02/2009','DD/MM/YYYY'), 25);
insert into t values (3, to_date('03/02/2009','DD/MM/YYYY'), 30);
insert into x values (1, 'Fred');
insert into x values (2, 'Bob');
insert into x values (3, 'Tim');
8) Expected Output
Provide us with an example of the expected output. You can show this in a table, as with the sample data, or just as formatted output.
9) Formatting code and data
Ensure you format your code and data so that it's readable on the forum.
On the new forum, this is now achieved using the Syntax Highlighting option, by copy/pasting your code to the editor, highlighting it, then clicking the "insert" (>>) button, selecting "Syntax Highlighting >" and choosing SQL (or the appropriate language). Your code should then appear formatted and highlighted. (although day 1 this still appears buggy)
DECLARE v_str VARCHAR2(100); BEGIN v_str := 'This is some sample code'; END;
The other option at the minute seems to be pasting your code/data and ensuring you change it to Courier New font, so that the indentation stays correctly.
10) Error Messages
If your code is producing an error, copy and paste the error message for everyone to see. Don't just say "it doesn't work" as that means nothing. Also try and include the full message, not just the ORA-XXXXX code. There are some common codes people may know immediately, but most people have not memorised the meaning of the hundreds of possible ORA codes that can be produced.
11). Post Answered Etiquette
It's good etiquette, when your question has been answered to mark it as answered, and mark posts as "correct" or "helpful". You can mark 1 post as correct and up to 5 as helpful. Don't be pushed into marking people's answers if they say "please mark my answer correct/helpful" as once you've marked them, you cannot remove it. Ensure you are happy you've got the correct answer to your question first and wait and listen for the responses of those who are experts as they may be able to improve on previous replies. People who ask a lot of questions but don't mark them as answered may find they get less responses as people are less willing to help if they don't get any feedback from you.
12) Homework/Study/Interview questions
If you question relates to homework, your studies or was something you were asked in an interview, ensure you show your own answer or what you've tried yourself so far, even if it's not working. Members have their own work to do and do not just want to do your work for you, though most are willing to help out if you can show that you've made some effort yourself and you're clearly stuck.
Performance tuning is not always as simple as just showing people a query and them telling you where it needs improving.
So that people can assist you better with your performance issue, please see the following threads which give you some ideas of what you will need to be looking at, as well as what information you should provide to forum members if you want help:
This is called "pivoting" and is asked on this forum almost every day.
See these threads:
"But I want to pivot an unknown number of rows to columns?"
This is not easily possible as the number of columns returned by an SQL must be known before any data is fetched, it would have to be done dynamically.
See these threads:
"But I want to get all the data in a single column?"
This is called string aggregation.
See these threads:
Re: Concat rows values into single column (and a sys_connect_by_path example further up)
Re: Multiple rows into a single line in 'Single Column Table'
LISTAGG 11g function
Various string aggregation techniques
Tom Kytes string aggregation
Note: recommendations to use the wm_concat function should be considered poor suggestions as it is an undocumented function, not supported and subject to change in future versions of Oracle. Even Tom Kyte says so... Re: DISTINCT not working with wmsys.wm_concat
Reading Excel files
Is it really an Excel file with a .xls extension?
See the following thread:
Re: Read CSV/XLS file to insert into Oracle database.
or is it a character seperated file (CSV) file?
See the following:
Writing Excel Files
For Excel 2007 and onwards, see ascheffer's package here: http://technology.amis.nl/blog/10995/create-an-excel-file-with-plsql
For doing it using COM automation see Saubhik's package here: http://saubbane.blogspot.co.uk/2011/05/writing-and-formatting-excel-using.html
"I have a list of values in a variable/parameter that I'm using with an 'IN' clause in my query, but I'm not getting results?"
This is the "Varying IN list" issue.
Your "list of values" is actually a single value, not a list of seperate values. Tom Kyte has a good article on this...
Tom Kytes Varying In Lists
... and Adrian Billington gives some great examples...
CD wrote a few excellent threads on this:
1) Consideration for new members
Be considerate of new members. Remember they probably haven't seen this FAQ thread and may not have thought about how to ask the question properly; or may be new to using forums (or even the internet!!). If it's an obvious document question help them by providing a link to the documentation (not everyone knows where to find the documentation or knows what they need to search for to find their answer in it). If they haven't formatted their post, link to this FAQ (quick way is copying http://forums.oracle.com/message/9362002#9362002 in your reply) so they can learn how to do it themselves. The post may also get formatted by a moderator if it's just a case of sticking some code
tags in and providing a moderator is around.
2) Consideration for non-new members
If someone is not a new member and had posted a document type question or not formatted their code etc. feel free to point out (as politely as you can manage) that they should know better and link to this FAQ thread.
3) Duplicate postings
Bear in mind that the forum software can sometimes be glitchy and generate multiple posts, but if a member is obviously asking the same question more than once, politely point out to them that it won't help them to get the answer any quicker and will just confuse matters. Also provide a link back to their original thread if possible and indicate that answers should go on that thread. The moderators will do what we can to clean up duplicate posting (removing or locking them as appropriate).
4) Formatting your answers
As per the FAQ information on how to ask a question on the forums, it's useful if the members answering can also ensure they have formatted the code in their responses, and where necessary given any useful information to explain any more complex parts of the answer. Also consider the other points in the above FAQ relating to not using SMS speak etc.
5) Links to personal websites and Signatures.
Avoid answers that just link to personal blogs and websites (The site admin have indicated that this will be treated as spamming/promotional), but feel free to link to relevant information in support of an answer you've posted on the forum. Putting a simple signature in your answers is ok such as your name and a basic link to your oracle based blog. However blatently commercial links and wording may be referred to the site admin to deal with. Remember the purpose of the forums is to assist each other, not to advertise products and/or services.
6) Links to 3rd party websites
Avoid linking to 3rd party websites that throw up popup windows or adverts (or require subscriptions/payments) or are clearly more about commercial services and products than providing a good comprehensive answer. e.g. There's little use in linking to a site that provides a basic pointer to an answer, and is more focused on selling books or "DBA Services" etc. in order to resolve the persons issue. Obviously there are some good websites with comprehensive answers and a little advertising on them (google ads or whatever), and those are generally ok, as long as it's helping to answer the persons question.
7) Begging for points
Posting an answer and finishing the post by saying something like "If my answer useful, mark it as correct or helpful" is not acceptable. This is considered begging for points and the site admin have indicated that this is not acceptable.
The points system on the forums, as agreed by most of the regulars on here, is not an ideal system. The last thing it needs is people begging to gain points for themselves so that they can boost their ego by trying to reach "Guru" status to impress their friends. The purpose of these professional forums is to help others, have our own questions answered and to learn new things for ourselves. Most of the experts and gurus on these forums have gained points and their status through simply answering questions well without asking for them. Sometimes points are awarded, sometimes not, and that is up to the original poster as to whether they follow the forum etiquette. Anyone spotted asking for points in any form is liable to have such comments edited out of their post or the post deleted (if deletion isn't removing an actual answer). *You have been warned!*
8) Requesting a question is marked as answered
Furthering the above point, if the original poster has indicated that their question has been answered, and they haven't marked the thread as answered, by all means feel free to say "Please mark the thread as answered so members can focus on other threads". Just don't ask for points.
Commonly we get asked what's the differences between version X and version Y of the database, or what new features it has...
It's all documented...
10.1 New Features
10.2 New Features
22.214.171.124 New Features
126.96.36.199 New Features
188.8.131.52 New Features
12.1 New Features
Oracle Database 12c Release 1 (12.1) New Features
Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.0.1
Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.2
Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.1
Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.2
Compatibility and Interoperability Issues in Oracle Database 11g Release 1 (11.1)
Compatibility and Interoperability Issues in Oracle Database 11g Release 2 (11.2)