Forum Stats

  • 3,734,237 Users
  • 2,246,916 Discussions
  • 7,857,196 Comments

Discussions

SQL and PL/SQL FAQ

BluShadow
BluShadow Member, Moderator Posts: 40,989 Red Diamond
edited Sep 20, 2018 10:21AM in SQL & PL/SQL

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.

Contents



LakshmiNarasimhaKayKDude!2705266a_alok25858328Rahul_Indiapradeepn2724157sagar gateuser133934092738740Biju DasMohammed Sardarzsj20022782263Igoroshka27955712790070Dlundgren-Oracleuser11997764Sanjay Desai EBSRafusandeeprkuChandan Sandilya-OracleSaravanan VijayasundaramJitendra2709439Bhavin MamtoraWadhahDaouehiAndra Gangadharspur230User91024356729462702999661abhishek choudharySQLIgniteDonGRAfaAM9Mateusz IwanNaveen -Oracle3169018ivwviMOHAMMED IBRAHIMDforzVysakh Suresh - 3035408KalpataruPranay KambleBharath BallipongalaDairy LandAkshsMuhammad Masoom AnsariGeoff Grandstaff-Oraclejr-phDBAHolgerHAsterix45Gbenga AjakayeSrini.RamanujamRicardo Lavezzi3565308sazam13jariolaFrank KulashSaurabh PhapaleSamorakoDejan T.MANOJ PRABHAKARjaramillGanesh kumarArsalan Dehghanisariyarghan

Comments

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Jun 11, 2013 3:43AM

    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

    User910243567AkshsFrank Kulash
  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Nov 24, 2016 4:38AM

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

    Static Pivoting
    See these threads:



    Tom Kyte - Pivoting pre-11g

    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:

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

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Sep 20, 2018 10:21AM

    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

    Dairy LandHolgerH
  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Jun 11, 2013 7:04AM

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

    See the following threads:

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Jan 29, 2018 8:04AM

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


    Adrian Billingtons Examples

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Jun 11, 2013 7:06AM
  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited May 22, 2015 7:11AM

    See this community document:

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited May 29, 2015 8:55AM

    See this document:

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Jun 2, 2015 6:10AM

    Based on an excellent thread by Aketi Jyuuzou please see the following document:



    Biju DasDejan T.
  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Sep 17, 2013 9:28AM

    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

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited May 6, 2015 7:09AM

    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 <span style="font-family: arial,helvetica,sans-serif;">tags in and providing a moderator is around.  <span __jive_emoticon_name="wink" __jive_macro_name="emoticon" class="jive_macro jive_emote" src="/7.0.0.1ca6acb/images/emoticons/wink.png"></span></span>

    <span style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><strong>2) Consideration for non-new members</strong></span>

    <span style="font-family: arial,helvetica,sans-serif;">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.</span>

    <span style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><strong>3) Duplicate postings</strong></span>

    <span style="font-family: arial,helvetica,sans-serif;">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).</span>

    <span style="font-family: arial,helvetica,sans-serif;"> <span style="font-size: 12pt;"><strong>4) Formatting your answers</strong></span></span>

    <span style="font-family: arial,helvetica,sans-serif;">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.</span>

    <span style="font-family: arial,helvetica,sans-serif;"><strong>5) Links to personal websites and Signatures.</strong></span>

    <span style="font-family: arial,helvetica,sans-serif;"> 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.</span>

    <span style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><strong>6) Links to 3rd party websites</strong></span>

    <span style="font-family: arial,helvetica,sans-serif;">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.</span>

    <span style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><strong>7) Begging for points</strong></span>

    <span style="font-family: arial,helvetica,sans-serif;">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.  </span></code><code class="jive-code"><span style="font-family: arial,helvetica,sans-serif;">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).  <strong>*You have been warned!*</strong></span>

    <span style="font-size: 12pt; font-family: arial,helvetica,sans-serif;"><strong>8) Requesting a question is marked as answered</strong></span>

    <span style="font-family: arial,helvetica,sans-serif;">Furthering the above point, <span style="text-decoration: underline;">if the original poster has indicated that their question has been answered</span>, 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".  <strong>Just don't ask for points</strong>.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    </span>

    KayKSaravanan Vijayasundaram
  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited Jan 6, 2015 9:32AM

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


    11.2.0.1 to 11.2.0.4 New Features

    http://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#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

    Kalpataru
  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    edited May 6, 2016 8:36AM

    Ed Stevens has produced an excellent article explaining how and why you don't store dates in any particular format, and why such formats are a display concern instead...

    http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

    Also, the Community Document:

    gives an in depth look at the DATE datatype and how to consider it's use properly.

This discussion has been closed.