Today, a coworker brought me a strange SQL that produced ORA-018 error on one test Oracle instance and not in others. I looked at the SQL that read something like below. It was flawed with same table joined in twice, unnecessarily:

Assuming 2 tables employee and department_team and both having department_id and team_id, here is a sample SQL:

SELECT employee.employee_id, department_team.department_id,
department_team.department_name, department_team.team_name
FROM employee
LEFT JOIN department ON employee.department_id = department_team.department_id
LEFT JOIN department ON employee.team_id = department_team.team_id
;

This gave error “ORA-00918: column ambiguously defined” in one instance, while other instances produced result.

I told them to fix the SQL, so it will run on all instances. The fix was to remove redundant join:

SELECT employee.employee_id, department_team.department_id,
department_team.department_name, department_team.team_name
FROM employee
LEFT JOIN department ON (employee.department_id = department_team.department_id
AND employee.team_id = department_team.team_id)
;

There may be other scenarios, where you genuinely need same table joined multiple times. For e.g., if you want to if an employee is a manager that manages 2 departments and we want to list out employees in both departments. Here is an example for that:
https://www.queryexamples.com/sql/select/join-same-table-twice-in-sql-query/

In either case, having the same name (be it column or table) repeated twice should throw the above error. But Oracle didn’t, before 19.17! This was a bug in Oracle that allowed that weird SQL with double join work.

It was hard to find on Google, but I eventually landed on the below page that explains this bug beautifully!

https://mikedietrichde.com/2022/10/27/silent-ora-918-behavior-change-in-ru-19-17-0-and-newer/

Oh yeah, as for our databases the one where the SQL worked was indeed at 19.17 and others older! Our tables are more complicated than the ones I used in this example, but I just wanted to show a simple example.

Thanks for reading! Happy Coding!

Hope this finds you all in good health and spirits. Sorry I haven’t updated this blog for a while. Migration to Java, family illnesses, Covid times took away focus. Through the downtime, I kept in touch with technology by doing courses on Coursera, Udemy etc. I will share that too.

Recently, I got invited back to my original job after 18 months with better involvement in technology decisions. I no longer code in PowerBuilder, which was the motivation for the original site. But, I still love it and I do look at the old code (before Java migration) longingly. Now, I know a thing or 2 about the 2 platforms, I may have a comment. I am deeply entrenched in Java world now, so expect more in those lines.

I am also still using Oracle for the database. Earlier, my tips on SQL*Plus quirks were the most sought after. I recently found something about SQL Developer tool, that motivated me to come back here.

With the cloud migration project ahead of us, lot to learn and share. I hope to share tips, tricks and experiences here again. I originally, intended to create a new blog for it. But, I will try to continue here and switch, if there is a need for a new one.

I do this as a sort of “give back” to the IT community that helped me grow and pay my bills! Blogging was fun and it became a repository of memory refreshers for myself, when I had to return to some issues. It always feels good, when I get feedback about how my tip saved someone’s day. I got the best compliment when my friend said that a Serena (makers of PVCS) support person referred to my blog post as a go to page for certain issue in PVCS. That made my day!

I hope my new tips will help a few more (or may be my own memory later). Hope you will all give me the same support as you did before. Come see what I have to say, share your thoughts as comments, we can learn and grow together. Have a nice day!

I am signed up for an online course on Pirple. The course is called “Frontend Fundamentals” and covers HTML5 and CSS. It’s sort of back to the basic for me, I am taking it while waiting to get back into the workforce. I have worked on many websites, but never formally learned HTML or CSS. Often times, if I needed something, I would Google for the solution, customize it, paste it and move on. Copy-paste programming. Also, I only knew HTML4. Surprised to see so many elements in HTML5 and recently I saw the magic CSS can do. This time, I am determined to learn the basics, so I can do a better job on the front end as well and become a truly fullstack developer.

I am currently working on a project using HTML5 to create a web page that has several HTML elements including the new HTML5 elements and showcase a story/book I read. I chose to do a story and resorted to a storyteller site called telltale.eu.

I decided to use header and footer elements. Though, these may be just syntax sugar of DIV, they offer the possibility of better division and styling of content. These are called semantic elements as they offer a attached meaning to the content unlike the DIV. I also used other semantic elements such as form, article, aside, section etc. Here is a nice website that describes HTML5 in detail. I have included the short story in text as well as a Video element to point to the narration by Richard Martin. It’s a Vimeo video.

To embed the video, I was not able to just include Video Tag pointing to the video url. I had to go to Vimeo Video and click on Share to get the embed code. This contains an IFrame. See W3schools about iFrame – the site calls it an external document embedded within the HTML document. I probably needed to use iFrame, as I was embedding a web page and not a video (like .mp4) directly.

After this, I included a form to add user comments. I made use of Label…for and also “placeholder” attribute for input fields to clearly mark what the user needs to enter. I have included a Select field as well.

To top it all off, I included the new DL/DT/DD (Description List and items) elements to include a Glossary for the terms and names that may not be universally known.

The web page is plain and simple – it doesn’t have any style nor does it have any Javascript to add dynamic capabilities. But, I have generously used DIV, SECTION and ARTICLE elements and also id for the elements in hopes of styling them better when the CSS class is done.

In the project description, they asked us to use 5 elements that they didn’t cover yet. I think, I have used more than that – aside, article, section, ds, dt, dd as well as video, form, input and Select elements.

If you are interested in web building and you want to start from scratch, this course on Pirple is definitely a winner. For tools, you can use variour editors on the desktop, but I prefer to do it in jsbin.com, where you can actually create HTML, CSS and Javascript based web pages and share it with others. Here is a snapshot of my html file on JSBin.

We all use Bluetooth. It’s on your cell phone, earphone, laptop, eve in your car. We use it to link up 2 devices so they can connect wireless-ly. I always wondered what kind of technical term is that. We all know Blu Ray – a device with Blue ray (laser) used to read/write to the disk. Infrared – uses infra red to connect. What kind of ray is in Blue tooth?

Recently, while I was browsing something else, came across a little anecdote about the name. Would you believe, it may be referring to blue teeth a king had due to eating too many blueberries or due to tooth infection!

“

When one of the founds of Bluetooth, Kardach from Intel, was looking for a name for the new technology, had been reading a book about Vikings that featured the reign of Harald Bluetooth (Scandinavian Blåtand/Blåtann), whom he viewed as an ideal symbol for bringing competing Danish tribes together, as he explained:

Bluetooth was borrowed from the 10th-century, second king of Denmark, King Harald Bluetooth; who was famous for uniting Scandinavia just as we intended to unite the PC and cellular industries with a short-range wireless link.

”

Apparently, the name stuck when he couldn’t register the technical name for it in time.

The bluetooth logo is a combination of couple of Rune characters as well, as Wikipedia article puts it:

The Bluetooth logo is a bind rune merging the Younger Futhark runes  (Hagall) (ᚼ) and  (Bjarkan) (ᛒ), Harald’s initials.

https://www.pcworld.com/article/2061288/so-thats-why-its-called-bluetooth-and-other-surprising-tech-name-origins.html

We are getting into Redhat JBOSS for our Java project. I am currently exploring all the options available in JBOSS (also comparing it with Sybase EA Server we are using currently). I intend to share our experiences with the platform here.

One of the things I use a lot in EA Server will be the Jagtool – a command line interface to the app Server’s repository. In JBoss, I found Jboss-cli to do this for us. And I just found out from the below link, that you can actually start that in GUI mode as well. This link has a detailed information on the GUI mode. More on the tool later.

Source: A GUI for the Command Line Interface | JBoss Developer

Sharing:

Learn C programming by reading some of the best books from expert authors

Source: 5 Best C Programming Books A C Programmer Must Read

Sharing:

An exhaustive list of best hand picked free Eclipse plug-ins for a developer to be efficient

Source: 27 Best Free Eclipse Plug-ins for Java Developer to be Productive

I earlier posted about exciting world of Regex here.  Let’s try to learn some more in this followup post.

First if you want to play with Regex, here is a nice tool you can use:

http://regexr.com

Regex Tool online

By default it comes up with a sample pattern and the matches in the given sample text.  You can type in the pattern you are testing, the actual text in the middle box and if you chose Replace, you get to see what the output will look like.

Let’s use this tool to convert our RSVP list in free text to a spreadsheet list.

If you read my original post, we had 4 RSVPs. It looked somewhat like this:

RSVP:

Name:
Will you attend:
Will you be here for lunch:
How many:

Here is my session in Regexr testing this:

Using Regexr to test/convert our RSVP text

The bottom portion shows the Replaced text – converted and cleaned, readied to be copied out to Excel. The spreadsheet will look like this:

Here is the actual regular expression used in this example:

RSVP[0-9]+:\n+Name:[ ]*(.+)\nWill you attend:(.*)\nWill you be here for lunch:(.*)\nHow many:.*([0-9]+).*[\n]*

Let’s go through the regular expression for arriving at this:

First line would have, RSVP:

In Regex any number can be represented by [0-9]+ **. That is a digit is represented by any character between 0 and 9. A whole number would then be one or more digits.

Thus, RSVP: can be written as,

RSVP[0-9]+:

Next text is actually on another line. This can represented by \n. You may know this is newline. Now we are on the second line.

Name:<name>

This can be matched by, Name: (.*)

That would be any character after Name: . If you notice, we have a space after Name:. What if the user didn’t add any space or added many spaces in between? We can rewrite the above as,

Name:[ ]*(.*)

That’s a space inside the square brackets. [ ]* represents 0 or more spaces. This way, we will match it, even if there is no space between the label “Name:” and the name that followis it. If used with a +, it will mean one or more spaces – at least one space must be there.

Next we have (.*). Dot (.) is any character and .* means zero or more characters. By enclosing a pattern within parantheses (), we make a “Capture Group”. This is what will be remembered (like in a variable) when this pattern is matched with a text. This can be returned and useful in Replace functionality. Each captured group will have a number associated with it – like $1*** for the first, $2 for the second etc. This is also called back reference. More on this later.

And we continue with the rest of the lines. The replace pattern will look like this:

$1\t$2\t$3\t\$4\n

That’s all captured variables ($1 – $4) separated by tab characters recognized by Excel as cell separators (we could also use Comma). End it with \n to push to the next line.

Voila! We have successfully converted a free-form text into a spreadsheet.

That was a rudimentary example. And if you noticed, I left out some complications – for e.g., what if they said 1 or 2 people will be coming and I want to take the maximum, meaning 2.

The fact is, Regex is never used alone in a tool like this. Often times, you will build this (and more regex) into a script/program to achieve the full desired results. Perl and Python are heavily invested in Regex. Other programming languages (including Java) do support Regex using some library API.

To be contd…

** As an aside, this is similar to the BNF form for writing language grammar (I’ve considered only positive whole numbers for this discussion):

*** Sometimes you will see \1 instead of $1.