-
1. Re: 1. Where can I find Oracle Documentation?
All oracle documentation is located on the following website:
The "Database" section (Oracle Database Help Center) is where you'll find stuff on SQL and PL/SQL. Pick your database version and click on the "All Oracle Database Books" link at the bottom of the page to get to all the documentation. From there you can pick certain books, or you can search for what you want.
New to Oracle SQL and PL/SQL?, which Documentation to read first?
Start with the "Concepts" manual.
Then perhaps:
"2 Day Developers Guide"
"PL/SQL Language Reference" (and "PL/SQL Packages and Types Reference")
"SQL Language Reference"
as appropriate for your interest. -
2. Re: 2. How do I ask a question on the forums?
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 unfavorably.
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 and IDE Version
Ensure you provide your database version number e.g. 11.2.0.3 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.
In addition, it's a good idea to tell people what Integrated Development Environment (IDE) you are using e.g. SQL*Plus, SQL Developer, TOAD, PL/SQL Developer etc. and what version it is. The IDE could relate to your issue, or could help responders in giving you an answer that best suits that IDE.
6) Tables/Indexes
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 allselect 2, to_date('02/02/2009','DD/MM/YYYY'), 25 from dual union allselect 3, to_date('03/02/2009','DD/MM/YYYY'), 30 from dual),x as (select 1 as id, 'Fred' as name from dual union allselect 2, 'Bob' from dual union allselect 3, 'Tim' from dual)or by providing create table and insert statements e.g.
create table t (id number,date_created date,val number)/create table x (id number,name varchar2(20))/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) Explain the Actual Issue
Too often we see people asking for help to fix some code they're trying to write, but they haven't actually explained what the issue is the code is supposed to be resolving. Ensure you've explained clearly what the actual requirements are that you are trying to achieve with the code as, more often than not, there will be a better solution to your problem, completely different from how you are trying to solve it. See the following: http://xyproblem.info/
10) Formatting code and dataEnsure 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.
11) 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.
12). 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 any others 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.
13) 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.
14) New to SQL or PL/SQL. Need someone to teach you?
Please read the following blog post which reflects the opinions of most community experts: https://oracle-base.com/blog/2016/07/27/learning-career-development-and-mentoring/
And consider what you're asking before you post your question. People are willing to help, if you can show you're helping yourself.
-
3. Re: 3. How to improve the performance of my query? / My query is running slow.
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:
HOW TO: Post a SQL statement tuning request - template posting
-
4. Re: 4. How do I convert rows to columns?
This is called "pivoting" and is asked on this forum almost every day.
Static Pivoting
See these threads:Dynamic Pivoting
"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/documents:PL/SQL 101 : Cursors and SQL Projection
in conjunction with https://technology.amis.nl/2006/05/16/pivot-dynamic-data/
A clever technique using the PIVOT functionality of SQL (11.2 and above)...
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
String Aggregation
"But I want to get all the data in a single column?"
This is called string aggregation.
See these threads:
(and a sys_connect_by_path example further up)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... -
5. Re: 5. How do I read or write an Excel file?
Reading Excel files
Is it really an Excel file with a .xls extension?
See the following thread:or a more recent way by Odie, detailed on his blog:
https://odieweblog.wordpress.com/2016/06/21/reading-an-excel-file-xlsx-as-an-external-table/
(Odie's project is on GitHub: https://github.com/mbleron/ExcelTable)
or is it a character seperated file (CSV) file?
See the following:
Morgans Library - External Tables
Morgans Library - SQL Loader
Anton Scheffers XLSX reading through PL/SQL
Writing Excel Files
For Excel 2007 and onwards, see ascheffer's package here: https://technology.amis.nl/2011/02/19/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
Other Office XML links:
http://support.microsoft.com/kb/288215
http://blogs.msdn.com/brian_jones/archive/2005/06/27/433152.aspx
-
6. Re: 6. What is the difference between count(*) and count(1)?
Count(1) is rewritten by the optimiser to Count(*) so they are identical:
See the following threads: -
7. Re: 7. List of values in an IN clause?
"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...
https://asktom.oracle.com/Misc/varying-in-lists.html... and Adrian Billington gives some great examples...
-
8. Re: 8. Can anyone explain Regular Expressions to me?
-
-
11. Re: 11. How do I group sequences of numbers/dates etc.?
Based on an excellent thread by Aketi Jyuuzou please see the following document:
-
12. Re: 12. ORA-01031: insufficient privileges when running PL/SQL code
This is often caused by permissions only existing through a role rather than being directly granted to a user.
See the following: -
13. Re: 13. How do I answer a question on the forums?
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 codetags in and providing a moderator is around.2) Consideration for non-new membersIf 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 postingsBear 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 answersAs 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. Remember the purpose of the forums is to assist each other, not to advertise products, services or your own websites.6) Links to 3rd party websitesAvoid 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 pointsPosting 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 answeredFurthering 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. -
14. Re: 14. What's the difference between different versions of the database?
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
http://docs.oracle.com/cd/B14117_01/server.101/b10750/toc.htm10.2 New Features
http://docs.oracle.com/cd/B19306_01/server.102/b14214/toc.htm
11.1 New Features
http://docs.oracle.com/cd/B28359_01/server.111/b28279/toc.htm
11.2.0.1 to 11.2.0.4 New Featureshttp://docs.oracle.com/cd/E11882_01/server.112/e41360/toc.htm
12.1 New Features
http://docs.oracle.com/database/121/NEWFT/
-----
Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.0.1
http://docs.oracle.com/cd/B19306_01/server.102/b14238/compat.htm#i1014343Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.2
http://docs.oracle.com/cd/B19306_01/server.102/b14238/compat.htm#i1014197
Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.1
http://docs.oracle.com/cd/B19306_01/server.102/b14238/compat.htm#i1014342Compatibility and Interoperability Issues Introduced in Oracle Database 10g Release 10.2
http://docs.oracle.com/cd/B19306_01/server.102/b14238/compat.htm#CHDFHCHD
Compatibility and Interoperability Issues in Oracle Database 11g Release 1 (11.1)
http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#BEHJEAJBCompatibility and Interoperability Issues in Oracle Database 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#BABJBDBI
Register and Participate in Oracle's online communities. Learn from thousands of experts, get answers to your questions and share knowledge with peers.