Home » SQL & PL/SQL » SQL & PL/SQL » Issues in creating a trigger which fires during user modification
Issues in creating a trigger which fires during user modification [message #390673] Mon, 09 March 2009 04:21 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I am using oracle 10.2.0.3 on Linux

I want to create a trigger which fires when a user gets created, modified or deleted.

This is the code of the trigger
CREATE or REPLACE TRIGGER update_schemas_trig

AFTER CREATE OR ALTER OR DROP ON DATABASE

DECLARE

  l_stmt varchar2(1000);

  l_sql_text ora_name_list_t;

  n number;

  l_action VARCHAR2(4000);

  check_flag NUMBER := 0;

  p_jobno NUMBER;

  inst_name VARCHAR2(30) := '';

  user_name VARCHAR2(30);

BEGIN

  user_name := ora_dict_obj_name;

  SELECT instance_name INTO inst_name FROM v$instance;

  IF (ora_dict_obj_type = 'USER') THEN

    n := ora_sql_txt(l_sql_text);

    FOR i IN 1..n LOOP

      l_stmt := l_stmt || l_sql_text(i);

    END LOOP;

    IF regexp_like(l_stmt, 'create', 'i')  THEN

	  l_action := 'INSERT INTO schema_list@orcl SELECT ''' || inst_name || ''', username, account_status, lock_date, default_tablespace, temporary_tablespace, created FROM dba_users WHERE username= ''' || user_name || ''';';

        dbms_job.submit(

        p_jobno,

        l_action,

        sysdate + interval '10' second

        );

    END IF;

    IF regexp_like(l_stmt, 'drop', 'i')  THEN

	  l_action := 'DELETE FROM schema_list@orcl WHERE instance_name = ''' || inst_name || ''' AND username=regexp_replace(''' ||l_stmt || ''',''[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*'',''\1'',1,1,''i'');';	  

        dbms_output.put_line(l_action);

        dbms_job.submit(

        p_jobno,

        l_action,

        sysdate + interval '10' second

        );

    END IF;

    IF regexp_like(l_stmt, 'alter', 'i')  THEN

	  l_action := 'DELETE FROM schema_list@orcl WHERE instance_name = ''' || inst_name || '''AND username= ''' || user_name || ''';';

	  l_action := l_action || 'INSERT INTO schema_list@orcl SELECT ''' || inst_name || ''', username, account_status, lock_date, default_tablespace, temporary_tablespace, created FROM dba_users WHERE username= ''' || user_name || ''';';

        dbms_job.submit(

        p_jobno,

        l_action,

        sysdate + interval '10' second

        );

    END IF;

  END IF;

END;



I am facing issues when I drop a user.

I get this error
SQL> drop user sample1;

DELETE FROM schema_list@orcl WHERE instance_name = 'EMREP' AND

username=regexp_replace('drop user sample1

','[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*','\1',1,1,'i

');

drop user sample1

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-06550: line 1, column 185:

PL/SQL: ORA-01756: quoted string not properly terminated

ORA-06550: line 1, column 93:

PL/SQL: SQL Statement ignored

ORA-06550: line 1, column 202:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the

following:

;

ORA-06512: at "SYS.DBMS_JOB", line 79

ORA-06512: at "SYS.DBMS_JOB", line 136

ORA-06512: at line 29




If I insert a linel ike this (setting l_stmt to a static value) , it works fine
      IF regexp_like(l_stmt, 'drop', 'i')  THEN

          l_stmt := 'drop user sample1';

	  l_action := 'DELETE FROM schema_list@orcl WHERE instance_name = ''' || inst_name || ''' AND username=regexp_replace(''' ||l_stmt || ''',''[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*'',''\1'',1,1,''i'');';	  

        dbms_output.put_line(l_action);

        dbms_job.submit(

        p_jobno,

        l_action,

        sysdate + interval '10' second

        );

    END IF;

SQL> /
DELETE FROM schema_list@orcl WHERE instance_name = 'EMREP' AND
username=regexp_replace('drop user
sample1','[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*','\1'
,1,1,'i');

User dropped.


Any suggestions would be of great help
Re: Issues in creating a trigger which fires during user modification [message #390684 is a reply to message #390673] Mon, 09 March 2009 04:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you are creating a dynamic query, step one in debugging should always be to print the generated string.
Step 2 is to break it (comment parts) until you have a compiling piece of code.

Hint: To include a quote in a string, you have to add two quotes
Re: Issues in creating a trigger which fires during user modification [message #390709 is a reply to message #390673] Mon, 09 March 2009 06:47 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I have already tried printing the same which I've shown in my earlier post too.

This is what is getting printed

DELETE FROM schema_list@orcl WHERE instance_name = 'EMREP' AND
username=regexp_replace('drop user sample1','[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*','\1',1,1,'i');


I'm actually using 2 quotes to include a quote in a string.
In the earlier post I've even mentioned that both the o/p are the same except that
l_stmt is replaced in the second trigger wth 'drop table sample1'
and that makes it work.

Original code
    IF regexp_like(l_stmt, 'drop', 'i')  THEN
	  l_action := 'DELETE FROM schema_list@orcl WHERE instance_name = ''' || inst_name || ''' AND username=regexp_replace(''' ||l_stmt || ''',''[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*'',''\1'',1,1,''i'');';	  
        dbms_output.put_line(l_action);
        dbms_job.submit(
        p_jobno,
        l_action,
        sysdate + interval '10' second
        );
    END IF;


Static assignment which was included and this works.
IF regexp_like(l_stmt, 'drop', 'i')  THEN
          l_stmt := 'drop user sample1';
	  l_action := 'DELETE FROM schema_list@orcl WHERE instance_name = ''' || inst_name || ''' AND username=regexp_replace(''' ||l_stmt || ''',''[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*'',''\1'',1,1,''i'');';	  
        dbms_output.put_line(l_action);
        dbms_job.submit(
        p_jobno,
        l_action,
        sysdate + interval '10' second
        );
    END IF;


I'm confused why it doesn't work if I directly use l_stmt?


Re: Issues in creating a trigger which fires during user modification [message #390813 is a reply to message #390709] Mon, 09 March 2009 17:45 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
I think you may need to trim any CHR(0) from the end of l_stmt. Also, I think there may be a problem with your regexp_replace statement. If you try just selecting your regexp_replace from dual I don't think it will return anything. I might be inclined to do it without regular expressions as demonstrated below.

SCOTT@orcl_11g> CREATE TABLE schema_list
  2    (instance_name	     VARCHAR2 (30),
  3  	username	     VARCHAR2 (30),
  4  	account_status	     VARCHAR2 (30),
  5  	lock_date	     DATE,
  6  	default_tablespace   VARCHAR2 (30),
  7  	temporary_tablespace VARCHAR2 (30),
  8  	created 	     DATE)
  9  /

Table created.

SCOTT@orcl_11g> CREATE or REPLACE TRIGGER update_schemas_trig
  2  AFTER CREATE OR ALTER OR DROP ON DATABASE
  3  DECLARE
  4    l_stmt	   varchar2(1000);
  5    l_sql_text  ora_name_list_t;
  6    n	   number;
  7    l_action    VARCHAR2(4000);
  8    check_flag  NUMBER := 0;
  9    p_jobno	   NUMBER;
 10    inst_name   VARCHAR2(30) := '';
 11    user_name   VARCHAR2(30);
 12  BEGIN
 13    user_name := ora_dict_obj_name;
 14    SELECT instance_name INTO inst_name FROM v$instance;
 15    IF (ora_dict_obj_type = 'USER') THEN
 16  	 n := ora_sql_txt (l_sql_text);
 17  	 FOR i IN 1..n LOOP
 18  	   l_stmt := l_stmt || l_sql_text(i);
 19  	 END LOOP;
 20  	 IF regexp_like(l_stmt, 'create', 'i')	THEN
 21  	     l_action := 'INSERT INTO schema_list
 22  			  SELECT ''' || inst_name || ''',
 23  				     username, account_status, lock_date,
 24  				     default_tablespace, temporary_tablespace, created
 25  			  FROM	 dba_users
 26  			  WHERE  username= ''' || user_name || ''';';
 27  	     dbms_output.put_line (l_action);
 28  	     dbms_job.submit(
 29  	     p_jobno,
 30  	     l_action,
 31  	     sysdate
 32  	     );
 33  	 END IF;
 34  	 IF regexp_like(l_stmt, 'drop', 'i')  THEN
 35  --        l_action := 'DELETE FROM schema_list@orcl WHERE instance_name = ''' || inst_name || ''' AND username=regexp_replace(''' ||l_stmt || ''',''[:alnum:]*[:space:]*[:alnum:]*[:space:]*([:alnum:]*).*'',''\1'',1,1,''i'');';
 36  	     l_stmt := RTRIM (l_stmt, CHR(0));
 37  	     l_action := 'DELETE FROM schema_list WHERE instance_name = '''
 38  		      || inst_name
 39  		      || ''' AND username= '''
 40  		      || UPPER (SUBSTR (l_stmt, INSTR (l_stmt, ' ', -1) + 1))
 41  		      || ''';';
 42  	     dbms_output.put_line (l_action);
 43  	     dbms_job.submit(
 44  	     p_jobno,
 45  	     l_action,
 46  	     sysdate
 47  	     );
 48  	 END IF;
 49  	 IF regexp_like(l_stmt, 'alter', 'i')  THEN
 50  	       l_action := 'DELETE FROM schema_list WHERE instance_name = ''' || inst_name || '''AND username= ''' || user_name || ''';';
 51  	       l_action := l_action || 'INSERT INTO schema_list SELECT ''' || inst_name || ''', username, account_status, lock_date, default_tablespace, temporary_tablespace, created FROM dba_users WHERE username= ''' || user_name || ''';';
 52  	     dbms_job.submit(
 53  	     p_jobno,
 54  	     l_action,
 55  	     sysdate + interval '10' second
 56  	     );
 57  	 END IF;
 58    END IF;
 59  END update_schemas_trig;
 60  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE USER sample1 IDENTIFIED BY sample1
  2  /
INSERT INTO schema_list
                     SELECT 'orcl',

username, account_status, lock_date,

default_tablespace, temporary_tablespace, created
                     FROM
dba_users
                     WHERE  username= 'SAMPLE1';

User created.

SCOTT@orcl_11g> EXEC DBMS_LOCK.SLEEP (15)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM schema_list
  2  /

INSTANCE_NAME                  USERNAME
------------------------------ ------------------------------
ACCOUNT_STATUS                 LOCK_DATE DEFAULT_TABLESPACE
------------------------------ --------- ------------------------------
TEMPORARY_TABLESPACE           CREATED
------------------------------ ---------
orcl                           SAMPLE1
OPEN                                     USERS
TEMP                           09-MAR-09


SCOTT@orcl_11g> drop user sample1
  2  /
DELETE FROM schema_list WHERE instance_name = 'orcl' AND username= 'SAMPLE1';

User dropped.

SCOTT@orcl_11g> EXEC DBMS_LOCK.SLEEP (20)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM schema_list
  2  /

no rows selected

SCOTT@orcl_11g>


Previous Topic: Execute or Call shell script from oracle PLSQL procedure
Next Topic: UNION ALL with ORDER BY
Goto Forum:
  


Current Time: Mon Dec 05 14:53:16 CST 2016

Total time taken to generate the page: 0.08844 seconds