Calling procedure within a system trigger


CREATE OR REPLACE PROCEDURE prc_dblink
AS
vdb_name VARCHAR2 (100);
vserver_name VARCHAR2 (100);
verror VARCHAR2 (1000);
v_count NUMBER;
BEGIN
SELECT instance_name, host_name
INTO vdb_name, vserver_name
FROM v$instance;
DBMS_OUTPUT.PUT_LINE('Column 2 = ');
EXECUTE IMMEDIATE 'Create database link "DBLink_apex3dev"'
|| ' connect to q758388'
|| ' identified by q758388'
|| ' using ''(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = you-abc.net)'
|| ' (Port = 1521)) (CONNECT_DATA = (SID = abcd)))''';

EXECUTE IMMEDIATE 'SELECT COUNT (1)
FROM db_status@dblink_apex3dev
WHERE db_name = '''
|| vdb_name
|| ''' AND server_name ='''
|| vserver_name
|| ''''
INTO v_count;
IF v_count >= 1
THEN
EXECUTE IMMEDIATE 'UPDATE db_status@dblink_apex3dev SET
db_status = ''0'', MECHANISM=''T'', status_ts = SYSDATE WHERE db_name = '''
|| vdb_name
|| ''' AND server_name ='''
|| vserver_name
|| '''';
ELSE
EXECUTE IMMEDIATE 'INSERT INTO db_status@dblink_apex3dev
(server_name, db_name, db_status, mechanism, status_ts) '
|| 'VALUES ('''
|| vserver_name
|| ''', '''
|| vdb_name
|| ''', ''0'', ''T'', SYSDATE)';
END IF;
COMMIT;
EXECUTE IMMEDIATE 'alter session Close Database Link "DBLINK_APEX3DEV"';
EXECUTE IMMEDIATE 'Drop database link "DBLink_apex3dev"';

END;
/

The above code execute fine but wont work with system trigger

eg.


SQL> CREATE OR REPLACE TRIGGER trg_startup
after startup ON DATABASE
DECLARE
verror VARCHAR2 (255);
BEGIN
prc_dblink;
EXCEPTION
WHEN OTHERS
THEN
verror :=sqlerrm;
insert into test_p values(verror);
COMMIT;
END; 2 3 4 5 6 7 8 9 10 11 12 13
14 /

Trigger created.

SQL> shu imemdiate;
SP2-0717: illegal SHUTDOWN option
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 2140152 bytes
Variable Size 148723720 bytes
Database Buffers 369098752 bytes
Redo Buffers 4325376 bytes
Database mounted.
Database opened.
SQL> select * from test_p;

DUMMY
--------------------------------------------------------------------------------
ORA-30511: invalid DDL operation in system triggers



ORA-30511: invalid DDL operation in system triggers

Cause:
An attempt was made to perform an invalid DDL operation in a system trigger. Most DDL operations currently are not supported in system triggers. The only currently supported DDL operations are table operations and ALTER?COMPILE operations.

Action: Remove invalid DDL operations in system triggers.

A very few DDL operations are supported in system triggers.





you need to create job.