Category Archives: curious

Little quiz: Ordering/Grouping – Guess the output

Posted on by Posted in curious, oracle, SQL 1,796 Page views Leave a comment

How many times have you guessed the right answer? 🙂

1

[sourcecode language=”SQL”]
select * from dual order by -1;
select * from dual order by 0;
[/sourcecode]

[collapse]
2

[sourcecode language=”SQL”]
select * from dual order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;
[/sourcecode]

[collapse]
3

[sourcecode language=”SQL”]
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0+0;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 3+7 desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(3.1+0f) desc;
[/sourcecode]

[collapse]
4

[sourcecode language=”SQL”]
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 1.9;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.5;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by 2.7 desc;
select column_value x,10-column_value y from table(ku$_objnumset(5,4,3,1,2,3,4)) order by -2.7 desc;
[/sourcecode]

[collapse]

REGEXP_LIKE: strange unspecified value in parameter “modifier”

Posted on by Posted in bug, CBO, curious, oracle 1,740 Page views Leave a comment

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.

Strange moving filter predicates from index to table

Posted on by Posted in CBO, curious, oracle 2,696 Page views 9 Comments

It seems strange to me:
When all needed columns are in the index, filter predicates are expectedly applied to the index

select a,b from xt_test where a=1 and
(:b is null or b = :b)

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| PK_XT_TEST |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------

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

   1 - access("A"=1)
       filter((:B IS NULL OR "B"=:B))

But if I add another column “PAD”, the filter moves to the table filters:

select a,b,pad from xt_test where a=1
and (:b is null or b = :b)

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |      1 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| XT_TEST    |      1 |      1 |      1 |       4 |
|*  2 |   INDEX RANGE SCAN          | PK_XT_TEST |      1 |     10 |     10 |       2 |
---------------------------------------------------------------------------------------

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

   1 - filter((:B IS NULL OR "B"=:B))
   2 - access("A"=1)

As workaround we can use something like that:

select--+ NO_ELIMINATE_JOIN(t) NO_ELIMINATE_JOIN(t2@sel$2) gather_plan_statistics
   a,b,pad
from xt_test t
where t.rowid in ( select t2.rowid
                   from xt_test t2
                   where a=1
                     and (:b is null or b = :b)
                 );

Plan hash value: 1464320522

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |      1 |      3 |
|   1 |  NESTED LOOPS               |            |      1 |      1 |      1 |      3 |
|*  2 |   INDEX RANGE SCAN          | PK_XT_TEST |      1 |      1 |      1 |      2 |
|   3 |   TABLE ACCESS BY USER ROWID| XT_TEST    |      1 |      1 |      1 |      1 |
--------------------------------------------------------------------------------------

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

   2 - access("A"=1)
       filter((:B IS NULL OR "B"=:B))

Full test case

[sourcecode language=”sql”]
create table xt_test(a,b,pad,constraint pk_xt_test primary key(a,b))
as select
mod(rownum,10) a
,rownum b
,rpad(rownum,10) pad
from dual
connect by level<=100;
call dbms_stats.gather_table_stats(”,’XT_TEST’);
var b number;
exec :b:=1;
select/*+ gather_plan_statistics */
a,b,pad
from xt_test
where a=1 and (:b is null or b = :b);
select * from table(dbms_xplan.display_cursor(”,”,’allstats last’));

select/*+ gather_plan_statistics */
a,b
from xt_test
where a=1 and (:b is null or b = :b);
select * from table(dbms_xplan.display_cursor(”,”,’allstats last’));

select–+ NO_ELIMINATE_JOIN(t) NO_ELIMINATE_JOIN(t2@sel$2) gather_plan_statistics
a,b,pad
from xt_test t
where t.rowid in ( select t2.rowid
from xt_test t2
where a=1
and (:b is null or b = :b)
);
select * from table(dbms_xplan.display_cursor(”,”,’allstats last’));
[/sourcecode]

[collapse]

Update:
I just forgot to mention that there is another workaround – to force concatenation:

SQL> select--+ use_concat(or_predicates(2))
  2     a,b,pad
  3  from xt_test where a=1 and (:b is null or b = :b);

         A          B PAD
---------- ---------- ----------------------------------------
         1          1 1

Plan hash value: 3582916188

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |     3 (100)|      1 |00:00:00.01 |       2 |      2 |
|   1 |  CONCATENATION                |            |      1 |        |            |      1 |00:00:00.01 |       2 |      2 |
|   2 |   TABLE ACCESS BY INDEX ROWID | XT_TEST    |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      2 |
|*  3 |    INDEX UNIQUE SCAN          | PK_XT_TEST |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |      1 |
|*  4 |   FILTER                      |            |      1 |        |            |      0 |00:00:00.01 |       0 |      0 |
|   5 |    TABLE ACCESS BY INDEX ROWID| XT_TEST    |      0 |     10 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |
|*  6 |     INDEX RANGE SCAN          | PK_XT_TEST |      0 |     10 |     1   (0)|      0 |00:00:00.01 |       0 |      0 |
----------------------------------------------------------------------------------------------------------------------------

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

   3 - access("A"=1 AND "B"=:B)
   4 - filter(:B IS NULL)
   6 - access("A"=1)
       filter(LNNVL("B"=:B))

To_char, Infinity and NaN

Posted on by Posted in curious, oracle 3,253 Page views Leave a comment

Funny that oracle can easily cast ‘nan’,’inf’,’infinity’,’-inf’,’-infinity’ to corresponding binary_float_infinity,binary_double_nan, but there is no any format models for to_char(binary_float_infinity,format) or to_binary_***(text_expr,format) that can output the same as to_char(binary_float_infinity)/to_binary_float(‘inf’) without format parameter:

If a BINARY_FLOAT or BINARY_DOUBLE value is converted to CHAR or NCHAR, and the input is either infinity or NaN (not a number), then Oracle always returns the pound signs to replace the value.

Little example:

SQL> select to_binary_float('inf') from dual;

TO_BINARY_FLOAT('INF')
----------------------
                   Inf

SQL> select to_binary_float('inf','9999') from dual;
select to_binary_float('inf','9999') from dual
                       *
ERROR at line 1:
ORA-01722: invalid number

SQL> select
  2     to_char(binary_float_infinity)         without_format
  3    ,to_char(binary_float_infinity,'99999') with_format
  4    ,to_char(1e6d,'99999')                  too_large
  5  from dual;

WITHOUT_F WITH_FORMAT        TOO_LARGE
--------- ------------------ ------------------
Inf       ######             ######

SQL> select to_char(0/0f) without_format, to_char(0/0f,'tme') with_format from dual;

WITHOUT_F WITH_FORMAT
--------- --------------------------------------------------------------------------
Nan       ################################################################

ps. it’s just crossposting of my old blog.

Friday prank: select from join join join

Posted on by Posted in curious, oracle 2,827 Page views 1 Comment

Valid funny queries 🙂

select the join from join join join using(the,some) 
/
select some join from left join join right using(some,the) 
/
select 1 join from join join join join join using(the) on 1=1
/
select the some from join 
where the=some( the(select some from join) 
               ,the(select the  from join)
               ) 
/

tables

[sourcecode language=”sql”]
create table join as select 1 some,1 the from dual;
create table left as select 1 some,1 the from dual;
create table right as select 1 some,1 the from dual;
[/sourcecode]

[collapse]
🙂

Little addition 🙂

update two tables set join=2;
select join from two tables;
select first, second, random from two tables join three tables on 1=1;

Another one 🙂

select first
     , case when the=some(some,some) then join end true
from two tables join three tables using(random);

….
and…

select random columns from two tables join four tables on the=some(some,some);

Oracle 12c: behavior tests of the Inline functions, “Identities” and “defaults”

Posted on by Posted in 12c, curious, undocumented 2,933 Page views 4 Comments

I have done several minitests:
1. SQL and PL/SQL engines: which functions will be executed if there are two functions with same name as in SQL, as in PL/SQL (like “USER”, LPAD/RPAD, etc..)
– PL/SQL.

PL/SQL

[sourcecode language=”sql”]
SQL> @trace_on
Enter value for trace_identifier: inline
Enter value for level: 12
Tracing was enabled:

TRACEFILE_NAME
—————————————————————————-
/u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc

SQL> with
2 function inline_user return varchar2 is
3 begin
4 return user;
5 end;
6 select
7 inline_user
8 from dual
9 /

INLINE_USER
——————————
XTENDER

1 row selected.

SQL> @trace_off
— unlike SQL’s "USER", PL/SQL function SYS.STANDARD.USER recursively executes "select user from sys.dual":
SQL> !grep USER /u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc
SELECT USER FROM SYS.DUAL

SQL>
[/sourcecode]

[collapse]

2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
– Yes

Test 1

[sourcecode language=”sql”]
SQL> sho parameter max_string

NAME TYPE VALUE
———————————— ———— ——————————
max_string_size string STANDARD

SQL> @trace_pl_on

Session altered.

SQL> with
2 function blabla(p_str varchar2) return varchar2 is
3 begin
4 return lpad(p_str, 5000, ‘*’);
5 end;
6 select
7 length(blabla(dummy)) lpad_plsql
8 from dual;
9 /
from dual
*
ERROR at line 8:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

SQL> @trace_pl_last.sql

RUNID EVENT_SEQ EVENT_COMMENT EVENT_UNIT_OWNER EVENT_UNIT
———- ———- ——————————– —————— ———–
1 1 PL/SQL Trace Tool started
1 2 Trace flags changed
1 3 PL/SQL Virtual Machine started <anonymous>
1 4 PL/SQL Internal Call <anonymous>
1 5 PL/SQL Virtual Machine stopped

[/sourcecode]

[collapse]

Test 2

[sourcecode language=”sql”]
SQL> @trace_pl_on

Session altered.

SQL> create or replace function f_standalone(p varchar2) return varchar2 is
2 begin
3 return lpad(‘x’,3)||p;
4 end;
5 /

Function created.

SQL> with
2 function blabla(p_str varchar2) return varchar2 is
3 s varchar2(32767);
4 begin
5 s:= lpad(p_str, 100, ‘1’);
6 s:= s||s;
7 s:= s||lpad(p_str, 100, ‘3’);
8 s:= s||s;
9 s:= s||(1+10);
10 s:= f_standalone(s);
11 s:= f_standalone(s);
12 s:= f_standalone(s);
13 return s;
14 end;
15 select
16 length(blabla(dummy)) lpad_plsql
17 from dual
18 /

LPAD_PLSQL
———-
611

SQL> @trace_pl_last.sql

RUNID EVENT_SEQ EVENT_COMMENT EVENT_UNIT_OWNER EVENT_UNIT
———- ———- ——————————– —————– ————
2 1 PL/SQL Trace Tool started
2 2 Trace flags changed
2 3 PL/SQL Virtual Machine started <anonymous>
2 4 PL/SQL Internal Call <anonymous>
2 5 PL/SQL Virtual Machine stopped
2 6 PL/SQL Virtual Machine started <anonymous>
2 7 PL/SQL Virtual Machine started <anonymous>
2 8 PL/SQL Internal Call <anonymous>
2 9 PL/SQL Virtual Machine stopped
2 10 PL/SQL Virtual Machine stopped
2 11 PL/SQL Virtual Machine started <anonymous>
2 12 PL/SQL Virtual Machine started <anonymous>
2 13 PL/SQL Internal Call <anonymous>
2 14 PL/SQL Virtual Machine stopped
2 15 PL/SQL Virtual Machine stopped
2 16 PL/SQL Virtual Machine started <anonymous>
2 17 PL/SQL Internal Call <anonymous>
2 18 PL/SQL Internal Call <anonymous>
2 19 Procedure Call <anonymous>
2 20 PL/SQL Internal Call XTENDER F_STANDALONE
2 21 Return from procedure call XTENDER F_STANDALONE
2 22 Procedure Call <anonymous>
2 23 PL/SQL Internal Call XTENDER F_STANDALONE
2 24 Return from procedure call XTENDER F_STANDALONE
2 25 Procedure Call <anonymous>
2 26 PL/SQL Internal Call XTENDER F_STANDALONE
2 27 Return from procedure call XTENDER F_STANDALONE
2 28 PL/SQL Virtual Machine stopped

28 rows selected.
[/sourcecode]

[collapse]

Test 3

[sourcecode language=”sql”]
SQL> @trace_pl_on

Session altered.

SQL> with
2 function blabla(p_str varchar2) return varchar2 is
3 s varchar2(32767);
4 begin
5 s:= lpad(p_str, 100, ‘1’);
6 s:= s||s;
7 s:= s||lpad(p_str, 100, ‘3’);
8 s:= s||s;
9 s:= s||(1+10);
10 return s;
11 end;
12 select
13 length(blabla(dummy)) lpad_plsql
14 from dual
15 /

LPAD_PLSQL
———-
602

1 row selected.

SQL> @trace_pl_last.sql

RUNID EVENT_SEQ EVENT_COMMENT EVENT_UNIT_OWNER EVENT_UNIT
———- ———- ——————————– —————— ————
3 1 PL/SQL Trace Tool started
3 2 Trace flags changed
3 3 PL/SQL Virtual Machine started <anonymous>
3 4 PL/SQL Internal Call <anonymous>
3 5 PL/SQL Internal Call <anonymous>
3 6 PL/SQL Virtual Machine stopped

6 rows selected.

[/sourcecode]

[collapse]

3. How IDENTITY works?
For all identity columns Oracle creates a sequence with name like “ISEQ$$_XXX”, where “XXX” is the object_id of the table. All identities we can get through DBA_TAB_IDENTITY_COLS.
All Identity sequences:

select i.*
      ,tab.owner       tab_owner
      ,tab.object_name tab_name
      ,sq.object_name  sequence_name
from sys.idnseq$ i
    ,dba_objects tab
    ,dba_objects sq
where tab.object_id=i.obj#
  and sq.object_id = i.seqobj#

And we can see usage of this sequence in plans:

[sourcecode language=”sql”]
SQL_ID fn5tjw6hu0dtn, child number 0
————————————-
insert into xt_identity (description) values(‘1’)

Plan hash value: 3838626111

————————————————————————————————–
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers |
————————————————————————————————–
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.01 | 35 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:00.01 | 35 |
| 2 | SEQUENCE | ISEQ$$_91720 | 1 | | 1 |00:00:00.01 | 4 |
————————————————————————————————–
[/sourcecode]

[collapse]

4. When executes “default seq.nextval”?
Test

[sourcecode language=”sql”]
SQL> create sequence xt_sq1;
SQL> create sequence xt_sq2;
SQL> create table xt_default(
2 id1 int default xt_sq1.nextval
3 , pad varchar2(30)
4 , id2 int default xt_sq2.nextval
5 );

Table created.

SQL> insert into xt_default(pad) values(‘1’);

1 row created.

SQL> select xt_sq1.currval, xt_sq2.currval from dual;

CURRVAL CURRVAL
———- ———-
1 1

SQL> insert into xt_default(pad) values(1/0);
insert into xt_default(pad) values(1/0)
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> select xt_sq1.currval, xt_sq2.currval from dual;

CURRVAL CURRVAL
———- ———-
2 2
[/sourcecode]

[collapse]

Why between to_date(‘1582-10-15′,’yyyy-mm-dd’) and to_date(‘1582-10-04′,’yyyy-mm-dd’) only one day

Posted on by Posted in curious, oracle 2,476 Page views Leave a comment

You may wonder why between these dates only one day:

SQL> select date'1582-10-15'-date'1582-10-04' from dual;

DATE'1582-10-15'-DATE'1582-10-04'
---------------------------------
                                1

SQL> select date'1582-10-05'                   "dt_1"
  2        ,date'1582-10-05' + 1               "dt_1 + 1"
  3        ,date'1582-10-05' - 1               "dt_1 - 1"
  4        ,to_date('1582-10-05','yyyy-mm-dd') "dt_1 and to_date"
  5  from dual;

dt_1               dt_1 + 1           dt_1 - 1           dt_1 and to_date
------------------ ------------------ ------------------ ------------------
October   05, 1582 October   16, 1582 October   04, 1582 October   15, 1582

Over the last 2 months, I gave link to answer several times, so i decided to post it here: http://www.orafaq.com/papers/dates_o.doc

BTW, yet another trick:

SQL> select date'0000-02-29','to_char:'||date'0000-02-29' from dual;

DATE'0000-02-29'    'TO_CHAR:'||DATE'0000-02-29'
------------------- ---------------------------
29.02.0000 00:00:00 to_char:00.00.0000 00:00:00

SQL> select to_date('0000-02-29','yyyy-mm-dd') error from dual;
select to_date('0000-02-29','yyyy-mm-dd') error from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Link to the online unwrapper in the official Oracle documentation

Posted on by Posted in curious, documentation, oracle 5,886 Page views 3 Comments

Of course, everyone knows about “unwrappers” and I guess many of you even tried to make an unwrapper of your own (I also tried to make one after Pete Finnigan’s presentation:). But nevertheless its funny that Oracle in its documentation for 11.2 “loudly” states that unwrap does not protect anything from viewing, and even gives a direct link to the online unwrapper 🙂

11.2 PL/SQL Source Text Wrapping:

Note:

Wrapping text does not prevent anyone from displaying it with a utility such as:
http://www.codecheck.info/UnwrapIt/

For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator’s Guide.

Here is a comparison of unwrap capabilities in documentations for different versions:

  • In 9.2 everything is safely hidden, except for literals, names of variables, tables and columns:

    String literals, number literals, and names of variables, tables, and columns remain in plain text within the wrapped file. Wrapping a procedure helps to hide the algorithm and prevent reverse-engineering, but it is not a way to hide passwords or table names that you want to be secret.

  • In 10.2 the data is hidden only from “most users”, but at least it makes reverse-engineering difficult!

    Although wrapping a compilation unit helps to hide the algorithm and makes reverse-engineering difficult, Oracle Corporation does not recommend it as a secure method for hiding passwords or table names. Obfuscating a PL/SQL unit prevents most users from examining the source code, but might not stop all attempts.

  • In 11.1 everything looks humble and boring:

    Wrapping is not a secure method for hiding passwords or table names. Wrapping a PL/SQL unit prevents most users from examining the source code, but might not stop all of them.