Failed 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 value is ora-00942 . I checked about this ... on some website it was mentioned that you should give the following privileges ...

 grant select on sys.v_$paramenter to abc 

I can't do that either. Can any body help me with this?

Answer 1

change it so that it is executed using two consecutive steps (not in one anonymous PL / SQL block, as it is now):

First it

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

THEN, like SECOND, 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 the comment:

Before an analysis is performed, the WHOLE PL / SQL block is executed - all objects used in the PL / SQL block must exist before the PL / SQL block is executed ...

Answer 2

You must do this in two separate blocks.

First block:

 begin ... end; / 

A 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 following:

 declare ... begin ... end; / 

So you have:

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

It works under SQL * Plus and SQLDeveloper.