Ora 01422 описание ошибки

Вопрос:

declare
retVal INTEGER;
rptID INTEGER;
catID INTEGER;
wsID INTEGER;
paramID INTEGER;
dtID INTEGER;
begin

select PK into catID from RPT_CATEGORY where KEYVALUE = 'ProductivityReportsCategory';
select PK into rptID from RPT_REPORT where KEYVALUE = 'ProductivitySummaryReport2';
select PK into wsID from RPT_WEBSVC where KEYVALUE = 'NotApplicable' and category_fk = catID;

  select PK into wsID from RPT_WEBSVC where KEYVALUE = 'GetMachineNameList' and category_fk = catID;
paramID := RPT_CONFIGURATION.ADD_PARAMETER( rptID, wsID, dtID, 'Machine', 'parameters.GetProductivityDataSet3.inserterid', 4, NULL, NULL, NULL, 0, 0, 0, 'Y', 'Y', 'N', 'N', 'Y' );

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 30

Лучший ответ:

select PK into wsID from RPT_WEBSVC
where KEYVALUE = 'GetMachineNameList'
and category_fk = catID;

alter table RPT_WEBSVC add constraint RPT_WEBSVC_UK
    unique (KEYVALUE, CATEGORY_FK);

Ответ №1

Ответ №2

Я бы рассмотрел номер 1 как причину проблемы здесь. Не видя всех утверждений, трудно сказать, какой из этих запросов может возвращать несколько строк, но это хорошее место для запуска, поскольку база данных схемы могут меняться…

In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain another common error, which has been searched on google approximately 10 k times per month. ORA-01422 is another simple error, which is commonly come in database when select into statement when it retrieves more than one row. All oracle errors are categorized in to two types one is network and memory issues and other are syntax errors come due to bad syntax. ORA-01422 is user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.

Cause and resolution of this error:

The ORA-01422 error is most common error, which will come because of the multiple reasons .The main reason of this error, is ‘SELECT INTO’ statement. Oracle has one important inbuilt error named ‘TOO_MANY_ROWS’ error.

1.More than requested rows in “Select into”:

If ‘Select Into’ statement returns more than one rows in variable then this error will come. The error says fetch returns more than requested number of rows means ‘Exact Fetch’ will return ‘More than one row’. The basic problem will come from ‘Select Into’ statement from oracle, which will return more than one rows. The select statement will fetch the record from multiple databases and multiple tables and it will store into variable. The Oracle engine will fetch more than one record for single specified row. The ORA-01422 error will trigger when PLSQL engine returns multiple rows of data. The select into statement has default setting, which is designed to retrieve one row, but when it retrieves more than one rows, the ORA-01422 error will trigger.

Consider following real life example:

If table named ‘test_error’ has more than one records and user tries to fetch all records in to one variable this error will come.

Procedure :

DECLARE

v_test VARCHAR2(30);

BEGIN

SELECT roll_no INTO v_test FROM test_error;    —-Error statement

end;

Output:

Error report:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at line 4

01422. 00000 –  “exact fetch returns more than requested number of rows”

*Cause:    The number specified in exact fetch is less than the rows returned.

*Action:   Rewrite the query or change number of rows requested

Resolution of this error:

Handle the exception:

Just make the small change in the specified code and write the exception block for the same.

Procedure :

DECLARE

V_SRNUM VARCHAR2(20);

DECLARE

v_test VARCHAR2(30);

BEGIN

SELECT roll_no INTO v_test FROM test_error;

exception WHEN too_many_rows THEN

dbms_output.put_line(‘Errors fetching are more than one’);

end;

The above procedure will handle the error and anonyms block will complete successfully.So if this kind of error will occure handle user defined exception named ‘TOO_MANY_ROWS’.

2. Add cursor with loop:

There are many primitive adjustments to resolve this error. The approach needs to be chosen by the user dependent on the database tables and scenarios. The error will occur because of multiple rows are returning so user will change the code by adding the cursor.

Therefore, the above procedure will be:

Declare

v_test VARCHAR2(30);

begin

for c in (SELECT roll_no INTO v_test FROM test_error)

loop

v_test := c.roll_no;

end loop;

end;

3. Recommend to use aggregate function:

This error will come because of multiple rows selection. Therefore, as per requirement if user uses the aggregate function like sum, count then it will fetch only one row.

Therefore, the above procedure will be:

DECLARE

v_test VARCHAR2(30);

BEGIN

SELECT count(roll_no) INTO v_test FROM test_error;    —-Error statement

end;

4. Use of bulk collect:

This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.

So there are different ways to deal with this error, my best recommendation is to use the cursor for fetching more than one rows and processing it.

A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you’ll get a no_data_found exception. If it returns more than 1 row, you’ll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, you do not want a SELECT INTO statement here.

Most likely, you want to use a cursor to iterate over (potentially) multiple rows of data (I’m also assuming that you intended to do a proper join between the two tables rather than doing a Cartesian product so I’m assuming that there is a departmentID column in both tables)

BEGIN
  FOR rec IN (SELECT EMPLOYEE.EMPID, 
                     EMPLOYEE.ENAME, 
                     EMPLOYEE.DESIGNATION, 
                     EMPLOYEE.SALARY,  
                     DEPARTMENT.DEPT_NAME 
                FROM EMPLOYEE, 
                     DEPARTMENT 
               WHERE employee.departmentID = department.departmentID
                 AND EMPLOYEE.SALARY > 3000)
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec.EMPID);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec.ENAME);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec.DESIGNATION);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec.SALARY);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec.DEPT_NAME);
  END LOOP;
END;

I’m assuming that you are just learning PL/SQL as well. In real code, you’d never use dbms_output like this and would not depend on anyone seeing data that you write to the dbms_output buffer.

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

Встроенные исключительные ситуации

В этом учебном материале вы узнаете, как использовать встроенные исключительные ситуации в Oracle/PLSQL c синтаксисом и примерами.

Описание

Встроенные исключительные ситуации это исключительные ситуации, которые имеют определенные имена в PL/SQL. Они определены в стандартном пакете в PL/SQL и не могут быть определены программистом.
Oracle имеет стандартный набор встроенных исключительных ситуаций:

Синтаксис

Рассмотри синтаксис встроенных исключительных ситуаций в процедуре и функции.

Синтаксис для процедуры

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

Синтаксис для функции

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

Пример использования исключительных ситуаций в процедуре.

Источник

PL/SQL Exception

Summary: in this tutorial, you will learn about PL/SQL exception and how to write exception handler to handle exceptions.

Introduction to PL/SQL Exceptions

PL/SQL treats all errors that occur in an anonymous block, procedure, or function as exceptions. The exceptions can have different causes such as coding mistakes, bugs, even hardware failures.

It is not possible to anticipate all potential exceptions, however, you can write code to handle exceptions to enable the program to continue running as normal.

The code that you write to handle exceptions is called an exception handler.

A PL/SQL block can have an exception-handling section, which can have one or more exception handlers.

Here is the basic syntax of the exception-handling section:

In this syntax, e1 , e2 are exceptions.

When an exception occurs in the executable section, the execution of the current block stops and control transfers to the exception-handling section.

If the exception e1 occurred, the exception_handler1 runs. If the exception e2 occurred, the exception_handler2 executes. In case any other exception raises, then the other_exception_handler runs.

After an exception handler executes, control transfers to the next statement of the enclosing block. If there is no enclosing block, then the control returns to the invoker if the exception handler is in a subprogram or host environment (SQL Developer or SQL*Plus) if the exception handler is in an anonymous block.

If an exception occurs but there is no exception handler, then the exception propagates, which we will discuss in the unhandled exception propagation tutorial.

PL/SQL exception examples

Let’s take some examples of handling exceptions.

PL/SQL NO_DATA_FOUND exception example

The following block accepts a customer id as an input and returns the customer name :

If you execute the block and enter the customer id as zero, Oracle will issue the following error:

The ORA-01403 is a predefined exception.

Note that the following line does not execute at all because control transferred to the exception handling section.

To issue a more meaningful message, you can add an exception-handling section as follows:

If you execute this code block and enter the customer id 0, you will get the following message:

PL/SQL TOO_MANY_ROWS exception example

First, modify the code block in the above example as follows and execute it:

Second, enter the customer id 10 and you’ll get the following error:

This is another exception called TOO_MANY_ROWS which was not handled by the code.

Third, add the exception handler for the TOO_MANY_ROWS exception:

Finally, if you execute the code, enter 10 as the customer id. You will see that the code will not raise any exception and issue the following message:

PL/SQL exception categories

PL/SQL has three exception categories:

• Internally defined exceptions are errors which arise from the Oracle Database environment. The runtime system raises the internally defined exceptions automatically. ORA-27102 (out of memory) is one example of Internally defined exceptions. Note that Internally defined exceptions do not have names, but an error code.
• Predefined exceptions are errors which occur during the execution of the program. The predefined exceptions are internally defined exceptions that PL/SQL has given names e.g., NO_DATA_FOUND , TOO_MANY_ROWS .
• User-defined exceptions are custom exception defined by users like you. User-defined exceptions must be raised explicitly.

The following table illustrates the differences between exception categories.

In this tutorial, you have learned about the PL/SQL exceptions and how to write exception handlers to handle the possible exceptions in a block.

Источник

ORA-01422: fetch returns more than requested number of rows | ORA-01422

In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain another common error, which has been searched on google approximately 10 k times per month. ORA-01422 is another simple error, which is commonly come in database when select into statement when it retrieves more than one row. All oracle errors are categorized in to two types one is network and memory issues and other are syntax errors come due to bad syntax. ORA-01422 is user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.

The ORA-01422 error is most common error, which will come because of the multiple reasons .The main reason of this error, is ‘SELECT INTO’ statement. Oracle has one important inbuilt error named ‘TOO_MANY_ROWS’ error.

1.More than requested rows in “Select into”:

If ‘Select Into’ statement returns more than one rows in variable then this error will come. The error says fetch returns more than requested number of rows means ‘Exact Fetch’ will return ‘More than one row’. The basic problem will come from ‘Select Into’ statement from oracle, which will return more than one rows. The select statement will fetch the record from multiple databases and multiple tables and it will store into variable. The Oracle engine will fetch more than one record for single specified row. The ORA-01422 error will trigger when PLSQL engine returns multiple rows of data. The select into statement has default setting, which is designed to retrieve one row, but when it retrieves more than one rows, the ORA-01422 error will trigger.

Consider following real life example:

If table named ‘test_error’ has more than one records and user tries to fetch all records in to one variable this error will come.

Procedure :

SELECT roll_no INTO v_test FROM test_error; —-Error statement

Output:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at line 4

01422. 00000 – “exact fetch returns more than requested number of rows”

*Cause: The number specified in exact fetch is less than the rows returned.

*Action: Rewrite the query or change number of rows requested

Resolution of this error:

Handle the exception:

Just make the small change in the specified code and write the exception block for the same.

Procedure :

SELECT roll_no INTO v_test FROM test_error;

exception WHEN too_many_rows THEN

dbms_output.put_line(‘Errors fetching are more than one’);

The above procedure will handle the error and anonyms block will complete successfully.So if this kind of error will occure handle user defined exception named ‘TOO_MANY_ROWS’.

2. Add cursor with loop:

There are many primitive adjustments to resolve this error. The approach needs to be chosen by the user dependent on the database tables and scenarios. The error will occur because of multiple rows are returning so user will change the code by adding the cursor.

Therefore, the above procedure will be:

for c in (SELECT roll_no INTO v_test FROM test_error)

3. Recommend to use aggregate function:

This error will come because of multiple rows selection. Therefore, as per requirement if user uses the aggregate function like sum, count then it will fetch only one row.

Therefore, the above procedure will be:

SELECT count(roll_no) INTO v_test FROM test_error; —-Error statement

4. Use of bulk collect:

This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.

So there are different ways to deal with this error, my best recommendation is to use the cursor for fetching more than one rows and processing it.

Источник

Exceptions

Whenever you create a program block (no matter it is a trigger, procedure, package, …) you have to face errors, in Oracle PL/SQL they are called “exceptions”. We distinguish two types of exceptions: system (or internal) and user-defined exceptions.

What is an exception

As mentioned above, exceptions are blocks inside PL/SQL codes that are triggered at certain occasions by “exception handlers”. In exception blocks, you can define how that particular “event” will be treated. For instance, you can stop the whole processing, log the error and continue, skip to another part of your code and so on.

How does it work

When your code ends up with an error there are two possible scenarios. You either have an exception handler implemented or you don’t 🙂 If you don’t, the program will stop and you will have no idea what happened – whether it was successful or not. Most internal/system exceptions are logical problems in your process flow (such as dividing by zero, no data found, invalid number, too many rows, …) but you can also implement some business rules as cross-checks in your code logic (such as input parameter checks, no available funds, not authorized, …).

The biggest advantage of using exceptions is the scalability, readability and convenient way of error checking 🙂 Instead of checking every single line in your program for errors (which would be the case without exceptions) you can easily handle them in the exception block (see below).

How to use it

This is the part when I will show you all types of exceptions (internal as well as user-defined) and how you can use them 🙂 Let me start with internal exceptions.

Internal Exceptions

These exceptions are raised when an Oracle rule is violated or any kind of system limit is exceeded. There is a certain list of exceptions with associated error numbers. Even though you know the error number, you have to handle exceptions by their names. See the list of the most common exceptions below (for the full list, please visit Oracle Doc pages).

For the rest that is not defined by Oracle, you can use either user-defined exceptions (see below) or simple use OTHERS. For the latter choice, there are very handy “variables” you can use SQLERRM and SQLCODE to display details about the error.

Examples:

Of course, you can define multiple exceptions handler in the EXCEPTION block (see below).

User-defined Exceptions

As I mentioned above, you can implement your own business logic and rules in error and exception handling to divert the process flow according to your needs. You can either define a general exception name or associated with EXCEPTION_INIT number. Please note, that if you use multiple-level blocks (block inside block) you have to stick to exceptions in each level. Whenever an exception is raised inside the sub-block you have to catch it there because it won’t be triggered in the “outer” block. See the examples below.

General exception

EXCEPTION_INIT

Multiblock exceptions

Multiblock exceptions – passing the exception to the outer block

There are many ways how to use exceptions and you can get really creative with them. For all the details please visit Oracle Docs 🙂

I want to mention one more thing and it is GOTO directive. With this one, you can actually jump between the parts of your process from A to B to D back to C etc based on your exceptions or logic implemented. However, I strongly discourage you from using this. It is not a good programmer habit to use GOTO directives and you can end up in an infinite loop 🙂 Always try to rewrite your code to avoid using GOTO.

Lastly, you can do execute as many commands as you want or implement as robust logic into the EXCEPTION part as possible 🙂 Let me show you the same examples at the end of this article.

Источник