| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_SQL
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> create view foo as select * from dual;
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence dbms_sql won't be able to do it either.
You probably have the "create view" privelege from the DBA role. Grant the privelege directly to the owner of the procedure and it'll work.
On Thu, 04 Sep 1997 21:28:32 +0200, Dietrich Mueller <dmueller_at_mail.oec.uni-osnabrueck.de> wrote:
>
>Hi there,
>need some help regarding the DBMS_SQL package
>I want to dynamically create Views using this package. For a starter I
>wrote the following code:
>
>Create or replace Procedure create_view(view_name IN varchar2)
> is
>cursor_id Integer;
>tab_name varchar2(30);
>create_statement varchar2(100);
>Begin
> cursor_id:=dbms_sql.open_cursor;
> create_statement:='Create or Replace View '||view_name||' As Select
>tname from tab';
> dbms_output.Put_Line('create_statement: '||create_statement);
> dbms_sql.parse(cursor_id,create_statement,dbms_sql.v7);
> --dbms_sql.define_column(cursor_id,1,tab_name);
> dbms_sql.close_cursor(cursor_id);
> dbms_output.Put_Line('View '||view_name||' wurde erstellt');
>end create_view;
>
>The procedure is compilied without errors.
>But when I execute the procedure from SQL*PLUS like
>
>SQL> execute create_view('test_view');
>
>the following error messages appear:
>
>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 25
>ORA-06512: at "DMUELLER.CREATE_VIEW", line 10
>ORA-06512: at line 1
>
>First I thought that I might need more privileges so I ran the script as
>DBA but the same error message kept poping up.
>Then I tried to define the column on the Select but that didn't work
>either.
>At the moment I'm stuck and I only got the following ideas;
> * Statements like that can't be handeld that way because of a mixture
>of DML and DDL
> * if that error_message is right (which I really doubt) then it could
>be a passwordfile problem (because the database has set the parameter
>remote_login
> passwordfile=none ) (allthough this idea seems not very logical to
>me)
> * ????
>OS-parameters:
>Oracle7 Server Release 7.2.2.3 running on AIX 4.x
>
>Any ideas?
>Any hints/help very much appreciated
>
> thanks a lot in advance!!!
>
>Regards,
>
>Dietrich
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |