Kevin Meade's blog

Working with LDAP has made me appreciate the maturity of the Oracle RDBMS. That said, LDAP is pretty popular it seems. To that end my cohort in crime Dave Smith and I (Kevin Meade) have been tasked with many a work request to update LDAP entries related to database data. In integrating our databases and LDAP via the DBMS_LDAP package we came across this error. A quick Internet search revealed lots of people with the same error but no answers. It turns out that the error is exactly what it says it is, but that finding the reason for it is another matter. Here we discuss what we think the error means and the three most likely ways to get it.

I saw one of those really interesting pieces of code recently. A guy wanted to run his transaction and make it fail when it was all done. Normally one would put ROLLBACK at the end of the transaction in order to undo a transaction's work. But this guy did not want to do that. He wanted to keep his transaction code unchanged, commit at the end and everything. He had several reasons for this, among them being that he did not have access to all the code he was working with and thus could not put the ROLLBACK where it was needed, and indeed suspected (as we eventually found to be true), that somewhere in the code stream there was a commit being done without his permission thus splitting his transaction in ways he did not intend. So he wanted a way to FOREORDAIN (determine ahead of time) that his transaction would fail no matter even if it went to conclusion without error. For this he came up with a I think a clever hack. Seems to me this might have some use, if I can figure out what that use might be. So here is the cool solution.

Recently a friend asked me for this. I see it a lot on OraFaq as a question in the forums so here are the basics of working with delimited strings. I will show the various common methods for creating them and for unpacking them. Its not like I invented this stuff so I will also post some links for additional reading.

Star Schemas are proliferating with warehouses these days. Many practitioners I have met in this space are a bit new to the concept of star schemas and as such keep falling back to old habits. But this is only hurting them. So I'll try to give my simplistic view of how it works in the hopes of granting some clarity on the practice of Star Modeling and overcoming our previous training to resist its concepts.

Recently I have noticed there has been considerable talk on the WEB about date logic; in particular talk about overlapping date ranges and how to detect them. I can't say why there is such an interest, but some people have turned to an undocumented feature (a function called OVERLAPS) to solve their problems. This article will describe date overlap, and show equivelant SQL that will allow you to avoid using this undocumented feature which as always is a good idea, because undocumented features in Oracle have propensity to change and/or disappear unexpectedly leaving those who foolishly realied upon them in unanticipated trouble after an upgrade.

In a previous article I talked about using Natural Oracle Features to get XML data out of an Oracle database, and put data into an Oracle database, without actually having to learn a lot about XML related technologies. Still, there are many who continue to resist using OBJECTS, COLLECTIONS, and INSTEAD-OF-TRIGGERS in their daily work with the Oracle RDBMS. Since the biggest hurdle with XML seems to be parsing it into its component data elements, here is a more programming oriented approach to parsing XML data inside Oracle that should make this crowd happy.

I have been asked several times recently, how to create "dynamic" views such that a view can be created using "variables". Now I see this question asked on the OraFaq message board, so I thought maybe this is worth posting as an article, so here it is. My answer comes in the form of an Email reply to a friend (why bother to rewrite a prefectly good answer). Please feel free to abuse my answer if you think it deficient, but you better be posting your own alternatives if you do so.

In short an Autonomous Transaction lets a job, commit some data to the database as an ON-THE-SIDE event, without committing data in the MAIN-EVENT. Sounds useful and it can be. But the Autonomous Transaction can also be dangerous. An Autonomous Transaction is kind of like a teenage daughter (I have two) "Oh DAD! You just don't understand me". A lack of understanding is a foundation for trouble. Maybe we can’t understand our teenagers, but we can understand Autonomous Transactions a little better.

So here I am writing even more documentation for my current Gig, and thinking once again, why not post it to OraFAQ and get double duty out of the document. So here is a discussion of the WITH clause that comes with the SELECT statement now. It is easy to use, and handy as all get out, but seems many people have not yet noticed it. Hmm... a SELECT statement that does not start with SELECT.

Analytics are magic. But as with most software products; I seem to use only one percent of the features, ninety nine percent of the time. Indeed, having built warehouses and reporting systems for the last eighteen months, I look back and see that I got a lot done with only three Analytics, SUM, LAG, and LEAD. Knowing how intimidating Analytics can look to those who have not used them, I figured I’d show the uninitiated, how to get in through the back door, with a reduced look at the capability. You can do a lot with very little. We are going to discuss just one Analytic, SUM used to create running totals.