OTN Apprecition Day: the OTN forum

The SQL and PLSQL forum

Today is OTN Apprecitation day so I decided to write a short article about my favourite Oracle feature. It is the OTN SQL and PLSQL forum! Reading and posting on this forum made me a better developer.  I also frequently visit other forums like Database General, Apex and lately the Oracle JET, but not as intensively as the SQL and PLSQL forum.

My OTN forum handle is Sven W.

screen-shot-2016-10-11-at-23-57-04

greetings/honorable mentions

BluShadow – for moderating the forum and creating the FAQ list

Frank Kulash – for always answering in a nice and calm way insistently leading the OP to the final solution.

Billy Verrennye – for makeing me rethink old habits (like naming conventions) and for providing excellent and well thought source code examples.

William Robertson – for always beeing spot on

Odie_63 – For answering some of my questions, for example by taking apart the internal meaning of ROWIDs for external tables.

Boneist and ApexBine – for makeing their statements in a male dominated industry

noticable threads

About naming conventions in PLSQL

Coding Standards and Code Critique Request

features / ideas

PLSQL 101: Datatypes – DATE

About Ansi Joins

Introduction to regular expressions

SQL Assertions / declarative multi-row constraints

other

The 10 database commandmends

Are databases still nice and quick and simple to use like they once were?

Fun stuff from the past

Developers sometimes can be funny and sometimes they just need to boil of some steam.

Here is a collection of thread snippets from the past of the forum. Some years ago I collected memorable posts, but I don’t do this anymore. So the collection is slighty outdated now, but I tried to add a few recent quotes as well.

“OP” is used when I cite the “Original Poster” without giving the real forum handle. Otherwise usually only the first name for some of the well known members are used. Comments to the original quote from myself are in italic. Different threads are separated by a line.

Best of Forum 2007


OP> want a procedure or a block which will give a tree like structure using loops and cursors
3360> Why? Do you have a requirement to make this as slow as possible?


Dave Hemming> select ‘don”t stop me now, I”m having such a good time’ from dual

Special thanks to Dave! This is one of my favourite Queen songs.


APC> Late breaking newsflash: users are not developers!


Damorgan> Writing “working on tables” is as informative as writing “using keyboard and mouse.” See your instructor.


OP> SO CAN YOU PLEASE ASSIST ME ON THAT .

APC> On most keyboards the CAPSLOCK key is halfway down the lefthand side. Please learn to use it.


Billy> The features and flexibility and power of Oracle is NO substitution for a solid relational design.


Sentinel>

insert into table (column) values ('I have John''s shoes');

Sentinel>Of course what I’m doing with his shoes is a completely different story.
John Spencer> Since I only have one pair, I had to go to work barefoot this morning 🙂
John Spencer> Shoeless John


Damorgan> Without a context your posting is just a waste of perfectly good electrons.


OP>Is it possible to do something like this from a running program ?

Billy>Is it possible to jump from an aircraft at 5000 feet? Yes.

Billy>Of course, this has to be questioned as when it is done without a parachute, the changes of survival are very very slim. Never mind that if you’re the pilot, you are sending that plane down.

Billy>Yes, columns can be renamed dynamically from a running program. But it makes as much sense as jumping from a perfectly capable plane without a parachute.


APC> In general computing is about precision and removing ambiguity. That’s why the industry is full of pedants. Maddeningly, there is a direct correlation between pedantry and good programming.


Best of forum 2008


Billy>Be careful about making conclusions using observation only. Simple example. Observe how the WARP_SPEED hint makes the SQL go faster:

SQL> set timing on
SQL> select count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:09.60
SQL> select /*+ WARP_SPEED */ count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:00.50
SQL>

The empirical conclusion is that the WARP_SPEED hint made the query faster by 90%.

This conclusion (based on observation) is incorrect. The real reason why the 2nd query is faster is that it made substantially less physical I/O than the 1st query. The 1st query loaded a lot of data needed into the buffer cache. The 2nd query found that data there and had no need to perform the same expensive and slow physical I/Os that the 1st query did. Nor is there a WARP_SPEED hint.

So be very careful on making assumptions and basing conclusions solely on observation.


Sven> If you provide some example data and your insert statement as everybody suggested, we could give much better solutions without guessing all around.

Hans> But then we would only average one reply per question. And we would not get to spend as much time on the forums, getting to know each other so well.


Billy>Users make incredibly poor Oracle gods. That is what the DBA role is – godlike in Oracle and should be treated with care and respect and given only to those persons responsible for actual database administration. And no, users cannot administrate an Oracle database either.


WhiteHat>Hi all,

The Powers that Be at my work have decided to cut back on the number of different systems we have by re-writing a lot of them from scratch and combining functionality in order to reduce downtime caused by ETL processes and the like. so as a result I’m trying to implement the unified theory of everything in my stored proc and I can’t get it to work. Specifically I’m having difficulties combining quantum mechanics and general relativity into a single SQL statement. I’m getting ORA-06502: numeric or value error: String theory conversion error at line 3523

Is this possible in oracle v150.2.0.5 or will I have to upgrade to 153gR2?

being friday afternoon, my brain isn’t really in gear so I’m certain I’ve overlooked something simple. I suspect my basic architecture assumptions are incorrect but not sure. any advice?

Cheers,
WH.
Dave>It’s possible you’re trying to imply a quantum function to a relativistic variable. You’ll need to explicitly CAST it first.

Of course, I definitely think you need to show us your code. 🙂
Leo>Thats not always necessary as especially the quantum function sometimes can be decrypted itself by Oracle.

But definitely we need the code from line 3517.3 until line 3527.8


Billy>If this was ancient times, and you wrote this code to run on any of my databases, I would have handed your over to the SQL Inquisition for showing you the error of your ways.


Sarma>OP already told it is just an exercise for him on PL/SQL. In short, home-work.
Billy>Oh… I see.. You mean like attending Police College and committing, as home work, crimes like armed robbery, assault with a dangerous weapon, vehicular manslaughter, arson, and so on.
Billy>Yeah, I can see how this can teach you how to enforce the law.. NOT!


Justin>It’s generally helpful to specify the actual exception you’re getting rather than just saying “raises an exception”. Oracle error numbers and error strings are exceptionally useful debugging tools.


Michael O>Isn’t this the Oracle Support Forum where all wishes are granted?


Unknown>It is a basic problem that we face too here in forums. How do we show The Good Stuff of Oracle to a SQL-Server fanboy that cannot bare to empty his cup of SQL-Server in order to taste some Oracle?

Or dealing with a Java zealot that has been bitten badly by the J2EE religion, and sees Oracle as a mere persistence layer.. and not good for anything else?

Some people are so convinced that they are so absolutely right, they cannot even entertain the idea of something alternative.. never mind the idea that they just may be horribly wrong.


WhiteHat>[clippy]

Hi! it looks like you’re trying to use Oracle!

Do you:
( ) want to INSERT data to a table
( ) want to UPDATE existing data in a table
( ) ALTER the structure of the table
( ) search the internet for other queries

[clippy]

It’s not clear what you’re trying to do:
as we understand it it seems like:
you have a newly created table and you want to make it so there’s data in it is this correct?


OP> what if i will have thousands of record.i cant write
them all.
Dave> BANGS HEAD ON DESK
Dave> Instead of the select … from dual union select … from dual… perhaps you could use YOUR OWN TABLE.
OP> yeah i told you that i got it already so i dont need to bang head on desk……thanx neways


OP> Can you just tell me which is the best oracle performance tuning tool in the market? It should be free download.
Guido> It’s name is BRAIN (Biological Resource for All Informations Needs). If you really need to download that you should opt for another career path, I guess. 😉


OP>PL DESCRIBE U’R TABLE WORD.
Billy>Use proper English and not IM SPEAK as this is a technical forum and not some SMS teenage chat room.
padders>Please note however that it is considered acceptable to refer to someone’s ‘leet SQL skillz’.
Dave>Although it’s worth first establishing a reputation that clearly indicates that you do not think “irony” means “similar to iron”.


OP>i have run the package and it will take execution time more than 1 hour, how can i redure the execution time? any one help on this issue.
Matt>Remove all the code from the package.


shoblock> I really wish people would read the responses before they complain that they
aren’t working as desired.
APC> Aw c’mon. Next you’ll be wishing people would look stuff up in the documentation instead of straightaway posting questions here.


Laurent> of course regexp could save ink when printed


OP>i’ve try to add commit; but doesn’t work.

Dave>Glad to see you picked up on the need for a more complete explanation than “doesn’t work”.
Dave>Oh wait, you didn’t.

Someoneelse>That’s a new error in 11g:
Someoneelse>ORA-00042 DOESN’T WORK


Someonelse>IF Using_SQL_Server THEN
Someonelse> EXIT Oracle_Forums;
Someonelse>END IF;


Keith>If thats not clear, I’ll join the hitting head against the brickwall gang.


Billy> Do you fix the symptoms? Or do you fix the problem?
Padders>Erm. The problem I think. Aren’t we supposed to hit the symptoms with the lead pipe?


OP>Thread with title like “urgent help in sql plz ”
Billy>STOP!!

For that you need to fill in the “It Is Truly Urgent” form via the request link on the Oracle Forum main page. In triplicate. Submit it to the moderator. Wait for an urgency verification key to be supplied by the moderator. And only then can you post your urgent posting by attaching the urgency key to it for verification purposes.

Since you did not do it, your account is being reviewed for a possible 6 month suspension. You will also be prohibited from practicing Oracle during that time as you have illustrated the lack of common sense by posting this totally uncalled for and unwarranted “urgent” posting in this forum. And not applying common sense when using Oracle can cause serious injury to your database, cause serious damage to the scalability and performance of your applications, and may just cheese off your Oracle DBA resulting in a lead pipe being taken to your knee caps.


William>A right outer join is just a normal outer join written backwards to confuse everyone


More forum fun


John Stegeman> Last time I checked (1 minute ago), there is no “PL/SQL for SIM cards”


John Stegeman> Or even a entry in the mystical magical caverns of the registry, if none of those are set.
Ed Stevens> Please!  I’ll do anything!  I wash your car!  I’ll mow your lawn! Just don’t send me to the registry!


Someoneelse> We are under attack!
The Database General forum is being flooded with spam!
Here are some of the userids:  …
What the hell, is this a new feature of Jive?

jgarry> You want Jive to pump up social media, Jive pumps up social media.

jgarry> On other places I’ve been surprised by being blocked for too much posting.
I’m really not a robot!  It’s hard to tune that limit right, and some people may compose things beforehand.
But worse, spammers would consider it damage and route around it, with whatever they need to do to have numerous logons.
Like when I knocked some fuzzy balls off the umbrella next to my pool and little black widow spiders scattered everywhere.

Dude!> There is already a feature in place that does not allow people to post one message right after another without waiting for a while; 5 min. if I remember correctly.

BluShadow> It’s 30 seconds Dude!, not 5 minutes.

Dude!> Ah well, time is relative

KayK> all you need is a DeLorean


Dude!> How long will it take until everything implodes?
Billy>  Everything? I assume you are limiting “everything” to our solar system?
In that case, around 5 billion years from now, our sun will run out if fuel, shed its outer layers, and implodes into a white dwarf. Unfortunately it is too small to become a black hole. Which would be a kewl thing. Size some time matters.
Everything as in the universe? Guestimate is a 100 or so trillion years – depending on the theory you deem most likely (of which there are more than a few) describes the end of the universe. Implosion is just one of the theories. Perhaps an Asimov’s The Last Question end and beginning?

Dude!> I don’t worry so much about 5 billion years from now — not even history of the past 100 years is correct.


Billy> Disk space is cheaper than the effort to rebuild tables and indexes in order to reclaim space – and to support this effort as SOP.


William> So ‘QTR’ means ‘Quarter’? What is this, Twitter?


Jonathan Lewis> I got to the end to the first line (after the Hi) and thought: “we’re going to see a match_recognize() solution from Stew Ashton here”.

He was right.


“Re: What is the difference between select count(1) from tab and select count(*) from tab;”

Well after some short ramblings about performance and table sizes the gurus discussion went on to the right track.

Dave> One press on the shift key on my keyboard
William> “count(1)” is a nonstandard variation that takes more keystrokes and requires the parser to substitute “*” in place of the “1”, while making the person who wrote the query look foolish.
If you want an approximate result for a large data set quickly, have a look at the SAMPLE clause, e.g.

select count(*) * 20 from somebigtable sample(5);

Frank> Actually, on my keyboard, ‘1’ takes fewer keystrokes (depending on how you count) than ‘*’.  To type ‘1’, I just press the ‘1’ key, but to type ‘*’ I have to hold down the SHIFT key and then press the ‘8’ key.
Even though it’s that much harder to type, “COUNT (*)” is still better than “COUNT (1)”, for the reasons you mentioned.
Jonathan> You may be taking too narrow a view on the problem – although the correct view may, of course, be keyboard-dependent.  You need to step back from the 1/* dichotomy and consider the effect of parentheses:on the problem.

On my keyboard (*) requires me to do:  {shift} 980 {release}   (a total of 4 keystrokes – or 5 finger movements)

but (1) requires me to do: {shift} 9 {release} 1 {shift} 0 {release}  (a total of 5 keystrokes – or 6 finger movements)

Note also that if you are a “classical typist” your are probably going to use {left shift}, which means a large movement to the 1, unless you use a numeric keypad – in which case the 9 requires you to make a large lateral movement with your right hand (which can then stay in place until after the 8 stroke, of course).

Youngsters these days! Just don’t think things through properly!   (;)
William> Perhaps the round bracket keys are not shifted on some keyboards? I don’t think I’ve ever seen that though.
rp0428>Can you provide a specific reference to ANY of your books or blogs that cover an advanced topic such as this?

Sometimes ‘youngsters’ can benefit from seeing the explanation in context with some example code, trace files and execution plans.
Ospin> Just to inform for people with spain keyboards, this keyboards has “(” in shift+8 and “)” in shift+9, so is quit bit easy type “(8)”, so less finger movements and same results
John> To really figure this out, we probably need sql_trace for brains and bodies – when is Oracle going to wake up and put a bunch of SQL coders under a functional MRI scanner and do metabolic analysis to determine the precise effort involved?
However, i’ll say this: even if select(1) was an order of magnitude easier to type than select (*) (which it’s not), the dissonance and mental stress caused by seeing select(1) is probably enough to kill a few million brain cells of my own (not to mention people who come after me and have to read my code)…


All time classics:
Frameworkia – the NEW PLSQL development standard

Apex and Jet – a fairy tale

In the old times, when it was still of some use to wish for the thing one wanted, there lived a King named Joel R. whose daughters were all handsome, but the youngest was so beautiful that the sun himself, who has seen so much, wondered each time he shone over her because of her beauty. The name of the little girl was Apex.

Near the royal castle build with bricks of Forms there was a great dark wood where birds were twittering and many squirrels were running up the trees. In the bark of some trees there were mysterious inscriptions from the foreign county of Java. And in the wood under an old js-tree was a font; and when the day was hot, the King’s daughter used to go forth into the wood and sit by the brink of the cool font. The font was simply awesome and if the time seemed long, she would take out a golden chart, and throw it up and catch it again, and this was her favourite pastime.

On her 12th Birthday Apex looked into a codemirror and saw Apex 5.1 always wearing beautiful purple boots. Now it happened one day that her boots were strapped so tight that the chart, instead of falling back into the maiden’s little hand which had sent it aloft, dropped to the ground near the edge of the well and rolled in. The king’s daughter followed it with her mobile-UI as it sank, but the well was deep, so deep that the bottom could not be seen. Then she began to weep, and she wept and wept as if she could never be comforted. And in the midst of her weeping she heard a voice saying to her: “What ails thee, king’s daughter Apex? Thy tears would melt a heart of stone.” And when she looked to see where the voice came from, there was nothing but a Toad stretching his thick ugly head out of the water. – “I weep because my golden chart has fallen into the font.” – “Never mind, do not weep,” answered the Toad, “I can help you; but what will you give me if I fetch up your chart again?” – “Whatever you like, dear toad,” said she, “any of my wizards, dynamic actions and interactive reports, or even the golden cloud that I wear.” – “Thy wizards, thy dynamic actions and interactive reports, and thy golden cloud are not for me,” answered the Toad, “but if thou wouldst love me, and have me for thy companion and play-fellow, and let me sit by thee at the Universal Theme, and eat from thy tables, and drink from thy views, and sleep in thy little pages, if thou wouldst promise all this, then would I dive below the water and fetch thee thy golden chart again.” – “Oh yes,” Apex answered, “I will promise it all, whatever you want, if you will only get me my chart again.” But she thought to herself: What nonsense he talks! As if he could do anything but sit in the water and croak with the other frogs, or could possibly be any one’s companion.

But the Toad, as soon as he heard her promise, drew his head under the font and sank down out of sight, but after a while he came to the surface again with the chart in his mouth, and he threw it on a nearby Peake. The King’s daughter was overjoyed to see her pretty plaything again, and she required it up and ran off with it. “Stop, stop!” cried the Toad, “take me up too. I cannot run as fast as you!” But it was of no use, Apex had no listener for him, and made haste home, and very soon forgot all about the poor Toad.

The next day, when the King’s daughter was sitting at table with the King and all the court, and eating from her golden plate, there came something up the marble stairs, and then there came a knockout at the door, and a voice crying: “Apex, Apex, let me in!” And she got up and ran to see who it could be, but when she opened the door, there was the Toad sitting outside. Then she shut the door hastily and went back to her server, feeling very uneasy. King Joel noticed how quickly her heart was beating, and said: “My child, what are you afraid of? Is there a giant Page Designer standing at the door ready to carry you away?” – “Oh no,” answered she, “no Page Designer, but a horrid Toad.” – “And what does the Toad want?” asked the King. “O dear father,” answered she, “when I was sitting by the font yesterday, and playing with my golden chart, it fell into the water, and while I was crying for the loss of it, the Toad came and got it again for me on condition I would let him be my companion, but I never thought that he could leave the application server and come after me; but now there he is outside the door, and he wants to come in to me.” And then they all heard him hammering the second time and crying:

“Youngest King’s daughter,
Open to me!
By the fonts water
What promised you me?
Youngest King’s daughter
Now open to me!”

“That which thou hast promised must thou perform,” said King Joel, “so go now and require him in.” So she went and opened the door, and the Toad hopped in. Then he stopped and cried: “Lift me up to install beside you.” But she delayed doing so until the King ordered her. When once the Toad was on the chair, he wanted to get on the table, and there he sat and said: “Now push your page a little nearer, so that we may eat together.” And so she did, but everybody might see how unwilling she was, and the Toad feasted heartily, but every jquery seemed to stick in her throat. “I have had enough now,” said the Toad at last, “and as I am tired, you must deploy me onto your server, and make ready your image folder, and we will lie down and go to sleep.” Then the King’s daughter began to weep, and was afraid of the cold Toad, that nothing would satisfy him but he must sleep in her pretty clean workspace. Now the King grew angry with her, saying: “That which thou hast promised in thy time of necessity, must thou now perform.” So she picked up the Toad with her finger and thumb, carried him upstairs and put him in a corner, and when she had lain down to sleep, he came creeping up, saying: “I am tired and want sleep as much as you; take me up, or I will tell your father.” Then she felt beside herself with rage, and picking him up, she threw him with all her strength against the browser, crying: “Now will you be quiet, you horrid Toad!”

But as he fell, he ceased to be a Toad, and became all at once a prince with beautiful kind shapes. And he told her his name was Jet and how the wicked witch of ADF had bound him by her spells, and how no one but she alone could have released him. Apex soon forgot about her old pal AnyChart and only had an UI for the young and beautiful Jet. And they two would go together to his father’s kingdom. And there came to the door an interactive grid, and behind the grid was standing faithful John Snyders, the servant of the young prince Jet. Now, faithful John had suffered such care and pain when his master was turned into a Toad, that he had been obliged to wear three iron libraries over his heart, to keep it from breaking with trouble and anxiety. When the grid started to take prince Jet to his kingdom, and faithful John had helped them both in, he got up behind, and was full of joy at his master’s deliverance.

And when they had gone a part of the way, the prince heard a sound at the back of the interactive grid, as if something had broken, and he turned round and cried:

“John, the other real data service must be breaking!”

“The ORDS does not break,
‘Tis the library round my heart
That, to lessen its ache,
When I grieved for your sake,
I bound round my heart.”

Again, and yet once again there was the same sound, and the prince thought it must be some ORDS breaking, but it was the breaking of the other library from faithful John’s heart, because he was now so relieved and happy.

The End


Other fairy tales to come:

Patrick Wolf and the seven little Shakeebs