SQL Developer Update - What?

Today I used the update feature of SQL Developer (version 1.5.3). After stepping on (there were no errors) I saw this summary screen:


Looks like someone forgot to fill their data model properly... :-)

Scoping of Functions in PL/SQL Packages

There was this recent
discussion on comp.databases.oracle.misc about scoping of functions in PL/SQL.
I have set up a test script that you can send through SQL Plus. The script is here:

spool C:\Temp\pkg-test.txt
set echo on pagesize 100 linesize 150 serverout on
create package call_test
as
function f_pub(x in number) return number;
procedure p_pub_direct;
procedure p_pub_direct_qualified;
procedure p_pub_immediate;
procedure p_pub_immediate_qualified;
procedure p_priv_direct;
procedure p_priv_immediate;
procedure p_priv_immediate_qualified;
procedure p_priv_var_direct;
procedure p_priv_var_immediate;
end call_test;
/
show errors package call_test
create package body call_test
as

function f_pub(x in number) return number
as
begin
  return x * 10;
end f_pub;

function f_priv(x in number) return number
as
begin
  return x * 20;
end f_priv;

procedure p_pub_direct
as
begin
  dbms_output.put_line('direct call of public function');
  for c in ( select f_pub(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
end p_pub_direct;

procedure p_pub_direct_qualified
as
begin
  dbms_output.put_line('direct call of public function using qualified name');
  for c in ( select call_test.f_pub(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
end p_pub_direct_qualified;

procedure p_pub_immediate
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with public function');
  execute immediate 'select f_pub(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_pub_immediate;

procedure p_pub_immediate_qualified
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with public function using qualified name');
  execute immediate 'select call_test.f_pub(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_pub_immediate_qualified;

procedure p_priv_direct
as
begin
  dbms_output.put_line('direct call of private function');
  dbms_output.put_line('this will not compile');
  /*
  for c in ( select f_priv(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
  for c in ( select call_test.f_priv(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
  */
end p_priv_direct;

procedure p_priv_immediate
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with private function');
  execute immediate 'select f_priv(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_priv_immediate;

procedure p_priv_immediate_qualified
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with private function using qualified name');
  execute immediate 'select call_test.f_priv(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_priv_immediate_qualified;

procedure p_priv_var_direct
as
  foo number := 123;
begin
  dbms_output.put_line('direct call with private variable');
  for c in ( select foo as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
end p_priv_var_direct;

procedure p_priv_var_immediate
as
  tmp number;
  foo number := 123;
begin
  dbms_output.put_line('execute immediate with private variable');
  execute immediate 'select foo as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_priv_var_immediate;

end call_test;
/
show errors package body call_test
set echo off
show release
exec call_test.p_pub_direct;
exec call_test.p_pub_direct_qualified;
exec call_test.p_pub_immediate;
exec call_test.p_pub_immediate_qualified;
exec call_test.p_priv_direct;
exec call_test.p_priv_immediate;
exec call_test.p_priv_immediate_qualified;
exec call_test.p_priv_var_direct;
exec call_test.p_priv_var_immediate;
drop package call_test
/
spool off

And here's how output looks from a 10.2.0.4 on Linux

release 1002000400
direct call of public function
value 20

PL/SQL procedure successfully completed.

direct call of public function using qualified name
value 20

PL/SQL procedure successfully completed.

execute immediate with public function
BEGIN call_test.p_pub_immediate; END;

*
ERROR at line 1:
ORA-00904: "F_PUB": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 39
ORA-06512: at line 1


execute immediate with public function using qualified name
value 20

PL/SQL procedure successfully completed.

direct call of private function
this will not compile

PL/SQL procedure successfully completed.

execute immediate with private function
BEGIN call_test.p_priv_immediate; END;

*
ERROR at line 1:
ORA-00904: "F_PRIV": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 72
ORA-06512: at line 1


execute immediate with private function using qualified name
BEGIN call_test.p_priv_immediate_qualified; END;

*
ERROR at line 1:
ORA-00904: "CALL_TEST"."F_PRIV": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 81
ORA-06512: at line 1


direct call with private variable
value 123

PL/SQL procedure successfully completed.

execute immediate with private variable
BEGIN call_test.p_priv_var_immediate; END;

*
ERROR at line 1:
ORA-00904: "FOO": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 101
ORA-06512: at line 1

I do not find these results surprising at all:

• You are never allowed to use a package private function in a SELECT because the SQL engine does not see package private items.


• When using EXECUTE IMMEDIATE this is like a call to a global function which cannot know anything about the package context, hence you need to use a qualified name.

Table History - with a Twist

The Task

The other day colleagues wanted to track changes of rows in a table. They wanted to fill a history table which contains all the columns from the original table plus a timestamp. You would immediately think "triggers". Well yes, but with INSERT and UPDATE triggers you would get an entry per change while colleagues wanted an entry per transaction only. This makes matters slightly more complicated.

First Solution?

I thought, this is easy: just record changes in a package variable and then write them to the history table on COMMIT. Of course, you need INSERT and UPDATE triggers - and a COMMIT trigger. It turns out, while you will find plenty on the matter the beast simply does not exist.

Final Solution!

Well the solution is not that far away. Ingredients are

1. a history table sharing the original table's columns plus a TIMESTAMP column but without primary key,
   
2. INSERT and UPDATE triggers,
3. a global temporary table,
4. ROWID.

Now stir thoroughly and everything falls into place. The final solution works like this: when the fist change of a record in the original table appears in a transaction a history record is created in the history table and the ROWID of it is stored in the global temporary table. For all subsequent changes of the same record (identified by its primary key) the history record is looked up via the global temporary table and then updated. That way no more than one history record is created per transaction per original record. When the transaction terminates (either via COMMIT or ROLLBACK) the global temporary table is cleared.

Here is a script that you can execute in SQL Plus and which will demonstrate how this works. You can concatenate all three parts and put them in a single file for execution with SQL Plus.

Setup:

alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI.SS.FF3';
prompt "---------- START ---------- "
column ts format a30
set feedback on
set serveroutput on

create sequence s1
start with 1 increment by 1 cache 20
/
create table t1 (
  id number(10) primary key,
  name1 varchar2(15 char),
  name2 varchar2(15 char)
)
/
create table t1_hist (
  ts timestamp(3) not null,
  id number(10),
  name1 varchar2(15 char),
  name2 varchar2(15 char),
  constraint pk2 primary key ( id, ts ) using index compress 1
)
/
create global temporary table t1_hist_ptr (
  id number(10) primary key,
  rid rowid not null            -- we use rowid for efficient row access
)
on commit delete rows
/
create trigger t1_insert
after insert or update of id, name1, name2
on t1
for each row
declare
  rid rowid;
begin
  if updating and :new.id <> :old.id then
    -- PK has changed!
    update t1_hist_ptr set
      id = :new.id
    where id = :old.id;

    dbms_output.put_line('===> updated id ' || :old.id || ' -> ' || :new.id );
  end if;

  update t1_hist set
    ts    = systimestamp,
    id    = :new.id, -- important!
    name1 = :new.name1,
    name2 = :new.name2
  where t1_hist.rowid = ( select rid from t1_hist_ptr where id = :new.id );

  dbms_output.put_line('===> updated rowcount ' || sql%rowcount);

  if sql%rowcount = 0 then
    -- not there yet, need to insert history record
    insert into t1_hist values ( systimestamp, :new.id, :new.name1, :new.name2 )
      returning rowid into rid;
    insert into t1_hist_ptr values ( :new.id, rid );
  end if;
end t1_insert;
/
show errors trigger t1_insert

Update tests:

/*
 * MAIN START
 */
-- initially empty
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
-- inserts
set echo on
insert into t1 values ( s1.nextval, '1 original', 'bar' )
/
insert into t1 values ( s1.nextval, '2 original', 'buz' )
/
set echo off
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
-- update
set echo on
update t1 set name1 = '1 updated 1' where id = 1
/
set echo on
update t1 set name1 = '1 updated 2' where id = 1
/
set echo off
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
commit
/
-- update with rollback
set echo on
update t1 set name1 = '2 updated 3' where id = 2
/
set echo off
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
rollback
/
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
-- insert with dummy id
insert into t1 values ( -1, '-1 original', 'dummy entry' )
/
update t1 set id = s1.nextval where id = -1
/
commit
/
select t1_hist.*
from t1_hist
order by t1_hist.ts, t1_hist.id
/
/*
 * MAIN END
 */

Cleanup:

drop table t1_hist_ptr
/
drop table t1_hist
/
drop table t1
/
drop sequence s1
/
prompt "---------- END ----------"

Welcome!

I am a senior software engineer doing a lot of things (sometimes I even think too many at a time). Among these are Java application development and improvement, database development and maintenance as well as investigation of performance issues in Oracle databases.

In case you wonder what error ORA-0815 is - it does not exist as far as I know. The phrase "08/15" seems to be mostly used in German speaking countries. It originated from a machine gun model and means something like "ordinary" or "cheap". See the German Wikipedia for details.

In this blog I will try to present Oracle related stuff that I believe is interesting to others. The focus will be on SQL development and improvement and not so much about DBA topics. Please do not expect too much as my writing time resources are limited. If you find anything helpful I would be glad. Enjoy!

robert