Home » SQL & PL/SQL » SQL & PL/SQL » Select in trigger - strange NO_DATA_FOUND (Select in trigger)
Select in trigger - strange NO_DATA_FOUND [message #303255] Thu, 28 February 2008 09:30 Go to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Hello,
I have strange behavior of select within trigger.
I have 2 schemas (1. ROY, 2.SAM)

I Grant ROY schema for full administer trigger:
GRANT Administer Database TRIGGER TO roy;
GRANT alter any TRIGGER TO roy;


I created a trigger (simple trigger) and run it on ROY schema:
CREATE OR REPLACE TRIGGER trig1
	BEFORE ALTER OR DROP OR CREATE ON database
DECLARE
	v_object_name VARCHAR2(120);
BEGIN
	--[1# select]
	--SELECT object_name INTO v_object_name FROM all_objects 
        --WHERE owner LIKE 'SAM' 
        --AND rownum <= 1;
	
	--[2# select]
	SELECT object_name INTO v_object_name FROM all_objects 
        WHERE rownum <= 1;
	dbms_output.put_line('The object_name IS : '|| v_object_name);
	
	EXCEPTION WHEN NO_DATA_FOUND THEN 
	dbms_output.put_line('NO_DATA_FOUND');
END;
/
sho err

Trigger created.

ROY> sho err
No errors.
ROY> 


I created some test_1 from SAM schema, the trugger must print "v_object_name"
SAM> create table test_1(id int);
The object_name IS : DUAL

Table created.


But when i mark [1# select] and release [2# select]
SELECT object_name INTO v_object_name FROM all_objects 
WHERE rownum <= 1;
dbms_output.put_line('The object_name IS : '|| v_object_name);


I compiled the trigger again from ROY schema.
The SAM schema have full grant on all_objects.

I create another test_2 table from SAM schema, i got NO_DATA_FOUND
SAM> create table test_12(id int);
NO_DATA_FOUND  

Table created.


Did i forgot something ! it's so strange !!

------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production








Re: Select in trigger - strange NO_DATA_FOUND [message #303279 is a reply to message #303255] Thu, 28 February 2008 11:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Since creation of the object causes the trigger to fire, all_objects is mutating while the select from it is occurring, so rownum 1 just might happen to be the row affected. Also, the trigger is running under roy's privileges, not sam's, so it is roy that needs the privileges. However, if you are trying to output the name of the object created, then you should just use dictionary_obj_name, as demonstrated below. I am guessing that this is just an initial test and you will probably change the trigger to insert the object name and other data into an audit_table instead of outputting it.

SCOTT@orcl_11g> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE	11.1.0.6.0	Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SCOTT@orcl_11g> CREATE USER roy IDENTIFIED BY roy
  2  /

User created.

SCOTT@orcl_11g> GRANT CONNECT TO roy
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE TRIGGER TO roy
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT ADMINISTER DATABASE TRIGGER TO roy
  2  /

Grant succeeded.

SCOTT@orcl_11g> CREATE USER sam IDENTIFIED BY sam
  2  DEFAULT TABLESPACE USERS
  3  QUOTA 10M ON USERS
  4  /

User created.

SCOTT@orcl_11g> GRANT CONNECT TO sam
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE TABLE TO sam
  2  /

Grant succeeded.

SCOTT@orcl_11g> CONNECT roy/roy
Connected.
ROY@orcl_11g> 
ROY@orcl_11g> CREATE OR REPLACE TRIGGER trig1
  2  	     BEFORE ALTER OR DROP OR CREATE ON database
  3  BEGIN
  4  	     dbms_output.put_line('The object_name IS : '|| dictionary_obj_name);
  5  END;
  6  /

Trigger created.

ROY@orcl_11g> sho err
No errors.
ROY@orcl_11g> CONNECT sam/sam
Connected.
SAM@orcl_11g> 
SAM@orcl_11g> create table test_1(id int)
  2  /
The object_name IS : TEST_1

Table created.

SAM@orcl_11g> create table test_2(id int)
  2  /
The object_name IS : TEST_2

Table created.

SAM@orcl_11g> 


Re: Select in trigger - strange NO_DATA_FOUND [message #303286 is a reply to message #303255] Thu, 28 February 2008 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara gives you the correct way to get the object name.

About the result you get:
ALL_OBJECTS lists all objects you have access.
ALL_OBJECTS inside a trigger gives all objects trigger owner has access directly (that is not via a role).
Roy can't have direct access to Sam's table as this table does not yet exist.

Barbara,
Quote:
all_objects is mutating while the select from it is occurring,

Actually all_objects can't mutate but it is a good question to know when the trigger fires. Is it during obj$ insertion (which mean a trigger on obj$) or is it completly unrelated to any dictionary modification?
I have not a database by hand but will make some tests tomorrow and come back.

Regards
Michel
Re: Select in trigger - strange NO_DATA_FOUND [message #303294 is a reply to message #303286] Thu, 28 February 2008 13:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Thu, 28 February 2008 10:27
Actually all_objects can't mutate



To be technically correct, I suppose that I should say that one or more of the underlying base tables that the all_objects data dictionary view is selecting from is mutating. Since it is a before trigger, the insertion of a row into one or more of those base tables that results in a new row in the all_objects view has not yet been committed.
Re: Select in trigger - strange NO_DATA_FOUND [message #303397 is a reply to message #303294] Fri, 29 February 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just for information.
Trigger is not associated to any dictionary object, it seems to be executed by SQL run-time after syntax analysis, it executes a query on sys.trigger$ to get database triggers.

One weird thing is that before insert the new object in obj$, Oracle check with a select if it is already there (at least in 10.2.0.3 code) when there is a unique key on obj#!

Regards
Michel
Re: Select in trigger - strange NO_DATA_FOUND [message #303714 is a reply to message #303397] Sun, 02 March 2008 04:49 Go to previous message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Thanks guys for your replaies Smile
Previous Topic: Need help understanding
Next Topic: Code1
Goto Forum:
  


Current Time: Wed Dec 07 18:56:47 CST 2016

Total time taken to generate the page: 0.22328 seconds