GeeKs Blogging @ dotCOM

Query on the Long Column or Field

by Nitin Jain

---

A typical problem in any EAI project. Others too!!

One of the most common ways of building custom Error Handling or Logging mechanisms include dumping huge messages and XMLs in the database for future reference. It works as an excellent logging mechanism which can be pulled out in case something in the system fails.

This is generally achieved by using the “Long” column of the Oracle Database, and/or concatening multiple String type of columns.

Now, the typical problem. We dump the XMLs, fair enough. However, we can only pull out specific information from this XML and populate on some other columns, but, definitely not ALL the data in the huge XML that we have. We may pull out the Order Id, Reference Number and a couple of more fields, yet we definitely cannot drill out all the information.

However, the XML contains a lot more data. How do we query on the data in the Long column, in case the information is hidden deep into the XML, and is not saved separately in a String type column? I am assuming the Table based Logging mechanism.

I ran across something similar recently when I realized that I could not query on the Long Database column. Now, I have not discovered a special way of querying on the Long column that I am going to share here. Rather, this is a workaround.

Query for the relevant data in a traditional PL/SQL Editor like the SQL Developer or TOAD. You can still not run a query on the Long data type of column, but, with sufficient filters, you can reach out to a limited RecordSet around the actual record.

Right click -> Export -> TXT / CSV format and open the newly created file in a file editor. Run our favourite “Find..” tool to query for the exact data that you are looking for. And, voila, it could help you get as close to the actual record as possible.

Yeah, I agree if some of you out there are shouting, “Dumb, dumb, dumb tip!!”. But then, worked for me.

I had this typical interface which was just dumping XMLs in the Logs one after the other, without any identifiers. And, it was becoming terribly difficult to dig out the XML with a particular identifier. After all else that I could think of failed, this one worked like a charm.

I thank all my friends who were with me till late night, helping me get this through! Thanks, Madhvi. Thanks, Himanshu. Cheers..

Related posts:

1. Siebel EIM – Nomenclature of Custom Column Previous post regarding new custom columns mapping in EIM table...
2. Siebel EIM – Custom column mapping It’s a very basic concept explaining how we can generate...
3. Siebel – FINS Industry XML Query Service “Extracting values from a tag deep down in the Hierarchy.”...
4. Siebel – Looping multiple records – Query and Process In my last post, I discussed as to how we...
5. Siebel – Showing red asterisk on a conditionally required field In Siebel vanilla Application, we see a red asterisk (...
6. Siebel – Make MVG Field required Siebel supports the concept of Multi Valued Fields, or the...
7. Siebel – Make MVG Field required – II Hi friends, This is a follow-up post of the post...