In the last days, I touched hierarchical queries – it’s a strange field for a DBA, but I did need it for DBA desires: A sophisticated query on statspack. Why? Since oddly, the stats$sql_text table of statspack / perfstat DOES contain the full SQL for a statement, but fragmented over various lines. But I had to display all queries in one line, as one string. The hierachical function sys_connect_by_path() looked sexy for it, so I dived into for good. :)
But soon a problem occured: sys_connect_by_path() needs a seperator string, that does not already occur within the to-be-concatenated strings. Okay, this is understandable and rather easy to cope with: Just replace the known string out of the result, done. But most annoying, this seperator has to be LITERAL – no function is allowed there! So in my case, a cause-loop rised its ugly head: As soon as this literal string is stated, it will occur within the stats$sql_text table, because this table contains (nearly) all SQL ran against the DB. Whoop – I’m out of business?
Not really: the oracle-l mailing list saved me (again). This thread resolved the problem – if you ever should need a similar query, just have a look at the solution of this nice little puzzle: