我们应该读取一个文本文件,在处理异常的同时将该数据插入到表中。这是我的函数的代码:
set serveroutput ON;
CREATE OR replace FUNCTION Order_func(ldir VARCHAR2,
lfile VARCHAR2)
RETURN BOOLEAN
AS
result BOOLEAN;
f utl_file.file_type;
s VARCHAR2(200);
v_row VARCHAR2(2000);
v1 NUMBER;
v2 NUMBER;
v3 NUMBER;
v4 DATE;
v5 DATE;
v6 NUMBER;
v7 NUMBER;
v8 NUMBER;
v9 NUMBER;
customer_error EXCEPTION;
employee_error EXCEPTION;
item_error EXCEPTION;
customerids NUMBER;
employeeids NUMBER;
inventoryids NUMBER;
BEGIN
SELECT cno
INTO customerids
FROM customers;
SELECT employeeno
INTO employeeids
FROM employees;
SELECT itemno
INTO inventoryids
FROM inventory;
f := utl_file.Fopen(ldir, lfile, 'R');
LOOP
utl_file.Get_line(f, v_row);
v1 := Substr(v_row, 1, 4);
v2 := Substr(v_row, 6, 9);
v3 := Substr(v_row, 11, 12);
v4 := Substr(v_row, 15, 23);
v5 := Substr(v_row, 27, 35);
v6 := Substr(v_row, 38, 41);
v7 := Substr(v_row, 43);
v8 := Substr(v_row, 45, 48);
v9 := Substr(v_row, 50, 51);
IF v2 <> customerids THEN --checking customer id
RAISE customer_error;
ELSIF v3 <> employeeids THEN --checking employee id
RAISE employee_error;
ELSIF v6 <> inventoryids THEN --checking item1 id
RAISE item_error;
ELSIF v8 <> inventoryids THEN --checking item2 id
RAISE item_error;
ELSE
INSERT INTO transactions
(tid, orderno, cno, employeeno, received,
shipped, itemno1, quantity1, itemno2, quantity2)
VALUES (sequence_tid.NEXTVAL, v1, v2, v3, v4, v5,
v6, v7, v8, v9);
END IF;
END LOOP;
result := TRUE;
RETURN result;
EXCEPTION
WHEN customer_error THEN
dbms_output.Put_line('Customer not found in parent Customer table'); WHEN
employee_error THEN
dbms_output.Put_line('Employee not found in Employee table');
WHEN item_error THEN
dbms_output.Put_line('Item not found in inventory table'); WHEN OTHERS THEN
dbms_output.Put_line('Error code:'
|| SQLCODE
|| '. Error Message: '
|| SQLERRM);
utl_file.Fclose(f);
result := FALSE;
RETURN result;
END order_func;
这就是我调用函数的方式(但我认为这是错误的):
DECLARE
results boolean;
BEGIN
results := order_func('forQues','items.txt');
DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is: ' || results);
END;
这是我遇到的错误:
DECLARE
results boolean;
BEGIN
results := order_func('forQues','items.txt');
DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is: ' || results);
END;
Error report -
ORA-06550: line 5, column 26:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
我试图删除 dbms_output 中的结果,这就是发生的事情:
DECLARE
results boolean;
BEGIN
results := order_func('forQues','items.txt');
DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is:');
END;
运行后我得到:
Error code:-1422. Error Message: ORA-01422: exact fetch returns more than requested number of rows
Result for ORDER_FUNC Function is:
PL/SQL procedure successfully completed.
请有人帮忙,如何解决这个问题?
ORA-01422: exact fetch returns more than requested number of rows
当您的查询返回多行但您有一个INTO
只期望返回 1 行的子句时,就会发生错误。
例如,您的三个查询:
select cno into customerids from customers;
select employeeno into employeeids from employees;
select itemno into inventoryids from inventory;
PLS-00306: wrong number or types of arguments in call to '||'
抛出错误是因为您试图将一个字符串与一个布尔值连接起来
DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is: ' || results);
这是不允许的。
ORA-01422
错误的可能修复:将您SELECT
的FOR
循环放入如下循环中:
FOR c IN (
SELECT
cno
INTO customerids
FROM
customers
) LOOP
--do stuff, access column value like c.cno
END LOOP;
PLS-00306
错误的可能修复:将串联更改为
DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is: ' || case when results then 'true' else 'false' end);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句