Author: C. Peter Chen

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.

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;