Unable to create table using pl/sql

Question

declare
type yy is table of t12.name%type index by binary_integer;
y yy;
n number:=1;
begin
execute immediate 'create table rat1 ( name varchar2(10) )';
commit;

select name bulk collect into y from t12;
for i in (select id,name from t12)
loop
dbms_output.put_line(y(n));
n:=n+1;
end loop;
forall i in y.first..y.last
insert into rat1 values(y(i));
end;

Its giving ora-00942. I checked about it...at some website it was mentioning that you have to give following privilages...

grant select on sys.v_$paramenter to abc

I am unable to do that also..Can any body help me with this

Answer 1

change it so that it is executed in 2 successive steps (NOT in one PL/SQL anonymous block like it is now):

First this

begin
execute immediate 'create table rat1 ( name varchar2(10) )';
commit;
end;

THEN as a SECOND block this

declare
type yy is table of t12.name%type index by binary_integer;
y yy;
n number:=1;
begin

select name bulk collect into y from t12;
for i in (select id,name from t12)
loop
dbms_output.put_line(y(n));
n:=n+1;
end loop;
forall i in y.first..y.last
insert into rat1 values(y(i));
end;

EDIT - as per comment:

Before execution the WHOLE PL/SQL block is parsed - all objects used in an PL/SQL block must exist BEFORE the PL/SQL block is executed...

Answer 2

You should do it in two separate blocks.

First block:

begin
...
end;
/

The slash indicates that your buffer should be sent to the DBMS and evaluated. It indicates where your PL/SQL code ends and evaluation can begin.

Then the next:

declare
...
begin
...
end;
/

Thus, you have:

begin
...
end;
/
declare
...
begin
...
end;
/

It works under SQL*Plus and SQLDeveloper.

Answer 3

If working withing within SQLPlus block, all DDL changes mus be performed with execute immediate. Unfortunately, those changes are invisible until block finishes execution.

To work around this problem, please use consequently execute immediate for all following statements that depends on former "hidden" changes. That applies also for DML statements. In your case:

...
execute immediate 'insert into rat1 values('||y(i)||')';
...