Forum Stats

  • 3,817,240 Users
  • 2,259,294 Discussions
  • 7,893,710 Comments

Discussions

How to write a procedure to execute set of queries automatically

2»

Answers

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Nov 20, 2015 9:14AM

    Check the contents of the dba_recylebin for tables from one or more of the users whose tables you are trying to grant,  You may need to add an additional predicate to the query against dba_tables like table_name not like 'BIN$%' to avoid objects in the recycle bin that still appear in xxx_tables views.

    John

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Nov 20, 2015 9:20AM

    Chris:

    You are correct that it could be done in a single loop, but for this type of "maintenance" scripting I prefer the more explicit approach since, it seems to me, that it is much clearer in intent and simpler to verify the correctness.  I had to read your query a couple of times to be sure it was doing the "correct" thing.

    Obviously a personal preference.

    John

    3123956
  • 3123956
    3123956 Member Posts: 9
    edited Nov 23, 2015 5:28AM

    I tried it already. Even that is not working.

  • Martin Lutz
    Martin Lutz Member Posts: 28
    edited Nov 23, 2015 5:45AM
    'ORA-00911: invalid character
    ORA-06512: at "SABARISH.GRANTS_PROC", line 24
    ORA-06512: at line 2
    Process exited.'
    
    Sorry, it returns table (or) View doesn't exist. Not the above mentioned error.
    

    If the error really is " table or view doesn't exist": Which user is executing this procedure? DBA privileges?

  • 3123956
    3123956 Member Posts: 9
    edited Nov 23, 2015 11:36PM

    Yes. Not the above error. It returns table or view doesn't exist. And the user through which I execute has DBA privilege.

  • Unknown
    edited Nov 23, 2015 11:46PM
    3123956 wrote:
    
    And the user through which I execute has DBA privilege. 
    

    Words matter & have meaning.

    DBA is not a privilege, but a ROLE.

    Privileges acquired via ROLE do NOT apply within named PL/SQL procedure.

    So having DBA ROLE may not be sufficient to do what you desire!

    3123956
This discussion has been closed.