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

execute sql script from pl/sql

When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of […]

PLS-00201 in stored procedures

When you grant table access thru a role, you cannot use that role in a stored procedure or view. create role r; create user u1 identified by ***; grant create procedure, create session to u1; create user u2 identified by ***; grant create procedure, create session, r to u2; conn u1/*** create procedure u1.p1 is […]

Duplicate table over database link

The old-style approach would be CREATE TABLE T AS SELECT * FROM T@L, where T is the table you want to duplicate and L the database link pointing to the remote database If you want to keep more info about the structure of t, the constraints, the indexes, you may use Datapump. Here it is… […]

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

ora-984 and sqlerrm

What’s wrong with this code ? Connected to: Oracle9i Enterprise Edition Release – 64bit Production With the Partitioning option JServer Release – 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 […]


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

v$session_longops in 10gR2

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

read user-input in plsql

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

to divide or to multiply

warning, this test is cpu intensive, do not try on your productive server One user on the 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 […]

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