Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: commit within plsql

    Hello when I run the following it works:

    DECLARE
    CURSOR ProcCursor IS SELECT OBJECT_NAME FROM DBA_PROCEDURES WHERE OWNER = 'ISH';
    BEGIN
    FOR ProcRecord IN ProcCursor LOOP
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ISH.'||ProcRecord.OBJECT_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    DECLARE
    CURSOR RecCursor IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'ISH';
    BEGIN
    FOR RecRecord IN RecCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, DELETE, UPDATE ON ISH.'||RecRecord.TABLE_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    DECLARE
    CURSOR ViewCursor IS SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER = 'ISH';
    BEGIN
    FOR ViewRecord IN ViewCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ISH.'||ViewRecord.VIEW_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    But when I run it like this:

    CREATE OR REPLACE PROCEDURE ish.applsp_AssignRightsToUmmUser
    AS
    CURSOR ProcCursor IS SELECT OBJECT_NAME FROM DBA_PROCEDURES WHERE OWNER = 'ISH';
    CURSOR RecCursor IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'ISH';
    CURSOR ViewCursor IS SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER = 'ISH';
    BEGIN
    FOR ProcRecord IN ProcCursor LOOP
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ISH.'||ProcRecord.OBJECT_NAME||' TO UMMUSER';
    END LOOP;
    EXECUTE IMMEDIATE 'COMMIT';

    FOR RecRecord IN RecCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, DELETE, UPDATE ON ISH.'||RecRecord.TABLE_NAME||' TO UMMUSER';
    END LOOP;
    EXECUTE IMMEDIATE 'COMMIT';

    FOR ViewRecord IN ViewCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ISH.'||ViewRecord.VIEW_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    CALL ish.applsp_AssignRightsToUmmUser();

    It doesn't work. It gets stuck, I probably think, because of no commit or something like that. When I insert some commit's within my procedure, it still doesn't work. does anyone have a clue?

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

    Re: commit within plsql

    Originally posted by thepercival
    Hello when I run the following it works:

    DECLARE
    CURSOR ProcCursor IS SELECT OBJECT_NAME FROM DBA_PROCEDURES WHERE OWNER = 'ISH';
    BEGIN
    FOR ProcRecord IN ProcCursor LOOP
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ISH.'||ProcRecord.OBJECT_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    DECLARE
    CURSOR RecCursor IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'ISH';
    BEGIN
    FOR RecRecord IN RecCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, DELETE, UPDATE ON ISH.'||RecRecord.TABLE_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    DECLARE
    CURSOR ViewCursor IS SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER = 'ISH';
    BEGIN
    FOR ViewRecord IN ViewCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ISH.'||ViewRecord.VIEW_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    But when I run it like this:

    CREATE OR REPLACE PROCEDURE ish.applsp_AssignRightsToUmmUser
    AS
    CURSOR ProcCursor IS SELECT OBJECT_NAME FROM DBA_PROCEDURES WHERE OWNER = 'ISH';
    CURSOR RecCursor IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'ISH';
    CURSOR ViewCursor IS SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER = 'ISH';
    BEGIN
    FOR ProcRecord IN ProcCursor LOOP
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ISH.'||ProcRecord.OBJECT_NAME||' TO UMMUSER';
    END LOOP;
    EXECUTE IMMEDIATE 'COMMIT';

    FOR RecRecord IN RecCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, DELETE, UPDATE ON ISH.'||RecRecord.TABLE_NAME||' TO UMMUSER';
    END LOOP;
    EXECUTE IMMEDIATE 'COMMIT';

    FOR ViewRecord IN ViewCursor LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ISH.'||ViewRecord.VIEW_NAME||' TO UMMUSER';
    END LOOP;
    END;
    /

    CALL ish.applsp_AssignRightsToUmmUser();

    It doesn't work. It gets stuck, I probably think, because of no commit or something like that. When I insert some commit's within my procedure, it still doesn't work. does anyone have a clue?
    Not sure what your problem is exactly (i.e. why it "gets stuck"), but:

    1) There is no need for EXECUTE IMMEDIATE on COMMIT statement: COMMIT is valid in PL/SQL

    2) There is no need to commit at all: DDL like GRANT... is automatically committed by Oracle. So leave out the COMMITs altogether.

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    i knew

    i knew that commit is valid pl/sql. but just commit didn't work. what you said about ddl, i was thinking the same. then i thought, but why is it locked or something when i do the granting within a procedure. so i did just the sql-statement and it was finished within a few seconds, while the stored_procedure is stuck or locked or whatever. sqlplusw says 'not responding'. You know why?

  4. #4
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    lots of code

    i don't want to put the whole code, everytime i assign rights to users, so i would like to have it in a st_proc. you know how it is possible?

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

    Re: i knew

    Originally posted by thepercival
    i knew that commit is valid pl/sql. but just commit didn't work. what you said about ddl, i was thinking the same. then i thought, but why is it locked or something when i do the granting within a procedure. so i did just the sql-statement and it was finished within a few seconds, while the stored_procedure is stuck or locked or whatever. sqlplusw says 'not responding'. You know why?
    I don't know why. "Not responding" just means that the client is waiting for the server to return control. It could be that you are being blocked by another user (not sure how), or it could simply be that the PL/SQL process takes a long time. If you comment out the EXECUTE IMMEDIATE statements and put "NULL;" instead, does it still take a long time?

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

    Re: lots of code

    Originally posted by thepercival
    i don't want to put the whole code, everytime i assign rights to users, so i would like to have it in a st_proc. you know how it is possible?
    It seems it should be possible - I don't see why not. However, it is not an approach I would use. A stored procedure is for reusable code, where as your procedure will only be run once since it assigns specific privileges to a specific user.

    What I would do would be create a static script to grant appropriate privileges to ROLES, and then assign those roles to users.

    I might use PL/SQL to generate the script:

    FOR ProcRecord IN ProcCursor LOOP
    print('GRANT EXECUTE ON ISH.'||ProcRecord.OBJECT_NAME||' TO ISH_ROLE1');
    END LOOP;
    ...

    ("print" being shorthand for some kind of PUT_LINE)

    I would probably then want to edit the script, because not all roles require the same access to all objects (that's the point of security after all) - rather than give blanket privileges to all. So the final script might look like:

    GRANT SELECT ON emp TO ish_user_role;
    GRANT SELECT,INSERT,UPDATE,DELETE ON emp TO ish_admin_role;
    ...

    Now whenever I want to grant a user access to the ISH application I just say:

    GRANT ish_user_role TO username;

    Just 1 simple command, no stored procedure required.

  7. #7
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149
    ummuser is already a role. this role is granted to all users except sysadmin. he gets another role. the st_proc is also used in the code. when a new table is created we call this st_proc.. So you are right again! I put worthy to your reply's. The problem was: that i granted execution-priv on ish_st_proc while i was executing one. So have to come up with an alternative. many thanks anyway!

  8. #8
    Join Date
    Sep 2003
    Posts
    1
    i have problem when execute this package
    does any one know how this prodedure has runtime error
    the table exist in two database but it get error while running
    SQL> execute test ;
    BEGIN test ; END;

    *
    ERROR at line 1:
    ORA-00906: missing left parenthesis
    ORA-06512: at "SYSTEM.TEST", line 5
    ORA-06512: at line 1

    the procedure is :

    CREATE OR REPLACE procedure test is
    begin
    execute immediate 'insert into APPS.table1 select * from APPS.table1@db1';
    end test;



    regards
    adhiprana

  9. #9
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Originally posted by adhi_prana
    i have problem when execute this package
    does any one know how this prodedure has runtime error
    the table exist in two database but it get error while running
    SQL> execute test ;
    BEGIN test ; END;

    *
    ERROR at line 1:
    ORA-00906: missing left parenthesis
    ORA-06512: at "SYSTEM.TEST", line 5
    ORA-06512: at line 1

    the procedure is :

    CREATE OR REPLACE procedure test is
    begin
    execute immediate 'insert into APPS.table1 select * from APPS.table1@db1';
    end test;



    regards
    adhiprana
    Try :

    execute immediate 'insert into APPS.table1 values (select * from APPS.table1@db1');

    That should work.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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