FORALL was introduced into PL/SQL in Version 8i. It is a fantastic feature; you should use it in place of all loops that contain DML statements performing row-by-row processing. You will generally see performance improvements of at least an order of magnitude. 

If you are not familiar with FORALL, make it a priority to get familiar. You can start with my brand new 5th edition of Oracle PL/SQL Programming,...

Quest Software has two of the most powerful tools for PL/SQL development: Toad for Oracle and SQL Navigator for Oracle. Personally, I spend more time working with Toad for Oracle and I truly enjoy the solution. Before I proceed any further, I want to clarify a point. I am not advocating one solution over the other for PL/SQL development. That’s a conversation I refuse to have, because it borders on arguing which religion is...

We are pleased to provide two new command-line utilities that were developed for the next version of JProbe. The first can be used standalone to obtain basic memory usage reports from JVM memory dumps. The second can be used for controlling a JProbe Analysis Engine in a headless environment or for scripting JProbe actions.

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

 

Around 15 years ago, when I was a DBA both for Oracle and Sybase, a lot of SQL came from my colleagues which I needed to tune.  Initially, I enjoyed the satisfaction of tuning a SQL statement that ran in a few minutes down to a...

I recently published the following puzzle in the ToadWorld newsletter:

Which of the following queries return the names of programs (without duplication) defined in the currently connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?

A.   SELECT name FROM USER_DEPENDENCIES     WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE' B.  ...

It’s been a while since I wrote my data warehousing book, so I wanted to read something hip and new on the topic. Lucky for me someone suggested that I examine “Oracle Warehouse Builder 11g: Getting Started”...

Quick, what’s the number one complaint about Toad for Oracle?

“The User Interface is waaaaaaaaay too complicated!”



Out of the box, Toad’s setup to pretty much have everything enabled. If we hid stuff, there’s a good chance users would assume the functionality just wasn’t there to be taken advantage of.  And, as John so elegantly put it in his latest whitepaper on QuestDotCom,...

Many people these days have to work on both traditional “online transactional processing” (i.e. OLTP) systems as well as data warehouses. But just what is a data warehouse? Generally speaking a data warehouse is nothing more than a specialized business reporting system used by mid to senior level management to research and develop a tactical plan and/or true long term strategy. Thus in many cases while these systems may be considered mission critical, they are not really true 24 X 7 systems. Often data warehouses are utilized during normal to extended business hours, with some batch activities for data loading and aggregation/summary calculations.

...

Did you know that in Code Tester 1.8.5 you can now perform in-line validation of your customization code? For more complex tests, this will save you lots of time and make it easier to build your regression tests.

 

The ultimate objective of Code Tester is easy to state: allow developers to test arbitrarily complex PL/SQL programs without writing any test code....

I want to start by thanking everyone who participated in the free TOAD book contest by submitting new feature ideas or enhancement requests. I received numerous good ideas. I also received about a third that were already in TOAD – and have been for a long time.  I actually can benefit from those emails too, since Quest can probably better expose and/or offer training on those issues. Remember too this was an entirely subjective...

I was recently in Germany, and did an interview with Erik Franz of database|pro. One of the questions he asked me was: "Which programming language would you recommend a college student learn to give them the strongest chance at a good job upon graduation?" I would have loved to be able to answer: "Learn PL/SQL!

Oracle introduced support for XML in the relational database going back at least to about the 8i release. Toad for Oracle introduced formal support for XML in the 8.0 release.

XML and Toad v8.0 – v9.7



As you browse or query an object, if it contains an XML

...

So with Toad v10 being released, my Tips & Tricks guide became a bit out of date.  You can find an updated copy, as well as our v9 copy, here.  All of your favorite keyboard shortcuts and workflows remain largely unchanged, so this is more of an update for making sure screenshots and naming conventions are correct...

 In one of my previous lives, I worked at a startup company testing software. Our application used Oracle as the back-end and the nature of my role had me working closely with our Oracle DBA. As part of our processes, our DBA had to build documentation describing the schemas our application needed. The reports included things like table names, column names and their data types, indexes, etc. This used to take him hours until...

As I write this, I am flying back to Chicago now, 9 PM Oct 21, from Mexico City, after a very nice two day visit.

 

Quest Mexico asked me to come down to help celebrate the release of Toad Version 10. I said "OK."

 

And, wow, they did a really fantastic job of making me feel appreciated. Besides...

When designing and constructing a successful (i.e. effective and efficient) relational database, there are two fundamental sets of rules or design principles that are universally accepted and generally practiced by database architects.

Steven discusses how LTRIM works and an alternative solution for a user's problem.

TOAD offers three distinct ways to connect to your database, as shown by the three tabs circled below on the connection screen. In this week’s blog we’ll examine the differences between the first two: TNS and Direct. No matter which of the three methods you choose, TOAD requires the Oracle “SQL*Net” client to be on your PC. TOAD needs to talk over the network to your database, and that requires certain Oracle network DLL files...

A little known, but very handy feature of PL/SQL is the ability to apply set operators, like union, intersect and minus, to nested tables.

In case you missed my announcement on the Code Tester community forum, we have now posted a beta version of 1.9.

 

The major focus for 1.9 to date has been to add support for automated testing of object type methods. This beta release offers the first glimpse of this functionality; you should now be able to define tests for constructors, static methods, and member procedures and functions.

...

Written by Steven Feuerstein   Code Tester is a relatively young tool (first released for production use in February 2007), but it has matured quickly (hey, at least I think so!) and is packed full of handy features.

 

As with all other tools, however, it can sometimes be hard to find the "jewels," so I thought I would highlight a couple.

 

1. Dynamically generated test cases

 

Rather...

Undocumented functionality in TDA, Toad for SqlServer, Toad for DB2 and Toad for MySQL to use advanced flow control when executing scripts.

The very latest generation of Toad for Oracle is about to hit the streets (early October) so we thought we’d give you a quick preview now so you’ll be up to speed when it’s released. This blog is part 2 and covers more of the new features and updates.

JProbe's new JDBC component nodes help link Java and SQL performance analysis together, to identify the root causes of performance issues in your applications, and to help you resolve those issues.

As DBAs it seems that we are always faced with issues of Database Performance. As gate keepers to our multiple database environments many of our Service Level Agreements (SLA's) can be directly tied into the health of our databases. Working with Quest's Toad solution we can now take a comprehensive look at the health of our databases with the DB Health Check.  Note: This Toad feature is only available in the commercial version...

Version 7.5 of Quest SQL Optimizer for Oracle will be released in October. The major effort for this new release was in the optimizer engine and the new UI for the optimization, index generation and execution functions. The following is a screen shot of the new UI. It looks tidy and easier to use compared to the old version. All the statistics names now use the Oracle standard naming, so, users can easy understand each statistics...

With General Availability of JProbe 8.2, here is a preview of some of the changes and improvements that you will find in this new release:

The JProbe 8.1 release focused on improvements to JProbe Memory analysis. The JProbe 8.2 release focuses on improvements to JProbe Performance analysis.

Users asked us for the ability to track the runtime execution status of threads. JProbe 8.2 introduces a new Threads sub-tab...

Last week’s blog was part 2 of a 3 part series about techniques for optimizing a notebook PC for running Oracle database within a virtual machine. In part 1 I detailed my “top ten” ideas for proper host (i.e. notebook) configuration. In part 2,...

Sometimes, I’m asked to help a customer work through a particular challenge they’re facing when using one of Quest’s solutions. Many times, the meetings are one-on-one sessions with the customer where we take turns sharing our desktops to better understand the problems being faced and how the solution might be able to address the problems. I actually enjoy the one-on-one sessions, because it gives me deeper insight into how our customers are using our solutions. Plus, it’s a great way to meet and connect with people, which is the part of my job I enjoy the most. Yes, at times, I’ve been called ‘weird’.

...

The very latest generation of Toad for Oracle is about to hit the streets (early October) so we thought we’d give you a quick preview now so you’ll be up to speed when it’s released.

Last week’s blog was part 1 of a 3 part series about techniques for optimizing a notebook PC for running Oracle database within a virtual machine. In part 1 I detailed my “top ten” ideas for proper host (i.e. notebook) configuration. This week I’ll offer a comparable...

Written by Jeff Smith, Quest Solutions Architect

Toad is a brand at Quest. We have several tools that have been built explicitly for developers and administrators in the Oracle, DB2, SQL Server, and MySQL universe. After gaining the following of about 2 million users over the past decade we realized that more than a third of our users did not fall into the ‘developer’...

People have often asked me “How do I optimize my Oracle virtual machines running on my meager notebook PC?” So I decided to put together this quick checklist to assist with just that question.

 

Let's assume that you have the typical recent or last generation of notebook PC, that is one with a dual core CPU and say a max 4 GB of RAM. Here's how to optimize your Oracle VM on that hardware. The advice below are...

Check out this video. IBM claims to now provide native support for PL/SQL programs, including support for many built-in packages, collections, etc. Wow! Lots more details here, but you will find below some



...

Written by Finn Ellebaek Nielsen. Learn about automated testing and much more at the OPP2009 conference November 10-11 in Atlanta, Georgia, USA.

Written by Finn Ellebaek Nielsen. Best practices for determining test amount required through analysis of product risk.

Like most applications, Preferences or Options enable the user to make changes to the default configuration to suit the way they work. The same is true for Toad but there are so many, for some it can be a bit of a challenge finding what they are looking for.

 

The objective of this blog is to help you get a handle on what the main options are that you probably need to know about.  Note that the screen captures...

While TOAD offers numerous capabilities, many people often spend a majority of their time simply “browsing” existing table data. Even when writing SQL or PL/SQL code to perform some business function or action, developers need to see what the data looks like in order to write the code. And of course business analysts and managers often look at the data in order to spot patterns or drive report design. For these needs people...

Managing and working with large scripts can be challenging. Failures during the execution of such scripts can make a person downright cranky. Personally, I’ve never liked scrolling through hundreds of lines of code to isolate the problem in a script, hopefully correct the issue, and then attempt to restart the execution at the proper line. For those of you using Toad for DB2 4.5, you may not be aware of a new feature that...

When the going gets tough and budgets get tight, money for training is often cut first. You still, however, need to learn how to best leverage the PL/SQL language and write high quality code. To help you, Quest is offering a three-part, webcast training series that I will be teaching. Each session consists of two hours of in-depth coverage of a specific functionality area or best practice.

 

The cost is far less...

What a big relief....I was in the beta program, checking out the new features of PL/SQL in this latest release of the Oracle database. That's quite a privilege, but also a burden; Oracle is very severe about not wanting us to talk about upcoming features before they officially release the product to their adoring public.

 

That's tough on me, because I have a hard time keeping my mouth shut about anything, especially...

Face it, we’ve all been there – that moment when we’ve done something a wee bit too fast and then realize we probably shouldn’t have done it. Then we often generally say something like “stupid database” or “stupid TOAD’ or “stupid whatever. But there are ways to recover without having to go hat in hand to the DBA and beg forgiveness and obtain a recovery. More recent versions of Oracle offer several wonderful “Flashback” options...

Under the Editor menu in Toad for Oracle we have the “Make Code” & “Strip Code” features which allow you to embed SQL Statements in a programming language of your choice.

 

For example you can take the following statement:

SELECT   b.order_date, d.price, a.cust_id, a.NAME, c.qty, d.part_id,         d.qty_on_hand    FROM quest_dev.ow_customers a, quest_dev.ow_orders b, quest_dev.ow_order_lines c, quest_dev.ow_parts...

Have you considered switching to Toad for Data Analysts but have too much invested in your old query builder? Have you ever looked at a query with a complex join clause and wished that you could see it mapped out visually? You're in luck - Toad for Data Analysts can help! Toad for Data Analysts can take your SQL statement and reverse engineer it into a visual Query Builder. In this blog post, I’ll show you how to do it quickly and easily!

[ It is very likely that if you are reading this you are a developer, but this is really geared towards software managers. So feel free to copy and paste into a Word doc and pass it along to your manager or team lead! ]

 

I very often give trainings and presentations on the topic of PL/SQL best practices. I offer lots of ideas for writing code that is easier to read and maintain, runs more efficiently, can be...

Written by Rene Woody

This blog is a continuation of a series about test running the SQL statements in the Tuning Lab in Quest SQL Optimizer for Oracle to find the best performing SQL statement in your database environment. It covers the expectation that Quest SQL Optimizer will always be able to improve the performance of your SQL statements.

...

I have written a book about Optimizing Oracle on VMware, plus have both a paper and presentation regarding the same. Furthermore I have also written blogs about Virtual Paranoia and a four-part series Oracle + VMware = Success. These basically all espouse essentially the same fundamental belief – that database virtualization is here to stay, and is rapidly moving towards mainstream.

 

But a couple legitimate questions or concerns are always brought up (and they are good questions). I’m going to address the three that I hear most often, because I believe having the answers to just these will knock down 80+% of all the roadblocks to virtualizing your databases.

...

This blog is the fourth in a series about misconceptions surrounding SQL tuning that are quite common. It covers the misconception that the goal of SQL tuning is only to make a SQL return all the records faster.

I still remember around 20 years ago, I was using SQL*Plus to tune my SQL statements. At that time, it was my impression that a good SQL rewrite was one that normally would return the records faster.  No SQL...

One critical task for DBA's is maintaining their database versioning history. For example the database objects were in some specified state (in terms of their construction) on such and such a date. The key reason this is critical is that application updates and bug fixes often require database modifications. So the DBA must maintain a database versioning history that matches such changes. There are two chief reasons for this:...

By default, adding an entity to a Workspace will cause this entity to be added to every workspace within your model. Well, the entity isn’t really being duplicated; it’s just that a shortcut to that entity is being added to every workspace in the model. Recently, we’ve received some questions from customers asking how they can change this behavior. Fortunately, it’s really easy to do. Before I tell you how to change this...