ThatJeffSmith

More PL/SQL Fun in Toad version 11

You have some SQL in your PL/SQL program, and you need to execute it to see if it’s doing what you want. Today that means you need to extract the inputs, replace with binds, and remove the INTO syntax.

What if Toad had a ‘magic button’ to do this work for you?

In version 11, this long requested feature will become a reality. Actually if you are participating in the Toad for Oracle Beta program (link), you can take advantage of this feature today.

An Example

CREATE OR REPLACE FUNCTION QUEST_OPTI.qctod#employee_salary (
   employee_id_in IN qctod#employees.employee_id%TYPE
)
   RETURN qctod#employees.salary%TYPE
IS
   retval   qctod#employees.salary%TYPE;
BEGIN
   SELECT salary
     INTO retval
     FROM qctod#employees
    WHERE employee_id = employee_id_in;

   RETURN retval;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END qctod#employee_salary;
/

A VERY simple program. One input passed to a query. If you want to execute the query directly in Toad, place your cursor inside the SELECT statement, and use +F9.

Toad will parse the text, identify the query, replace the inputs with binds, prompt you for the bind values, execute the query, and return the results to the data grid.

You can even preview/alter the query to be executed.

Step1: Execute

Start the Process with SHIFT+F9

Step2: Provide inputs as necessary

Step3: View the Results