SQL & PL/SQL

Well, data is case sensitive (unless you've done a whole lot of fiddling with NLS parameters to force case-insensitive queries), so a lower case letter sorts after an upper case letter.

  1* select (case when 'j' > 'J' then 'TRUE' else 'FALSE' end) from dual
SCOTT @ jcave102 Local> /

(CASE
-----
TRUE

That means that 'JUSTIN' comes after 'JAMES' and before 'James'. The same goes with any name that comes between 'J' and 'JAMES' in alphabetical order.

Justin

As an addendum to Justin's response ... depending on your character
set the following should give you a good idea of how things will sort:

SELECT 'MORGAN', dump('MORGAN') FROM dual;

SQL> SELECT 'MORGAN', dump('MORGAN') FROM dual;

'MORGA DUMP('MORGAN')
------ -------------------------------
MORGAN Typ=96 Len=6: 77,79,82,71,65,78

SELECT 'MORGAn', dump('MORGAn') FROM dual;

'MORGA DUMP('MORGAn')
------ --------------------------------
MORGAn Typ=96 Len=6: 77,79,82,71,65,110

Note that 78 will precede 110

Though for those with a more technical bent it is likely that what Oracle is doing
is more like this:

SQL> SELECT 'MORGAN', sys_op_map_nonnull('MORGAN') FROM dual;

'MORGA SYS_OP_MAP_NON
------ --------------
MORGAN 4D4F5247414E00

precedes

SQL> SELECT 'MORGAn', sys_op_map_nonnull('MORGAn') FROM dual;

'MORGA SYS_OP_MAP_NON
------ --------------
MORGAn 4D4F5247416E00

But I wouldn't bet my reputation on it.

Daniel

Thanks

Function DUMP: Learned a new thing :)

Sidhu
http://amardeepsidhu.blogspot.com

Daniel

Thanks

Function DUMP: Learned a new thing :)

Sidhu
http://amardeepsidhu.blogspot.com

OOPS...got posted twice...sorry :)

Message was edited by:
Sidhu

As an addendum to the addendum, single letters are always smaller than words beginning with that letter. So, your third query would find all employees with a name that began with J and ignore anybody with a name beginning with W or later.

SQL> SELECT CASE WHEN 'WARD' > 'W' THEN 'TRUE' ELSE 'FALSE' END
  2  FROM dual
 
CASEW
-----
TRUE

John

Thankyou Justin and damorgan. I learnt a handful.

You are welcome.

If you would like to see demos of almost all of Oracle's documented functions, and a few that are not, try this:

http://www.psoug.org/reference/builtin_functions.html

You will find DUMP there as well as SYS_OP_MAP_NONULL

PS: If anyone knows of an Oracle function not on the page ... please email me an example. Thanks.

what about REGEXP_LIKE?

> PS: If anyone knows of an Oracle function not on the

page ... please email me an example. Thanks.

And XMLELEMENT.

Regards,
Rob.

Strictly said - is not a function ;-)

Best regards

Maxim

why not maxim?

regexp_like is a function returning boolean like lnnvl

out of the standard package source

-- REGEXP_LIKE --
function REGEXP_LIKE (srcstr   VARCHAR2 CHARACTER SET ANY_CS,
                      pattern  VARCHAR2 CHARACTER SET srcstr%CHARSET,
                      modifier VARCHAR2 DEFAULT NULL)
  return BOOLEAN;
  pragma FIPSFLAG('REGEXP_LIKE', 1452);

function REGEXP_LIKE (srcstr   CLOB CHARACTER SET ANY_CS,
                      pattern  VARCHAR2 CHARACTER SET srcstr%CHARSET,
                      modifier VARCHAR2 DEFAULT NULL)
  return BOOLEAN;
  pragma FIPSFLAG('REGEXP_LIKE', 1452);

Well, in the sql reference it is placed with LIKE expression together, is described as similar to LIKE expression, you can't use REGEXP_LIKE in SELECT list directly ( unlike regexp_substr,regexp_instr and regexp_replace ) - for me that are reasons enough to consider it not as function but as expression.
Of course, the point was - stirctily
;-)

Best regards

Maxim

Stand corrected, learned something new...

Best regards

Maxim

and, to name just a few documented,

nchr, to_binary_double, to_binary_float

and one undocumented just for fun

begin 
  if (xor(true,false)) then null;
  end if;
end;
/

PL/SQL procedure successfully completed.

Disclaimer: do not use function like XOR, REVERSE or SYS_OP_MAP_NONNULL in your production code because they are undocumented, unsupported, they may be removed in any patchset without notice, and they may not work as expected

Message was edited by:
Laurent Schneider
nchr() corrected

Message was edited by:
Laurent Schneider
I removed localtime function from the list...

and, to complete the list

SCN_TO_TIMESTAMP            
STATS_T_TEST_INDEP          
STATS_T_TEST_INDEPU         
STATS_T_TEST_ONE            
STATS_T_TEST_PAIRED         
TIMESTAMP_TO_SCN            
XMLCOLATTVAL          

glad that I cleared your doubt about REGEXP_LIKE!

what about FIRST? Well, FIRST and LAST are documented as functions, but they are not regular functions. They are almost keywords imho !

as is OVERLAPS ...

I did not know the OVERLAPS operator !

Thanks for the hint

select 1 from dual 
where (date '2000-01-01', date '2001-01-01') 
overlaps (date '2000-07-01',date '2001-07-01'); 

1

True. The talk is about built-in functions ;-)

So what about all those »functions« under sys/xdb/system ..., which are (I think) not documented, as e.g.:

select sys.sys_nt_collect_imp (cast ('01' as raw(1))) from dual
/
select sys.sysevent from dual
/
select sys.instance_num from dual
/

?

This is new to me. Can you provide any link which describes this operator with some example?

Regards.

Satyaki De.

author of this page is going to be busy this week-end ;-)

no I did not find any related to oracle

but it is implemented in other sql engines. so I just tried

http://books.google.com/books?id=e0vdxbY9ZocC&pg=PA319&lpg=PA319&dq=oracle+%22overlaps+operator%22&source=web&ots=QSKAivC6-n&sig=s2Lc2WyzwQ6aicN8tkIYw1XFJlA&output=html

Not clear with this link. I've tried but not getting enough information. Please, share your thought -

satyaki>
satyaki>create table meeting
  2    (
  3       id      number(5),
  4       st_dt   date,
  5       en_dt   date
  6    );

Table created.

satyaki>
satyaki>
satyaki>insert into meeting values(1,sysdate,sysdate+5);

1 row created.

satyaki>
satyaki>
satyaki>insert into meeting values(2,sysdate+4,sysdate+7);

1 row created.

satyaki>
satyaki>
satyaki>insert into meeting values(3,sysdate+8,sysdate+9);

1 row created.

satyaki>
satyaki>
satyaki>commit;

Commit complete.

satyaki>
satyaki>select * from meeting;

        ID ST_DT     EN_DT
---------- --------- ---------
         1 24-AUG-07 29-AUG-07
         2 28-AUG-07 31-AUG-07
         3 01-SEP-07 02-SEP-07

satyaki>

satyaki>select 1 "Ovrlap"
  2  from meeting
  3  where (st_dt,en_dt) overlaps (st_dt,en_dt);

    Ovrlap
----------
         1
         1
         1

satyaki>

After watching this, i was bit confused. Or, whether i've done something wrong here. Please rectify my error.

Regards.

Satyaki De.

In [url http://forums.oracle.com/forums/thread.jspa?messageID=1690280&#1690280]this thread Alex Nuijten introduced the OVERLAPS function to me and other forum members. He has a nice example in there as well.

Regards,
Rob.

select T1.ID, T2.ID, 
  CASE WHEN (T1.st_dt,T1.en_dt) overlaps (T2.st_dt,T2.en_dt) THEN 'YES' ELSE 'NO' END OVERLAP
from meeting T1, MEETING T2;

        ID         ID OVE
---------- ---------- ---
         1          1 YES
         1          2 YES
         1          3 NO
         2          1 YES
         2          2 YES
         2          3 NO
         3          1 NO
         3          2 NO
         3          3 YES

Thanks Rob & Laurent.

Now, it is clear.

Regards.

Satyaki De.