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.

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

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?

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.

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

What version of the database are you using. Oracle 11 has a built in pivot command.

How do you manage to keep straight the resultant the varying number of columns & their datatypes?

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.

please post examples as end results would exist.

SQL> DESC <day1_view>
SQL> DESC <day2_view>

I had to solve this same issue, but instead of creating a view I returned a ref cursor from the generated sql.

HTH.

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.