Cool PL/SQL code

Thought I’d share some code I put together here to check groups in BO.

Client needed a secure object so that a value would be displayed based upon a level of users.

What I did was build an Oracle function that would call the BO repository and return an indicator whether the user belong to that group.

Here’s the code:

CREATE OR REPLACE FUNCTION FIND_CNO
 (in_bouser varchar2    <----------------------- @Variable('BOUSER') goes here
 )
 RETURN number
 IS
-- PL/SQL Specification

	l_return_value number := 0;

-- PL/SQL Block
begin
	select count(*)
	  into l_return_value
	from borepdw.obj_m_actor a1,
	     borepdw.obj_m_actor a2,
		 borepdw.obj_m_actorlink l1,
		 borepdw.obj_m_actorlink l2
	where a1.m_actor_n_id=l2.m_actl_n_actorid
	  and a1.m_actor_n_type = '16'
	  and a2.m_actor_n_type='1'
	  and l1.m_actl_n_id = l2.m_actl_n_fatlinkid
	  and a2.m_actor_n_id = l1.m_actl_n_actorid
	  and a2.m_actor_c_name = 'CNO'    <--- BO Group
	  and a1.m_actor_c_name = in_bouser; <--- BO User
	  
dbms_output.put_line('Total count = ' || to_char(l_return_value) );	  
	  
RETURN l_return_value;	   	
	
EXCEPTION
	WHEN OTHERS THEN
		RETURN 0;
end find_cno;
/

The object built was this:

DECODE(find_cno(@Variable('BOUSER')), 0,@Select(EVENT FACTS\SSN - Last 6 digits ONLY),@Select(EVENT FACTS\SSN))

This has been built in PL/SQL, but could be translated into other SQL code.

This trick eliminates the problem where query don’t work with object level security.

:lol:


dmorgan28202 :us: (BOB member since 2003-04-01)