How do I call a procedure of another schema from the trigger of a different schema

Question

I am trying to create a trigger that logs all the ddl changes of a particular schema (Schema DS) and updates those changes in another schema (Schema SD).

CREATE OR REPLACE TRIGGER audit_ddl_trg AFTER DDL ON SCHEMA DECLARE
    sql_text   ora_name_list_t;
    v_stmt     VARCHAR2(2000);
    n          PLS_INTEGER;
BEGIN
    n := ora_sql_txt(sql_text);
    FOR i IN 1..n LOOP v_stmt := v_stmt || sql_text(i);
    END LOOP;

    v_stmt := regexp_replace(v_stmt, 'rename[[:space:]]+.*[[:space:]]+to[[:space:]]+([a-z0-9_]+)', '\1', 1, 1,
               'i');
    IF ( ora_sysevent = 'TRUNCATE' ) THEN
        NULL;
    ELSE
        INSERT INTO audit_ddl (
            d,
            osuser,
            current_user,
            host,
            terminal,
            owner,
            type,
            name,
            sysevent,
            statements
        ) VALUES (
            sysdate,
            sys_context('USERENV', 'OS_USER'),
            sys_context('USERENV', 'CURRENT_USER'),
            sys_context('USERENV', 'HOST'),
            sys_context('USERENV', 'TERMINAL'),
            ora_dict_obj_owner,
            ora_dict_obj_type,
            ora_dict_obj_name,
            ora_sysevent,
            v_stmt
        );

        sd.insert_log(sql_text);
    END IF;

END;
/ 

However, it does not seem to recognize the procedure. I tried creating a database link. But that doesn't work too. Is there anyway to get around this?

Answer 1

I suspect that you've got the trigger in one schema and the table in another, but I don't know which is which. Let's assume that AUDIT_DDL_TRIG is in schema DS and table AUDIT_DDL is in schema SD. In that case, you need to qualify the name of the table with the schema that it's in, i.e. INSERT INTO SD.AUDIT_DDL(...whatever...). In addition, schema DS is going to need to have an appropriate GRANT so it can store data into that table, as in GRANT INSERT INTO SD.AUDIT_DLL TO DS.

EDIT

If the problem is the call to SD.INSERT_LOG at the end of the procedure, you may need to grant execute permission to schema DS:

GRANT EXECUTE ON SD.INSERT_LOG TO DS;