Skip navigation.

Development

Missing Named Credentials in OEM 12c

Arun Bavera - Thu, 2014-08-28 06:56

We are seeing that the list sometimes doesn’t show all the named credentials.

Yet to see if this resolves the issue but need to restart OMS …

emctl set property -name oracle.sysman.emdrep.creds.region.maxcreds -value 500

Oracle Enterprise Manager Cloud Control 12c Release 3

Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

SYSMAN password:

Property oracle.sysman.emdrep.creds.region.maxcreds has been set to value 500 for all Management Servers

OMS restart is required to reflect the new property value

Ref:

EM 12c: Missing Named Credentials in the Enterprise Manager 12c Cloud Control Jobs Drop Down List (Doc ID 1493690.1)

Categories: Development

PRECOMPUTE_SUBQUERY hint

XTended Oracle SQL - Wed, 2014-08-27 16:01

I’ve just found out that we can specify query block for PRECOMPUTE_SUBQUERY: /*+ precompute_subquery(@sel$2) */
So we can use it now with SQL profiles, SPM baselines and patches.

SQL> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);

D
-
X

SQL> @last

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c437vsqj7c4jy, child number 0
-------------------------------------
select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in
(select chr(level) from dual connect by level<=100)

Plan hash value: 272002086

---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |      1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("DUMMY"='' OR "DUMMY"='' OR "DUMMY"='♥' OR "DUMMY"='♦'
              OR "DUMMY"='♣' OR "DUMMY"='♠' OR "DUMMY"='' OR "DUMMY"=' OR
              "DUMMY"=' ' OR "DUMMY"=' ' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=' '
              OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='►' OR "DUMMY"='◄' OR
              "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"='' OR "DUMMY"='↑' OR "DUMMY"='↓' OR "DUMMY"='' OR
              "DUMMY"=' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"=' ' OR "DUMMY"='!' OR "DUMMY"='"' OR "DUMMY"='#' OR
              "DUMMY"='$' OR "DUMMY"='%' OR "DUMMY"='&' OR "DUMMY"='''' OR
              "DUMMY"='(' OR "DUMMY"=')' OR "DUMMY"='*' OR "DUMMY"='+' OR "DUMMY"=','
              OR "DUMMY"='-' OR "DUMMY"='.' OR "DUMMY"='/' OR "DUMMY"='0' OR
              "DUMMY"='1' OR "DUMMY"='2' OR "DUMMY"='3' OR "DUMMY"='4' OR "DUMMY"='5'
              OR "DUMMY"='6' OR "DUMMY"='7' OR "DUMMY"='8' OR "DUMMY"='9' OR
              "DUMMY"=':' OR "DUMMY"=';' OR "DUMMY"='<' OR "DUMMY"='=' OR "DUMMY"='>'
              OR "DUMMY"='?' OR "DUMMY"='@' OR "DUMMY"='A' OR "DUMMY"='B' OR
              "DUMMY"='C' OR "DUMMY"='D' OR "DUMMY"='E' OR "DUMMY"='F' OR "DUMMY"='G'
              OR "DUMMY"='H' OR "DUMMY"='I' OR "DUMMY"='J' OR "DUMMY"='K' OR
              "DUMMY"='L' OR "DUMMY"='M' OR "DUMMY"='N' OR "DUMMY"='O' OR "DUMMY"='P'
              OR "DUMMY"='Q' OR "DUMMY"='R' OR "DUMMY"='S' OR "DUMMY"='T' OR
              "DUMMY"='U' OR "DUMMY"='V' OR "DUMMY"='W' OR "DUMMY"='X' OR "DUMMY"='Y'
              OR "DUMMY"='Z' OR "DUMMY"='[' OR "DUMMY"='\' OR "DUMMY"=']' OR
              "DUMMY"='^' OR "DUMMY"='_' OR "DUMMY"='`' OR "DUMMY"='a' OR "DUMMY"='b'
              OR "DUMMY"='c' OR "DUMMY"='d'))

PS. I’m not sure, but as far as i remember, when I tested it on 10.2, it didn’t work with specifying a query block.
And I have never seen such usage.

Categories: Development

Flipping Herman Millers

FeuerThoughts - Mon, 2014-08-25 12:44
I was one of those dot.com boom excitables who decided that he must own a Herman Miller Aeron chair in order to be super cool and incredibly productive while I blogged and tweeted (well, I don't think there was Twitter back then) and so forth.

Soon after I got the chair, I realized that I really didn't like it very much at all. It didn't seem to provide support in the right places, didn't make it easy to avoid slouching....it wasn't the desk chair for me.
[My chair of choice, for years now, is the Swopper.]
Tried to get my son interested. Nope.
Tried to get my wife interested. Nope.
Moved it to the garage. 
Finally decided to offer it up on Craigslist nice and cheap to make it go away. Posted the ad for $200 and within minutes had someone lined up. He lived in St. Louis, but his dad lived nearby, and he told me when picking up the chair:
His son has built up a business "flipping" Aeron chairs. Buy them cheap, buy lots of parts, fix 'em up, and spin 'em back out into the market. 
Who knew?
What a remarkable system is capitalism. Sure, it's not nice. It's not fair. It's played a key role in the destruction of our planet as humans suck out the "natural resources" to make all the stuff that consumers want to buy. 
But capitalism is a very organic way of organizing an economy. 
Oh and this same chair-flipping fellow also does a wholesale business in ugly Christmas sweaters.
Finger on the pulse of a nation.
Categories: Development

Searching and installing Linux packages

Arun Bavera - Tue, 2014-08-12 11:12

yum search vnc

Loaded plugins: security

public_ol6_UEKR3_latest | 1.2 kB 00:00

public_ol6_UEKR3_latest/primary | 7.7 MB 00:01

public_ol6_UEKR3_latest 216/216

public_ol6_latest | 1.4 kB 00:00

public_ol6_latest/primary | 41 MB 00:03

public_ol6_latest 25873/25873

=============================================================================== N/S Matched: vnc ================================================================================

gtk-vnc.i686 : A GTK widget for VNC clients

gtk-vnc.x86_64 : A GTK widget for VNC clients

gtk-vnc-devel.i686 : Libraries, includes, etc. to compile with the gtk-vnc library

gtk-vnc-devel.x86_64 : Libraries, includes, etc. to compile with the gtk-vnc library

gtk-vnc-python.x86_64 : Python bindings for the gtk-vnc library

libvncserver.i686 : Library to make writing a vnc server easy

libvncserver.x86_64 : Library to make writing a vnc server easy

libvncserver-devel.i686 : Development files for libvncserver

libvncserver-devel.x86_64 : Development files for libvncserver

tigervnc.x86_64 : A TigerVNC remote display system

tigervnc-server.x86_64 : A TigerVNC server

tigervnc-server-applet.noarch : Java TigerVNC viewer applet for TigerVNC server

tigervnc-server-module.x86_64 : TigerVNC module to Xorg

tsclient.x86_64 : Client for VNC and Windows Terminal Server

vinagre.x86_64 : VNC client for GNOME

xorg-x11-server-source.noarch : Xserver source code required to build VNC server (Xvnc)

Name and summary matches only, use "search all" for everything.

yum install tigervnc-server.x86_64

Loaded plugins: security

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package tigervnc-server.x86_64 0:1.1.0-8.el6_5 will be installed

--> Processing Dependency: xorg-x11-fonts-misc for package: tigervnc-server-1.1.0-8.el6_5.x86_64

--> Running transaction check

---> Package xorg-x11-fonts-misc.noarch 0:7.2-9.1.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================================================

Package Arch Version Repository Size

=================================================================================================================================================================================

Installing:

tigervnc-server x86_64 1.1.0-8.el6_5 public_ol6_latest 1.1 M

Installing for dependencies:

xorg-x11-fonts-misc noarch 7.2-9.1.el6 public_ol6_latest 5.8 M

Transaction Summary

=================================================================================================================================================================================

Install 2 Package(s)

Total download size: 6.9 M

Installed size: 9.7 M

Is this ok [y/N]: y

Downloading Packages:

(1/2): tigervnc-server-1.1.0-8.el6_5.x86_64.rpm | 1.1 MB 00:00

(2/2): xorg-x11-fonts-misc-7.2-9.1.el6.noarch.rpm | 5.8 MB 00:01

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

Total 2.5 MB/s | 6.9 MB 00:02

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Warning: RPMDB altered outside of yum.

Installing : xorg-x11-fonts-misc-7.2-9.1.el6.noarch 1/2

Installing : tigervnc-server-1.1.0-8.el6_5.x86_64 2/2

Verifying : xorg-x11-fonts-misc-7.2-9.1.el6.noarch 1/2

Verifying : tigervnc-server-1.1.0-8.el6_5.x86_64 2/2

Installed:

tigervnc-server.x86_64 0:1.1.0-8.el6_5

Dependency Installed:

xorg-x11-fonts-misc.noarch 0:7.2-9.1.el6

Complete!

Categories: Development

Test your WebLogic 12.1.3 enviroment with Robot

Edwin Biemond - Sun, 2014-08-10 11:42
Robot Framework is a generic test automation framework which has an easy-to-use tabular test data syntax and it utilizes the keyword-driven testing approach. This means we can write our tests in readable and understandable text. If we combine this with the REST Management interface of WebLogic 12.1.3 we are able to test every detail of a WebLogic domain configuration and when we combine this

Required Field Validation in Oracle MAF

Shay Shmeltzer - Fri, 2014-08-08 16:38

A short entry to explain how to do field validation in Oracle MAF. As an example let's suppose you want a field to have a value before someone clicks to do an operation.

To do that you can set the field's attribute for required and "show required" like this:

  <amx:inputText label="label1" id="it1" required="true" showRequired="true"/>

 Now if you run your page, leave the field empty and click a button that navigates to another page, you'll notice that there was no indication of an error. This is because you didn't tell the AMX page to actually do a validation. 

 To add validation you use an amx:validationGroup tag that will surround the fields you want to validate.

For example:

     <amx:validationGroup id="vg1">

    <amx:inputText label="label1" id="it1" required="true" showRequired="true"/>

    </amx:validationGroup>

Then you can add a amx:validateOperation tag to the button that does navigation and tell it to validate the group you defined before (vg1 in our example).

       <amx:commandButton id="cb2" text="go" action="gothere">

        <amx:validationBehavior id="vb1" group="vg1"/>

      </amx:commandButton>

Now when you run the page and try to navigate you'll get your validation error.

Categories: Development

Create with WLST a SOA Suite, Service Bus 12.1.3 Domain

Edwin Biemond - Thu, 2014-08-07 13:14
When you want to create a 12.1.3 SOA Suite, Service Bus Domain, you have to use the WebLogic config.sh utility.  The 12.1.3 config utility is a big improvement when you compare this to WebLogic 11g. With this I can create some complex cluster configuration without any after configuration. But if you want to automate the domain creation and use it in your own (provisioning) tool/script then you

Set Focus on Added Row Tabular

Denes Kubicek - Tue, 2014-08-05 09:19
This sounds quite simple:

"I would like to set focus on the new added row in my tabular form."

In the following example, you can se how to acomplish that.

Categories: Development

Cascading Shuttle - Keep Selected Values

Denes Kubicek - Tue, 2014-08-05 06:14
There are probably a several good ways to solve the following problem:

    1. you have a simple select list which cascades a shuttle element
    2. you select something and the shuttle is populated
    3. after that, you pick a value in the shuttle and move it to the rigt side
    4. then you change the select list again
    5. the values you selected in your shuttle are now lost


This example shows how this can be done. This question apears in the APEX Forum from time to time and I decided to provide one simple solution for it.

Categories: Development

Accessing remote databases from Oracle MAF with the TopLink/EclipseLink REST CRUD Services

Shay Shmeltzer - Mon, 2014-08-04 15:17

In the last post I showed you how simple it is to expose CRUD REST operations on your database with TopLink/EclipseLink.

The next logical step is to then consume those with Oracle MAF to build a mobile application.

This is quite simple with the REST data control. All you need to do is just map the right URLs and create the operation.

Here is a quick demo:

One trick I show in the demo is how to delay the call to a REST service until the user actually provides a value to a parameter. A common issue people have when they have the parameter form and the results on the same page. The solution is easy using the refresh condition of the executables of the page and using the "ne null" check on the parameter value. 

Categories: Development

REST enable your Database for CRUD with TopLink/EclipseLink and JDeveloper

Shay Shmeltzer - Fri, 2014-08-01 17:10

It seems that REST interfaces are all the rage now for accessing your backend data, this is especially true in the world of mobile development. In this blog I'm going to show you how easy it is to provide a complete REST interface for your database by leveraging TopLink/EclipseLink and JDeveloper.

This relies on a capability that is available in TopLink 12c where every JPA entity that you have created can be RESTified with a simple servlet that TopLink provides.

All you need to do is locate the file toplink-dataservices-web.jar on your machine (this is included in the JDeveloper install so you can just search that directory) and then package your project as a WAR.

At that point you'll be able to get a complete CRUD set of operation for this entity.

In the video below I'm to retrieving departments by their id using a URL like this:

http://127.0.0.1:7101/TLServices-Project1-context-root/persistence/v1.0/out/entity/Departments/30

(out - name of my persistence unit. Departments - name of my entity) 

A complete list of all the REST URL syntax is here part of the TopLink documentation on this feature.:

http://docs.oracle.com/middleware/1213/toplink/solutions/restful_jpa.htm#CHDEGJIG

Check out how easy the process is in this video (using MySQL database):

Here are some additional URL samples for getting other types of queries:

Get all the Employees -  http://127.0.0.1:7101/TLServices/persistence/v1.0/out/query/Employees.findAll

Get all the Employees in department 50 - http://127.0.0.1:7101/TLServices/persistence/v1.0/out/entity/Departments/50/employeesList

Executing a specific named query (@NamedQuery(name = "Employees.findByName", query = "select o from Employees o where o.first_name like :name order by o.last_name"))  -http://127.0.0.1:7101/TLServices/persistence/v1.0/out/query/Employees.findByName;name=John

Categories: Development

How to find out session info about session that comes from remote database through dblink

XTended Oracle SQL - Thu, 2014-07-24 19:28
.syntaxhighlighter { width: 1800px; overflow-x: auto !important;}

It is well known thing and you can even find it on MOS, but I have a little more simple script for it, so I want to show little example.

First of all we need to start script on local database:

SQL>                                                                                                                                                                   
SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask :
Remote_db mask:

 INST_ID  SID    SERIAL# USERNAME REMOTE_DB REMOTE_DBID TRANS_ID         DIRECTION   GLOBALID                                           EVENT                      
-------- ---- ---------- -------- --------- ----------- ---------------- ----------- -------------------------------------------------- ---------------------------
       1  275       4469 XTENDER  BAIKAL     1742630060 8.20.7119        FROM REMOTE 4241494B414C2E63616336656437362E382E32302E37313139 SQL*Net message from client
                                                                                                                                                                  

Then we need to copy GLOBALID of interested session and run script on database that shown in column REMOTE_DBID, but with specifieng GLOBALID:

SQL>                                                                                                                                                                                                 
SQL> conn sys/syspass@baikal as sysdba
Connected.

======================================================================
=======  Connected to  SYS@BAIKAL(baikal)(BAIKAL)
=======  SID           203
=======  SERIAL#       38399
=======  SPID          6536
=======  DB_VERSION    11.2.0.4.0
======================================================================

SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask : 4241494B414C2E63616336656437362E382E32302E37313139
Remote_db mask:

INST_ID   SID    SERIAL# USERNAME  REMOTE_DB  REMOTE_DBID TRANS_ID   DIRECTION   GLOBALID                                            STATE                     
------- ----- ---------- --------- ---------- ----------- ---------- ----------- --------------------------------------------------  --------------------------
      1     9      39637 XTENDER   BAIKAL      1742630060 8.20.7119  TO REMOTE   4241494B414C2E63616336656437362E382E32302E37313139  [ORACLE COORDINATED]ACTIVE

It’s quite simple and fast.

Categories: Development

Standalone sqlplus script for plans comparing

XTended Oracle SQL - Thu, 2014-07-24 18:00

I have a couple scripts for plans comparing:

1. https://github.com/xtender/xt_scripts/blob/master/diff_plans.sql
2. http://github.com/xtender/xt_scripts/blob/master/plans/diff_plans_active.sql

But they have dependencies on other scripts, so I decided to create a standalone script for more convenient use without the need to download other scripts and to set up the sql*plus environment.
I’ve tested it already with firefox, so you can try it now: http://github.com/xtender/xt_scripts/blob/master/plans/diff_plans_active_standalone.sql

Some screenshots:
diff_plans.sql:
diff_plans

plans_active.sql:
plans_active

Usage:
1. plans_active:

SQL> @plans_active 0ws7ahf1d78qa 

2. diff_plans:

SQL> @diff_plans 0ws7ahf1d78qa 
 *** Diff plans by sql_id. Version with package XT_PLANS. 
Usage: @plans/diff_plans2 sqlid [+awr] [-v$sql] 

P_AWR           P_VSQL 
--------------- --------------- 
false           true 

Strictly speaking, we can do it sometimes easier: it’s quite simple to compare plans without first column “ID”, so we can simply compare “select .. from v$sql_plan/v$sql_plan_statistics_all/v$sql_plan_monitor” output with any comparing tool.

Categories: Development

Bug with xmltable, xmlnamespaces and xquery_string specified using bind variable

XTended Oracle SQL - Thu, 2014-07-24 12:54

Today I was asked about strange problem: xmltable does not return data, if xquery specified by bind variable and xml data has xmlnamespaces:

SQL> var x_path varchar2(100);
SQL> var x_xml  varchar2(4000);
SQL> col x format a100;
SQL> begin
  2      :x_path:='/table/tr/td';
  3      :x_xml :=q'[
  4                  <table xmlns="http://www.w3.org/tr/html4/">
  5                    <tr>
  6                      <td>apples</td>
  7                      <td>bananas</td>
  8                    </tr>
  9                  </table>
 10                  ]';
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select
  2        i, x
  3   from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
  4                  :x_path -- bind variable
  5                  --'/table/tr/td' -- same value as in the variable "X_PATH"
  6                  passing xmltype(:x_xml)
  7                  columns i    for ordinality,
  8                          x    xmltype path '.'
  9                );

no rows selected

But if we comment bind variable and comment out literal x_query ‘/table/tr/td’, query will return data:

SQL> select
  2        i, x
  3   from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
  4                  --:x_path -- bind variable
  5                  '/table/tr/td' -- same value as in the variable "X_PATH"
  6                  passing xmltype(:x_xml)
  7                  columns i    for ordinality,
  8                          x    xmltype path '.'
  9                );

         I X
---------- -------------------------------------------------------------------
         1 <td xmlns="http://www.w3.org/tr/html4/">apples</td>
         2 <td xmlns="http://www.w3.org/tr/html4/">bananas</td>

2 rows selected.

The only workaround I found is the specifying any namespace in the x_query – ‘/*:table/*:tr/*:td’

SQL> exec :x_path:='/*:table/*:tr/*:td'

PL/SQL procedure successfully completed.

SQL> select
  2        i, x
  3   from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
  4                  :x_path -- bind variable
  5                  passing xmltype(:x_xml)
  6                  columns i    for ordinality,
  7                          x    xmltype path '.'
  8                );

         I X
---------- -------------------------------------------------------------------
         1 <td xmlns="http://www.w3.org/tr/html4/">apples</td>
         2 <td xmlns="http://www.w3.org/tr/html4/">bananas</td>

2 rows selected.

It’s quite ugly solution, but I’m not sure whether there is another solution…

Categories: Development

REGEXP_LIKE: strange unspecified value in parameter “modifier”

XTended Oracle SQL - Tue, 2014-07-22 15:05

Today I noticed strange thing in predicate section of execution plan for simple query with regexp_like, where 3rd parameter “MODIFIER” was not specified:

SQL> select * from dual where regexp_like(dummy,'.');

D
-
X

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  97xuqf9cmjsta, child number 0
-------------------------------------
select * from dual where regexp_like(dummy,'.')

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( REGEXP_LIKE ("DUMMY",'.',HEXTORAW('F07FD85CFF0700006A1116
              45010000000000000000000000FC12164501000000000000000000000000000000000000
              0010000000000000001880D85CFF07000002000000000000000000000081000000') ))


20 rows selected.

It is particularly interesting that the values in HEXTORAW() are always different for different first parameters:

SQL> select * from dual where regexp_like(dummy,'x');
...
   1 - filter( REGEXP_LIKE ("DUMMY",'x',HEXTORAW('3895D330FF0700006A1116
              45010000000000000000000000FC12164501000000000000000000000000000000000000
              0011000000000000006895D330FF07000002000000000000000000000081000000') ))
SQL> select * from dual where regexp_like(dummy,'y');
...
   1 - filter( REGEXP_LIKE ("DUMMY",'y',HEXTORAW('00DA3C3FFF0700006A1116
              45010000000000000000000000FC12164501000000000000000000000000000000000000
              00110000000000000030DA3C3FFF07000002000000000000000000000081000000') ))
SQL> select * from dual where regexp_like(dummy||'','x')
...
   1 - filter( REGEXP_LIKE ("DUMMY"||'','x',HEXTORAW('70964F2FFF0700006A
              111645010000000000000000000000FC1216450100000000000000000000000000000000
              0000001100000000000000A0964F2FFF07000002000000000000000000000081000000')
               ))

I don’t know, what does it mean, but it looks like garbage from memory.
When I noticed this, I decided to check how regexp_like will work in function-based indexes:

SQL> create table xtest as
  2    select dummy||level as str
  3    from dual
  4    connect by level<=30;

Table created.

SQL> select * from xtest where case when regexp_like(str,'1') then 1 end = 1;
...
12 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  7ztp0k8c1zn2h, child number 0
-------------------------------------
select * from xtest where case when regexp_like(str,'1') then 1 end = 1

Plan hash value: 4207139086

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| XTEST |    12 |   264 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(CASE  WHEN  REGEXP_LIKE
              ("STR",'1',HEXTORAW('68F9CB32FF0700006A111645010000000000000000000000FC1
              216450100000000000000000000000000000000000000110000000000000098F9CB32FF0
              7000002000000000000000000000081000000') ) THEN 1 END =1)

SQL> create index xtest_fbi on xtest(case when regexp_like(str,'1') then 1 end);

Index created.

SQL> select * from xtest where case when regexp_like(str,'1') then 1 end = 1;
...
12 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  7ztp0k8c1zn2h, child number 0
-------------------------------------
select * from xtest where case when regexp_like(str,'1') then 1 end = 1

Plan hash value: 1479471124

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| XTEST     |    12 |   300 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | XTEST_FBI |    12 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("XTEST"."SYS_NC00002$"=1)

SQL> select column_expression from user_ind_expressions e where e.index_name='XTEST_FBI';

COLUMN_EXPRESSION
-----------------------------------------------------------------------------------------
CASE  WHEN  REGEXP_LIKE ("STR",'1') THEN 1 END

As you can see it works fine, although the predicate from first execution plan differs from the FBI expression.
Then I dumped 10053 trace and noticed that the HEXTORAW(…) function appeared in “Explain Plan Dump” only, so it looks just like plan output bug.

Categories: Development

New Continuous Integration tutorial published

Lynn Munsinger - Mon, 2012-07-02 09:44
Hot off the press – a new continuous integration tutorial. It’s really not just about continuous integration, though! You’ll find it useful even if you aren’t using a continuous integration server like Hudson. It’s useful if you are doing any part of the scenario it documents: Setting up Team Productivity Center for your team and [...]

Advanced ADF eCourse, Part Deux

Lynn Munsinger - Tue, 2012-06-19 15:11
In February, we published the first in a series of FREE(!) online advanced ADF training: http://tinyurl.com/advadf-part1 The response to that course has been overwhelmingly positive as more and more people are moving past the evaluation/prototype stages with ADF and looking for more advanced topics. I’m pleased to relay the good news that the 2nd part [...]

Fun with Hudson, Part 1.1

Lynn Munsinger - Tue, 2012-06-05 09:19
Earlier I posted that I had used the following zip command in the ‘execute shell’ action for my Hudson build job: zip -r $WORKSPACE/builds/$JOB_NAME-$BUILD_NUMBER * -x ‘*/.svn/*’ -x ‘*builds/*’ This zips up the content of the exported source, so that I can send it on to team members who need the source of each build [...]

Hiring a Curriculum Developer

Lynn Munsinger - Tue, 2012-05-15 09:34
If you are an instructional designer with an eye for technologies like ADF, or if you are an ADF enthusiast and excel at creatively producing technical content, then ADF Product Management would like to hear from you. We’re looking for a curriculum developer to join our ADF Curriculum team, which is tasked with ensuring that [...]

Hiring a Curriculum Developer

Lynn Munsinger - Tue, 2012-05-15 09:34
If you are an instructional designer with an eye for technologies like ADF, or if you are an ADF enthusiast and excel at creatively producing technical content, then ADF Product Management would like to hear from you. We’re looking for a curriculum developer to join our ADF Curriculum team, which is tasked with ensuring that [...]