奇怪!动态建表不成功

显示全部楼层 · 2005-9-12 01:01:28

奇怪!动态建表不成功
declare
begin
execute immediate 'create table test(id number)';
end;
/
这样简单写写的过程,可以成功,但是我把这语句放到一个比较长的脚本的开头部分,却执行不成功
也没有任何出错信息
晕啊

千问 · 2005-9-12 01:01:28

9.2.0.4

千问 · 2005-9-12 01:01:28

加上exception，捕捉错误信息

千问 · 2005-9-12 01:01:28

最初由 hzhu001 发布
[B]加上exception，捕捉错误信息 [/B]
加了,可是连exception都不起作用,

千问 · 2005-9-12 01:01:28

SQL> drop table test;
±íÒÑ¶ªÆú¡£
SQL> declare
2begin
3execute immediate 'create table test(id number)';
4end;
5/
PL/SQL ¹ý³ÌÒÑ³É¹¦Íê³É¡£
SQL> desc test
Ãû³Æ
ÊÇ·ñÎª¿Õ? ÀàÐÍ
----------------------------------------- -------- -------------------------
ID
NUMBER
SQL>

千问 · 2005-9-12 01:01:28

提问的智慧哦~~~

千问 · 2005-9-12 01:01:28

[PHP]
declare
typet_type is table of number(10);
v_batch_idt_type;
begin
execute immediate 'create table test2(batch_id number(10))';
select distinct batch_id bulk collect into v_batch_id from test2;
if v_batch_id.count>0 then
for i in v_batch_id.first..v_batch_id.last loop
DBMS_OUTPUT.PUT_LINE('test');
end loop;
end if;
end;
/
ERROR at line 7:
ORA-06550: line 7, column 62:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored

..
[/PHP]
晕倒
我如果去掉后面的BULK COLLECT和循环,就能建表成功
Oralcee 9.2.0.4

千问 · 2005-9-12 01:01:28

[PHP]
再简单一点,晕啊
SQL> declare
2typet_type is table of number(10);
3v_batch_idt_type;
4begin
5execute immediate 'create table test2(batch_id number(10))';
6selectbatch_id bulk collect into v_batch_id from test2;
7end;
8/
selectbatch_id bulk collect into v_batch_id from test2;

*
ERROR at line 6:
ORA-06550: line 6, column 54:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored
失败
Elapsed: 00:00:00.00
SQL> declare
2typet_type is table of number(10);
3v_batch_idt_type;
4begin
5execute immediate 'create table test2(batch_id number(10))';
6--selectbatch_id bulk collect into v_batch_id from test2;
7end;
8/
PL/SQL procedure successfully completed.
成功
Elapsed: 00:00:00.00
SQL> desc test2;
Name
Null?Type
----------------------------------------- -------- ---------------------
BATCH_ID
NUMBER(10)
SQL>
..
[/PHP]

千问 · 2005-9-12 01:01:28

加一个pragma transaction的事务独立看看。

千问 · 2005-9-12 01:01:28

最初由 jidongzheng 发布
[B]加一个pragma transaction的事务独立看看。 [/B]
能否说清楚点啊,谢谢