Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    14

    Question Unanswered: Calling Functions from a different schema?

    I loaded a java class into an oracle schema called A and published one of the functions called INSERT_AGE(). Can i call the function from another schema called B? Can it be done? Have tried writting a trigger dat will execute the function in a schema A, but the trigger will not compile?

    here is wat i wrote:

    CREATE OR REPLACE TRIGGER "B"."TR_FILE" BEFORE
    INSERT ON "B"."FILEDOC" FOR EACH ROW begin
    :new.age:=A.UPDATE_AGE('WILL');
    end;

    the error is
    PLS-00201: identifier 'A.UPDATE_AGE' must be declared

    Just curios if the functions can be called between schemas..

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Calling Functions from a different schema?

    Yes you can, but user A must "GRANT EXECUTE ON UPDATE_AGE TO B".

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: Calling Functions from a different schema?

    Even after granting execute privilege, i think the trigger will not compile
    as You can not assign value to :new.age.


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Jan 2004
    Posts
    14
    I have tried granting execute on user A and user B, but the trigger still will not compile with the same error.

    Do you guys/gals have any ideas on how to do this?

  5. #5
    Join Date
    Mar 2004
    Posts
    14

    Unhappy May be ...

    I not sure about the java class refference - i mean may be you need something between scheme name and function name - you'll sure if you create public synonyms or just try to use this function in select b.func from dual. If you can do this in select but not in trigger - oops, I don't know than
    and in any way you can't use :new not for each row statement.

  6. #6
    Join Date
    Mar 2004
    Posts
    14

    Re: May be ...

    Originally posted by andrev
    and in any way you can't use :new not for each row statement.
    oh , sorry - now I see that you for each row

Posting Permissions

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