Category Archives: plsql

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…


SQL> var n number
SQL> exec :n := dbms_datapump.open('IMPORT','TABLE','L')

PL/SQL procedure successfully completed.

SQL> print n
         N
----------
        28

SQL> exec dbms_datapump.metadata_filter(:n,'SCHEMA_LIST','''SCOTT''')

PL/SQL procedure successfully completed.

SQL> exec dbms_datapump.metadata_filter(:n,'NAME_LIST','''T''')

PL/SQL procedure successfully completed.

SQL> exec dbms_datapump.start_job(:n)

PL/SQL procedure successfully completed.
SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                 NOT NULL NUMBER

This is utterly simple. If you mess up with the link name, global name, syntax, and so on, you may end up with orphan jobs in DBA_SCHEDULER_JOBS. There is some metalink note on dropping the underlying tables (Note 336914.1) but first log off, get a coffee, and they may vanish after a few minutes.

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 want to increase all my sequences by 1000


SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> begin
  2    for f in (select sequence_name n from user_sequences)
  3    loop
  4      execute immediate
  5        'select '||f.n||'.nextval from dual connect by level<=1000';
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
         1

Hmm, it does not work. Does SELECT work at all? Yes when it is a SELECT INTO :-)


SQL> drop sequence s;

Sequence dropped.

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> declare
  2    type t is table of number index by pls_integer;
  3    c t;
  4  begin
  5    for f in (select sequence_name n from user_sequences)
  6    loop
  7      execute immediate
  8        'select '||f.n||'.nextval from dual connect by level<=1000'
  9        bulk collect into c;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
      1001

I wonder in which version this optimization/bug was introduced…

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 in Thailand.

We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…

Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.

No, I think that you would better store the date in a table and update it daily.

So you will have SELECT SUM(T.AMT) FROM T, TRADINGDAY WHERE T.EXPIRY_DATE>TRADINGDAY.TRADINGDAY and a daily job running UPDATE TRADEDAY SET TRADINGDAY=TRADINGDAY+1;.

You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…

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, sysdate);
  6  end;
  7  /

PL/SQL procedure successfully completed.

I was in the process of migrating a database with 10g and this piece of code became invalid :(

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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, sysdate);
  6  end;
  7  /
        values (sqlerrm, sysdate);
                *
ERROR at line 5:
ORA-06550: line 5, column 17:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL> 

Ok, the code is wrong. It has been running for half a decade in production but the exception was never reached.

Note it is a bug, and putting sqlerrm after sysdate would have generate the exception even in 9i

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> begin
  2    null;
  3  exception when others then
  4    insert into lsc_t (y,x)
  5          values (sysdate,sqlerrm);
  6  end;
  7  /
        values (sysdate,sqlerrm);
                        *
ERROR at line 5:
ORA-06550: line 5, column 25:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

Just having fun with 10g migration ;)

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 you write your own aggregate, distinct may be interesting!

create type myudag_type as object
(
  myudag INTEGER,
  static function ODCIAggregateInitialize(
    sctx IN OUT myudag_type)
    return number,
  member function ODCIAggregateIterate(
    self IN OUT myudag_type,
    value IN INTEGER)
    return number,
  member function ODCIAggregateTerminate(
    self IN myudag_type,
    returnValue OUT INTEGER,
    flags IN number)
    return number,
  member function ODCIAggregateMerge(
    self IN OUT myudag_type,
    ctx2 IN myudag_type)
    return number
);
/

create or replace type body myudag_type is
static function ODCIAggregateInitialize(
  sctx IN OUT myudag_type)
  return number is
begin
  sctx := myudag_type(0);
  return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
  self IN OUT myudag_type,
  value IN INTEGER)
  return number is
begin
  -- doing nothing will cost you a lot !!!
  for i in 1..1000000 loop null; end loop; 
  return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
  self IN myudag_type,
  returnValue OUT INTEGER,
  flags IN number) return number is
begin
  returnValue := self.myudag;
  return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
  self IN OUT myudag_type,
  ctx2 IN myudag_type)
  return number is
begin
  return ODCIConst.Success;
end;
end;
/

CREATE FUNCTION myudag (
  input INTEGER)
  RETURN INTEGER
  AGGREGATE USING myudag_type;
/

SQL> select myudag(deptno) from emp;

MYUDAG(DEPTNO)
--------------
             0

Elapsed: 00:00:00.57
SQL> select myudag(distinct deptno) from emp;

MYUDAG(DISTINCTDEPTNO)
----------------------
                     0

Elapsed: 00:00:00.13

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 (documented in Using Predefined Inquiry Directives With Conditional Compilation)

Ok, here I go


create or replace procedure p is
   rindex binary_integer;
   slno binary_integer;
   sofar number;
   totalwork number;
   target number;
begin
   totalwork := 5;
   sofar := 0;
   select object_id 
   into target 
   from user_objects 
   where object_name = $$PLSQL_UNIT;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
   -- do something 
   sofar:=sofar+1;
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
     rindex,slno,'Executing...',target,null,
     sofar,totalwork,null,'Statement');
end;
/

Now I execute it


SQL> exec p

PL/SQL procedure successfully completed.

and monitor it


SQL> select sid,opname,target,sofar,totalwork,units
  2   from v$session_longops;
 SID OPNAME       TARGET   SOFAR TOTALWORK UNITS    
---- ------------ -------- ----- --------- ---------
 538 Executing... SCOTT.P      5         5 Statement

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';

declare
inFile utl_file.file_type;
outFile utl_file.file_type;
x varchar2(40);
begin
inFile := utl_file.fopen('TMP','in','R');
outFile := utl_file.fopen('TMP','out','W');
utl_file.put_line(outFile,'Enter a value for x : ');
utl_file.fflush(outFile);
utl_file.get_line(inFile,x);
utl_file.put_line(outFile,'you enterred '||x);
utl_file.fclose(inFile);
utl_file.fclose(outFile);
end;
/

quit

$ mknod /tmp/out p; mknod /tmp/in p
$ (cat /tmp/out &);(sqlplus -s scott/tiger @interactiveplsql &
);cat>/tmp/in
Enter a value for x :
ABC123
you enterred ABC123

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 loop. To avoid incrementation, I divide by 1.014… and multiply by 0.986… in a way that the result keep the same all over the loop.


SQL> 
SQL> var z number
SQL> var y number
SQL> exec :z := power(2,102)*2e-31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop
Elapsed: 00:00:00.42
SQL> set timi off
SQL> print y

                               Y
--------------------------------
9.9879215917842541374103299E-126

SQL> exec :z := power(2,-104)*2e31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y<1e-125) loop :y:=:y*:z; end loop
Elapsed: 00:00:00.28
SQL> set timi off
SQL> print y

                               Y
--------------------------------
9.9879215917842541374103299E-126

It has been difficult to find an example with clear difference and not too weird :twisted:

The operation divide is slower than multiply, probably to catch divide by zero errors...

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> begin
  2    dbms_lob.open(:x,dbms_lob.lob_readonly);
  3    dbms_lob.read(:x,:n,7 /*position*/,:c);
  4    dbms_lob.close(:x);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------
World

dbms_lob.createtemporary(:x,true); caches the lob. If the lob is large (a large object is often large), it may use a lot of memory, use cache=false for slower performance but more economic memory consumption