DBMS_OUTPUT is one of the most well-known and least understood SYS packages. If you have questions about how it works in general, you might want to read my previous post on the subject. What I want to show you today is a ‘trick’ that will help you bypass the step of enabling polling for DBMS_OUTPUT for all of your connections. I’m all about saving clicks, so let’s save you 3 clicks right now. Instead of…
I’ve answered this question a few times over the past several months, but I’m just now getting around to putting my answer onto ‘paper.’ The common scenario is someone decides to alter a table. Immediately the paranoid – is it really paranoia if someone is actually out to get you? – start to worry about how many programs they’ve just borked. The database does track immediate object dependencies. However, it’s possible that your code is…
I’m a keyboard ‘ninja.’ I know how to get a lot of characters to splat up on my screen in as few keystrokes as possible. Yet sometimes I do fall back to the mouse. Cue the command-line SQL*Plus diehards rolling over in their graves. One of my favorite tricks is the ability to drag 2 or more tables into the worksheet and have SQL Developer automatically create a SELECT with the WHERE clause joins pre-built…
Data Definition Language (DDL) is used to describe an object in SQL. When you model a table in your relational model using Oracle SQL Developer Data Modeler, you are probably very curious as to the code that is being generated to represent that object. You can peek into this code using your mouse – You can right-mouse-click on an object and choose ‘DDL Preview,’ or you can use the keyboard shortcut, Alt+Shift+I. Here’s trick #1:…
What is Oracle SQL Developer? If you’re brand new to Oracle or are just now discovering the joys of using a GUI over the command-line interface, this video will show you a high level overview of SQL Developer’s main feature areas. Those include: an IDE for querying, scripting, debugging, and executing statements and programs a database navigation tree and object editors for pointing and clicking your way through database an extensible framework where users can…
It’s easy to forget that many people still have not discovered SQL Developer, or are just getting started. Many of my recent posts have been on advanced topics, hidden tricks, and productivity tips. I want to spend some time in the next few weeks helping the beginners out there. I can’t think of a better place to start than with how to ‘install’ SQL Developer. I’ve been telling anyone who will listen than you can…
If you have problems in your PL/SQL program that prevent compilation and execution of said program, then that is reported back as an ‘error.’ It could be as simple as missing a semicolon – 6878224186815bbce9dc05_000012 When you run this through SQL Developer, an error message is returned a la Well that’s an easy fix. We’re all done! Or are we? Enter the world of PL/SQL compile-time warnings: To make your programs more robust and avoid…
Martin asks – I am searching for any method to pass any table name to a sql statement in a self-written report. My initial response was to quote Lewis’ article in Oracle Magazine where he discusses bind variables, Making the Most of Oracle Developer Reports. So binds work, but what about SQL*Plus Substitution Variables? You’ve seen these before: & – Input value and use once&& – Input value and use for entire session. The answer…
Updated: May 11, 2015 I’ve been giving this presentation for about 4 years now. It’s morphed, mutated, and improved incrementally for that entire time. I say I have a ‘top 10’ but it’s closer to 15 or so. It’s a VERY interactive session, with live demo. And there’s always lots of questions as we go. But, if you’re not able to see this in person, I do have a slidedeck on slideshare – you can…
SQL Developer is a pretty mouse-heavy tool. By that I mean that many of the functions are accessed via mouse-clicks. When browsing tables in the tree you may have noticed that you can expand a table node to display the table columns. There are some pretty cool features available under the context menu when clicking on a table column: Some of these actions are available under the table right-clicks, but a couple are not, ‘Encrypt’…