In this interview, Peasland discusses why he doesn't have much use for the planned XML upgrades in 11g and talks about the fact that many users he speaks with are hoping the next version of PL/SQL will include a better way to trace the PL/SQL call stack. Peasland also offers advice for folks getting ready to take the Oracle Certified Professional exam for 10g and offers some words of wisdom for database newbies and veterans alike who want to make sure that Database 10g is properly locked down.
Oracle says the next version of its flagship DBMS -- Oracle Database 11g -- includes new XML-related upgrades. They focus on XML DB and include a new XML binary data type and a new XML index. Are these types of upgrades important? Why or why not?
Brian Peasland: To me, most of the XML stuff inside the database is not important at all because XML is structured in a hierarchical format, and it was proven in the 1980s that hierarchical formats for data do not allow for fast, efficient retrieval of that data. I've always been confused when they start putting XML format into a relational database. We can't simply query one [piece of] XML data in one table and compare it to another one quickly and easily. Doing so normally means that you have to break the data apart. I've never been a fan of putting XML inside the database, but I haven't had a need to really do that. I do know some people who do need to do that and it does make some sense for their specific application. But they're storing XML more like someone would store a Word document or a .wav file inside the database. They're not storing it as corporate data that they want to query on later. They want to actually store XML as a complete file, not as pieces of data.
Why do those people you speak of store XML as a complete file?
Peasland: For instance, the new version of Microsoft Office is going to XML as its standard format. Instead of storing the old Word-type proprietary document format inside the database, maybe they want to store this new XML format inside the database as a document. And then they can run something like Oracle text against that to do rapid searches of documents. But that's a different use than what I see some people trying to do, where they're taking corporate data such as a list of employees and storing that as XML inside the database. To me, those attributes and those rows which represent an instance of that entity should be stored in a relational table.
What types of PL/SQL changes would you like to see in Database 11g or other future releases of Oracle's DBMS?
Peasland: A lot of people are asking these days for easier ways to trace the PL/SQL call stack. To be honest with you, I'm not sure why, but I actually come across people who want to do that. One guy wanted not only to be able to trace it but to be able to store it. The DBMS_DEBUG bug utility has been around for a long time, and with that you can trace your PL/SQL call stack, but it's very cumbersome to use. SQL Developer has come along to help out [in this regard]. But something like that inside the database would be nice.
What other PL/SQL change would you personally like to see?
Peasland: A change that I've been looking for is something that is actually done quite easily in SQL Server, but it's not done so easily in Oracle's PL/SQL. In SQL Server, I can take a stored procedure and have it just spit out the data almost as if that was a query itself. In Oracle, in order to do that, you have to make special data type as one of your parameters and then query out of it. You have to use a Ref Cursor or something like that. It's a little more cumbersome, and it would be nice if you could do that. Other platforms let you do that and Oracle doesn't.
What do you think of SQL Developer as compared with similar products?
Peasland: One of the things I liked about SQL Developer was that I was able to do some quick queries in SQL Developer the same way I do them in SQL Server. The ability in SQL Developer to comment out parts of my query but still leave them there for instant recall by just un-commenting them, is a great way to debug stuff and to run queries for different testing purposes -- definitely not for production use, but this is a developer tool.
Your Web site includes a document of notes designed to help people pass the Oracle 10g Oracle Certified Professional exam. What advice can you give to folks planning to take that exam?
Peasland: The big things that I tell people are, one, read the documentation on the new features, because almost everything on the exam is in the documentation. That's huge. And I know people are afraid of the documentation, but I'm kind of a proponent of it because a lot of the answers to questions I get [as a SearchOracle.com site expert] are found right in the documentation. Read the documentation because it gets you used to that sort of thing. The other thing is, practice what you learn, because as you practice it you will start to learn some ins and outs of how that new feature works. Then, intentionally try to break things. Obviously, you want to do this on a test system that you have available to yourself and no one else because you don't want to screw up anybody else's work. In the process of trying to break things, you'll learn a lot about that new feature.
Your Web site also includes some information related to policy violations and securing Oracle 10g. What are some good ways to make sure that Oracle 10g is properly locked down?
Peasland: One of the policy violations that comes up with everybody, even if you install Oracle straight out of the box, is that there are a couple of supplied packages that are made available to the public, meaning that anybody in the database can use them. Having the ability to use some of those packages can lead to security holes. For instance, one of those is a package called UTL_File. It lets somebody in a stored procedure interact with the file system that the database server is running on. And you probably don't want anyone just writing files because they have the potential to cause damage. Also, some of the well-known Oracle experts have pointed out exploits in things like UTL_File that have let people exploit the database. We hear about security holes like these all the time, and it's not just related to Oracle. Users in the database should have the concept of least privilege, which means they should have only the minimum set of privileges they need to get the job done.