ISO year week day to date

Trying to get date out of three of numbers. Three numbers are ISO standard year, ISO standard week and number of a day in a week. The first day of a week is Monday.

select to_date ('2010 40 4','iyyy iw d') from dual;

ORA-01820: format code cannot appear in date input format
01820. 00000 -  "format code cannot appear in date input format"

A date specification contained an invalid format code. Only the following
may be specified when entering a date: year, month, day, hours, minutes,
seconds, Julian day, A.M./P.M. and B.C./A.D.

select to_timestamp ('2010 40 4','iyyy iw d') from dual;

Surprise, no luck, the same error.

From Oracle support formely known as metalink can be found a statement that the feature has not been in such priority to be implemented. Build your own function. I am too lazy to do that. And as I know the timerange I am interested in I use brute force. Use data. We are in a database. It is built to store data. I will use that ability.

create table isoyearweekday_to_date(
isoyearweekday number(7) constraint isoyearweekday_to_date_pk primary key
, dat date not null) 
organization index;

insert into isoyearweekday_to_date 
select to_char(d,'iyyyiwd')
     , d 
  from (
   select to_date('17991231','yyyymmdd')+level d
     from dual 
   connect by level <= to_date('22000101','yyyymmdd')-to_date('18000101','yyyymmdd')+1
  )
;

select * from isoyearweekday_to_date where isoyearweekday = 2010404
;

2010404 07.10.2010

null - quilty or not

It is Friday evening. Watching a recording of Mentalist.

true - false - null
had some drinks - sober - a drink too much
innocent - pulled the trigger - too drunk to remember

Usefull indexes

Do we actually need such indexes?

select owner,table_name,index_name 
  from all_indexes 
 where distinct_keys < 2
   and num_rows > 100;

Ezconnect

Today I noticed the reason why I have not bothered to use ezconnect method with sqlplus connections.

sqlplus system/oracle@localhost/orcl

I do not like to reveal my passwords on screen if possible.

sqlplus system@localhost/orcl

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Additional quotes are needed with ezconnect, when I do not want to write the password on command line.

sqlplus system@\"localhost/orcl\"
Enter password:

or

sqlplus /nolog
SQL> conn system@"localhost/orcl"
Enter password:
Connected.

Seems like 11.2 client does not need NAMES.DIRECTORY_PATH= (ezconnect) line in sqlnet.ora anymore to use the method. 10.1, 10.2 and 11.1 version client installations need that line there.

extended audit_trail

Want to audit issued sql_text and binds in 11g database?

Documentation says that audit_trail parameter may have values

Parameter type String
Syntax AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }

Trying

SQL> alter system set audit_trail='db, extended' scope=spfile;
alter system set audit_trail='db, extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value db, extended for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Uups, the documented one did not work. I will try out the first suggested one.

SQL> alter system set audit_trail='extended' scope=spfile;
alter system set audit_trail='extended' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00096: invalid value EXTENDED for parameter audit_trail, must be from among
NONE | OS | DB | DB,EXTENDED | XML | XML,EXTENDED

Uups, Oracle did it again. DB,EXTENDED seems a bit closer to the one documented.

SQL> alter system set audit_trail='DB,EXTENDED' scope=spfile;
alter system set audit_trail='DB,EXTENDED' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value DB,EXTENDED for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Back to the original error message. Yet another quess...

SQL> alter system set audit_trail='db_extended' scope=spfile;

System altered.

Yes I found it. After reboot sql_text and binds are collected.

Should the documentation say

Parameter type String
Syntax AUDIT_TRAIL = { none | true | false | os | db | db_extended | xml }

No. The problem is that issuing a String parameter as a quoted 'string'.

The correct way to do documented way

SQL> alter system set audit_trail=db, extended scope=spfile;

System altered.

This way also the new xml, extended is accepted, if someone likes it that way. Also some old (10.1) and alternative ways are also accepted. Although we should use only the documented ones.

SQL> alter system set audit_trail=true scope=spfile;
SQL> alter system set audit_trail=false scope=spfile;
SQL> alter system set audit_trail=db_extended scope=spfile;
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> alter system set audit_trail=db,  extended scope=spfile;
...

So minor issue, but should the ORA-00096 error messages be updated? Maybe. Compared to other product error messages the information Oracle gives in those are mostly understandable.

Anoying SQL Developer

Yet another new installation of SQL Developer. I am writing SQL. The query includes ANALYTIC functions not AGGREGATE ones. SQL Developer hits and adds some group by clause somewhere in between my clause. Getting rid of that.

Tools

Preferences...

Code editor

Completion Insight

unselect Autogenerate GROUP BY clause.

OK

I so wish this would be the default.

Lean

Just had a pleasure to participate a session with Jim Coplien. His earlier presentation about the main issues seems to be found also online. Need to listen that again later. MVC and DCI. Model and data in both the part closest to the storage seems to be the first words to name software architectures. Maybe also the Lean Architecture book to be read.