Re: Create function withou using utl_file

From: Bernard Polarski <bpolarsk_at_yahoo.com>
Date: 2000/06/27
Message-ID: <8ja4gv$442$1@nnrp1.deja.com>#1/1

> Couldn't you just use
> :new.<column_name> := upper(:new.<column_name>)
>
> Why so difficult?
> Am I missing something?
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
> "Bernard Polarski" <bpolarsk_at_yahoo.com> wrote in message
> news:8j798a$154$1_at_nnrp1.deja.com...
> > Hello,
> >
> > Is it a way to create a function or a procedure from within a
package,
> > compile it, without using utl_file_dir ? I would like to have a
trigger
> > modify a case in a user defined function whenever rows mutates.
> >
> > --
> > B.Polarski
> >
> > The 'world of Smenu' is at http://www.geocities.com/bpolarsk
> > Email : bpolarsk_at_yahoo.com
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

No, it was no the question. The following partially solves the problems :

The 'st' variable holds the code of the newly created User Defined Function, and the 'dbms_sql.parse' compile it on the fly. Of course my function is limitated to 2000 char on v7 and 4000 in v8. But I think that this limitation may be lifted.

<cut>

CREATE OR REPLACE TRIGGER "BPA"."IT001_CPL" AFTER INSERT OR UPDATE OR DELETE ON bpa."IT001" declare

   cursor c1 is select usrid, vdsk1 from IT001 order by usrid ;    st varchar2(2000) ;
   st1 varchar2(255) ;
   old_id varchar2(35) ;
   flag int ;
   c integer default dbms_sql.open_cursor; begin
debug := 0 ;
old_id := 'init_old' ;
flag := 0 ;

st := 'create or replace function vdsk(st_vdsk varchar2) return int is ' || chr(10) ;

 st := st || ' -- Return 0 if access is denied' || chr(10) ;
 st := st || ' -- Return 1 if access to row is granted' ||chr(10) ;
 st := st || 'begin ' ||chr(10) ;
 for r in c1 loop
      if ( old_id != r.usrid ) then
	  flag := 0 ;
          if ( old_id != 'init_old' ) then
    	       st := st || '    else ' ||chr(10 ) ;
       	       st := st || '        return(0) ; ' ||chr(10 ) ;
	       st := st || '    end if ; ' ||chr(10 ) ;
               st := st || 'elsif ( USER = '''|| r.usrid || '''' ||' )
then' || chr(10) ;
          else
               st := st || 'if ( USER = '''|| r.usrid || '''' ||' )
then' || chr(10) ;
          end if ;
      end if ;
      if ( flag = 0 ) then
          st := st || '  if (st_vdsk = ''' || r.vdsk1 || ''''||' ) then
' || chr(10) ;
	  flag := 1 ;
      else
   	  st := st || '  elsif (st_vdsk = ''' || r.vdsk1 || ''''||')
then ' || chr(10) ;
      end if ;
      st := st || '         return(1) ; ' || chr(10)  ;
      old_id := r.usrid ;

end loop ;

 st := st || '    else ' ||chr(10 ) ;
 st := st || '        return(0) ; ' ||chr(10 ) ;
 st := st || '    end if ; ' ||chr(10 ) ;
 st := st || ' end if ; ' ||chr(10 ) ;
 st := st || ' end ; ' || chr(10 ) ;

dbms_sql.parse(c,st,dbms_sql.native) ;

END ; </cut>

Now here is the function generated by this trigger : (Thids obtained it I transform the code of the trigger as a procedure or pkg:

<cut>

function vdsk ( st_vdsk varchar2 ) return int is

Return 0 if access is denied
Return 1 if access to row is granted begin if ( USER = 'BPA' ) then if (st_vdsk = 'CA123' ) then return(1) ; elsif (st_vdsk = 'CA400' ) then return(1) ; else return(0) ; end if ; elsif ( USER = 'SYS' ) then if (st_vdsk = 'CA300' ) then return(1) ; else return(0) ; end if ; end if ; end ;

</cut>

But I can't put it as a trigger, otherwise I get an ORA-4092 or ORA- 4091 (It becomes an ORA-4092 if I set the trigger as 'after each rows'):

<cut>
The following error has occurred:

ORA-04091: table BPA.IT001 is mutating, trigger/function may not see it
ORA-06512: at "BPA.IT001_CPL", line 3
ORA-06512: at "BPA.IT001_CPL", line 27

</cut>

Table IT001 :
CREATE TABLE IT001 (
USRID VARCHAR2 (8) NOT NULL,
VDSK1 VARCHAR2 (8) NOT NULL
)

Content of table :

USRID       VDSK
-----       -----
BPA         CA123
BPA         CA300
SYS         CA400

I am trying to find a solution to this ora-4091, but it seems out of the scope of my knowlege. Such a trigger enable to genrate a function that test is a User as the right to see records, based on the value of security code (st_vdsk param). This is the kind of security you find a in SAP, But I think it is much more efficient if the work is done in memory, rather than througt a select.

--
B.Polarski

For good scripts, Visit The 'world of Smenu':
http://www.geocities.com/bpolarsk
Email : bpolarsk_at_yahoo.com

--
B.Polarski

http://www.geocities.com/bpolarsk
Email : bpolarsk_at_yahoo.com


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Jun 27 2000 - 00:00:00 CDT

Re: Create function withou using utl_file_dir ?