Laurent Schneider

SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase.

SQL> SELECT DUMMY FROM DUAL;
D
-
X

SQL> select dummy from dual;
D
-
X


Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and columns.


/* Formatted on 2008/09/19 10:00
(Formatter Plus v4.8.8) */
SELECT dummy
FROM DUAL;


Well, Toad decided to put DUAL in uppercase. Anyway, this is no authority, define your own guideline and stick to it. If you use TOAD a lot, it is maybe OK to use lowercase for non-keywords.

The column dummy is actually the uppercase “DUMMY” column of “DUAL”. Putting double quotes around a column of table makes it case sensitive, so “DUMMY” is not “dummy”.

SQL> select "dummy" from "dual";
select "dummy" from "dual"
*
Error at line 1
ORA-00942: table or view does not exist

SQL> SELECT "DUMMY" FROM "DUAL";
D
-
X


OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!

SQL> ALTER USER scott IDENTIFIED BY BIG;
User altered.
SQL> connect scott/BIG
Connected.
SQL> /* Formatted on 2008/09/19 10:17 (Formatter Plus v4.8.8) */
SQL> ALTER USER scott IDENTIFIED BY big;
SQL> connect scott/BIG
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.


Congrats to Denes Kubicek for being the first journeyman on OTN.

OTN stars Justin and Nicolas (18954 and 15321 posts) do not get this title, as we Oracle Ace are sticked to our Ace logos. OTN upgraded the forums recently, many posts about this :

OTN Forums – Change the look and feel yourself

OTN Forums Update – Take Two

New OTN Forums – not great yet

OTN forums get a new look

OTN Forums Upgrade

Forums.oracle.comUpgrade: New Rewards System/Known Issues

In the last link, Justin explains the new ranking system. Aces are aces, and everyone else but Denes are newbies 😉

Ow, I forget, they got smileys too

Happy :)
Sad :(
Grin :D
Love :x
Mischief ;\
Cool B-)
Devil ]:)
Silly :p
Angry X-(
Laugh :^0
Wink ;)
Blush :8}
Cry :_|
Confused ?:|
Shocked :0
Plain :|

one colleague just showed me how to insert duplicate rows in a table that has a primary key.


create table t(x number primary key);[F5]
Table created.
insert into t values (1);[F5]
1 row created.
insert into t values (1);[F5]
1 row created.


Hey, what happened? It took me quite a while to figure out which feature of Toad was “allowing” this.

Let’s look at Toad.ini (or View – ToadOptions – Transaction)

[SETTINGS]
ALWAYSROLLBACK=1
THREADQUERIES=1


Which means, Toad is starting a separate session each time a query is run (by F5 for instance) and at the end of this child session, it rollbacks. This is not the default, but this is pure magic!

Let’s prove it :

select count(*) from t where x=1;[F5]

COUNT(*)
----------
0

1 row selected.


Oh no, not another post about COUNT(*) and COUNT(1)

Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow.


SQL> create table t as select rownum id,
2 lpad('a',4000,'a') a,
3 lpad('b',4000,'b') b,
4 lpad('c',4000,'c') c,
5 lpad('d',4000,'d') d
6 from dual connect by level<10000; Table created. SQL> create index i on t(id);

Index created.

SQL> alter table t add primary key (id)
2 using index i disable novalidate;

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'I')

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> select count(id) from t;

COUNT(ID)
----------
9999

Elapsed: 00:00:00.01
SQL> select count(*) from t;

COUNT(*)
----------
9999

Elapsed: 00:00:01.43


My count(*) just takes too long… Why that?

I have no index on a not null column. The primary key is disabled.

Of course the count(pk) does not count the rows with pk=null, but it is faster 🙂

What is the difference between rollbac and rollback?


SQL> create table t as select 1 x from dual;

Table created.

SQL> update t set x=2;

1 row updated.

SQL> savepoint a;

Savepoint created.

SQL> update t set x=3;

1 row updated.

SQL> rollbac to savepoint a;
Rollback complete.
SQL> select * from t;
X
----------
1


WTF! rollbac does not seem to work correctly 😉

Actually, ROLL, ROLLB, ROLLBA and ROLLBAC are not valid SQL statements. However, SQLPLUS recognizes it a sqlplus statement but this behavior is not documented.


SQL> del *
SQL> roll
Rollback complete.
SQL> list
SP2-0223: No lines in SQL buffer.


The statement does not end with semi-column and is not stored in the SQLPLUS buffer.

So it is not a SQL command.

the default isolation level is READ COMMITTED. It means, a session read the committed data.

Session 1:

SQL> set transaction isolation level read committed;

Transaction set.


Session 2:

SQL> update emp set sal=4000 where ename='SCOTT';

1 row updated.


Session 1:

SQL> select sal from emp where ename='SCOTT';

SAL
----------
3000


Session 2:

SQL> commit;
Commit complete.


Session 1:

SQL> select sal from emp where ename='SCOTT';

SAL
----------
4000
SQL> update emp set sal=3000 where ename='SCOTT';

1 row updated.

SQL> commit;
Commit complete.


When the session 1 reads the salary of Scott, it gets the value that is committed in the database.

Another isolation level is SERIALIZABLE.

Session 1:

SQL> set transaction isolation level serializable;

Transaction set.


Session 2:

SQL> update emp set sal=5000 where ename='SCOTT';

1 row updated.

SQL> commit;

Commit complete.


Session 1:

SQL> select sal from emp where ename='SCOTT';

SAL
----------
3000

SQL> update emp set sal=sal+1;
update emp set sal=sal+1
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
SQL> roll
Rollback complete.
SQL> select sal from emp where ename='SCOTT';

SAL
----------
5000

SQL> update emp set sal=3000 where ename='SCOTT';

1 row updated.

SQL> commit;

Commit complete.


In session 1, the isolation level of the transaction is set to SERIALIZABLE. Session 2 update the salary of Scott to 5000 and commits. The session 1 therefore does not read committed data and any tentative to change the committed data will fail. Roll[back;] ends the transaction. The session 1 can then read committed data and update the salary to 3000.

Ok, let’s imagine you have to interview an OCM and you want to ask him a very difficult question 😉 :

– Is it possible in Oracle to read uncommitted data from another session?

Let’s try 🙂

Session 1:

SQL> var rc number
SQL> set autop on
SQL> select sal from emp where ename='SCOTT';

SAL
----------
3000

SQL> exec :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1),
DBMS_XA.TMNOFLAGS)

PL/SQL procedure successfully completed.

RC
----------
0

SQL>
SQL> UPDATE emp SET sal=6000 WHERE ename='SCOTT';

1 row updated.

SQL> exec :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1),
DBMS_XA.TMSUSPEND)

PL/SQL procedure successfully completed.

RC
----------
0


Session 2:

SQL> select sal from emp where ename='SCOTT';

SAL
----------
3000

SQL> var sal number
SQL> var rc number
SQL> set autop on
SQL> begin
:rc:=DBMS_XA.XA_START(DBMS_XA_XID(1),DBMS_XA.TMRESUME);
SELECT SAL INTO :SAL FROM EMP WHERE ENAME='SCOTT';
:rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), DBMS_XA.TMSUCCESS);
:rc:=DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(1));
end;
/

PL/SQL procedure successfully completed.

SAL
----------
6000

RC
----------
0

SQL> select sal from emp where ename='SCOTT';

SAL
----------
3000


So yes, you can read uncommitted data in a global transaction from another session. But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle

The width of a columns is known before execution.

Well, in most cases…


SELECT TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=FRENCH')
FROM DUAL;
TO_CHAR(
--------
SAMEDI


The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8.


SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWO THOUSAND EIGHT


Oracle thinks the length is at most 42 characters. This is a bad guess, the year 7777 is the longest to spell and is only 27 characters. So the width is 42.

SELECT TO_CHAR(SYSDATE,'JSP') FROM DUAL;
TO_CHAR(SYSDATE,'JSP')
---------------------------------------------------------
---------------------
TWO MILLION FOUR HUNDRED FIFTY-FOUR THOUSAND SIX HUNDRED
EIGHTY-EIGHT


again, this is a bad guess, the maximum length of a spelled Julian day is 77 (May 9, 4388) not 78.

let’s try with spelling the nanoseconds :

select to_char(current_timestamp,'FF9SP') from dual;
TO_CHAR(CURRENT_TIMESTAMP,'FF9SP')
-------------------------------------------------------
-----------------------
SEVENTY-FOUR MILLION


here 78 is a really bad guess… the nanoseconds could be 100 character long !

What happened then :

select to_char(timestamp '0001-01-01 00:00:00.777777777',
'FFSP') from dual;
select to_char(timestamp '0001-01-01 00:00:00.777777777',
*
ERROR at line 1:
ORA-01877: string is too long for internal buffer


The query fails. Does it means the maximum length returned by TO_CHAR is 78? Not at all!

It simply means Oracle sets the width column to something too small to contain the result.

By setting the column to something longer, the query should work…

select cast(to_char(timestamp '0001-01-01 00:00:00.
777777777','FFSP') as varchar2(100)) from dual;
CAST(TO_CHAR(TIMESTAMP'0001-01-0100:00:00.777777777','FFSP')
------------------------------------------------------------
ASVARCHAR2(100))
----------------------------------------
SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-
SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN


Ok, let’s do something perverse

Let set the CURSOR_SHARING to FORCE !

Have a look :


SQL> alter session set cursor_sharing='FORCE';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1000000,'J'),'JSP')
----------------------------------------------------------
-----------------
ONE MILLION


the width of the column is evaluated to 75. 75 however is not the maximum length!


SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
select to_char(to_date(1777777,'J'),'JSP') from dual
*
ERROR at line 1:
ORA-01801: date format is too long for internal buffer


Not only CURSOR_SHARING=FORCE influence the output of the query, but it also make the SQL failing.

Let’s revert to EXACT for confirmation.

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_
-----------
ONE MILLION

SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1777777,'J'),'JSP')
--------------------------------------------------------------
--------------
ONE MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED
SEVENTY-SEVEN


I noticed a side effect of SET LONGC today…

The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80.

So why not setting it to the maximum?

Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE

SQL> create view v as select
'12345678910111213141516171819202' x
from dual;

View created.

SQL> set lin 80 long 20 longc 20
SQL> select text from user_views where view_name='V';

TEXT
--------------------
select '123456789101

SQL> set lin 80 long 1000000000 longc 20
SQL> select text from user_views where view_name='V';

TEXT
--------------------
select '123456789101
11213141516171819202
' x from dual

SQL> set lin 80 long 1000000000 longc 1000000000
SQL> select text from user_views where view_name='V';

TEXT
-----------------------------------------------------
select '12345678910111213141516171819202' x from dual


When I use a LONG setting smaller than the length of the TEXT column, I got it truncated. When I use a huge LONG setting but a LONGCHUNKSIZE setting smaller than the length of the TEXT column, I got it wrapped. When both are huge, it seems I am getting the expecting result. So why not setting SET LONG 2000000000 LONGC 2000000000 in your login.sql ?

Have a look :


SQL> SET LONG 10 LONGC 10 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X
----------
X

Elapsed: 00:00:00.01
SQL> SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X')
------------
X

Elapsed: 00:00:00.00
SQL> SET LONG 1000000000 LONGC 1000000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X')
------------
X

Elapsed: 00:00:02.06


2 seconds to select 1 character from dual !

I also tried to set it to 2000000000 but since I do not have 2Gb of free memory my notebook started swapping so badly I could not even move my mouse 🙁

I just notice a new blog on blogs.oracle.com :
Oracle Certification Blog

This is probably the right place to ask questions or get news about certification.

Out of the many questions I may ask here are just a few I am preparing.

– When will the OCM upgrade for 9i OCM be available ?
– When will be the 9i exams retired ?
– ocm upgrade : 10g upgrade has not been finalized yet. Why ?
– What is the expected date for 11g OCM ?

Ok, I have add this blog to my feed and will start posting comments soon …

How do I retrieve the second highest salary of emp?

There is no right or wrong treatment of duplicates, there are only buggy specifications…

There are plenty of ways to do this.

An Oracle 7 approach would be

SELECT MAX(SAL) 
FROM EMP, (
  SELECT MAX(SAL) MAXSAL FROM EMP) 
WHERE SAL>MAXSAL;

Using TOP-n queries I could use

 
SELECT SAL 
FROM (
  SELECT ROWNUM R,SAL FROM (
    SELECT SAL FROM EMP ORDER BY SAL DESC)) 
WHERE R=2;

Using ROW_NUMBER (or RANK or DENSE_RANK), I could use

SELECT SAL 
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) R,SAL FROM EMP) 
WHERE R=2;

Another approach would be to create a SecondMax aggregate function, as the following example from the doc
Example 11-12 How to Create and Use a User-Defined Aggregate Function

SELECT SecondMax(sal)
FROM emp;

Ok, if I know I have 14 employees in EMP, retrieving the second highest is way easier 🙂

SELECT PERCENTILE_DISC(2/14) WITHIN GROUP (ORDER BY SAL DESC) 
FROM EMP;

I have been using Vista for about 3 months and finally found a way to start my databases with a single click 🙂

The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. Way to much…

I have googled quite a lot and found Vista setuid – How to elevate without prompting

Ok, here we go :

1) run mmc
2) file – add snapin
3) Task Scheduler Library – Create Task
4) Name: startoracle
Run with highest privileges
Actions – New – Start a program – Program:net – Arguments: start OracleOraDb11g_home1TNSListener
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC01
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC02
…
5) create a batch file that runs : schtasks /run /tn startoracle

Same for stoporacle

It seems to work fine. Do not play too much with mmc if you do not know it. It is a powerful tool ❗

It is quite a while I have not posted about Linux. The reason is I have bought in new notebook three months ago with Vista and did not have a program to resize the partition…

Do I need a program?
NO, I do not !!!

Vista has disk management that can resize my online partition.

Run: compmgmt.msc /s
Or go to control panel.

And shrink/extend your volumes!

How do I get the current number of milliseconds since 1970-01-01.

I still have no access to oracle.com so I created a new account to answer this question.

Either you use the difference between timestamp ‘1970-01-01 00:00:00 +00:00’ and current_timestamp, or you can use java, which is more portable.

YMMV

create function epoch return number as language java name
'java.lang.System.currentTimeMillis() return int';
/
select epoch from dual;
EPOCH
----------------
1214562599878


One year ago I started writing a book on SQL. Writing a book is an amazing amount of work and I am glad I got helped from my five reviewers Chen, Andrew, Tom, Marco and Lutz.

It will ship in December 2008.

http://amazon.com/Advanced-Oracle-SQL-Programming-Focus/dp/0977671585

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone…

Ok, let’s recreate the scott schema.

C:> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> spool scott.txt
SYS@lsc02> drop user scott cascade;
User dropped.
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:> sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
MILLER

12 rows selected.


Hey Scott, I miss you!

Let’s see if I can dig out something in the log file I created with spool scott.txt

(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87',
'dd-mm-rr')-85,3000,NULL,20)
ERROR at line 2:
ORA-01858: a non-numeric character was found
where a numeric was expected


Hey! the UTLSAMPL script is not supposed to work where JUL is not a month ?
reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE /s /f NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ FRENCH_SWITZERLAND.WE8MSWIN1252

Since FRENCH has no month called JUL the UTLSAMPL failed to insert SCOTT. Let’s fix the issue :
C:>reg add HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
/v NLS_LANG /t reg_sz /d AMERICAN_AMERICA.WE8MSWIN1252
Value NLS_LANG exists, overwrite(Yes/No)? yes
The operation completed successfully.
C:>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252

and retry

C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.


Welcome back Scott!

Word 2007 allows to write a blog post and I thought I must try 🙂

• I used Windows Live Writer to add this comment

I never used Oracle Web Conference OWC, for security reason. Today I created one SR and read first time about Demo It To Oracle (DITO). This is basically a link CamStudio.org, which is a tool that records AVI file and converts AVI to flash SWF file. This is nice to have because you do need to grant access to your desktop to Oracle Support and you can record what’s happening on your screen !

Let’s have a look

defaultsysdate.swf

I just noticed 10.2.0.4 is out. I started downloading 1,916,781 KB !

RTFM is not the best answer …

man crontab
SunOS 5.10 Last change: 10 Nov 2005

User Commands crontab(1)
A crontab file consists of lines of six fields each. The
fields are separated by spaces or tabs. The first five are
integer patterns that specify the following:

minute (0-59),
hour (0-23),
day of the month (1-31),
month of the year (1-12),
day of the week (0-6 with 0=Sunday).


so far so good. But read this

Each of these patterns can be either an asterisk (meaning
all legal values) or a list of elements separated by commas.
An element is either a number or two numbers separated by a
minus sign (meaning an inclusive range). Time specified here
is interpreted in the timezone of the cron(1M) daemon, which
is set system-wide in /etc/default/init. Entries do not use
the invoking user's timezone. The specification of days can
be made by two fields (day of the month and day of the
week). Both are adhered to if specified as a list of ele-
ments. See .


See .

Let’s imagine you want to run a job today, Thursday May 15, 2008 at 2:15pm
You write:
15 14 15 05 4 /tmp/run-my-job

The job will be run Today at 2:15, next year Friday May 15, 2009 at 2:15pm and next week Thursday May 22, 2008 at 2:15pm…

The correct method is to use
15 14 15 05 * /tmp/run-my-job

Specifying both day of week and day of month is not what I expected 😈

warning: the script below is destructive and not 100% safe

update: it is unsafe to drop SYS_ objects, check for instance note 579399.1

A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; was suggested as an easy answer, but I commented that create user must re-grant quotas, roles, system privileges, table privileges. Create user must also set the correct security status of the account (password, case-sensitive password, lock status, expiration status, profile), and the password history and failed login history is lost. Also it must set the default and temporary tablespaces.

It is also important to note that “drop user” requires dba privileges, when dropping the own object does not require privileges

To drop all objects I tried with plsql and a dictionary loop.

select object_type, count(*)
from user_objects
group by object_type;

OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 6
TYPE BODY 1
INDEXTYPE 1
PROCEDURE 1
JAVA CLASS 2
JAVA RESOURCE 1
JAVA SOURCE 1
FUNCTION 1
TABLE 10
TRIGGER 1
TYPE 1
MATERIALIZED VIEW 1
DATABASE LINK 1
PACKAGE BODY 1
CLUSTER 1
DIMENSION 1
OPERATOR 1
SEQUENCE 1
PACKAGE 1

19 rows selected.

purge recyclebin;

Purge successfully completed.

begin
for f in (
select object_type, object_name from user_objects
where object_type in (
‘MATERIALIZED VIEW’)) loop
execute immediate
‘drop materialized view “‘||f.object_name||'” preserve table’;
end loop;
for f in (
select table_name from user_tables) loop
execute immediate
‘drop table “‘||f.table_name||'” cascade constraints’;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘DIMENSION’,’CLUSTER’,’SEQUENCE’,
‘VIEW’,’FUNCTION’,’PROCEDURE’,
‘PACKAGE’,’SYNONYM’,’DATABASE LINK’,
‘INDEXTYPE’)
and object_name like ‘SYS_%$’) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA SOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA RESOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA CLASS’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘TYPE’,’OPERATOR’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||
f.object_name||'” force’;
end loop;
end;
/

PL/SQL procedure successfully completed.
select * from user_objects

no rows selected.


All objects of the current schema disappeared, do not try this as sys, this script is destructive with no confirmation

I answered a question on otn today about distinct.

Reprased, how to select distinct collection?


select job, collect(distinct deptno) deptnos
from emp group by job;

JOB DEPTNOS
--------- -------------------------------------------
ANALYST SYSTPTJCzBffh0AjgQ59n0o3QCA==(20)
CLERK SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
MANAGER SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10)
SALESMAN SYSTPTJCzBffh0AjgQ59n0o3QCA==(30)

5 rows selected.

select distinct collect(distinct deptno) deptnos
from emp group by job;

ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method


the message is clear, i need a MAP or ORDER method. Ok, I can do this


create type tt_n as table of number(2);
/

Type created.

create type tt_n_ord as object(
c tt_n,
order member function
eq (tc tt_n_ord) return integer);
/

Type created.

create type body tt_n_ord as
order member function
eq (tc tt_n_ord) return integer
is
begin
if c=tc.c
then return 0;
else return 1;
end if;
end;
end;
/

Type body created.

select distinct
tt_n_ord(cast(collect(distinct deptno) as tt_n))
from emp group by job;

TT_N_ORD(CAST(COLLECT(DISTINCTDEPTNO)ASTT_N))(C)
------------------------------------------------
TT_N_ORD(TT_N(30))
TT_N_ORD(TT_N(10))
TT_N_ORD(TT_N(10, 20, 30))
TT_N_ORD(TT_N(20))

4 rows selected.

Hey,
This plugin seems to do exactly what I am looking for, enable users to write code in comments.
Preserve Code Formatting

Please test here and give feedback

Warning: this changed the behavior of old posts too, so I have to reformat all older posts to no longer use < in code 🙁

Unix users will enjoy this : 10.2.0.4 is available on Solaris and HPUX
patchset 6810189

Still waiting for AIX…

Today I answered a question on otn regarding order by.

Let’s build a different test case.

You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column.

SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A;

Grant succeeded.

SYS@LSC01> GRANT SELECT ON SCOTT.EMP TO USER_A WITH GRANT OPTION;

Grant succeeded.

SYS@LSC01> CREATE VIEW USER_A.V AS SELECT ENAME FROM SCOTT.EMP
ORDER BY HIREDATE;

View created.

SYS@LSC01> GRANT CREATE SESSION TO USER_B IDENTIFIED BY B;

Grant succeeded.

SYS@LSC01> GRANT SELECT ON USER_A.V TO USER_B;

Grant succeeded.

SYS@LSC01> CONNECT USER_B/B
Connected.
USER_B@LSC01> SELECT ROWNUM, ENAME FROM USER_A.V WHERE ROWNUM<4; ROWNUM ENAME ---------- ---------- 1 SMITH 2 ALLEN 3 WARD
To get top 3, that was easy 8)

USER_B@LSC01> SELECT R, ENAME FROM
2 (SELECT ROWNUM R,ENAME
3 FROM USER_A.V
4 ORDER BY ROWNUM DESC)
5 WHERE ROWNUM<4 ORDER BY R; R ENAME ---------- ---------- 12 MILLER 13 SCOTT 14 ADAMS

Here we have a sorted view. The OP had an external file. It could be an XML input, or any table or collection that is sorted but does not have a sort key

I have decided to remove disqus comments. What I considered to be a bonus, that is CODE POSTING, appears to be worst than before, because the PRE tag does not work.

The main reason I switched back to last week behavior is that the COMMENT FEED does not work any longer. And I hate changes that annoy my readers.

I will keep looking for a comment plugins that allow posting of code

The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way :
SQL> create or replace directory alert as
2 '/app/oracle/diag/rdbms/lsc01/lsc01/alert';

Directory created.

SQL> var c clob
SQL> declare
2 b bfile := bfilename('ALERT','log.xml');
3 begin
4 dbms_lob.open(b,dbms_lob.file_readonly);
5 dbms_lob.createtemporary(:c,true);
6 dbms_lob.loadfromfile(:c,b,dbms_lob.lobmaxsize);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select extractvalue(xmlparse(content :c),
2 '/msg[@time="2008-03-30T01:01:13.704+01:00"]/txt/text()')
3 from dual;
EXTRACTVALUE(XMLPARSE(CONTENT:C),'/MSG[@TIME="2008-03-30T01:01:1
----------------------------------------------------------------

Starting ORACLE instance (normal)


I just replaced the default wordpress comments by Disqus comments. Disqus enable you to track your comments add picture and other fancy staff.

I have been looking for such a plugin for a very long time (and was too foul to develop it myself).

The amazing bonus with Disqus is that you can post code! Your x<1 will be code in code, not a pseudo html tag annoying more than one of my fellow reader.

Feel free to test it on this post.

Thanks to Yas for making me discover this on : Oracle Today

I hope I will not have have to many issues with this new plugin! Feedback welcome

I have enabled tracing in a session and now I want to retrieve the name of the tracefile.

Ex: my session has sid 335.

How do I retrieve trace file name from sqlplus ?

select pid from v$process where addr in
(select paddr from v$session where sid=335);

PID
----------
47


Now I can use oradebug to reveal tracefile name

SQL> oradebug setorapid 47
Unix process pid: 1372408, image: oracle@dbsrv01 (TNS V1-V3)
SQL> oradebug tracefile_name
/u01/app/oracle/admin/LSC01/udump/lsc01_ora_1372408.trc


Read valuable information about oradebug on this site, amoung others

blog.gralike.com

Marco is one of the top expert with XML and Database. Congrats Marco for receiving the Ace trophee 🙂

What is worst? To use DD-MON-RR or to use DD-MON-YY? When entering the birthday of my grandfather, I will write it as 31-JUL-1912, so both formats will be fine. If I enter it 31-JUL-12, both formats will be wrong. Ok, which date will I enter now and in the future? For short-time contracts I will enter dates like 01-AUG-08 or 31-DEC-11, both formats will be fine. For long time contracts like retirement saving plan I will enter 31-MAR-36, which is still fine, because I am old enough Juniors in my company will enter dates like 30-JUN-52, which will be fine with YY and wrong with RR or RRRR.

Ok, what is the recommended format then?

DD-MM-FXYYYY is probably fine. FX forces you to enter 4-digits years.


SQL> select
2 to_date('31-JUL-1912','DD-MON-YY') YY1912,
3 to_date('31-JUL-12','DD-MON-YY') YY12,
4 to_date('31-JUL-12','DD-MON-RR') RR12
5 from dual;

YY1912 YY12 RR12
---------- ---------- ----------
1912-07-31 2012-07-31 2012-07-31

SQL> select
2 to_date('31-MAR-2036','DD-MON-YY') YY2036,
3 to_date('31-MAR-36','DD-MON-YY') YY36,
4 to_date('31-MAR-36','DD-MON-RR') RR36
5 from dual;

YY2036 YY36 RR36
---------- ---------- ----------
2036-03-31 2036-03-31 2036-03-31

SQL> select
2 to_date('30-JUN-2052','DD-MON-YY') YY2052,
3 to_date('30-JUN-52','DD-MON-YY') YY52,
4 to_date('30-JUN-52','DD-MON-RR') RR52
5 from dual;

YY2052 YY52 RR52
---------- ---------- ----------
2052-06-30 2052-06-30 1952-06-30

SQL> select
2 to_date('8/4/2008','DD-MM-FXYYYY') FXYYYY2008
3 from dual;

FXYYYY2008
----------
2008-04-08

SQL> select
2 to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
3 from dual;
to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
*
ERROR at line 2:
ORA-01862: the numeric value does not match the length
of the format item

SQL>

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen

Ok, I give a try.

To make the query unreadable and unformatable I used no space, no new line, and I started by q’<'>‘ to confuses sqlinform.

Ok, I added undocumented constructs like
connect by .1 < 1. connect by .25 > rownum/100.
with 2 connect by, the first is ignored. The second is buggy in 10gR1 and returns 25 rows in 10gR1 (but 24 rows in 9i, 10gR2, 11g).

and group by () which means only one group. Note this clause is mandatory, you cannot remove it when using an aggregate function and a scalar subquery like in
select (select 1 from dual), count(*) from dual group by ();

The utl_raw and to_char converts a number to the string Oracle Community. I will keep terse on the formula.
The scalar subquery inside TAN returns 1. The regexp removes some characters out of the extract tag. _x0032_ is the alias of the column “2”. The v$reservedwords use UNIQUE to sort the rows (UNIQUE was sorting in 10gR1 for UNIQUE/DISTINCT) and the MAX(rownum) retrieve the 316th row (FROM) and 845th row (SELECT). Remember ROWNUM is evaluated BEFORE aggregation, therefore ROWNUM 845 exists! Note the “+” is an alias and not a string!

With the SELECT and FROM keyword I build a new query that I dynamically evaluates with dbms_xmlgen.getxml. Rest of the first part is fancy calculation.

The dburi is also dynamic.
dburitype(
'/SYS/DBA_PROCEDURES/ROW[PROCEDURE_NAME=''GETKEY'']/OBJECT_NAME'
).getxml()
Well, I could have obfuscated the text a bit more but I was short of time… This simply return the name of the package that have a procedure called GETKEY. Which is DBMS_OBFUSCATION_TOOLKIT_FFI. This is not very clean as the query would fail if you have a PROC.GETKEY in your schema. Well, I wanted to add this not very well know mechanism to query the database.

Last part is in dict, I select the pattern ‘[COTTON+XE]{4,}’ in dict and return the Mode (or modal score), which is CONTEXT.

A bit formatting and I get
"NVL2"(Q'<'>',"UTL_RAW".CAST_TO_VARC
------------------------------------
Oracle Community_Obfuscation_Contest