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 […]

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 (primary key / not null). […]

EXECUTE IMMEDIATE ‘SELECT’ does not execute anything

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing. exec execute immediate 'select 1/0 from dual connect by level<9999999999999' PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 But it is really annoying… and not documented as far as I know. Imagine I […]

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 (x,y)   5          values (sqlerrm, […]

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 procedure successfully completed. SQL> […]