I teach for a living.I’m not a teacher, I see myself as an instructor, but saying “I instruct
for a living” sounds rather odd.The difference
mostly is in duration.I only have a
class for a couple of days and I don’t give any tests or grades.My goal is to impart knowledge to folks so
they are better at their jobs.
And I take my job very seriously.I spend hours making sure the content is
correct and up to date.(This is really
hard with Oracle, since its always changing.)I spend hours coming up with exercises that
will help students understand the concepts being presented in an easy to understand
way. I will stay late to help folks understand
something they didn’t get.I’ll work
with them even after class thru email.
I love what I do.I
really do.And based on the many years of feedback, I’m
quite good at it too.
What does bug me is that some students
show up to class with an idea that this is some sort of vacation.While I’m
not running a basic training camp (did that in a past life, not really into
that any more), what I do have is a really great curriculum that I
have poured my life into and I hope you will come with an eagerness to
I’m not here to start class late, take a 2 hour lunch and
then for you to cut out over an hour early “just because”.When you come to my class, be ready to learn. Ask the tough questions, I don’t know all the
answers but if no one asks the questions we’ll never have the opportunity to
find the answers.
True I don’t give a test at the end of class, your test is
when you get back on the job and can perform your job better.If you slept thru class then you will fail
This little script will read in a SQL file and return to you
the SQL_ID and HASH_VALUE of a statement without running it.It uses DBMS_SQL_TRANSLATOR.SQL_ID and
.SQL_HASH to get these values.The
tricky part was to make sure the file is in the same format as what the optimization
process uses when computing these values.Key is that the file is in UNIX format, meaning there is only a linefeed
at the end of each line.Also the
optimizer strips out trailing spaces or tabs (white space) before calculating
these values as well.
It was this second one that took me awhile to figure out.I didn’t know at first that the optimizer was
doing this. Once I knew what was going
on it was rather simple to fix.(Isn’t
that always the case?It’s finding the
real problem that is the problem!)My
solution is rather brute force, but I didn’t want to truly “parse” the
statement, as in go thru it character by character. So I instead just look for a space or tab with
a line feed after it, if there is at least one of either I replace “both” thru
out the entire CLOB.Like I said not eloquent
but it gets the job done. Part of the
issue was I also had to leave the spacing alone inside a line.The optimizer only strips off the white space
at the end of the line, not within the line.
The PL/SQL engine is different; it reduces ALL white space
to a single space and removes all line feeds.Hence it turns your SQL statement into one long string.But it does all this before it sends the SQL
to the optimizer. I’m not trying to
mimic the PL/SQL engine with this script.
So here it is.If
your find any flaws with this please let me know.I’ve tested it on several statements and on
UNIX and Windows, so far it has been correct with each test.If you like this and use this please keep the
header information to give me credit for it is all I ask.Thanks!
For those who were at the Great Lakes Oracle Conference this
week, #GLOC15, here is the working version I told you I was still working on in
my session about execution plans.
-- File name hgetsqlid.sql
-- Get the SQLID/HASH for a SQL statement in
-- The file must contain only ONE SQL
-- The Directory
"SQL_FILE_LOCATION" must be defined
-- This is the location where this will read
the file from
-- CREATE OR REPLACE DIRECTORY
SQL_FILE_LOCATION AS '/home/oracle/OP';
-- The file MUST end with a "/" on
-- Select * from emp
-- May 2015 RVD initial coding
set termout on
set tab off
set verify off
set serveroutput on
column directory_path format a100 wrap
prompt Current setting of SQL_FILE_LOCATION:
select directory_path from dba_directories
where directory_name = 'SQL_FILE_LOCATION';
accept hgetsqlid_file prompt 'Enter the full
file name (with extension): '
Ric Van Dyke is an Oracle Ace with over 30 years of experience in the IT industry. Coming from a 3GL programing back ground he started working with Oracle Version 6 as a developer with Forms 2.3 and became a DBA at Ford Motor Credit. He then worked at a variety of companies as an independent consultant. Ric worked for Oracle Corporation for 10 years, starting as a core database instructor where he taught and developed several courses. He became the Education Manager of the North Central Region. Ric then served in the Advanced Technologies Services consulting group as a Technical Manager where he worked with several clients doing RAC installs and performance engagements. Ric is currently the Education Director at Hotsos where he coordinates training activities. He also teaches both in-person and on-line training. Ric frequently speaks at user group meetings and gives on-line webinars.