oracle 无法使用 pl/sql 创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7858902/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unable to create table using pl/sql
提问by Tarun
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...
它的给予ora-00942。我查了一下...在某个网站上提到您必须提供以下特权...
grant select on sys.v_$paramenter to abc
I am unable to do that also..Can any body help me with this
我也做不到..任何机构都可以帮我解决这个问题吗
回答by Yahia
change it so that it is executed in 2 successive steps (NOT in one PL/SQL anonymous block like it is now):
更改它，使其在 2 个连续步骤中执行（而不是像现在这样在一个 PL/SQL 匿名块中执行）：
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...
在执行之前，整个 PL/SQL 块被解析 - 在 PL/SQL 块中使用的所有对象必须在执行 PL/SQL 块之前存在......
回答by Ludovic Kuty
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.
斜线表示您的缓冲区应该发送到 DBMS 并进行评估。它指示您的 PL/SQL 代码在哪里结束和评估可以开始。
Then the next:
那么接下来：
declare
...
begin
...
end;
/
Thus, you have:
因此，您有：
begin
...
end;
/
declare
...
begin
...
end;
/
It works under SQL*Plus and SQLDeveloper.
它在 SQL*Plus 和 SQLDeveloper 下工作。
回答by radzimir
If working withing within SQLPlus block, all DDL changes mus be performed with execute immediate. Unfortunately, those changes are invisible until block finishes execution.
如果在 SQLPlus 块内工作，则所有 DDL 更改都必须使用execute 立即执行。不幸的是，这些更改在块完成执行之前是不可见的。
To work around this problem, please use consequently execute immediatefor all following statements that depends on former "hidden" changes. That applies also for DML statements. In your case:
要解决此问题，请对依赖于先前“隐藏”更改的所有后续语句使用相应的立即执行。这也适用于 DML 语句。在你的情况下：
...
execute immediate 'insert into rat1 values('||y(i)||')';
...
