Generic Search Screen (and thoughts about blogs vs slides)

Talking about things that I missed here while not blogging... Years ago I wrote the whole series of posts about ways we can implement the most common part of any application - generic search screen (I even got some of those examples into my "PL/SQL Performance Tuning" book). That was a lot of fun, because it involved a lot of advanced features - object types, Dynamic SQL, XML, pipelined function etc.

Years later I finally decided to make a single-point-of-reference, where I aggregate all of the thoughts. And you know, I understand why people like PowerPoint - in presentation format it feels much more understandable. At least that's what I've heard from a number of my listeners. 

To be fair, I was surprised - personally I can much quicker go through textual format (book/article) comparing to various visually-enhanced content sources. I can still understand live webinar where you have a chance to ask the author direct questions... But listening to webcasts?! I even tried to do it on higher speed - still not my format. Summary: people are REALLY different :-)

Anyways, here is a link (if you still care):

https://www.slideshare.net/MishaRosenblum/building-a-generic-search-screen-using-dynamic-sql

Long-term IT survivability - educational impact?

Even though Memorial day is a big holiday here in the USA, I am still doing my tech-editing (quiet! I didn't name the book :) ) - got the new chapter a couple of days ago, and the publishing process should run no matter what is going on around the world. And of course, got a couple of thoughts that I wouldn't mind sharing ;)

When you talk about Oracle as a development platform, you can cleanly see two major patterns - either people are covering extremely advanced topics that only other gurus would understand, or people provide very light walk-throughs without providing real depth. As a result, in the IT you often see either over-utilization of Oracle platform or its under-utilization (depending on the type of available workforce).

Being educator myself for the last 20 years I often ask myself: how much knowledge is really needed to build systems efficiently? The current trend is that lots of mediocre developers is better, because they are predictable, and because if needed you can get more of them. As a result, wide knowledge of various platforms/tools is valued more than in-depth knowledge of smaller number of tools. I.e. IT education should be focusing on widening of the base + ease of switching from one environment to the other - but IMHO, that's a trap!

You cannot constantly jump from one solution to another - eventually you need to be able to provide-long term support and maintenance of existing systems (yes, rewrite is an option, but how often you can find THAT level of budgets?!)  And you, know - recently I made a presentation covering that long-term survivability of IT platforms, and I completely forgot to add it to this blog.

Here it is:
https://www.slideshare.net/MishaRosenblum/managing-the-earthquake-surviving-major-database-architecture-changes-rev2022

Back to live conferences

One of the reasons I decided to resurrect this blog is, to be fair, refresh my professional story-telling skills in English. As you may know, my native languages are Russian and Ukrainian, same for my family - and for the last two years I've been working from home. So, my English started to noticeably degrade. 

If you work online - everybody is more or less on their own schedule, so you would rather send email instead of calling, there is no coffee breaks etc. As a result, I started to write a bit more eloquently, but technology conversations lag what I would call "the natural flow": I have to search for correct words instead of searching for the correct intonations.

And here comes the key topic: why would I care, considering that nowadays significant number of IT people just work from whatever they call their base (home/hotel/summer house/winter house etc)? My answer - because I learn from inter-person communication significantly more than I learn from written text! The way people tell their stories is much more than "just content" - it is all of their feelings/struggles/discoveries. It is a lot of extras that you cannot just dig our from the article or a blog post.

I've been speaking at various IT conferences for the last 20 years - and it always amazes me how much people undervalue live contact. I understand convenience of your own chair - but stepping out of it could bring you so much more!

In about a week I will be flying to Las Vegas for the first in-person conference since the beginning of COVID pandemia - BLUEPRINT 4D (June 6/June 9). I will have my own presentation there on Wednesday at 9 am - "Through the Looking-Glass: Database Views". As you can guess from the title, it will be SQL and PL/SQL-heavy - well, that's my normal style. 

Hopefully, I will not stumble the same way I did at my first Oracle OpenWorld presentation (I had to simulate mic malfunction to buy some time and get back my voice :-) ). Two decades of experience are still two decades! And I really miss conversations with people, I really miss that great feeling of one big community that I had (and to be fair, that kept me within Oracle technology space).

Summary: person-to-person conversions are fun! Live events forever! :-)

Joys and Nightmares of a Tech Editor

Obviously, there was a lot of interesting things I did during all of those years since I more or less abandoned this blog. And filling in all holes would be more or less useless (since time had definitely changed). But there are some exceptions (as usual :-) ) - and one of those exceptions is that in 2019 I was pulled back to the book-publishing process! I am still to lazy to write something on my own (although, I have a couple of good drafts, so, well, "never say never") - but I don't mind looking over somebody's shoulder, especially if I care about the topic.

Frankly speaking, over the years I had mixed experiences - people are different, and not all authors take comments/critique in a friendly manner. But this time I lucked out - up to the point that I feel proud that I participated in the first book by Joh Heller (I bet, you may recognize this name from various extremely useful posts on StackOverflow)

But why I decided to write a post about my experience... Because working as a tech editor in Oracle technology space is a very tricky job, and lessons that I learned may be useful to other people. Let's do bullet points (prepping up for conferences!):

1. Anybody who've spent at least some time with Oracle tech stack knows that the most common answer on any question - "It depends!" I.e. there are different answers that could be valid within specific ranges of applicability. So, one of the main tasks for the tech editor is to keep the author from providing "universal" answers - only answers+conditions. 

2. Tech editor must always remember that it is not his book! The author has full rights to have his/her own opinion/style/favorites. As long as they are not factually incorrect, it is OK to not agree with the author - your role is to keep the author honest in front of the audience. I.e. you can point that something doesn't add up (or in some cases there are other approaches), but whether to follow this route - not your choice!

3. Even the best authors are sometimes very sensitive. So, treat them the same way you would talk to your teenagers :) - no direct orders, no harsh comments! Only suggestions, only very polite hints that this-and-that could be better. 

4. As you all know, teenagers can be VERY irritating from time to time - in that case you speak directly with the publisher (luckily, in this project I didn't have to use that "big hammer", but I had different experience too). Tech editors are hired by the publishing house, not by the author - so, your role is to be a safety check against bad quality books with technical errors.

Anyways, that was definitely fun - and here is a link on the current edition (yes, it is a hint :) ) 

Jon Heller, Pro Oracle SQL Development: Best Practices for Writing Advanced Queries

Return of a blogger (plus a bit of ORA-7445 [LpxMemFree()+200] )

Considering that "conference life" is picking up (two in-person events in June!), it is time to start writing something meaningful (yes, I am back to blogging after years of ignorance)! 

To cover event previews I need a bit more prep time, while today I would like to share a small story (narrow, but very practical!)

At Dulcian we have a couple of systems still on Oracle 11g (yeah, I understand that it is outdated). And today one of my support people started to complain about ORA-07445. To be precise - ORA-7445  [LpxMemFree()+200]. And you know how much fun is to sort out those errors, especially considering my gut feeling ("I bet, XMLtype is misbehaving!")

Obviously, first two steps were obvious: Google and MOS. 

Oook... Google gave me a direct hit - and I indeed was right, it was XML parsing issue - Doc ID 2220757.1 

And in case you don't have MOS access - here is a code snippet to replicate (it should blow up with "end of communication channel" plus corresponding trace file):

 declare
  lv_text varchar2(6000);
  lv_temp_xml xmltype;
  lv_len number := 0;
begin
  lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
  || to_char(sysdate, 'dd-Mon-yy hh24:mi')
  || ' '
  ||lpad('A',4033,'A')
  || '" />';
  lv_len := length(lv_text);
  dbms_output.put_line('Length of the 3rd run is '||lv_len);
  lv_temp_xml := sys.xmltype(lv_text);
 end;

The proposed solution was to enable some kind of "old parser" - you can do it on either session of system level. Considering that everything else in our system worked fine, I added it via Dynamic SQL just before the problematic code, so it will be less overall system impact:

 declare
  lv_text varchar2(6000);
  lv_temp_xml xmltype;
  lv_len number := 0;
begin
  execute immediate 'alter session set events ''31156 trace name context forever, level 1024''';
  lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
  || to_char(sysdate, 'dd-Mon-yy hh24:mi')
  || ' '
  ||lpad('A',4033,'A')
  || '" />';
  lv_len := length(lv_text);
  dbms_output.put_line('Length of the 3rd run is '||lv_len);
  lv_temp_xml := sys.xmltype(lv_text);
 end;

And the whole problem was gone! After a bit more digging I found that this kind of approach could help with a number of other similar ORA-07445 errors if you still on 11.2.0.4 (here is a doc from MOS: Bug 14822091)

Hope, it helps!

P.s. Footprint: in the doc they use hexadecimal level 0x400, but I prefer to use decimals (1024) for readability. What is funny, in the bug description whoever wrote it also used decimal - so, don't worry, it is the same stuff written differently.