Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

SQL and PL/SQL FAQ

BluShadowFeb 11 2011 — edited Mar 14 2023

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

  1. Posting on the Forum
  2. Where can I find the documentation?
  3. How best to respond to questions?
  4. Performance Tuning (aka “My Query Is Running Slow”)
  5. How do I convert rows to columns?
  6. How Do I Read/Write Excel Files in PL/SQL?
  7. Is Count(1) faster than Count(*)?
  8. Why doesn't my string of comma separated values work with the IN clause?
  9. Where can I learn about Regular Expressions?
  10. How can I store my numbers with leading zero's?
  11. What's the difference between Substitution and Bind Variables?
  12. Grouping Sequence Ranges (aka Tabibitosan Method)
  13. ORA-1030 Insufficient Privileges when I run my code in a procedure?
  14. How do I use Ref Cursors in my Query?
  15. What's the difference between version X and version Y of the database?
  16. How do I store my dates in a particular format?
  17. Why do I not see my output from my code with DBMS_OUTPUT?
  18. How do I use Exception Handling in my code?
  19. What's the point of the WITH clause? How can I write recursive WITH clauses?

Firstly the most important topic is how to post on this forum, so that will be detailed here. Other FAQ's will follow after that.

Posting on the Forum

Before you post…

  • 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 or online. Frequently asked questions can be found in the below posts.
  • Are you posting in the correct forum? The community has multiple forums for different products and purposes. e.g. the SQL and PL/SQL forum is for questions about the SQL and PL/SQL languages and coding whereas questions for SQL Developer (the tool) has it's own forum, as does Application Express (APEX), E-Business Suite (EBS) and other topics.
  • If you are replying to someone else's thread, check the age of the thread. If it's more than about 3 months since someone has posted on the thread, we generally consider it to be ‘inactive’ and would rather you start a new thread of your own, referencing the old thread if appropriate. That prevents us having old threads dragged up when there are more recent active ones that people are focusing on. And please don't just reply to an old thread to say “Thanks that helped me”, it adds nothing to the discussion.

1) New Thread
Click the "New Post" link.

2) Thread Title
Give your thread a meaningful title subject, not just "help please", "Query help" or "SQL" and certainly not the whole detailed question. 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, but keep it short and succinct.

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. A truly urgent issue should be raised directly with Oracle support https://support.oracle.com

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. Use online translation if English isn't your native language and you need to, we're cool with that.

4) Ask nicely
Typing your title 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 emphasizing 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. This is especially important if your question is about performance issues.

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) 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 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 Code Type menu. Copy/paste your code to the editor, highlight it, then clicking the Code Type Menu drop down:

https://objectstorage.us-phoenix-1.oraclecloud.com/p/8XchFSZkjz8qO4p8uxTZOqA0LbZbAcrp1MON3Ph8P79VRQLjAIsR8S0ieDkLokBl/n/axciphqpnohg/b/forums-prod-www/o/attachments/RhHFStmi-9037-fMFECBQS

and choose SQL/PLSQL (or the appropriate language). Your code should then appear formatted and highlighted.

DECLARE
   v_str VARCHAR2(100);
BEGIN
   v_str := 'This is some sample code';
END;

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 memorized the meaning of the hundreds of possible ORA codes that can be produced. (and if it's an ORA-00600 error, that's usually an internal error that you'll need to raise with Oracle Support directly)

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


Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Pinned by BluShadow on Mar 14 2023
Locked on Mar 14 2023
Added on Feb 11 2011
18 comments
238,223 views