DBMS_OUTPUT is a technology that many Oracle people will just assume you already grok. It is a very basic building block for Oracle PLSQL programs. If you do not understand it, then your first attempts at scripting will likely be very frustrating. So in a nutshell from the Oracle Docs – The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.…
This is a pretty old topic as far as SQL Developer goes. There are public articles going back to 2007 that show you how to extend the functionality in SQL Developer via SQL and XML. But I’m going to guess that for many of you that this is a new subject. Knowing that extensions exist versus being able to create one is an important distinction. I want this post to help you realize both! We’re…
You run a query. You wait patiently for your query and results to return. You want the results in Excel. You ask SQL Developer to ‘Export.’ SQL Developer seems to start over by running the query again. You are no longer waiting patiently. How can you avoid this? The solution is to ensure all of the data has been fetched into the grid before you ask for the Export. What the heck is he talking…
Javadoc is a tool for generating API documentation in HTML format from doc comments in source code. Wouldn’t it be nice if a similar tool existed for PLSQL? Well you’re in luck because someone already made that available via an open-source utility known as pldoc. Automatically document your database schema with SQL Developer using the DB Doc feature. Generate HTML pages and allow your teams to click through the PLSQL source and tables without access…
Near the end of a product demonstration with a customer yesterday, someone asked how they could build physical models for each of their different environments using Oracle SQL Developer Data Modeler (SQLDev.) Imagine having a application and database setup ‘out of the box’ that adequately described your meta data. But, you might have one implementation of that system for a very very large customer that required database partitioning. The physical aspects of that environment could…
I received a very interesting question from a reader the other day, which is awesome for two reasons. One, it gives me something to think about and play with. And ‘B’, it provides me with material for today’s blog post. So here’s the original question – We would like to capture inofmation like who, when and why a user connected to a database in production using SQL developer, so i was wondering if is it…
Just because you can do something, doesn’t necessarily mean that you should. Case in point, database object names. Yes, you can call a table in Oracle pretty much anything you want, including ‘TABLE.’ Using quotes allows you to do some pretty silly things like use reserved words, mixed case names, and worse. On a call today someone mentioned that they found it odd that you could have a table and an index share a name.…
Debugging PLSQL is one of the primary features of Oracle SQL Developer. Yes, its the IDE for the database, but PLSQL is the database’s programming language. It really, really needs to support all things PLSQL. Programming languages need modern day amenities if they are to be adopted by mainstream developers. One of those amenities is a debugger. A primary challenge I face as an advocate for Oracle, PLSQL, and our developer tools is that database…
One of the coolest parts of my job is that I actually get to use the software that I help manage. The end-user perspective is hard to imagine sometimes, but when you ARE the end-user things become clear as day. This morning I spent quite a bit of time in DataPump and the SQL Developer Cart. I needed to copy a schema from databaseA to databaseB. Everything’s relative, and this goes double when it comes…
So while hanging out with a bunch of co-workers last week, someone mentioned they had this trick. The challenge is knowing how many rows you have in your result set before you actually read them all. You see, Oracle has no way of telling us how many records are in our data set before we actually fetch the entire data set. Yes, Oracle gives us %ROWCOUNT, but that only tells us ‘…how many rows affected…
