How to get dbms_output to print line before the end of the procedure?

begin dbms_output.put_line(‘Kilroy1’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy2’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy3’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy4’); dbms_lock.sleep(1); dbms_output.put_line(‘Kilroy5’); dbms_lock.sleep(1); end; / You wait five seconds, then get the output. This is the way it works with dbms_output. Now I try a new trick The output does not wait the end. There was a trick I wrote in 2007 using utl_file, but it was… Continue reading How to get dbms_output to print line before the end of the procedure?

better than CTAS

SQL> create table t1(x number primary key); Table created. SQL> desc t1 Name Null? Type ———————– ——– —————- X NOT NULL NUMBER SQL> create table t2 as select * from t1; Table created. SQL> desc t2 Name Null? Type ———————– ——– —————- X NUMBER The table T2 has the column X, but not the constraint… Continue reading better than CTAS

Hardcoding SYSDATE

I see TRUNC(SYSDATE) in the code very often. Is this good or bad? Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date. But when does today starts and ends? And at the time of the writing, it is already tomorrow… Continue reading Hardcoding SYSDATE

ora-984 and sqlerrm

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… Continue reading ora-984 and sqlerrm

MIN(DISTINCT X)

What is doing the MIN(DISTINCT X) call? Basically, every distinct value of X is passed to the MIN function. Well, it is probably of very little interest as the MIN function is very fast and processing less rows than MIN(X) should not boost the performance because of the overhead of sorting distinct values. However, if… Continue reading MIN(DISTINCT X)

to divide or to multiply

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… Continue reading to divide or to multiply

clob hello world

write to a clob SQL> var x clob SQL> begin 2 dbms_lob.createtemporary(:x,true); 3 dbms_lob.writeappend(:x,12,’Hello World!’); 4 end; 5 / PL/SQL procedure successfully completed. SQL> print x X ————————————————– Hello World! read from a clob SQL> var c varchar2(10) SQL> var n number SQL> exec :n := 5 /* read 5 characters, if possible */ PL/SQL… Continue reading clob hello world