AskTom and formatting

Posted by

There’s an interesting “debate” going on under AskTom at the moment.  It’s hardly cause for major concern, but anyone is welcome to add their thoughts as comments to this blog post.  (Naturally, if your comments agree with my opinion, then that’s great.  If your comments don’t agree, then I’ll post them and ignore them Smile – ah the joys of being a blog owner)

So here’s the issue.  When we are posting code to the AskTom page as a solution to a question, we could post it as a mix of script and output, for example:

drop table T purge;
create table T ( x int );
insert into T select level from dual connect by level <= 10;
select * from T;


Or, we could post the code exactly as it would have appeared to us as we ran it in (say) SQL Plus.

SQL> drop table T purge;

Table dropped.

SQL> create table T ( x int );

Table created.

SQL> insert into T select level from dual connect by level <= 10;

10 rows created.

SQL> select * from T;


10 rows selected.

My personal preference is the latter, since it’s a no “smoke and mirrors” approach.  A common feedback is that you cannot cut-and-paste the second example and just run it in your session.  My counter-argument to that is that its pretty easy to clean it up.  For example, in any editor that supports regular expressions, you just do:

Change ^….. to nothing.

ie, remove the first 5 characters at the start of each line, and you’re almost there.

In reality, either option is perfectly adequate (but you’d be amazed how “passionate” some of the feedback we get is!)

So let me know what your preference is – if you make the correct choice, I’ll agree with you Smile


  1. I actually like both but for different reasons, and would prefer to pick the one most applicable to the situation.

    I like Option 1 just because it’s simpler – less noise, it is reasonable to assume that the commands succeeded, and only the output that is relevant to make the point is shown, so it is much easier to understand.

    I like Option 2 for the same reason you do – and for demonstrating peculiar behaviour, or to refute someone’s assertion, it’s necessary.

    So I would choose Option 1 where some of the statements are there to set the scene but not central to the point – especially where the code might include some “smoke and mirrors” e.g. “create table t (… some columns, not relevant here…)”.

    I would choose Option 2 where I need to demonstrate a working, repeatable test case.

  2. Hi Connor.
    How about combining both approaches?
    Make your SQL*Plus environment produce the outcome as in option 1, with:

    set sqlprompt “”
    set feed off

    This way there are no “smoke and mirrors” and we will be able to cut-and-paste the examples without manipulating them. The best of both worlds, I think.


  3. You are of course making the assumption people know what the heck SQL*Plus is. I see lots of developers who don’t. They open up TOAD or SQL Developer, so the format of code is confusing to them if it includes SQL*Plus specific bits. Amazing I know. 🙂

    I prefer option 1, as it is a copy/paste world. You will know from my website, that’s what I do. 🙂 I don’t mind option 2 though.

    To be consistent with the past, you should use option 2, because that’s the way Tom did things, but I see little problem in you, Chris and Tom all answering in your own preferred way.



  4. Usually you can’t simply remove the first n characters. Look at the first reply in (or your example for Option 2 above 🙂 )
    When I copy the code then I have a line between 12 and 13 where I would remove part of the code. And even if everything fits into a single line I have to remove garbage like “Table dropped.” etc manually.
    I prefer a simple copy and execute.

  5. Personally, I can live with both approaches.
    However, my preference is the second option.

    Do you build code, then test it and after finding out it’s perfect you modify it before deploying?
    Of course not (I sincerely hope).

    So, don’t do this with the examples/proof you wish to deploy to a blogpost/asktom reply.
    If you do, inevitably there will be a thread that goes like:
    Q: How do I do…..
    A: like this….
    Q: Tried, but doesn’t work for me.
    A: Oops, I deleted a line I shouldn’t have.

    Of course, to be able to replay by cut and paste some cleaning up needs to be done.
    But in my humble opinion this is the responsibility of the receiving party.
    It’s the responsibility of the sender to give all information.

    Plus, although this might sound more harsh than it’s meant to be, if you lack enough sql knowledge to recognise the sql-bits between the sql*plus outputs (as oraclebase suggests) then you probably shouldn’t be copy-pasting the sql anyway.
    No matter how knowledgeable Tom Kyte and his current ‘offspring’ are, you should always analyse and understand the answer before blindly executing it.
    If you’re not able to identify what is a statement to be executed, and what is sql*plus output, how can one be confident enough to just copy and paste the script if it’s presented as option 1?

  6. There is also option 0: what Tom actually did, which is post the SQL*Plus output with line numbers.

    I copied Tom’s code for years to run it myself, and cleaning up the line numbers was annoying. However, there is a big advantage to line numbers when there is an error message or when you want to refer to specific lines while explaining the code.

    That leads me to option 4: use a plugin that displays the code with line numbers but lets you copy the code without them. See WordPress blogs and the OTN forums.

    Why should it be important to post all the SQL*Plus output? Does that mean I’m not allowed to post output from Oracle SQL Developer?

    I don’t see any reason to make SQL*Plus the “privileged tool”, or to have a standard that the output must look like it came from SQL*Plus.

    I also disagree that the “sender” should leave cleanup to the readers: if the “sender” does a little extra work, all the readers will benefit. If each reader does the work, there will still be a chance that the reader tests something the “sender” didn’t post!

    To summarize, I think it best to post code that can be copied, pasted and run, and that displays with line numbers that don’t get copied.

  7. I like seeing the output. To me it is sort of like the difference between reading reference documentation versus seeing examples. Sure, I can read reference docs and learn how to use a command or feature, but it is much faster and easier to learn by looking at examples.

  8. IMHO, most convenient for readers would be splitted output in table format, but i’m too lazy for this 🙂

    COMMANDS          | OUTPUT
    select x          |           X
    from xxxx;        |  ----------
                      |           1
                      |           2
                      |           3
                      |           4
                      |           5
                      |           6
                      |           7
                      |           8
                      |           9
                      |          10
                      | 10 rows selected.
  9. I do both, a screenshot of me running the code with the results, and then the raw code ready for copy/pasting.

    Assuming folks know regex might be a bit of a stretch…even recognizing AskTom readers are the smartest.

    Just my 2 pence. Make it easier for the millions of readers, not the one writer.

  10. Here’s one more vote for option #2. Showing the exact same output you get proves you didn’t edit anything (e.g. cheat). I agree that removing SQL*Plus feedback is easy to do and can be done in a few seconds.

  11. I’m an option 2 man. Clarity is key in conveying correct/useful information via a blog/forum.

    The whole cut and paste argument doesn’t carry any weight with me.
    1) You can still cut and paste, you just need to do a few more of them.
    2) If you haven’t executed the code before you probably should be taking a little extra time to understand each step and make sure it ran successfully before moving on to the next one.

  12. Definitely option 2 for me – you can see exactly what is going on, nothing is “hidden” and if you’re reproducing it then you know what to expect your session to look like.

Leave a Reply to thatjeffsmith Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.