Author: C. Peter Chen

I recently encountered a situation where a small number of records in a large Oracle table contain wrong values, and naturally I need to find out exactly which program is causing this problem. I decided to use Oracle triggers to do this job, making use of the built-in DBMS_UTILITY.FORMAT_CALL_STACK function as the main ingredient.

create or replace trigger trg_stack_trace_logger
before insert or update on inventory_table
for each row
begin
	if (:old.expiration_date <> :new.expiration_date) then
		insert into stack_trace_log
		values(
		'User=' || user || '; ' ||
		'Date=' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') || '; ' ||
		'Old Value=' || :old.expiration_date || '; ' ||
		'New Value=' || :new.expiration_date || '; ' ||
		DBMS_UTILITY.FORMAT_CALL_STACK
		);
	end if;
end;

As you can see, the output contains both old/new values of the transaction as well as some metadata (ie. the stack trace) of the transaction itself. The output is inserted into a table called “stack_trace_log”, which, for simplicity sake, is just a table consisted of a single varchar2 field; if you will use this type of tracking over a longer period, it is probably best to track username, date, etc. in their own fields for better reporting capabilities.

select * from stack_trace_log;

LOGGED_INFO
---------------
User=JOE; 
Date=11/24/2015 08:33:39; 
Old Value=2015-11-15-00.00.00; 
New Value=2030-11-15-08.33.39; 
----- PL/SQL Call Stack -----    
object handle line number object name
0x91626018    1           anonymous block
0x8dcb7b30    3           ERP.TRG_STACK_TRACE_LOGGER
0x9657ec50    354         package body ERP.INVENTORY_API
0x9657ec50    1483        package body ERP.INVENTORY_API
0x8c7d2758    4254        package body ERP.INVENTORY_API
0x969315a0    650         package body ERP.RECEIVING_API
0x969315a0    3524        package body ERP.RECEIVING_API
0x969315a0    2861        package body ERP.RECEIVING_API
0x91411208    342         package body ERP.BARCODE_ARRIVAL_API
0x8bd5cca8    1           anonymous block
0x82871f48    1120        package body SYS.DBMS_SYS_SQL
0x82886f48    323         package body SYS.DBMS_SQL
0x99f8e6c0    138         package body ERP.BARCODE_INTERFACE_API
0x93980f88    1           anonymous block

1 rows selected

If business needs requires you to store sensitive data such as social security numbers, bank routing/account numbers, and so on, you should ensure the data is stored in a safe way. Below are a set of two simple functions to encrypt/obfuscate such data to get your started.

To encrypt a varchar2 string with a specific encryption phrase (or “key”):

create or replace function your_schema.encrypt(clear_varchar_ varchar2, key_ varchar2) return varchar2 
is
	v_clear_varchar varchar2(2000);
	v_enc_raw		raw(2000);
	v_enc_varchar	varchar2(2000);
begin
	if (mod(length(clear_varchar_), 8) != 0) then
		v_clear_varchar := rpad(clear_varchar_, length(clear_varchar_) + 8 - mod(length(clear_varchar_), 8), chr(0));
	else
		v_clear_varchar := clear_varchar_;
	end if;
	dbms_obfuscation_toolkit.desencrypt(input => utl_raw.cast_to_raw(rpad(v_clear_varchar, 64, ' ')),
		key => utl_raw.cast_to_raw(key_), 
		encrypted_data => v_enc_raw);
		v_enc_varchar := utl_raw.cast_to_varchar2(v_enc_raw);
	return v_enc_varchar;
end;

The following function decrypts; you must use the same key that was used to encrypt it.

create or replace function your_schema.decrypt(enc_varchar_ varchar2, key_ varchar2) return varchar2 
is
	v_tmp_raw    	 raw(2048);
	v_clear_varchar	varchar2(4000);
begin
	dbms_obfuscation_toolkit.desdecrypt(input => utl_raw.cast_to_raw(enc_varchar_),
		key =>  utl_raw.cast_to_raw(key_), 
		decrypted_data => v_tmp_raw);
	v_clear_varchar := replace(trim(utl_raw.cast_to_varchar2(v_tmp_raw)),chr(0),'');
	return v_clear_varchar;
end;

Here is an example usage: The following SQL statement inserts an obfuscated password into a table that stores user data.

insert into your_schema.user_accounts (username, password)
values(
	'scott', 
	your_schema.encrypt('tiger', '_seCret!keY:3')
);

And below is how you would retrieve and decrypt the password.

select your_schema.decrypt(password, '_seCret!keY:3') from your_schema.user_accounts where username='scott';

Security is a serious matter and it warrants extensive research. This article merely offers the awareness that sensitive data should not be stored in clear text, and hopefully provides a good starting point.

The SQL below can be used to provide a list of roles that inherits the CONNECT role, and with the use of START WITH clause, it will also iterate through all the roles beneath those roles, thus providing a hierarchy report.

select level, drp.granted_role, rpad('-',6*level,'-')||drp.grantee as grantee,
case
	when u.username is not null and account_status='OPEN' then 'Ua'
	when u.username is not null and account_status<>'OPEN' then 'Ux'
	when r.role is not null then 'R'
end as grantee_type
from dba_role_privs drp, dba_users u, dba_roles r
where drp.grantee=u.username(+) and drp.grantee=r.role(+)
start with drp.granted_role='CONNECT'
connect by prior drp.grantee=drp.granted_role;

Below is a sample of what may be returned.

LEVEL	GRANTED_ROLE	GRANTEE	GRANTEE_TYPE
1	CONNECT	——ADMINS	R
2	ADMINS	————ANDER	Ua
2	ADMINS	————MARY	Ux
2	ADMINS	————ZOE	Ua
1	CONNECT	——EMPLOYEES	R
2	EMPLOYEES	————ACCOUNTING	R
3	ACCOUNTING	——————DEBBIE	Ua
2	EMPLOYEES	————OPERATIONS	R
3	OPERATIONS	——————PETER	Ua
3	OPERATIONS	——————WILLEM	Ua

Recently I had the need to find out exactly what existing PL/SQL logic was touching a certain field that seems to update by itself, wiping out important data. Below was the quick query I put together, using the DBA_SOURCE view, to hunt down the culprit.

select name as package_name, line, text
from dba_source
where owner='MY_SCHEMA'
and type='PACKAGE BODY'
and (
  upper(text) like '%MY_TABLE_NAME%FIELD_NAME%'
  or
  upper(text) like '%FIELD_NAME%MY_TABLE_NAME%'
)
order by name, line;

Note that I could have searched for “and upper(text) like ‘%UPDATE%MY_TABLE_NAME%FIELD_NAME%'” rather than having two conditions, but I wanted to err on the side of caution, thus my wish to pull more data out to be safe.

IFS is an ERP system built by the Swedish software firm Industrial and Financial Systems AB. It exposes most of its functionality for automation, customization, etc. Below is an example of how we can use a quick script (to be executed by tools such as SQL*Plus) to automate the batch creation of detail lines in a Customer Order. Many of the other exposed APIs offered by IFS can be called in a similar manner.

declare
	info_	varchar2(4000) := '';
	attr_	varchar2(4000) := '';
	objid_	varchar2(2000) := '';
	objversion_	varchar2(2000) := '';

	cursor recs_ is
	select order_no, cust_id, part_no, qty, unit_cost 
	from my_tmp_worklist_tab;
begin
	for rec_ in recs_ loop
		Client_Sys.Clear_Attr(attr_);
		info_ := '';
		objid_ := '';
		objversion_ := '';

		-- Add attributes to the Customer Order detail line
		Client_Sys.Add_To_Attr('ORDER_NO', rec_.order_no, attr_);
		Client_Sys.Add_To_Attr('DELIVER_TO_CUSTOMER_NO', rec_.cust_id, attr_);
		Client_Sys.Add_To_Attr('CATALOG_NO', rec_.part_no, attr_);
		Client_Sys.Add_To_Attr('PART_NO', rec_.part_no, attr_);
		Client_Sys.Add_To_Attr('BUY_QTY_DUE', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('DESIRED_QTY', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('REVISED_QTY_DUE', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('COST', rec_.unit_cost, attr_);
		Client_Sys.Add_To_Attr('PLANNED_DELIVERY_DATE', sysdate+7, attr_);
		-- ... There may be other fields you may wish to populate...

		-- Example of how to update an already-established attribute
		if (rec_.unit_cost = 0) then
			Client_Sys.Set_Item_Value('COST', '0.01', attr_);
		end if;
		
		begin
			Customer_Order_line_API.New__(info_, objid_, objversion_, attr_, 'DO');
		exception
			when other then
			info_ := sqlerrm;
			dbms_output.put_line('Cust Order ' || rec_.order_no || ' error: ' || info_);
		end;

		commit;    
   end loop;   
end;

We can use an Oracle PL/SQL function to retrieve a list of values from the database. In this article, we will look at two sample methods, one of which returns a character string (ie. varchar2) while the other returns a record set (ie. sys_refcursor). Our application will determine which method is best in our implementation.

In the first example below, we use a for-loop to iterate through a select statement. In each iteration, we concatenate the new data into a varchar2 variable. Finally, at the end of the function, we return the said variable.

create or replace function get_city_list(in_country_ in varchar2)
return varchar2
as
ret_ varchar2(1000);
begin
	for rec in(select city from cities where country=in_country_) loop
		ret_ := ret_ || rec.city || ',';
	end loop;
	return ret_;
end get_city_list;

Alternatively, we can have the function return us a record set. To do so, we declare a sys_refcursor variable, and then in the body of the function we assign a SQL statement for it.

create or replace function get_city_list(in_country_ in varchar2)
return sys_refcursor
as
	ret_ sys_refcursor
begin
	open ret_ for select city from cities where country=in_country_;
	return ret_;
end get_city_list;