We are supposed to read a text file, insert that data into a table while taking care of exceptions. This the code for my function: This is how i've called the function (but i think it's wrong): And this is the error I've got: I tried to remove the results in the dbms_output and this is what happened: After running

Wrong number or types of arguments in call to ‘||’

We are supposed to read a text file, insert that data into a table while taking care of exceptions. This the code for my function:

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;

This is how i’ve called the function (but i think it’s wrong):

DECLARE
    results boolean;
BEGIN
    results := order_func('forQues','items.txt');
    DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is: ' || results);
END;

And this is the error I’ve got:

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:

I tried to remove the results in the dbms_output and this is what happened:

DECLARE
    results boolean;
BEGIN
    results := order_func('forQues','items.txt');
    DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is:');
END;

After running this i got:

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.

Please somebody help, how to fix this?

Answer

Error ORA-01422: exact fetch returns more than requested number of rows happens when your query returns multiple rows but you have an INTO clause which only expects 1 row to be returned.

For example, your three queries:

select cno into customerids from customers;
select employeeno into employeeids from employees;
select itemno into inventoryids from inventory;

Error PLS-00306: wrong number or types of arguments in call to '||' is thrown because you are trying to concatenate a string with a boolean at

DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is: ' || results); which is not allowed.

Possible fixes for ORA-01422 error: Put your SELECT in a FOR loop like:

FOR c IN (
    SELECT
        cno
    INTO customerids
    FROM
        customers
) LOOP
    --do stuff, access column value like c.cno
END LOOP;

Possible fixes for PLS-00306 error: Change the concatenation to

DBMS_OUTPUT.PUT_LINE('Result for ORDER_FUNC Function is: ' || case when results then 'true' else 'false' end);

Advertisement

Answer