Author: Laurent Schneider

create table t( deptno number, firstname varchar2(10), lastname varchar2(10), hiredate date); insert into t values ( 10,’Jo’,’Smith’,date ‘2001-01-01′); insert into t values ( 10,’Jack’,’River’,date ‘2002-02-02’); to get the latest hiredate per department select deptno, max(hiredate) hiredate from t group by deptno; DEPTNO HIREDATE ———- ——— 10 02-FEB-02 if you want to get the name of […]

I answered this question twice, once on otn forums and once on developpez.net Here is the latest to get the third column of emp select column_name as name, extractvalue(column_value,’/ROW/’||column_name) as value from table(xmlsequence(cursor(select * from emp))), user_tab_columns where COLUMN_ID=3 and table_name=’EMP’ ; NAME VALUE —- ———- JOB CLERK JOB SALESMAN JOB SALESMAN JOB MANAGER JOB […]

Congratulation to Eddie Awad for becoming an Oracle ACE Director !

If you have receive ddl statements from your developer and you want to check if it matches the current state of the development database, because the developer have done a lot of change in a quick and undocumented manner, what are your options? I found this handy feature in Toad : 1) I create my […]

I will go to Oracle OpenWorld in San Francisco this year again

http://www.oracle.com/technology/software/products/database/index.html

REMINDER: Premier Support for Oracle Database 9.2 ends in July 2007 The 9iR2 premier support just ended. Now the 9i is under Extended Support. The good news, it is free until July 2008! First year Extended Support fee waived for Oracle9i Database Release 9.2

begin dbms_output.put_line( to_char( timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’)); end; / NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE Unfortunately, I could not get this in sql/10.2.0.2 select to_char( timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’) X from dual; ORA-01877: string is too long for internal buffer Well, since the string is too long, let’s try with […]

I have attended the sql certified expert beta exam this morning. There were a lot of errors in it, I added in the comment that they have to groundly review their regular expressions questions. There were a lot of rubbish question, but hardly any challenge, it is more like detecting the incorrect syntax. So I […]

I read on this site that Oracle11g GA is for 2007 August http://www.dba-oracle.com/t_oracle_11g_hits_the_streets.htm

I re-edited this post and it is unresolved yet. I thought it was related to system time, but apparently not 😮 SQL> create table t(x number); Table created. SQL> set transaction read only ; Transaction set. SQL> select * from t; select * from t * ERROR at line 1: ORA-01466: unable to read data […]

Oracle Certification Program: Exam Details I immediately registered for this exam and will do it next week, I just cannot wait for this one…

I do update t set x=:a; If I do it twice, I am doing a lot of unnecessary updates. This is true in an update, and also in the update clause of a MERGE. I need to take care of null, I can update null with something, or something with null, but update null with […]

I have read an excellent overview of DBMS_APPLICATION_INFO in Andy Campbell blog on If only… I have written a procedure P which is executing 5 statements. I want to add the name of the procedure P as a target. I do not want to hardcode the name however… So I can use $$PLSQL_UNIT in 10gR2 […]

I have attend the RAC beta exam this afternoon. 181 questions in 3.5 hours, it is a lot of questions! I prepared by reading the 2-day dba RAC document. There are also some questions about Maximum Availability Architecture (MAA) with physical and logical standby. Also some question which are Linux speci I have been short […]

watch the launch event live http://www.oracle.com/pls/ebn/live_viewer.main?p_shows_id=5717957

How can I read user input in plsql? kind of begin write(‘Enter a value for x : ‘); read(x); write(‘you enterred ‘||x); end; / the short answer is : you cannot do that. Ok, let’s try to do it in Linux ! $ cat interactiveplsql.sql set feedb off create or replace directory tmp as ‘/tmp’; […]

SQL> create or replace type t is 2 table of varchar2(12); 3 / Type created. SQL> create or replace function f return t is 2 begin return t(‘foo’); end; 3 / Function created. SQL> select * from table(f); COLUMN_VALUE ———— foo What is this column_value field? It is a pseudo-column. But you may want to […]

Lutz announced his 11g courses in Switzerland in this postOracle University Switzerland announces brand new Oracle 11g courses

warning, this test is cpu intensive, do not try on your productive server One user on the developpez.net French forums asked today about rewritting a division in a multiplication for tuning. Like select avg(sal)/2 from emp; in select avg(sal)*.5 from emp;. Well, I had to test this ! I execute 41055 divisions in a plsql […]

With dbca you can fasten the procedure of creating databases in your company. You can also run this in silent mode and create exactly the database you want, with your redo/undo size, with your parameters settings. You create the template once, and use it many times. Fast and easy 😀 I have one database which […]

Lazyness at the development can have dramatic costs in production and maintenance. I want to summarize why and where you should always qualify your columns. Why? when you select or modify data from a table, you must qualify the columns you are using so if the order of the column change, or if one column […]

I just read on the General Notes For Oracle Database – Enterprise Edition in Metalink that 10.1.0.5 is the terminal patchset for 10gR1

SQL*Plus is a not only the command-line interface to the database server, it is also a featured reporting tool with paging capabilities. The pagesize is the number of rows of one page. The default is 14 and the maximum is 50000. One of the common property of the page is the headers when selecting from […]

I have seen a confusion between sequence and system change number quite often. The sequence is a fairly little number which is the number of log switch since database creation (it can be resetted by open resetlogs). The thread is only relevant in RAC. In single-instance database, it is always 1. You can find the […]

This is one question I solved today with XML on the developpez.net/forums I have a table T Un,Trois,Cinq,Six Un,Deux,Quatre Trois Sept,Huit Un,Six I want to select un,deux,trois,quatre,cinq,six,sept,huit. I could well have written a plsql function like create or replace type c as object (value varchar2(40)); / create or replace type t_c as table of c; […]

do not miss yas comment ! tested in 10.2 create or replace procedure explain_plan(hash_value number) is begin insert into plan_table select null, (select nvl(max(plan_id),0)+1 from plan_table), timestamp, remarks, operation, options, object_node, object_owner, object_name, object_alias, null, object_type, optimizer, search_columns, id, parent_id, depth, position, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, other, other_xml, distribution, cpu_cost, io_cost, temp_space, […]

Views: SQL> create view v1 as select * from dual; View created. SQL> rename v1 to v2; Table renamed. Tables: SQL> create table t1 as select * from dual; Table created. SQL> rename t1 to t2; Table renamed. Sequences: SQL> create sequence s1; Sequence created. SQL> rename s1 to s2; Table renamed. Synonyms: SQL> create […]

On the developpez.net forums I answered a question about referential integrity. How can you delete/update a parent row when the child exist ? SQL> create table continent( 2 name varchar2(10), 3 constraint continent_pk primary key(name)); Table created. SQL> create table country( 2 name varchar2(10), 3 continent varchar2(10), 4 constraint country_pk 5 primary key(name), 6 constraint […]

the following works only on 10gR2 Table PRODUCTCATEGORY: CATEGORY (PK) Fruit Fish Table TMP_PRODUCT: PRODUCT CATEGORY Banana Fruit Chicken Meat Table PRODUCT: PRODUCT (PK) CATEGORY (FK) Now I want to load product SQL> insert into product select * from tmp_product; insert into product select * from tmp_product * ERROR at line 1: ORA-02291: integrity constraint […]

With merge you can insert, update or both I want to import changes from one table in another table. Table T1: ID NAME 1 Apple 2 Orange Table T2: ID NAME 1 Pineapple 3 Lemon To insert the missing row from T2 into T1, I could need the following subquery with INSERT. insert into t1 […]