trigger no longer compiles

[bookouri]

I just finished migrating a database from an old SUN server to a new Windows server. I did a full export and import and got the database back up and running with only ONE problem. A trigger that worked in the old system comes up invalid in the new one and will not compile. Is there anywhere I can look to try to determine what the problem is? All the objects that were in the old database are in the new one. The script is not that complicated its just a logging script that gives me an entry every time a user adds a record to a certain table.

the trigger is really simple


CREATE OR REPLACE TRIGGER "app_OWNER".tr_gc_apptable_jn
before insert on gc_apptable
for each row
begin

insert into gc_apptable_jn
( id_NUMBER,
CODE_SECTION,
operator,
jn_oracle_user,
jn_date_changed,
jn_machine,
jn_osuser)

values (
:new.id_NUMBER,
:new.CODE_SECTION,
:new.operator,
user,
sysdate,
get_session_info (userenv('sessionid'),'MACHINE'),
get_session_info (userenv('sessionid'),'OSUSER'));
end;
/

thanks for any suggestions

 

[lewisp]

The error message you get back is the best place to start looking! If we knew what it was we could probably help you more easily.

Its likely to be a permissions issue - either on the gc_apptable_jn table or on get_session_info procedure.

 

[bookouri]

problem is, when i run the script to create the trigger all i get for an error is that "Warning: Trigger created with compilation errors."... if the actual error is logged anywhere i cant find it...

 

[hoinz]

To find the actual error, type 'show errors' just after compiling.

 

[bookouri]

when i "show errors" it says "no errors"

Warning: Trigger created with compilation errors.

SQL> show errors
No errors.
SQL>

 

[sem]

Try to look for error text in USER_ERRORS. Unfortunately sql*plus (or at least some its versions) ignore errors in triggers.

Regards, Dima

http://seminihin.narod.ru

 

[stefanhei]

Is the get_session_info function valid?
You may check with

SELECT status, owner, object_type 
  FROM dba_objects 
 WHERE object_name = 'GET_SESSION_INFO';

I'd try to compile it anyway:

ALTER FUNCTION get_session_info COMPILE;

Once I've seen a problem like you described after a export/import too - compiling showed errors where the view showed all objects valid - I had to recompile half the application-packages.

Stefan

 

[bookouri]

there is only one object "get_session_info" in dba_objects.

there were lots of other invalid objects but most of them did compile for me.

when i alter function get_session_info compile; i get an error that no such object exists..

 

[bookouri]

alter VIEW SYS._ALL_REPCOLUMN_GROUP compile;
alter VIEW SYS._ALL_REPCONFLICT compile;
alter VIEW SYS._ALL_REPPARAMETER_COLUMN compile;
alter VIEW SYS._ALL_REPRESOLUTION compile;

except for my trigger these are the only INVALID objects left, and these refuse to compile..

if i try to compile these i get an "invalid character" pointing to the _ALL.. if i take off the _ and start with the ALL prefix i get a message that the object compiled successfully.. but the _ALL versions still come up as invalid..

 

[stefanhei]

My query shows you the owner of the get_session_info function, which seems to be antother user than the owner of the trigger. You may need to create a synonym or specify the user in your trigger (like ... fkt_owner.get_session_info... ). Also, as you try to access an object in a foreign schema in a stored object you may need to expicitely grant execute on get_session_info to the trigger-owner.

Stefan

 

[bookouri]

i finally found the get_session_info function in the old database. I dont know why the function was not imported when we did the full db import, I still dont understand that. I added the function to the new db and i think I can get everything back in sync now. I figured a full db import meant a full db import....<G>

thanks for all the replies