create or replace type card_unit_varray
as varray(13) of varchar2;
/create or replace type card_suit_varray
as varray(4) of varchar2;
/
create or replace type card_table
as table of varchar2;
/set serveroutput ondeclare
counter number:=0;
unit card_unit_varray:=car_unit_varray('Clubs','Diamonds','Hearts','Spades');
suit card_suit_varray:=car_suit_varray('one','two','three','four','five','six',
'seven','eight','night','ten','eleven',
'twelve');
card card_table:=car_table();begin
for i in 1..suit.count loop
for j in 1..unit.count loop
counter:=counter+1;
card.extend;
card(counter):=units(j)||'of'||suit(i);
end loop;
end loop;
dbms_output.put_line('cards follows');
dbms_output.put_line('=================================');
for i in 1..counter loop
dbms_output.put_line('['||card(i)||']');
end loop;
end;
/请大家帮忙看一下这段代码有什么问题没有,我有几个疑问。
1
在SQL*PLUS中执行的时候有警告,说我在创建TYPE的时候发生了编译错误
可是当我用select object_name from user_objects where object_type='TYPE'查询的时候却发现这些TYPE已经存在了,可是它们的STATUS为INVALID,请问这是怎么回事?
2
现在如何删除它们重建,或者使它们的状态变为VALID?我尝试过CREATE OR REPLACE可是不管用,提示成功了可是状态仍然为INVALID。
3
在PL/SQL中分号;和斜线/有什么区别?我现在知道的只有如果某个.sql文本里没有/的时候,当在SQL*PLUS中执行的时候不会结束,直到你输入一个/
3. pl/sql程序块中因为有;号,这个时候/提示系统你的代码已经结束了是执行命令,等同于单行sql,比如select,update中末尾的;
如果是单条sql命令,就在末尾加;或者换行加/比如:
drop table a
这是单条命令,你可以在a后面加;执行,也可以换行加/执行再比如;
begin
null;
end;这样的程序块,就只能换行加/执行了,因为程序块里面每个语句结尾都是;
2 /Warning: Type created with compilation errors.SQL> exec dbms_ddl.alter_compile('TYPE','scott','CARD_UNIT_VARRAY');
BEGIN dbms_ddl.alter_compile('TYPE','scott','CARD_UNIT_VARRAY'); END;*
ERROR at line 1:
ORA-04047: object specified is incompatible with the flag specified
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at "SYS.DBMS_DDL", line 156
ORA-06512: at line 1
/
show error
/
看看
2 /Warning: Type created with compilation errors.SQL> show errors
Errors for TYPE CARD_UNIT_VARRAY:LINE/COL ERROR
-------- -----------------------------------------------------------------
1/40 PLS-00215: String length constraints must be in range (1 ..
32767)SQL> create or replace type card_unit_varray as varray(13) of varchar2(13);
2 /Type created.
唉。。
找到原因了
没有指定长度
我看了看找不出错在哪里 请帮忙看一下
文件cards.sql代码如下
create or replace type card_unit_varray
as varray(13) of varchar2(10);
/create or replace type card_suit_varray
as varray(4) of varchar2(10);
/create or replace type card_table
as table of varchar2(30);
/set serveroutput ondeclare
counter number:=0;
unit card_unit_varray:=card_unit_varray('Clubs','Diamonds','Hearts','Spades');
suit card_suit_varray:=card_suit_varray('one','two','three','four','five','six',
'seven','eight','night','ten','eleven',
'twelve','thirteen');
card card_table:=card_table();begin
for i in 1..suit.count loop
for j in 1..unit.count loop
counter:=counter+1;
card.extend;
card(counter):=unit(j)||'of'||suit(i);
end loop;
end loop;
dbms_output.put_line('cards follows');
dbms_output.put_line('=================================');
for i in 1..counter loop
dbms_output.put_line('['||card(i)||']');
end loop;
end;
/在SQL*PLUS中执行报错
SQL> @cards.sqlType created.
Type created.
Type created.declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 1不知道是哪里下标错了,我检查了下没找到。
错误提示在第一行?
将varray(13)和varray(4)两个对换下
应该就没问题了
as varray(13) of varchar2;
/create or replace type card_suit_varray
as varray(4) of varchar2;
/
的数量跟后面设置的数量不对,搞反了吧?