Archive for October, 2006

Interval data type not displayed in SQLDeveloper

October 24, 2006

If you select an interval data type column from a table or simply do math on two timestamp columns, the data is not displayed in SQLDeveloper. I guess this is a similar issue as the dbms_session.set_nls I blogged about.
There’s a related thread at OTN SQL Developer forum as well.
Of course, the workaround is to_char. Reiterate my comment. I don’t dispute that to_char should be used in production code when needed, but that doesn’t give SQLDeveloper an excuse for not doing the right thing in the first place. It may be just an additional query of v$nls_parameters.

Advertisements

Black-box testing of Cognos using HtmlUnit

October 24, 2006

Building a report in Cognos is one thing, testing of both correctness and performance is another.
It is possible to use HtmlUnit to do black-box testing. The process is pretty easy as HtmlUnit is just another HttpClient, like a browser. The annoying thing is JavaScript. Cognos uses JavaScript extensively and HtmlUnit’s support of JavaScript is spotty. I actually disabled JavaScript altogether and build my own custom Java code to emulate JavaScript. The key is to set values for all those elements in the formWarpRequest form correctly. Coupled with Congos tracing and DB tracing(Oracle for me), it’s possible to get a complete picture of a report run.
There are tons of similar packages available like HttpUnit or WebTest. Actually, WebTest looks very flexible and possibly no coding at all.

SQL Developer customization

October 16, 2006

One of the most common complaints about SQLDeveloper is that, unlike SQLPlus, which lets us run a glogin.sql and login.sql script to customize, SQLDeveloper doesn’t provide such a mechanism.
To customize, we could use database logon trigger as the following, be sure to grant select any dictionary privilege to the owner of the procedure db_trigger_proc.

create or replace procedure db_trigger_proc
is
  p_row v$session%rowtype;
begin
  select * into p_row
  from v$session
  where sid=(select sid from v$mystat where rownum=1);

  case
  when p_row.module = 'SQL Developer' then
  execute immediate 'alter session set nls_date_format='
  ||chr(39)||'YYYY-MM-DD HH24:MI:SS'||chr(39);
  -- add customization for other programs if needed.
  else null;
  end case;

  exception when NO_DATA_FOUND then null;
end;

create or replace trigger db_trigger
after logon on database
begin
db_trigger_proc;
end;

It’s SQLDeveloper

October 13, 2006

If I run the following in SQL Developer:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
select sysdate from dual;
begin
dbms_session.set_nls('nls_date_format','''DD-MON-YY''');
end;
/
select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
select sysdate from dual;

Here’s the output from SQL Developer:
alter session set succeeded.

PARAMETER                                                        VALUE                                                           
—————————————————————- —————————————————————-
NLS_DATE_FORMAT                                                  YYYY-MM-DD HH24:MI:SS                                           

1 rows selected

SYSDATE                  
————————-
2006-10-13 12:38:06      

1 rows selected

anonymous block completed

PARAMETER                                                        VALUE                                                           
—————————————————————- —————————————————————-
NLS_DATE_FORMAT                                                  DD-MON-YY                                                        <– This proves it’s not the database.

1 rows selected

SYSDATE                  
————————-
2006-10-13 12:38:06      

1 rows selected

So maybe SQLDeveloper is caching NLS parameters?

dbms_session.set_nls works for SQLPlus, not SQL Developer?

October 12, 2006

This is in SQLPlus,

SQL> alter session set nls_date_format=’YYYY-MM-DD HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE
——————-
2006-10-12 12:31:20

SQL> begin
  2   dbms_session.set_nls(‘nls_date_format’,”’DD-MON-YY”’);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
———
12-OCT-06

dbms_session.set_nls works perfectly, however, in SQLDeveloper, when I run the same thing:
alter session set succeeded.

SYSDATE                  
————————-
2006-10-12 12:30:42      

1 rows selected

anonymous block completed

SYSDATE                  
————————-
2006-10-12 12:30:42       <– Notice how the format is not changed.

1 rows selected

to_date and to_timestamp

October 5, 2006

I noticed the 9.2.0.7 and 10.2.0.1 CBO evaluates to_date and to_timestamp differently.
Here’s a test:

drop table t;
create table t (c timestamp(9));
— insert a whole year worth of timestamps, 5 minutes apart. Gather stats
begin
for i in 0..364 loop
for j in 0..12*24-1 loop
insert into t values (to_date( ‘2003-01-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’ ) + i + j/24/12);
end loop;
end loop;
dbms_stats.gather_table_stats(user, ‘T’,cascade=>true);
end;
/

— Now use to_date
explain plan for
select * from t where c between to_date( ‘2003-02-14 00:00:00′,’yyyy-mm-dd hh24:mi:ss’ )
and to_date( ‘2003-02-18 00:00:00′,’yyyy-mm-dd hh24:mi:ss’ );
And here’s the plan:
 
————————————————————————-
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
————————————————————————-
|   0 | SELECT STATEMENT     |             |  1154 | 12694 |    75  (59)|
|*  1 |  TABLE ACCESS FULL   | T           |  1154 | 12694 |    75  (59)|
————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   1 – filter(“T”.”C”<=TIMESTAMP’2003-02-18 00:00:00′ AND
              “T”.”C”>=TIMESTAMP’2003-02-14 00:00:00′ )

So far so good. CBO converts to_date to timestamp constant and gets the correct cardinality. 4 days * 24 * 12 = 1152. 1154 is very close.

— now to_timestamp
explain plan for
select * from t where c between to_timestamp(‘2003-02-14 00:00:00.000′,’yyyy-mm-dd hh24:mi:ss.ff3’ )
and to_timestamp(‘2003-02-18 00:00:00.000′,’yyyy-mm-dd hh24:mi:ss.ff3’ );

This is the plan
 
————————————————————————-
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
————————————————————————-
|   0 | SELECT STATEMENT     |             |   263 |  2893 |   179  (83)|
|*  1 |  FILTER              |             |       |       |            |
|*  2 |   TABLE ACCESS FULL  | T           |   263 |  2893 |   179  (83)|
————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   1 – filter(TO_TIMESTAMP(‘2003-02-14
              00:00:00.000′,:B1)<=TO_TIMESTAMP(‘2003-02-18 00:00:00.000’,:B2))
   2 – filter(“T”.”C”>=TO_TIMESTAMP(‘2003-02-14 00:00:00.000’,:B1) AND
              “T”.”C”<=TO_TIMESTAMP(‘2003-02-18 00:00:00.000’,:B2))

Notice how Oracle converts the format string to bind variables. That’s when the 0.25% rule kicks in and to_timestamp behaves almost like any PL/SQL function. 365*24*12*.05*.05=262.8. That’s where the cardinality 263 comes from.

This could result in disaster when the num_rows for T is small and T is joined to other tables. If CBO evaluates the cardinality down to 1…