Stump the Toad Chump!

thatjeffsmith Toad® Stuff (Retired) 21 Comments

Tell Others About This Story:

One of these babies could be yours!

Ask questions, get answers – and a Toadie!

He wants questions. You want answers (and a Toad). So, ask away – stump the Toad chump!

We’re looking for challenging questions dealing with Oracle Database and Toad, for example:

  • Can Toad do that?
  • I had this problem once where…
  • I hate it when…

The Rules!

If I like your question, you get a Toad beanie. I’ll answer any and all questions submitted. Please keep the questions to a technical nature, although if you have pricing or licensing questions, I’ll answer those off-line for you, no worries. The promotion is for our Public Sector Customers in the United States of America, but I am happy to take questions from any and all users from around the world.

We’ll run this gig for as long as people keep asking great questions, or until we run out of Toad beanies.

  • Leave your question as a comment on this page.
  • Be sure to use a ‘real’ name, so that I can track you down later to get your shipping information.

Bonus points for originality and humor.

We love our snark here at Quest Software 😉

Tell Others About This Story:

Comments 21

  1. Follow up to my previous question- I installed Toad 11 today, and really like the “memory sort” feature- EXCEPT that I have to right click and select it EACH time I do a retrieve. Is there a way to set that as the default (as opposed to the old-fashioned re-retrieve sorting)? I understand that this will cause all rows to be retrieved instead of the usual preview set of rows, but I am ok with that.

    Another quick question- Is is possible to use and save a custom color in the connections window? I have defined a custom color, assigned it to the DB profile I want it to apply to, and saved the profile. The color does appear properly around the associated windows, but will not display in the data connections grid. It says “Custom”, but does not show the color I assigned. This is more of an annoyance than anything else, but it would be nice to see the color I assigned in the list of connections.

    Thanks again!

    Mike Sinclair

    1. JeffS Post
      Author
  2. In my current environment I use these settings for PLSQL_WARNINGS, to enable all but a couple of specific warning messages.

    ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’,’DISABLE:07203′,’DISABLE:05018′

    Oracle’s “SQL Developer” has a Preferences setting that can cope with ‘ENABLE:ALL’ (and/or INFORMATIONAL, SEVERE and PERFORMANCE) but not enabling/disabling specific warning numbers.

    How can you do this in TOAD ?

    1. JeffS Post
      Author
    2. JeffS Post
      Author

      Quick thought Gary – have you tried putting
      ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’,’DISABLE:07203′,’DISABLE:05018′

      In a login.sql or glogin.sql script in your ORACLE_HOME and then enabled that in Toad? This allows you to set all of your session specific parameters automagically as your session is established.

  3. I am using TOAD 10.6.0.42. I hate it when I try to sort data in the data grid, and instead of just sorting the data I have already retrieved, TOAD re-retrieves the entire data set from the database. This seems a bit wasteful of network resources and time. If I have 500 rows in Excel and sort it by one column, the sort takes maybe half a second. If I have selected 500 rows from a table with over 5,000,000 rows, then try to sort them- THERE GOES TOAD- re-processing the entire SQL statement. Is there any way to make TOAD just sort what has already been retrieved?

    Thanks Guru!

    Mike Sinclair

    1. JeffS Post
      Author

      You are in luck! In v11, we now have a ‘memory sorting’ feature. In a data grid, right click and enable ‘memory sorting’. Toad will then fetch any remaining records so the entire dataset is in the grid. Any sorts done from that time on will be done in memory with no further queries sent to the database.

      Toad v11 is due to be release next week, so relief is in sight!

      Thanks for reaching out Mike, and be sure to let me know if you need any more help.

  4. Hi Jeff,

    To be clear, call it 80/20 – 80% I’m picking up an INSERT coded by someone else, 20% I’m coding my own from scratch. For writing my own INSERT, your solution is fine for generating code to load constants only, but usually I’m writing said INSERT into a PL/SQL procedure inserting a mix of constants and variables (using the values clause), or I’ve got an INSERT/SELECT command.

    The best of all worlds would be if Oracle offered an INSERT syntax that allowed one to parameterize VALUES clause (e.g. INSERT INTO T1 VALUES (C1=> ‘X’, C2=> LOCAL_VAR)) and some MATCH_COLUMNS syntax to do INSERT… SELECT… using the SELECT’ed column aliases to point to the desired INSERT columns (e.g. INSERT INTO T1 MATCH_COLUMNS SELECT ‘X’ C1, T2.C2 C2 FROM T2).

    So basically, what I’m looking for is a workaround to the lack of SQL support for such column-correlated syntax. It seems that TOAD should theoretically be able to parse a valid INSERT statement out of it’s existing context, present the developer with the actual column-to-value correlation, allow the developer to modify in that far less-error-prone context, and then reparse the changes back into the originating context.

    Yes, I realize the keyword there is “theoretically”, and there are probably 50 different things about INSERT variations that I haven’t taken into account… I’m just saying, it’d be pretty nice to have when I get the 3:00 am. call that some program failed with a “ORA-01401 Inserted value too large for column C34” and I need to figure out what went into C34. It’d also be nice for code reviews to check that the code is putting meaningful values into all columns.

    1. JeffS Post
      Author

      Gotcha Kevin. I have to admit we don’t have anything in Toad today that will do exactly what you want. Is there no way to build exception handling into your PL/SQL that will catch and log the bad values for ‘C34’ or whatever it happens to be? Or at least have the inputs validates before they are sent to the database?

      I hate to turn the tables back on you, but you might find relief faster there than waiting for this to be built into Toad.

  5. Feature request.

    SQL INSERT syntax sucks.

    Consider

    INSERT INTO T1 (C1, C2, C3,… C12, C14, C13, …, C73, …,C100)
    VALUES (V1, V2, V3,… V12, V13, V14, …, ‘My value’, …, V100)

    or
    INSERT INTO T1 (C1, C2, C3,… C12, C14, C13, …, C100)
    SELECT V1, V2, V3,… V12, V13, V14, …, V100 FROM T2;

    The bug in the above code (inversion of C14 with C13) is difficult to spot here, and is nearly impossible to spot when dealing with real-world INSERTs. Answering the question “what value went into column C73” is it’s own little exercise in patience.

    I would love to highlight a valid INSERT command, click a button “SHOW-INSERT-GRID”, and be presented with a grid listing the insert-table columns and the inserted values side-by-side.

    In such a Grid, the above example would look like:

    C1 | V1
    C2 | V2
    C3 | V3

    C12 | V12
    C14 | V13 <– much easier to spot the problem
    C13 | V14 <– much easier to spot the problem

    C73 | 'My value here'

    C100 | C100

    A read-only grid would be nice. An EDITABLE grid (where my changes in the grid can be fed directly back into the SQL INSERT syntax) would be astounding.

    So, to put as a "stump-the-chump" format, "Can TOAD do anything like this"

    1. JeffS Post
      Author

      Quick question before I continue: Who is writing the INSERTs – you, or someone else?

      >> An EDITABLE grid (where my changes in the grid can be fed directly back into the SQL INSERT syntax) would be astounding.
      Yes. Go to your table and it’s data grid in the schema browser. Add a new record, type in the fields. Select the row > Export Data Set > INSERT STATEMENT to Clipboard (with selected rows only option enabled). Toad will give you the INSERT for the row you just plugged in via the GRID.

      Example:

      Insert into HR.EMPLOYEES
      (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER,
      HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID,
      DEPARTMENT_ID)
      Values
      (500, ‘Jeff’, ‘Smith’, ‘[email protected]’, ‘011.44.1345.629268’,
      TO_DATE(’06/01/2011 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), ‘SA_REP’, 1, 0.2, 146,
      80);
      COMMIT;

      Maybe we can do better. Let me know what you think about this.

    1. JeffS Post
      Author

      We’re showing the plan either from an EXPLAIN, or pulling it from the SGA or from v$SQL_PLAN…so if you have a step that consumes the TEMP due to a SORT or HASH JOIN, then that should be recorded in the plan. To see it in Toad, you may need to mouse-right-click “Adjust Content” and be sure to toggle on ‘Temp Space’

      Here’s a purty picture for proof

    1. JeffS Post
      Author

      I think it can it can shorten the learning curve. If you are used to working with SSMS, a lot of Toad should feel familiar. Will it transform your T-SQL Kung Fu to PL/SQL Karate? No. It’s an interesting question though. Toad for SQL Server exists today in large part because of the emergence of SQL Server and the large number of our Oracle users who wanted a ‘Toad’ interface to help them be productive with SQL Server on Day 1.

  6. How about
    “Can you install TOAD to run from a USB drive or a straight unzip. Or do you need someone with admin rights on the particular machine to install it”

    1. JeffS Post
      Author
    1. JeffS Post
      Author

      Oh imagine all the fun we could have with users if such a magical thing existed! If you were going to get creative and use something like this, then yes, Toad would show that, just not as a trigger.

Leave a Reply

Your email address will not be published. Required fields are marked *