Laurent Schneider

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

dba, plsql sqlerrm

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

Blogroll, plsql, sql

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

Blogroll, dba, plsql, sql

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

Blogroll, linux, plsql, sqlplus

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  
The operation divide is slower than multiply, probably to catch divide by zero errors...
				

					Blogroll,  plsql,  sql
					
				
				

			

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

Blogroll, plsql