Shulou(Shulou.com)06/01 Report--

1 problem

A custom Oracle function is defined. Compilation is normal; using PL/SQL Developer's Test window mode, the test passes. However, the direct call to Java fails; using PL/SQL Developer's SQL window mode, execution fails.

There is no valid error message.

2 Analysis

There must be something wrong with the function itself, and we need to use effective tools to locate the problem.

In the Oracle function, add exception handling.

Exception handling (EXCEPTION) can be used to handle unexpected events during normal execution. If the PL/SQL block generates an exception but does not specify how to handle it, the program terminates automatically.

2.1 Oracle exception error type

There are 24 predefined exception error types. This exception does not need to be defined in the program, ORACLE will automatically throw. Non-predefined other ORACLE standard errors. This exception needs to be defined in the program and then thrown by ORACLE. An abnormal situation defined by a user-defined programmer. This exception needs to be defined in the program and then explicitly thrown.

2.2 predefined exceptions

Error code error name indicates that ORA-0001Dup_val_on_index violates the uniqueness limit. A timeout occurred while ORA-0051Timeout-on-resource was waiting for a resource. The ORA-0061Transaction-backed-out deadlock occurred and the transaction was undone. ORA-1001Invalid-CURSOR attempted to use an invalid cursor. ORA-1012Not-logged-on is not connected to the ORACLE. Invalid username / password for ORA-1017Login-denied. ORA-1403No_data_foundSELECT INTO could not find the data. ORA-1422Too_many_rowsSELECT INTO returns multiple rows. ORA-1476Zero-divide attempts to divide by zero. Failed to convert ORA-1722Invalid-NUMBER to number. ORA-6500Storage-error does not have enough memory, causing an internal error. ORA-6501Program-error internal error. ORA-6502Value-error conversion or truncation error. There is an incompatible line type between ORA-6504Rowtype-mismatch host cursor variable and PL/SQL variable. ORA-6511CURSOR-already-OPEN attempted to open a cursor that is already open. ORA-6530Access-INTO-null attempts to assign a value to the property of the null object. ORA-6531Collection-is-null attempts to apply a collection method other than Exists to an null pl/sql table or varray. ORA-6532Subscript-outside-limit references to nested or varray indexes are beyond the scope of the declaration. The reference of ORA-6533Subscript-beyond-count to a nested or varray index is greater than the number of elements in the collection.

2.3 exception handling statement

EXCEPTION WHEN first_exception THEN WHEN second_exception THEN WHEN OTHERS THEN END

2.4 DBMS_OUTPUT

DBMS_OUTPUT can be used to debug PL/SQL programs. It has these common methods:

The method indicates that put writes the content to memory until put_line and then outputs it at one time. Put_line output. New_line changes the line.

2.5 error handling object

Sqlcode: error code. Sqlerrm: error message.

Combined with the above, we can write such an exception output statement in the function:

Exception when others then DBMS_OUTPUT.put_line ('sqls:' | | sqls); DBMS_OUTPUT.put_line ('sqlcode:' | | sqlcode); DBMS_OUTPUT.put_line ('sqlerrm:' | | sqlerrm)

3 solution

Write the test function command and in the command window in PL/SQL Developer, execute the following command:

Set serveroutput on;declare r varchar2 (32); beginr:= function name (parameter 1, parameter 2...); end

Note: if you want to see the DBMS_OUTPUT output in PL/SQL, you must set the serveroutput parameter to on.

If you find the error message, you can find out the reason. O (∩ _ ∩) O ~

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.