Replace an User function with Sql Statement

From: Sam Palanivel <sam_at_yorcomp.com>
Date: Tue, 30 Nov 2004 16:28:55 -0500
Message-ID: <coiomo$g1r$1@zcars0v6.ca.nortel.com>

Hi,

I have the following Sql which uses a function (check_user_priv) . How do I replace the function with ordinary Sql Statement? Thanks Sam

SELECT DISTINCT a.owner||'.'||a.table_name FROM dcw_column a WHERE a.update_field_ind = 'Y' AND

        (
        exists
             (SELECT null FROM all_tab_privs tp
                WHERE table_schema = a.owner AND table_name = a.table_name
                AND privilege = 'UPDATE'
                        AND (grantee = 'ASKHELP' or grantee in
                        (SELECT role FROM session_roles))         )
         OR exists         (SELECT null FROM all_col_privs
                   WHERE table_schema = a.owner AND table_name =
a.table_name
                     AND column_name = a.column_name
                      AND privilege = 'UPDATE'
                AND (grantee = 'ASKHELP' or grantee in
                      (SELECT role FROM session_roles))     )
        OR exists         (SELECT null FROM user_tables WHERE table_name =
a.table_name)
        OR exists         (SELECT null FROM user_views WHERE view_name =
a.table_name)
      )

AND 'Y' in ( select
check_user_priv('ASKHELP',a.owner,a.table_name,a.column_name,'UPDATE') from dual)

ORDER BY a.owner||'.'||a.table_name

 Create or replace function check_user_priv (user_name_p varchar2  ,owner_name_p varchar2, table_name_p varchar2  ,column_name_p varchar2 ,privilegeType_p varchar2) return varchar2 is

tmp_priv varchar2 (1);
tmp_user_id varchar2(40);

begin
-- Check whether a record exists for an current user

   select

         case when privilegeType_p ='SELECT' then NVL
(SELECT_FIELD_IND,'Y')

              when privilegeType_p ='INSERT' THEN   INSERT_FIELD_IND
              when privilegeType_p ='UPDATE' THEN   UPDATE_FIELD_IND
              when privilegeType_p ='DELETE' THEN   UPDATE_FIELD_IND
         end,
         user_id
    into tmp_priv,tmp_user_id
     from  dcw_column_user_priv  up
    where  up.owner        =  owner_name_p
      and  up.table_name   = table_name_p
      and  up.column_name = column_name_p
      and  user_id           = user_name_p;

        return tmp_priv;
   exception
       when no_data_found then
          -- Check whether a record exists for any user.
          -- If a record exists do not allow the current user to view this
column.
         begin
            select
                  case
                  when privilegeType_p ='SELECT'  then  NVL


(SELECT_FIELD_IND,'Y')

                  when privilegeType_p ='INSERT' THEN   INSERT_FIELD_IND
                  when privilegeType_p ='UPDATE' THEN   UPDATE_FIELD_IND
                  when privilegeType_p ='DELETE' THEN   UPDATE_FIELD_IND
                 end,
                user_id
            into tmp_priv,tmp_user_id
            from  dcw_column_user_priv  up
           where  up.owner        =  owner_name_p
             and  up.table_name   = table_name_p
             and   up.column_name = column_name_p;
           return ('N');
         exception
            -- If there is no record, allow the current user to access based
on table privilege
             when others then  return ('Y');
         end;

end; Received on Tue Nov 30 2004 - 15:28:55 CST