insuffient privileges when using dbms_sql

[terry_...@my-deja.com]

I am using dbms_sql.parse in a procedure to create a view, but I get
the message "insufficient privileges". What am I missing?

Terry

Sent via Deja.com http://www.deja.com/
Before you buy.

[eldre...@my-deja.com]

Here's my code from a procedure I use to truncate tables. The
creator ID has DBA privileges however I believe you can grant
execute on the DBMS_SQL package.

PROCEDURE truncate_tables(result OUT varchar2)
is
cursor_int integer;
status_int integer;
p_statement varchar2(50);
begin
cursor_int := dbms_sql.open_cursor;
p_statement := 'truncate table installed_components';
dbms_sql.parse(cursor_int,p_statement,dbms_sql.native);
status_int := dbms_sql.execute(cursor_int);
--other code in here.
dbms_sql.close_cursor(cursor_int);
result := 'P';
dbms_output.put_line('Truncate tables successful');
EXCEPTION
when others then
result := 'F';
dbms_output.put_line('error'||sqlerrm);
dbms_output.put_line(result ||' in truncate routine.');
end;

In article <8qtn0k$jq4$1...@nnrp1.deja.com>,

[terry_...@my-deja.com]

Thanks, but it didn't work. I added the DBA role to my user id and it
still didn't work. I tried to add object privileges for
dbms_sql.execute and dbms_sys_sql.execute but I get the error
Insufficient Privileges.
If I change the text string to drop a view, it works fine, but I can't
create a view. However, I can create a view if I run it from the SQL
worksheet.

The error I get is:
ORA-01031 - Insufficient Privileges
ORA-06512 - SYS.DBMS_SYS_SQL Line 491
ORA-06512 - SYS.DBMS_SQL Line 32
ORA-06512 - RPC.REV_VIEW_BUILD Line 83
ORA-06512 At Line 3

Any more suggestions?

Terry

In article <8qtngc$k9q$1...@nnrp1.deja.com>,

[terry_...@my-deja.com]

Figured it out. I granted privilege of "Create Any View" and it runs
fine now.
Thanks,

[stas_...@my-deja.com]

In article <8qve23$t3m$1...@nnrp1.deja.com>,

terry_...@my-deja.com wrote:
> Thanks, but it didn't work. I added the DBA
role to my user id and it
> still didn't work. I tried to add object
privileges for
> dbms_sql.execute and dbms_sys_sql.execute but I
get the error
> Insufficient Privileges.
> If I change the text string to drop a view, it
works fine, but I can't
> create a view. However, I can create a view if
I run it from the SQL
> worksheet.
>
> The error I get is:
> ORA-01031 - Insufficient Privileges
> ORA-06512 - SYS.DBMS_SYS_SQL Line 491
> ORA-06512 - SYS.DBMS_SQL Line 32
> ORA-06512 - RPC.REV_VIEW_BUILD Line 83
> ORA-06512 At Line 3
>

It's old bug of DBMS_SQL.
Try to
GRANT CREATE ANY VIEW to ...

Stas.

[Sybrand Bakker]

It is no bug at all:
roles are not enabled during execution of pl/sql, and this is by design, as
roles are volatile.

Regards,

Sybrand Bakker, Oracle DBA

<stas_...@my-deja.com> wrote in message
news:8shsvl$prh$1...@nnrp1.deja.com...