This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jun 11, 2013 4:09 AM by BluShadow RSS

SQL and PL/SQL FAQ

BluShadow Guru Moderator
Currently Being Moderated
  • 1. Re: 1. Where can I find Oracle Documentation?
    BluShadow Guru Moderator
    Currently Being Moderated

    All oracle documentation is located on the following website:

     

    http://tahiti.oracle.com/

    (just select your version and then search)

    New to Oracle SQL and PL/SQL?, which Documentation to read first?

    Start with the "Concepts" manual.
    Then perhaps:
    "Application Developer's Guide - Fundamentals"
    "PL/SQL User's Guide and Reference"
    "SQL Reference"

    as appropriate for your interest.

  • 2. Re: 2. How do I ask a question on the forums?
    BluShadow Guru Moderator
    Currently Being Moderated

    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. 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.

    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 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,

                    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) 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.

  • 3. Re: 3. How to  improve the performance of my query? / My query is running slow.
    BluShadow Guru Moderator
    Currently Being Moderated

    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:

     

     

    When your query takes too long ...

    HOW TO: Post a SQL statement tuning request - template posting

  • 4. Re: 4. How do I convert rows to columns?
    BluShadow Guru Moderator
    Currently Being Moderated

    This is called "pivoting" and is asked on this forum almost every day.

    Static Pivoting
    See these threads:


    Help for a query to add columns
    Tom Kyte - Pivoting pre-11g
    Re: Help with PIVOT query (or advice on best way to do this)

     

     

    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:


    PL/SQL 101 : Cursors and SQL Projection

    Help for a query to add columns

    How to pipeline a function with a dynamic number of columns?

     

    String Aggregation
    "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

  • 6. Re: 6. What is the difference between count(*) and count(1)?
    BluShadow Guru Moderator
    Currently Being Moderated

    Count(1) is rewritten by the optimiser to Count(*) so they are identical:

    See the following threads:

    Re: Count(*)/Count(1)

    Re: Difference between count(*) & count(1)

  • 7. Re: 7. List of values in an IN clause?
    BluShadow Guru Moderator
    Currently Being Moderated

    "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...


    Adrian Billingtons Examples

  • 11. Re: 11. How do I group sequences of numbers/dates etc.?
    BluShadow Guru Moderator
    Currently Being Moderated

    See this excellent method by Aketi Jyuuzou

    Re: Tabibitosan method tutorial by Aketi Jyuuzou

  • 12. Re: 12. ORA-01031: insufficient privileges when running PL/SQL code
    BluShadow Guru Moderator
    Currently Being Moderated

    This is often caused by permissions only existing through a role rather than being directly granted to a user.
    See the following:

     

    ORA-1030 and PLS-201 in procedures

  • 13. Re: 13. How do I answer a question on the forums?
    BluShadow Guru Moderator
    Currently Being Moderated

    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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

  • 14. Re: 14. What's the difference between different versions of the database?
    BluShadow Guru Moderator
    Currently Being Moderated

    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.htm

    10.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/chapter1.htm#NEWFTCH1


    11.2.0.1 New Features
    http://docs.oracle.com/cd/E11882_01/server.112/e22487/chapter1.htm#NEWFTCH1-a

    11.2.0.2 New Features
    http://docs.oracle.com/cd/E11882_01/server.112/e22487/chapter1_2.htm#NEWFTCH1-b

    11.2.0.3 New Features
    http://docs.oracle.com/cd/E11882_01/server.112/e22487/chapter1_11203.htm#NEWFTCH1-c

     

    12.1 New Features

    Oracle Database 12c Release 1 (12.1) New Features
    -----

    Compatibility and Interoperability Issues Introduced in Oracle9i Release 9.0.1
    http://docs.oracle.com/cd/B19306_01/server.102/b14238/compat.htm#i1014343

    Compatibility 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#i1014342

    Compatibility 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#BEHJEAJB

    Compatibility and Interoperability Issues in Oracle Database 11g Release 2 (11.2)
    http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#BABJBDBI

1 2 Previous Next