hi all ,
I was trying to do a DDL IN PACKAGE but i am receiving an error.
Basically i am preparing for OCA so it came in my mind to do a ddl in package's procedure even if i do a ddl in BEGIN section of package body i still get the same error. The code that i have used is
create package p1 is
procedure p1;
end;
create or replace package body p1 is
cnt number;
cursor c1 is select * from hr.employees;
v1 c1%rowtype;
procedure p1 is
cnt number :=0 ;
begin
loop
exit when c1%notfound or cnt= 10;
fetch c1 into v1;
cnt := cnt+1;
dbms_output.put_line('The name is proc '||v1.first_name);
execute immediate('create table tst(no number)');
dbms_output.put_line('id is '||v1.employee_id);
end loop;
dbms_output.put_line(c1%rowcount);
end;
BEGIN
open c1 ;
fetch c1 into v1;
dbms_output.put_line('The name is '||v1.first_name);
end;
O/P is :->
The name is Donald
The name is proc Douglas
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PLSQL.P1", line 13
ORA-06512: at line 2
but if i do like
SQL> begin
2 execute immediate('create table tst(no number)');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from tst;
no rows selected
thanks