Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DDL in package

813348May 18 2011 — edited May 19 2011
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
This post has been answered by BluShadow on May 18 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 16 2011
Added on May 18 2011
9 comments
724 views