Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Create View using Execute Immediate giving ORA - 01031

    Hi,
    I have been creating a stored procedure that dynamically creates a SQL for creating or replacing the view and then executes it using Execute Immediate,
    but when I run this stored procedure I get following error:

    ORA - 01031 : issufficient priviledges

    But the problem is that I do have priviledges because if I run the generated sql manaully it creates the view without any problems.

    Reason that I need to do this is because the columns in this view will be ever changing and it this sp will get scheduled as part of batch job that will run once at night and will recreate the view depending upon the columns that it need to have from the tables.

    Any help would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    BAD IDEA!
    Why not just create separate views & be done with it?

    privileges acquired via ROLE do NOT apply within named PL/SQL procedures

    direct GRANT is required
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Privileges acquired via roles are not applicable to the stored procedure.
    All required privileges (CREATE VIEW, SELECT on all involved tables, EXECUTE on used functions etc) have to be granted directly to the user owning the procedure or the caller (depending on AUTHID value).
    See this on AskTom for detailed explanation: http://asktom.oracle.com/pls/asktom/...:1065832643319

    Just curious: as "the columns in this view will be ever changing", is also the code using it (whatever language it is written) able to cope with this behaviour?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Just because you can do something (dynamically CREATE VIEW),
    does not mean you should necessarily do it.

    Realize that you can poke yourself in the eye with a sharp pencil.

    I suggest that you take neither such foolish action.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2011
    Posts
    4
    Thanks a lot for your replies guys but yes problem is not that I want to create the view every time the solution of the problem is forcing me to do this.
    If you guys can suggest any other thing then I would be more happy to accept it.


    So here is the problem.
    I have a table that gets refreshed every night and now my reporting users want a pivoted view of that table depending upon one column so the data in that column can be changing and there is no set rule that max number or super set of data to that column so that I can fix the sql.

    So I created a sql that does the pivot of that table now the problem is that is ever chaning table and the pivoted view has to be dynamic so I have to create a view because sql is getting generated from stored proc and it will recreate the view at night for them to view in morning.

    Thanks

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What version of the database are you using. Oracle 11 has a built in pivot command.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How do you manage to keep straight the resultant the varying number of columns & their datatypes?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2011
    Posts
    4
    I am using Oracle 11G and using Pivot command to perform the pivot but in selection list you have to provide the full list and sub query does not work there so I am generating that list dynamically.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please post examples as end results would exist.

    SQL> DESC <day1_view>
    SQL> DESC <day2_view>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by har100 View Post
    ... Etc ...
    So I created a sql that does the pivot of that table now the problem is that is ever chaning table and the pivoted view has to be dynamic so I have to create a view because sql is getting generated from stored proc and it will recreate the view at night for them to view in morning.
    I had to solve this same issue, but instead of creating a view I returned a ref cursor from the generated sql.

    HTH.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Nov 2011
    Posts
    4
    Yes I thought to do the same but and in fact I created that but the problem is the calling application / users are more confident is simply querying a view then calling a stored procedure.
    but I guess I will have to talk to them again if they could call the sp then that would be great.

  12. #12
    Join Date
    Jun 2012
    Posts
    1

    Smile Fixing ORA-1031 when creating a materialized view in a stored procedure

    I had the same need to create a materialized view within a stored procedure (and for the database purists who say this should never be needed, try working in the world of clinical trials where the entire data model is dynamic). I also got an error ORA-1031 executing the stored procedure, even though the owner of the stored procedure has privileges to create a materialized view. I found a solution was to add AUTHID CURRENT_USER to the procedure declaration; then, provided the user running the stored procedure has the necessary privileges, it all works fine.

Tags for this Thread

Posting Permissions

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