Laurent Schneider

What’s wrong with this code ?

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> drop table lsc_t ;

Table dropped.

SQL> create table lsc_t (x varchar2(255), y date);

Table created.

SQL> begin
  2    null;
  3  exception when others then
  4    insert into lsc_t (x,y)
  5          values (sqlerrm, sysdate);
  6  end;
  7  /

PL/SQL procedure successfully completed.

I was in the process of migrating a database with 10g and this piece of code became invalid

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table lsc_t ;

Table dropped.

SQL> create table lsc_t (x varchar2(255), y date);

Table created.

SQL> begin
  2    null;
  3  exception when others then
  4    insert into lsc_t (x,y)
  5          values (sqlerrm, sysdate);
  6  end;
  7  /
        values (sqlerrm, sysdate);
                *
ERROR at line 5:
ORA-06550: line 5, column 17:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL> 

Ok, the code is wrong. It has been running for half a decade in production but the exception was never reached.

Note it is a bug, and putting sqlerrm after sysdate would have generate the exception even in 9i

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> begin
  2    null;
  3  exception when others then
  4    insert into lsc_t (y,x)
  5          values (sysdate,sqlerrm);
  6  end;
  7  /
        values (sysdate,sqlerrm);
                        *
ERROR at line 5:
ORA-06550: line 5, column 25:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

Just having fun with 10g migration

dba, plsql sqlerrm

As a developer, you sometimes need to contact your dba to get an user trace. As a dba, sending trace files to developer is not much fun.

But how do you get access to your traces without any dba ?

One way could be to set _trace_files_public=true, but this is bad. It is undocumented, it is unsupported, there is no control to who can access the files, and probably it will not help if you have no access to the database server.

I prefer to provide a function that reads from the trace directory.

Ok, here we go :

CREATE DIRECTORY user_dump_dest AS
  '/app/oracle/admin/DB01/udump';

CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
   RETURN VARCHAR2
IS
   dest_loc   CLOB;
   src_loc    BFILE;
   ret        VARCHAR2 (4000);
BEGIN
   src_loc := BFILENAME ('USER_DUMP_DEST', file_name);
   DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
   DBMS_LOB.createtemporary (dest_loc, TRUE);
   DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
   ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
   DBMS_LOB.CLOSE (src_loc);
   RETURN ret;
END;
/

Just a small function that returns the first 4000 characters of the trace file. I could then grant execute on that function to the developers.

it works quite well

SELECT get_tracefile ('db01_ora_6224.trc')
  FROM DUAL;
GET_TRACEFILE('DB01_ORA_6224.TRC')                                      
----------------------------------------------------------------------
/app/oracle/admin/DB01/udump/db01_ora_6224.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /app/oracle/product/dbms/DB01
System name:  SunOS
Node name:  dbsrv01
Release:  5.8
Version:  Generic_117000-05
Machine:  sun4u
Instance name: DB01
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6224, image: oracle@dbsrv01 (TNS V1-V3)

*** SESSION ID:(273.54591) 2009-04-27 12:13:57.292
*** 2009-04-27 12:13:57.292
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0x3EED05050], 
[], [], [], [], [], []
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
Current SQL statement for this session:
alter PACKAGE "LSC_PKG" compile body
----- PL/SQL Call Stack -----
...

I should mention than granting access to trace files is a security issue as a hacker could dump some security modules. But it will be fine for your trusted developers.

dba, security trace

http://www.oracle.com/sun

I really enjoyed this news. It is both my favorite OS (Solaris) and my favorite database (Oracle) committing to a common future. I have been more than sceptical about Oracle Linux and Oracle Database Machine entering the Datacenter world, but now I feel relieved that Oracle is putting value in acquiring serious database platform (oracle/sparc)

On the application side, acquiring Java is more than a smart move against Websphere …

news oracle, sun

I just wanted to give to my readers a bit fun for Eastern…

Using the well known EMP table, retrieve the name of the job that is the less common…

The solution must be a one-liner SELECT statement (max 80 chars)

Have fun!

sql challenge

I just read a post from Barry Mc Gillin about apex on MacOsX.

After having being using Vista for one year, I would really welcome a Mac as my next notebook. But what about Oracle Database on Mac OS X?

According to otn, the latest release as of today is a deprecated 10gR1 for the deprecated platform PowerPC.

However, according to metalink, a 10gR2 is planned for the first quarter of 2009 (sic)
Db 10gR2 on MacOS X Intel
And 11g is planned too Db 11gR1 on MacOS X Intel, Schedule to be announced.

11g, installation, news, support Mac

How do I count all objects in the current schema?

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 1 09:58:46 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

SQL> select
  2    user,
  3    count(*)
  4  from
  5    user_objects
  6  where
  7    status='INVALID';
  user,
  *
ERROR at line 2:
ORA-00937: not a single-group group function

Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

It does not seem to work … What’s wrong?

sql group by