AskTom – the personal touch

AskTom has been answering questions from the user community for nearly 16 years.

But what if that service could have an even more personal touch ?  We are pleased announce the largest advance in AskTom service since it started way back in 2000.

See all the details in the video below for the new service we are launching on April 1st.

Dealing with URL’s

If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier


SQL> create or replace function utl_url_escape(x varchar2) return varchar2 is
  2  begin
  3    return utl_url.escape(x,true);
  4  end;
  5  /

Function created.

SQL> col escaped_char format a20
SQL> WITH special_chars
  2  AS (SELECT '<' AS sp_char FROM dual UNION ALL
  3      SELECT '>' AS sp_char FROM dual UNION ALL
  4      SELECT '.' AS sp_char FROM dual UNION ALL
  5      SELECT '#' AS sp_char FROM dual UNION ALL
  6      SELECT '{' AS sp_char FROM dual UNION ALL
  7      SELECT '}' AS sp_char FROM dual UNION ALL
  8      SELECT '|' AS sp_char FROM dual UNION ALL
  9      SELECT '\' AS sp_char FROM dual UNION ALL
 10      SELECT '^' AS sp_char FROM dual UNION ALL
 11      SELECT '~' AS sp_char FROM dual UNION ALL
 12      SELECT '[' AS sp_char FROM dual UNION ALL
 13      SELECT ']' AS sp_char FROM dual UNION ALL
 14      SELECT '<' AS sp_char FROM dual UNION ALL
 15      SELECT '`' AS sp_char FROM dual UNION ALL
 16      SELECT '+' AS sp_char FROM dual UNION ALL
 17      SELECT '/' AS sp_char FROM dual UNION ALL
 18      SELECT '?' AS sp_char FROM dual UNION ALL
 19      SELECT '&' AS sp_char FROM dual UNION ALL
 20      SELECT '''' AS sp_char FROM dual)
 21  SELECT sp_char
 22  ,      utl_url.escape(sp_char) AS escaped_char
 23  FROM   special_chars;

S ESCAPED_CHAR
- --------------------
< %3C
> %3E
. .
# %23
{ %7B
} %7D
| %7C
\ %5C
^ %5E
~ ~
[ [
] ]
< %3C
` %60
+ +
/ /
? ?
& &
' '

19 rows selected.

Opatch quick tip

I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error:


C:\oracle\stage\22581007>opatch apply
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\1210~1.2
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : C:\oracle\product\1210~1.2\cfgtoollogs\opatch\22581007_Mar_31_2016_18_32_02\apply2016-03-31_18-32-01 PM_1.log

Unable to lock Central Inventory.  OPatch will attempt to re-lock.
Do you want to proceed? [y|n]

My first thought was this was due probably to the fact that I’ve got countless installations on this laptop, some running, some stopped, etc etc.

Turns out to be a much simpler issue. When running OPatch on Windows, make sure you are running it with the “As Administrator”.

Simple Smile

Median in SQL

Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial Smile query can determine the median salary from the EMP table


SQL> select avg(sal) from
  2  ( select x.sal
  3   from scott.EMP x, scott.EMP y
  4   group by x.sal
  5   having sum(sign(1-sign(y.sal-x.sal))) in
  6          ( trunc((count(*)+1)/2), trunc((count(*)+2)/2) ) );

  AVG(SAL)
----------
      1550

Of course, if that is basically gobbledygook to you (and it certainly is to me!)…well, you should check out Oracle’s cool Analytic SQL features here

Are your SQL Plus scripts going to ‘ell ?

Excuse the hideous pun in the title.  All will become clear shortly.

Often we use SQL Plus to run scripts on a scheduled basis in our database.  The “problem” is, they need to connect to that database.

Here’s a simple script I want to run:

image

and here’s my very sophisticated Smile batch file (which could just as easily be a Unix shell script) to run it:

image

In my case, I’m planning on using a wallet to not reveal the password (see here for details ).

But what if my wallet is wrong or missing?  Or if I’m explicitly coding a username / password and either are incorrect ?

Then this happens when I run my batch file:

image

Yes my connection failed…but notice that the script did not complete.

It…..just….sat….there.

That’s no good…from an observer’s perspective, it is still “running”.

So here’s a quick tip.  Whenever you launch SQL Plus from within a shell or script, always include the -L parameter (“minus elle”)

image

If you can’t connect, SQL Plus will exit and at least you’ll get some feedback that all is not OK.

So use minus elle, to stop your scripts from going to hell Smile

Up to date text indexes

If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT.  For example,


SQL> create table t1 (
  2    x int,
  3    y varchar2(1000) )
  4  ;

Table created.

SQL>
SQL> create index t1_iz on T1  ( y )
  2  indextype is ctxsys.context
  3  parameters ('sync (on commit)');

Index created.

SQL>
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1
  2  where contains(y,'quick') > 0 ;

         X
----------
Y
----------------------------------------------------------------------------------------------------
         1
The quick brown fox jumped over the lazy dog

         3
Several fabulous dixieland jazz groups played with quick tempo

But don’t forget. There is no such thing as a free lunch. As has always been the case, the more you can index “in batch” the less fragmentation you get with your text index, and obviously, if you’re planning on doing a stack of activities when you commit…then your commit performance will be impacted. Let’s compare the insert’s above, with and without a text index in place.


--
-- With index
--
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> set timing on
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

-- 
-- Without index 
-- 
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> set timing on
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Not too shabby…but noticeable.

India bound !

As part of the activities organised by the Oracle Ace Program and the All India Oracle User Group, the OTN Yathra tour is coming up in a few weeks.

OTNYathra2016

I’ll be speaking on 12c developer features and advanced SQL techniques at the following locations:

  • 23-Apr-16 Saturday,  Chennai
  • 24-Apr-16 Sunday, Bangalore
  • 26-Apr-16 Tuesday, Hyderabad
  • 28-Apr-16 Thursday, Pune

If you’re in any of these places, come along for some great education, good fun and don’t be shy – come up and say Hello.

Full details are here

(Special thanks to Sai for his organizational efforts in getting this all together).