PL/SQL INLINE feature, has some limits.

Check out this post about inlining of code, inspired by me.

http://stevenfeuersteinonplsql.blogspot.com/2015/07/inline-your-subprograms-but-only-within.html

Excellent stuff.

Just don't call me Sheldon.... :-)

I'm here to teach, are you here to learn?

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 learn.

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 your test. 

See you in class soon.

I'm an ACE!

BIG thanks to Tim Gorman and everyone who submitted an endorsement for me.  I very much appreciate being recognized for my years of working with Oracle and my active support of the Oracle community.

Let's keep on rockin! 

Getting the SQL_ID/HASH_VALUE with DBMS_SQL_TRANSLATOR

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 a file

-- The file must contain only ONE SQL statement

-- The Directory "SQL_FILE_LOCATION" must be defined

-- This is the location where this will read the file from

-- Example

-- CREATE OR REPLACE DIRECTORY SQL_FILE_LOCATION AS '/home/oracle/OP';

-- The file MUST end with a "/" on last line

-- Exmple

-- 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';

prompt ******************************************

accept hgetsqlid_file prompt 'Enter the full file name (with extension): '

DECLARE

    v_bfile BFILE;

    v_clob  CLOB;

    v_sqlid VARCHAR2(13);

    v_sqlhash number;

    v_slash integer;

    e_noslash exception;

BEGIN

    v_bfile := BFILENAME ('SQL_FILE_LOCATION', '&hgetsqlid_file');

    IF DBMS_LOB.FILEEXISTS (v_bfile) = 1 THEN

        DBMS_LOB.OPEN (v_bfile);

        DBMS_LOB.CREATETEMPORARY (v_clob, TRUE, DBMS_LOB.SESSION);

        DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));

        DBMS_LOB.CLOSE (v_bfile);

        -- remove all carrage returns (ASCII 13) from the clob

        -- each line must end with only a line feed (ASCII 10)

        v_clob := replace(v_clob, CHR(13) , '');

        -- remove any trailing spaces or tabs (ASCII 9)

        while DBMS_LOB.INSTR (v_clob, ' '||CHR(10)) > 0 or

              DBMS_LOB.INSTR (v_clob, CHR(9)||CHR(10)) > 0

             loop

             v_clob := replace(v_clob, ' '||CHR(10), CHR(10));

             v_clob := replace(v_clob, CHR(9)||CHR(10), CHR(10));

             end loop;

        -- Find the / at the end of the file

        v_slash := DBMS_LOB.INSTR (v_clob,'/');

        IF v_slash = 0 THEN RAISE e_noslash; END IF;

        -- remove the line with the slash and everything after it

        DBMS_LOB.TRIM (v_clob,v_slash-2);

        v_sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (v_clob);

        v_sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (v_clob);

        dbms_output.put_line ('*************************');

        dbms_output.put_line ('The SQL ID is '||v_sqlid);

        dbms_output.put_line ('Hash value is '||v_sqlhash);

        dbms_output.put_line ('*************************');

    ELSE

        dbms_output.put_line ('** File not found **');

    END IF;

    EXCEPTION

        when e_noslash then

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

        dbms_output.put_line ('Slash not found!');

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

END;

/

Here is an example:

SQL> get sqlid_test.sql

  1  SELECT e.employee_id, e.last_name, j.job_title, d.department_name

  2  FROM employees e, jobs j,   departments d

  3  WHERE e.department_id IN (10,20)

  4  AND j.job_id = e.job_id

  5  AND e.department_id = d.department_id

  6  ORDER BY e.last_name

  7* /

SQL>

SQL> @hgetsqlid

Current setting of SQL_FILE_LOCATION:

DIRECTORY_PATH

----------------------------------------------------------------------------------------------------

C:\OP

******************************************

Enter the full file name (with extension): sqlid_test.sql

*************************

The SQL ID is ap3yukgck02pd

Hash value is 3642755757

*************************

SQL> select sql_id, hash_value from v$sql where sql_text like 'SELECT e.employee_id, e.last_name,%';

SQL_ID             HASH_VALUE

------------- ---------------

ap3yukgck02pd      3642755757

SQL>