Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    10

    Unanswered: Calling DLL from a trigger

    How do I call a dll from a trigger..the trigger will be fired as soon as any data is inserted in the table...

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    Use "external procedure".
    Search docs for "external procedures"

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Here is procedure and trigger which work fine for me to grant automatically select to a role when a table or view is created :

    create or replace trigger tri_grantauto
    after create on test.schema
    declare
    v_job binary_integer;
    v_text varchar2(100);
    begin
    if ora_dict_obj_type in ('TABLE','VIEW') then
    v_text := 'p_grant_select('||chr(39)||ora_dict_obj_name||chr (39)||');';
    dbms_job.submit(v_job, v_text, sysdate);
    end if;
    end;
    /

    create or replace procedure p_grant_select(pi_objname varchar2)
    is
    PRAGMA AUTONOMOUS_TRANSACTION;
    begin
    commit;
    execute immediate 'grant select on '||pi_objname||' to TESTROLE';
    commit;
    exception
    when others then raise;
    end p_grant_select;
    /



    HTH,

    clio_usa - OCP 8/8i/9i DBA

  4. #4
    Join Date
    Jul 2004
    Posts
    10
    How do I use External Procedure???

  5. #5
    Join Date
    Feb 2004
    Posts
    108

  6. #6
    Join Date
    Jul 2004
    Posts
    10
    IS it that only .so file is supported in case of external Procedure??Or even .dll file is supported...
    bcos whereever I read about External Procedure only the example of .so file is shown :

    Firstly, create a makefile with the following contents:

    include $(ORACLE_HOME)/plsql/lib/env_plsql.mk

    .SUFFIXES: .pc .c .o

    EXTP_MKFILE = $(ORACLE_HOME)/rdbms/demo/demo_rdbms.mk

    myshared.so:

    $(MAKE) -f $(EXTP_MKFILE) extproc_callback SHARED_LIBNAME=myshared.so OBJS="test.o"

    WHat all changes do i need to make in it to get .dll file

    ---Arunava

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Create your .dll as normal, ensure you stick to the calling conventions as documented by Oracle.

    Your result may be 'fred.dll'. Place this file in an appropriate location.

    Then create the external library for it....

    Code:
    CREATE OR REPLACE LIBRARY FRED_LIBRARY AS 'C:\ORACLE\ORA81\FRED.DLL';
    Then create the actual wrapper package and function
    Code:
    CREATE OR REPLACE PACKAGE FRED_PACKAGE IS
       FUNCTION  FRED_FUNCTION RETURN VARCHAR2;
    END FRED_PACKAGE;
    /
    
    CREATE OR REPLACE PACKAGE BODY FRED_PACKAGE AS
    
    FUNCTION FRED_FUNCTION RETURN VARCHAR2 IS
       EXTERNAL   LIBRARY FRED_LIBRARY
       NAME       "Freds_Actual_Function_Name_Within_Dll"
       LANGUAGE   C;
    
    END FRED_PACKAGE;
    /
    You can now call FRED_PACKAGE.FRED_FUNCTION which calls the .dll function.

    The fun with these usually starts when trying to configure extproc/tns :-(

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Jul 2004
    Posts
    10
    Hi All,
    Is it necessary to run @extproc.sql for calling the external procedures?Actually I was reading one document in Metalink about External Procedures that states that one needs to run create a dll for extproc.c and then run @extproc.sql with login scott/tiger...I tried doing so but while creating the dll in VC++ was getting errors like OCIGetError and others..Most probably some library is missing..can anyonehelp me out??

    Arunava

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    extproc.c and extproc.sql are Oracle supplied demos for extproc. You don't need them to write your own extproc dll or to call your dll from Oracle.

    If you can get extproc.c to compile correctly to a .dll, then it us a useful example for you.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  10. #10
    Join Date
    Jul 2004
    Posts
    10
    Thank u Billm I was able to solve the problem.Actually problem was with running the extproc.sql...once i created the extproc.dll and followed the instructions provided in extproc.sql file it solved the problem

    Arunava

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •