Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2007
    Posts
    29

    Arrow Unanswered: Calling SP inside a Trigger

    Hi im using oracle 9 i. Currently im trying to calling the SP, DEV_SP_AUTO_FETCH_PRG_DET inside my trigger, TRIG_I_U_D_OUA_YEAR !

    But i got error like , Line # = 12 Column # = 9 Error Text = PLS-00103: Encountered the symbol "DEV_SP_AUTO_FETCH_PRG_DET" when expecting one of the following: := . ( @ % ; immediate The symbol ":=" was substituted for "DEV_SP_AUTO_FETCH_PRG_DET" to continue.


    my trigger is like this ....
    ==========================
    CREATE OR REPLACE TRIGGER "MCIIU_INTERFACE"."TRIG_I_U_D_OUA_YEAR"
    BEFORE
    INSERT
    ON "MCIIU_INTERFACE"."OINT_UNIM_ACADEMIC_YEAR" FOR EACH ROW
    BEGIN

    if INSERTING then
    INSERT INTO SASMCIIULIVE.OINT_UNIM_ACADEMIC_YEAR VALUES( :new.ACADEMIC_YEAR_ID,:new.UNI_ID, :NEW.ACADEMIC_SNO, :NEW.ACADEMIC_YEAR, :NEW.ACADEMIC_FROM_DATE, :NEW.ACADEMIC_TO_DATE, :NEW.ACADEMIC_DESC, :NEW.CURRENT_ACADEMIC, :NEW.SEASON);

    EXECUTE DEV_SP_AUTO_FETCH_PRG_DET (:new.ACADEMIC_YEAR_ID);
    end if;

    END;
    /

    COMMIT;


    =========================================

    i even tried remove the EXECUTE but still got error. Can sumone tll me wat is the syntax to call a SP that accepts a parameter inside a trigger. Thanx, realy appreciated..

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    EXECUTE is a SQL*Plus command, you cannot use it in PL/SQL.
    i even tried remove the EXECUTE but still got error.
    You acted right. However without knowing the error I cannot tell about its cause. So I just deduce, as you INSERT into table in other schema, the procedure is located in that schema too, so you should include the schema name too:
    Code:
    SASMCIIULIVE.DEV_SP_AUTO_FETCH_PRG_DET (:new.ACADEMIC_YEAR_ID);
    If you GRANT EXECUTE on DEV_SP_AUTO_FETCH_PRG_DET and INSERT on OINT_UNIM_ACADEMIC_YEAR to MCIIU_INTERFACE schema, it should work.

  3. #3
    Join Date
    Apr 2007
    Posts
    29
    Hi Actualy i hv created two schema under the same database instance,namely
    schema 1: SASMCIIULIVE
    &
    schema 2: MCIIU_INTERFACE
    I wanted to execute a sp,MCIIU_INTERFACE.DEV_SP_AUTO_FETCH_PRG_DET
    from the trigger,SASMCIIULIVE.TRIG_I_U_D_OUA_YEAR.

    I have re-write the trigger as below....
    --================================================== ==
    CREATE OR REPLACE TRIGGER "SASMCIIULIVE"."TRIG_I_U_D_OUA_YEAR"
    BEFORE
    INSERT
    ON "MCIIU_INTERFACE"."OINT_UNIM_ACADEMIC_YEAR" FOR EACH ROW
    BEGIN

    if INSERTING then
    INSERT INTO SASMCIIULIVE.OINT_UNIM_ACADEMIC_YEAR VALUES( :new.ACADEMIC_YEAR_ID,:new.UNI_ID, :NEW.ACADEMIC_SNO, :NEW.ACADEMIC_YEAR, :NEW.ACADEMIC_FROM_DATE, :NEW.ACADEMIC_TO_DATE, :NEW.ACADEMIC_DESC, :NEW.CURRENT_ACADEMIC, :NEW.SEASON);

    MCIIU_INTERFACE.DEV_SP_AUTO_FETCH_PRG_DET (:new.ACADEMIC_YEAR_ID);
    end if;

    END;
    /

    COMMIT;
    --=============================================

    after compile this trigger then i got the following error :
    Line # = 12 Column # = 1 Error Text = PLS-00201: identifier 'MCIIU_INTERFACE.DEV_SP_AUTO_FETCH_PRG_DET' must be declared
    Line # = 12 Column # = 1 Error Text = PL/SQL: Statement ignored
    __________________________________________________ ______

    I tried use the following method inside the inserting if else block (if INSERTING then)

    GRANT EXECUTE ON MCIIU_INTERFACE.DEV_SP_AUTO_FETCH_PRG_DET TO SASMCIIULIVE;
    BEGIN
    MCIIU_INTERFACE.DEV_SP_AUTO_FETCH_PRG_DET (:new.ACADEMIC_YEAR_ID);
    END;


    I am not sure if the syntax is correct , can u guide me on this.
    thanx

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't like this:

    CREATE OR REPLACE TRIGGER "SASMCIIULIVE"."TRIG_I_U_D_OUA_YEAR"
    BEFORE INSERT ON "MCIIU_INTERFACE"."OINT_UNIM_ACADEMIC_YEAR"

    Creating a trigger in "this" schema but which catches inserts into a table in "another" schema. Why? Why wouldn't you create a trigger in a schema that owns this table?

    I'd rather put it like this:
    Code:
    CONNECT sasmciiulive;
    
    GRANT INSERT INTO oint_unim_academic_year to MCIIU_INTERFACE;
    
    CONNECT MCIIU_INTERFACE;
    
    CREATE OR REPLACE TRIGGER MCIIU_INTERFACE.TRIG_I_U_D_OUA_YEAR
       BEFORE INSERT
       ON MCIIU_INTERFACE.OINT_UNIM_ACADEMIC_YEAR
       FOR EACH ROW
    BEGIN
       IF INSERTING
       THEN
          INSERT INTO sasmciiulive.oint_unim_academic_year
               VALUES (:NEW.academic_year_id, :NEW.uni_id, :NEW.academic_sno,
                       :NEW.academic_year, :NEW.academic_from_date,
                       :NEW.academic_to_date, :NEW.academic_desc,
                       :NEW.current_academic, :NEW.season);
    
          mciiu_interface.dev_sp_auto_fetch_prg_det (:NEW.academic_year_id);
       END IF;
       
       COMMIT ;
    END;
    /
    Here's a Coding Triggers chapter of the Application Developer's Guide; read it, perhaps you'll find it interesting.

  5. #5
    Join Date
    Apr 2007
    Posts
    29
    Hi Litlefoot,
    My main intension was to catch whatever inputs from user site into the mciiu_interface (a interface schema) and the trigger from sasmciiulive (the live schema) will then pick up the entries from there.

    Actualy mciiu_interface schema have all the tables which are identitical to the one from sasmciiulive schema.

    Well, if i follow ur method then , shall i change the grant statement like this? ==>
    connect mciiu_interface;
    GRANT INSERT INTO sasmciiulive.oint_unim_academic_year to MCIIU_INTERFACE;
    commit;

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I think I put it all into my sample script.

    Why would you connect as MCIIU_INTERFACE and grant insert on SASMCIIULIVE's table?!? Why wouldn't you connect as SASMCIIULIVE and grant insert to MCIIU_INTERFACE? Unless I'm wrong, you're doing it using inverted logic. Which isn't bad by itself, but - unnecessary, if you ask me.

  7. #7
    Join Date
    Apr 2007
    Posts
    29

    Red face

    Hi Littlefoot, thanks for your idea.I have tried your method and grant privilages (GRANT ALL on sasmciiulive.[tablenames] to mciiu_interface).
    Everything work fine but everytime i taken the back up of both schemas and restore it in to my own pc then those triggers from mciiu_interface are unable to compile.That requires me to grant the privilages again for all the tables involve from mciiu_interface to sasmciiulive. Issit one of the disadvantage from this method. Because last time i use to create the triggers in sasmciiulive and checked for any insert/update/delete on mciiu_interface and they didn't gv me problem when i restore the back up into my pc. any suggestion? Once again i realy appreciated your help, thnax
    Last edited by tommyboy1910; 05-25-07 at 00:37.
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How did you transfer data? Using export and import? What commands did you use?

    "Unable to compile" is less than descriptive. What error did you get?

  9. #9
    Join Date
    Apr 2007
    Posts
    29
    Hi,

    I usually backup those schemas through cmd. i use the following command :

    EXP MCIIU_INTERFACE/MCIIU@TOM FILE=MIIU_INTERFACE[250507].DMP

    and use the following command to restore it to my newly created user.

    IMP MCIIU_INTERFACE/MCIIU@TOM FILE=MIIU_INTERFACE[250507].DMP FULL=YES

    ERROR:table or view does not exist for sasmciiulive.oint_unim_academic_year , eventhough i have backed up and restored for the schema name, sasmciiulive!

    This error is because , at this level the schema, mciiu_interface doesn't have privilages on table name sasmciiulive.oint_unim_academic_year .Thus, i have to re-grant the privilage everytime when i restore it in order for it to be successfully compiled.
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What do you do at home? Do you
    A) drop users, create them and then perform import,
    B) drop objects (tables, procedures, ...) and then perform import,
    C) just perform import,
    D) something else?

    Import should import grants as well, so - I'm not sure why you got this error. If possible, could you try option A) next time you do that?

  11. #11
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    tommyboy1910 :
    try use import with fromuser=(user1....usern) touser=(user1....usern) when you exexute drop user dotn forget restore system privileges granted to user. better way is drop objects listed in user_objects view

  12. #12
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Quote Originally Posted by Littlefoot
    Creating a trigger in "this" schema but which catches inserts into a table in "another" schema. Why? Why wouldn't you create a trigger in a schema that owns this table?
    I actually find this useful - I have admin triggers in some environments that I don't want migrated with the actual business data schema.
    Export by data owner skips the admin owned triggers...

    Also - creating a trigger in someone else's schema by:
    create trigger someuser.trigger
    can cause problems if you later do an exp/imp to a new user using fromuser=
    the trigger may be created in the touser schema, but point to the fromuser schema

    Haven't checked datapump on this yet, legacy exp/imp worked this way...

  13. #13
    Join Date
    Apr 2007
    Posts
    29

    Question

    What do you do at home? Do you
    A) drop users, create them and then perform import,
    B) drop objects (tables, procedures, ...) and then perform import,
    C) just perform import,
    D) something else?
    Hi Littlefoot, actually i tried the option A all this whiles.This is because I only know the option A very well..lol! I beleive that when you drop the both users and recreate them back, will only manage to restore their tables,triggers and sp except for their privilages.Hence, the option A will smehow refresh/reset the schema privilages to the default state[I think so ].


    try use import with fromuser=(user1....usern) touser=(user1....usern)
    Hi Baloo99,
    I cannot do the import with fromuser=(user1....usern) touser=(user1....usern).. this is because my friend will send me the db backup from his place which is located in another place far from my LAN, :d thus i need to get his backup and restore back in my laptop.So, imp from another user to my local db will not work for me here.

    Other then that, you have mentioned about
    when you exexute drop user dotn forget restore system privileges granted to user. better way is drop objects listed in user_objects view
    Im sorry, I don't know how to restore the system privilages or just drop objects listed in user_objects view.Besides, I realy don't understand why i should only drop objects listed in user_objects view .sorry im such a *%$#
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  14. #14
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    tommyboy1910:
    i dont understand what is backup for you? full export of database ? cold database backup?
    when backup is exp dump file, you can use next :
    when you drop user, all privileges are dropped too. like granted roles, table and procedure privileges.
    after user re-creation you must grant each privilege again.
    you can user for recovery privileges use any tool eg toad/sqlnavigator/ pl/sql developer or you can before drop run those scripts

    select 'grant '||x.privilege||' on '||owner||'.'||table_name||' to '||grantee|| ';' from dba_tab_privs x
    where x.grantee='MY_USER';
    select 'grant '||y.granted_role||' to '|| y.grantee||' ;' from dba_role_privs y where y.grantee='MY_USER'
    and save results

    for generation script for drop user`s object you can use
    select 'DROP '||x.object_type ||' '||x.object_name||' ;' from User_Objects x

    when backup is cold backup than you must have script which reflect your changes on database. for example you can use pl/sql developer for difference script generation

  15. #15
    Join Date
    Apr 2007
    Posts
    29
    Hi baloo99,
    first of all, im very sore if i'd used the wrong term to define the backup ..lol

    well i gues what ive been doin was just a export and import method.
    i usually do this..

    to export:
    EXP MCIIU_INTERFACE/MCIIU@10.131.1.10 FILE=MIIU_INTERFACE[250507].DMP

    and use the following command to restore it to my newly created user:
    IMP MCIIU_INTERFACE/MCIIU@TOM FILE=MIIU_INTERFACE[250507].DMP FULL=YES
    i heard that cold backup before ..lol but not sure how to use in my case..i more prefer to use the method where later on able to restore all the tables, sp, triggers and even the privilages!
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

Posting Permissions

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