如何找出PLSQL编译错误 [英] How to find out PLSQL Compilation errors
问题描述
创建或替换过程address_insert作为CREATE 类型 colorarray 是 varchar2(10) 的 varray(10)；CREATE 类型城市是 varchar2(20) 的 varray(6)；CREATE 类型状态是 varchar2(15) 的 varray(6)；CREATE 类型的邮政编码是 number(10) 的 varray(6)；CREATE 类型的国家是 varchar2(15) 的 varray(6)；城市城市；颜色颜色阵列；邮政编码；状态状态；国家；身份证号码；x 数；编号；r数；开始x:=1;ca:=1;id1:=1;r:=1;城市:=城市('维萨卡帕特南'，'海得拉巴'，'班加罗尔'，'金奈'，'库尔努尔'，'secunderabad')；color :=colorarray('红色', '蓝色', '绿色', '深蓝色', '黄色', '橙色', '棕色', '黑色', '白色', '紫色');state:=states('Telangana','Tamilnadu','Karnataka','Andhra Pradesh','Madya Pradesh','Kerala');邮编:=邮政编码(530081,500072,316190,981272,717999,621896)；country:=countries('印度','尼泊尔','巴基斯坦','美国','孟加拉国','英国');while x<(select count(persons_id) from person_data) 循环如果 ca>10 那么ca:=1;万一;如果r＞6，则r:=1;万一;插入到persons_addresses(Address_id,Persons_id,flatname,flatno,house_color,contact_person,address_line1,address_line2,address_line3,城市、地区、州、邮政编码、国家)值(id1,(select people_id from (select people_id,row_number()over (order by people_id) as rn from person_data)tmp where rn=x),(SELECT dbms_random.string('L', 15) from dual),(SELECT round(dbms_random.value(100,1000)) num FROM dual),colour(ca),(SELECT dbms_random.string('L', 5)|| ' ' ||dbms_random.string('L', 7) from dual),(SELECT dbms_random.string('L', 9)|| ' ' ||dbms_random.string('L', 6)|| ' ' ||dbms_random.string('L', 8)|| ' ' ||dbms_random.string('L', 10)FROM dual),(SELECT dbms_random.string('L', 9)|| ' ' ||dbms_random.string('L', 6)|| ' ' ||dbms_random.string('L', 8)||' ' ||dbms_random.string('L', 10) FROM dual),(SELECT dbms_random.string('L', 9)|| ' ' ||dbms_random.string('L', 6)||' ' ||dbms_random.string('L', 8)||' ' ||dbms_random.string('L', 10) FROM dual),city(r),(SELECT dbms_random.string('L', 9) from dual),状态(r),zip(r),country(r));犯罪;id1:=id1+1;ca:=ca+1;r:=r+1;x:=x+1;结束循环；EXCEPTION -- 异常处理程序开始WHEN OTHERS THEN -- 处理所有其他错误DBMS_OUTPUT.PUT_LINE (SQLCODE|| ' ' || SQLERRM);结尾;有编译警告.
<块引用>警告:执行完成，警告过程address_insert已编译.
执行时:
执行address_insert<块引用>
从命令中的第 1 行开始出错:执行 address_insert 错误报告:ORA-06550:第 1 行，第 7 列:PLS-00905:对象DATAFOCUS_GROUP.ADDRESS_INSERT 无效 ORA-06550:第 1 行，第 7 列:PL/SQL:忽略语句06550. 00000 - 第 %s 行，第 %s 列:\n%s"*原因:通常是 PL/SQL 编译错误.*动作:
有什么办法可以找出编译错误吗?
SELECT *来自 USER_ERRORSWHERE NAME = 'ADDRESS_INSERT'上述查询有助于检索错误.
错误:
<块引用>PLS-00103:在期待以下其中一个时遇到符号CREATE"以下:
begin 函数 pragma procedure subtype type 当前游标删除存在先前的外部语言符号CREATE"被忽略."由于varray定义，遇到了上述错误.有什么办法吗?
其他错误:
<块引用>"PLS-00103:在期望其中一个时遇到符号"SELECT"以下:
( - + case mod new not null 继续平均计数当前存在最大最小先验 sql stddev 总和方差执行 forall 合并时间时间戳间隔日期管道
，来自
您可以从 DBA_ERRORS 或 USER_ERRORS 视图中获取编译错误.在你的情况下，尝试类似
SELECT *来自 USER_ERRORSWHERE NAME = 'ADDRESS_INSERT'请记住，在 Oracle 表中，视图、过程、包等名称默认为 UPPER_CASE，即使它们在源代码中以 lower_case 显示.
就在顶部，我可以看到 CREATE TYPE 在过程中无效.你可能想要类似的东西
TYPE COLORARRAY IS VARRAY(10) OF VARCHAR2(10)请参阅 PL/SQL 参考手册部分定义集合类型.了解更多信息.
祝你好运.
create or replace procedure address_insert
as
CREATE type colorarray is varray(10) of varchar2(10);
CREATE type cities is varray(6) of varchar2(20);
CREATE type states is varray(6) of varchar2(15);
CREATE type zipcodes is varray(6) of number(10);
CREATE type countries is varray(6) of varchar2(15);
city cities;
Colour colorarray;
zip zipcodes;
state states;
country countries;
id1 number;
x number;
ca number;
r number;
begin
x:=1;
ca:=1;
id1:=1;
r:=1;
city:=cities('Visakhapatnam','Hyderabad','Bangalore','Chennai','Kurnool','secunderabad');
colour :=colorarray('Red', 'Blue', 'green', 'Dark blue', 'yellow', 'orange', 'brown', 'black', 'white', 'purple');
state:=states('Telangana','Tamilnadu','Karnataka','Andhra Pradesh','Madya Pradesh','Kerala');
zip:=zipcodes(530081,500072,316190,981272,717999,621896);
country:=countries('India','Nepal','Pakistan','USA','Bangladesh','UK');
while x<(select count(persons_id) from person_data) loop
if ca>10 then
ca:=1;
end if;
if r>6 then
r:=1;
end if;
insert into persons_addresses(Address_id,Persons_id,flatname,flatno,house_color,contact_person,address_line1,address_line2,address_line3,
city,district,state,zipcode,country) values
(id1,(select persons_id from (select persons_id,row_number()over (order by persons_id) as rn from person_data)tmp where rn=x),
(SELECT dbms_random.string('L', 15) from dual),(SELECT round(dbms_random.value(100,1000)) num FROM dual),colour(ca),
(SELECT dbms_random.string('L', 5)|| ' ' ||dbms_random.string('L', 7) from dual),
(SELECT dbms_random.string('L', 9)|| ' ' ||dbms_random.string('L', 6)|| ' ' ||dbms_random.string('L', 8)|| ' ' ||dbms_random.string('L', 10)
FROM dual),(SELECT dbms_random.string('L', 9)|| ' ' ||dbms_random.string('L', 6)|| ' ' ||
dbms_random.string('L', 8)|| ' ' ||dbms_random.string('L', 10) FROM dual),(SELECT dbms_random.string('L', 9)|| ' ' ||
dbms_random.string('L', 6)|| ' ' ||dbms_random.string('L', 8)|| ' ' ||dbms_random.string('L', 10) FROM dual),city(r),(SELECT dbms_random.string('L', 9) from dual),
state(r),zip(r),country(r));
commit;
id1:=id1+1;
ca:=ca+1;
r:=r+1;
x:=x+1;
end loop;
EXCEPTION -- exception handlers begin
WHEN OTHERS THEN -- handles all other errors
DBMS_OUTPUT.PUT_LINE (SQLCODE|| ' ' || SQLERRM);
end;
There is a compilation warning.
Warning: execution completed with warning procedure address_insert Compiled.
when executed:
execute address_insert
Error starting at line 1 in command: execute address_insert Error report: ORA-06550: line 1, column 7: PLS-00905: object DATAFOCUS_GROUP.ADDRESS_INSERT is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
Is there any way we can find out the compilation errors?
SELECT *
FROM USER_ERRORS
WHERE NAME = 'ADDRESS_INSERT'
The above query helped retrieve the errors.
Errors:
"PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "CREATE" was ignored. " the above error have been encountered due to varray definition. Is there any alternative?
other errors:
"PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe
, from "
You can get compilation errors from the DBA_ERRORS or USER_ERRORS views. In your case, try something like
SELECT *
FROM USER_ERRORS
WHERE NAME = 'ADDRESS_INSERT'
Remember that in Oracle table, view, procedure, package, etc names are UPPER_CASE by default, even if they're shown in lower_case in your source code.
Right off the top I can see that CREATE TYPE is not valid in a procedure. You probably want something like
TYPE COLORARRAY IS VARRAY(10) OF VARCHAR2(10)
See the PL/SQL Reference Manual section on defining collection types. for further information.
Best of luck.
这篇关于如何找出PLSQL编译错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
