| 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: Truncate in stored procedure
Diane wrote:
>
> In article <76bdjh$7bs$1_at_nnrp1.dejanews.com>, andrewf_at_jaredgroup.com
> writes
> >I was creating a simple procedure,
> >
> >Create procedure p_truncate
> >as
> >begin
> > truncate table employee;
> >end;
> >
> >and got compilation error. It seems the pl/sql does not recognize the Truncate
> >syntax. Should I change some configure parms or other solutions?
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> What is the syntax error you are getting?
> It may be because the privilege to truncate a table has not been granted
> directly to the procedure owner or to the procedure executor.
> If truncate privilege is granted via a role it will not be possible to
> truncate in a procedure.
> We have a number of procedures which truncate partitions in tables so it
> is possible to do it. The stored procedure is owned by and executed by
> the table owner in all cases.
> --
> Diane
truncate is DDL and thus not permitted in PL/SQL...Use dbms_sql..
create or replace procedure do_trunc(p_table varchar2) is
c integer := dbms_sql.open_cursor;
d integer;
begin
dbms_sql.parse('truncate table '||p_table,dbms_sql.native);
d := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception when others then
dbms_sql.close_cursor(c);
raise;
end;
--
 |
 |