Microsoft has a lot to be proud of. They helped pioneer desktop computing. They have very successful platforms like SQL Server and Sharepoint. They are even working on a new mobile phone that doesn’t support copy and paste. But, I would argue their most popular product in use today is Excel.

...

By Vickie Farrell

According to Thomas Davenport in the Harvard Business Review article “Competing on Analytics,” successful analytics competitors understand their customers better and make better decisions by consistently making widespread use of...

I haven’t written anything in my blog in the last two months since I’ve been busy developing and testing the new version 8 of Quest SQL Optimizer for Oracle. It has been a long time since I’ve been as deeply involved in the development as I’ve been in last few months. In Chinese, “8” is a lucky number - it means prosperous and good luck.  So we have put a lot of effort to make it happen and hope this new version will bring you prosperity and solve more SQL performance problems than ever before.

I recently received this question from Syed:

"How does Oracle store the data for associative arrays and nested tables internally? Which type of collection is better for processing a huge volume of data without putting back into the database?"

First, regarding the question of "internal" storage: I have no idea how Oracle manages these structures. I expect that the PL/SQL development team uses some kind of linked list implementation. I also suppose that I could ask Bryn Llewellyn (the PL/SQL Product Manager) about this, but I am certain his answers will be:

...

I was recently demonstrating our SQL Optimizer for DB2 LUW solution when a question came up regarding comparing Access Plans in the Index Expert portion of the Optimizer.  Excellent question indeed!  However, before I go any further, perhaps a little background on the Index Expert is warranted.

The Index Expert allows you to simulate indexes for a SQL statement.  We use our technology to generate virtual indexes (i.e....

No, we have not added native support for Toad to run on a platform other than Windows. No, I can’t tell you when that will happen. In the meantime, I thought you might enjoy a story about someone who has successfully setup his iPad to run Toad. @BigJim is a friend of mine on Twitter and he reached out to me to let me know he had been able to set this up....

I spend a lot of time speaking to PL/SQL development teams. A conservative estimate would break down to about 200 presentations a year and 2,000 developers. I have several stock presentations, but I prefer to have conversations with my audience rather than just start throwing slides up on the projector. If we find a topic of interest, then I can jump into presentation mode.

When I get desperate for audience participation,...

I’ve been working with our Visual Studio add-in solution called Toad Extension for Visual Studio (commonly known as TEx).  This solution is basically a database schema provider that allows me to work with an off-line model of my Oracle schema as an Oracle database project within Visual Studio.  When it’s time to push your changes (deploy in Visual Studio), TEx allows you to specify deploying to a database, a script, or both.  Well, traditionally I’ve pushed changes strictly to my database, but I thought I’d poke around with the script option.

A lot of times people will post an issue to the Toad discussion forum or email me offline about Toad running too slow and what can they do. I’ve always told them to run the following two commands to correct the problem:

One of the cool things about my job is that I get to play with Beta versions of our software.  Actually, you can play too by going to the product’s Beta download page on Toad World.  I’ve been playing a little bit with version 4.7 of Toad for DB2 which, as you’ve probably guessed by now, is currently in Beta.  There are a couple of new features I find exciting in this upcoming...

Step by step instructions for changing the colour of report text based on the text’s value

ERD – Fancy way of saying Entity-Relationship Diagram

Since version 10.0 of Toad for Oracle, users have been able to select a table and have an ERD generated that automatically includes any related tables via Referential Integrity.

However, what if you are trying to document a view which may bring in one or more other views and any number of tables? Fortunately Toad has a diagramming feature that is not limited...

On 15 July 2010, 32 developers participated in the first-ever PL/SQL Challenge championship playoff. This playoff (consisting of ten questions to be answered in 15 minutes) was the culmination of a three-month competition in which PL/SQL developers from around the world tested their knowledge of PL/SQL against one another through daily quizzes.

This feature has been around for awhile in the Toad product family (SQL Server, DB2, MySQL, and Data Analysts), however it was upgraded for Toad for SQL Server, version 5.0.

Here’s a quick run-down:

Open the Editor Load up your T-SQL and get ready to have fun. You will need to activate the ‘Group Execute’ panel by mouse-right-clicking in the editor and toggling ‘on’ the ‘Show Group Execute’ item at the bottom...

Just this week, I was on-site with a customer and learned about a neat feature of Toad for Oracle 10.x’s new data grid component. Apparently, you can view parent table data from a child record. This was news to me.

Now there is a brand new, exciting way to share your ideas for things you would like to see in Toad for Oracle. You can also validate ideas that we or other users propose. www.ToadIdeaPond.com. With the Toad Idea Pond, you can sign in with your existing Yahoo account or create a new account.

Steven explains the difference between RAISE_APPLICATION_ERROR and RAISE and why you would use one vs. the other.

SQL is not an overly complex language in theory. The data manipulation language or DML consist of four primary commands: INSERT, UPDATE, DELETE and SELECT. Newer versions of Oracle also offer the MERGE command, which is an “upsert” – meaning it tries an update followed by an insert as a single command. Since the SELECT command is the one we use most since queries are the majority of database operations, let’s examine statement...

In this post I’ll finish the conversation with an overview of Toad’s Compare and Synch feature set.

If you're a .NET developer using Visual Studio 2010 Professional, then this is for you!

Many people firmly believe in Cary Millsap’s database optimization approach known as Method-R from his very popular book Optimizing Oracle Performance. That includes...

One aspect of Toad for DB2 drives me a little nutty. Specifically, it’s that the Object Compare feature doesn’t actually show me what’s different. Oh, it shows me that differences exist, but that’s not enough information for me. I want to actually SEE what’s different. I know, shame on me.

The Object Compare feature actually runs a compare and sync operation. The resulting DDL script generates the appropriate CREATE/ALTER...

We are working on some new SQL transformation rules for upcoming releases of Quest SQL Optimizer for Oracle. For every new transformation rule candidate, we have to do a lot of testing to make sure that there is at least some performance gain in some environments. But the most important task we have to do is to prove that the potential transformation rule is semantically equivalent before and after the SQL transformation.

Toad Extension for Visual Studio is designed to integrate your Oracle development process with your .NET process.

A little known feature in TOAD is the ability to monitor and track both the database’s disk space usage and disk IO patterns. Moreover TOAD offers the ability to perform projections for each as well.

That’s right folks! Toad for SQL Server has been recognized AGAIN as the best development tool for SQL Server.

Servers everywhere are being virtualized. Yet many DBA’s are hesitant to embrace virtualized database servers. I’ve been mystified by this anomaly, so I’ve asked those opposed for their rationale. While there are numerous arguments against, basically two pervasive themes surface from amongst all the replies.

First and foremost, DBA’s almost universally claim that their databases are “mission critical” and cannot suffer...

I recently needed to move the contents of clobs and collections to and from files, and also write simple reporting routines. So I ended up creating the following and uploading them to my demo.zip download:

export_clob.sql - move the contents of a clob to the specified file or to system output, if no file is specified.    file_to_clob.sql - load the contents...

Step by step instructions for hiding TOAD Report Manager report rows with no data – including example report

This is not a volley in the RDMBS wars. This is a quick cheat sheet for getting started using Toad for SQL Server if you are already used to using Toad for Oracle.

I really should be more honest. In this case I’m referring to the title of this blog. ‘Sneak Peek’ infers I’m being naughty and showing you something that is secret or not available to the masses. The real story here is that any licensed user of Toad current on maintenance is able to participate in Toad’s Beta Program which runs 24x7x12.

Version 10.6 is tentatively scheduled to be released this Fall. In the mean-time,...

For all you Oracle DBAs out there, have you often wished there was a better way to manage your Oracle RMAN backups? Have you often asked yourself - is there some way to have a single, consolidated report of which backups executed successfully, which failed, what type they were, how much space they consumed, whether there were policy violations, some sort of health check?

You can now download Code Tester 2.0. beta 2 from http://unittest.inside.quest.com/beta.jspa. We are making this beta available primarily to get your feedback on the overall direction of design and workflow. We look forward to that feedback, which you can post in the Beta discussion forum.

For those of you who have been living under a rock for the past decade, Toad is one of the most popular database administration and development tools for the Oracle RDBMS. It just so happens that Toad is also available for Microsoft’s SQL Server platform.

This blog serves as a quick introduction to Toad for SQL Server for those who may have just heard about it. It seems that many folks find Toad extremely powerful for...

Way back when Oracle10g was released, Oracle added compile-time warnings to the PL/SQL compiler.

When you enabled warnings in your session (or for a specific program), then at the time of compilation, Oracle will check to see if it can identify ways in which you might be able to improve the quality or performance of your code.

You can read more about the compile time warnings feature here.

...

I recently received an email from Syed asking this very question as follows:

"Could you please let me know why DDL statements are not allowed [natively] in PLSQL? I read somewhere that the reason is that DDL statements will cause objects to be invalidated and then require recompilation. As all objects are already in a compiled [VALID] state, compiling again will be an overhead. But when we execute DDL statements [with...

This blog is the seventh in a series about misconceptions surrounding SQL tuning that are quite common.

On 24 May 2010, I presented a one-hour webinar on some of the challenges we database programmers face setting up regression tests for programs that change database tables.

 

Here's the description:

"Most PL/SQL programs modify the contents of one or more tables. How can you verify that the modifications were correct? This is one of the biggest challenges facing PL/SQL developers. "Join Steven Feuerstein...

I wrote a blog a few weeks back titled TOAD 10.5 Freeware Runs on Linux. So here comes the second and more important part for many – getting the official commercial product installed and working on Linux. And to me great and very happy surprise, the install simply worked. Look at the screen snapshot below. It shows that I now have both the TOAD freeware and commercial installed and working side by side on my Ubuntu 10.04 Linux machine. Hurray!

...

When it comes to making sure your program works as it should, there are two general categories of test cases:

Tests of requirements: every requirement needs to have at least one test case devoted exclusively to verifying that the requirement has been met: the code performs as required. If you don't test for a requirement, then you do not know if it works. It is, ahem, theoretically correct. In other words, it is very likely buggy....

Toad is a robust and mature tool – so there are many more features than most of us can ever hope to master. But one that’s very useful yet widely unknown is the ability in the Schema Browser to perform intelligent/contextual jumps to related items.

What’s the first thing we learn when it comes to databases and/or SQL? Odds are it was how to write a SQL statement to retrieve data from one or more tables.

Step by step instructions for change a TOAD Manager reports name and or category

Today I want to talk about something that should be beneficial to any person who has ever needed to connect to an Oracle database, comparing data.

I’m running Toad for Oracle 10.5 freeware “natively via WINE” on Linux – specifically Ubuntu 10.04. Therefore you can now run the world’s favorite Oracle database tool on Linux. Goodbye Windows!

I recently received this question from a PL/SQL developer:

"What is the maximum size of a PL/SQL procedure?"

The answer is interesting: there is not a maximum size, per se. Instead, the limitation has to do with the maximum number of DIANA nodes your program generates in the compilation process.

DIANA is an intermediate language produced and used by the compiler. DIANA standards for “Descriptive Intermediate Attributed Notation for Ada" - and reminds us that PL/SQL is based on Ada, a programming language originally developed for the U.S. Department of Defense.

...

Quest recently released a new and updated version of the Toad for Oracle freeware. The old freeware was based on the old Toad 8.5 code base which came out before 10gR2, 11gR1, and 11gR2. So for example it had some problems supporting database versions and features that came out years later (e.g. 11g case sensitive passwords). The new freeware is based off the latest and...

It’s been about 2 weeks now, and I’ve already found a few things that’s I’d consider ‘upgrade worthy’ regarding Toad for Oracle v10.5. Whether they be groundbreaking new features, or just convenient behavioral updates, here a few things to look for when you’re ready to upgrade.