Re: [PL/SQL] Dynamic SQL: execute immediate with insufficient privileges

manfred.pruntsch_at_ifcos.com said...
> Hi all,
>
> I try to get a create table statement running in a stored procedure.
> The code looks:
>
> begin
> ...
> EXECUTE IMMEDIATE 'drop table temp_tbl';
> EXECUTE IMMEDIATE 'create table temp_tbl(id integer, entry varchar2(64))';
> ....
> end;
>
> I've got the error message 'ORA-01031: insufficient privileges' while
> performing the second execute. The first one works fine. When I'm running
> the create statement separately (e.g. sql worksheet) it works.
> I'm always logged on as the same user.
> Any hints would be appriciated.
>
> regards
> Manfred
>

Do you own the procedure, or was it created by another schema userid and you're just calling it? Stored procedures run under the owner's privileges.

Dropping and creating tables from a stored procedure is an inefficient practice in Oracle (though it does work in other databases). If this was just a quick-and-dirty conversion from another database (like SQL Server), then reworking your design to make better use of Oracle's architecture differences should be a high priority.

Remember, each database has its own architecture. They are different ... sometimes vastly different! If I were converting from Oracle to another database, I'd have to consider those differences carefully. The same goes for converting *to* Oracle.

-- 
/Karsten
DBA > retired > DBA