how to find out which row/s causes ora01422 in view and function

Quetsion:Is there a way to find out which row/s that causes the view/function to crash and how do I do it?


Background:

In a view VVGRUPP when I click DATA and then In the result displayed click ctrl+end, after about 9500 rows I get the following message:

ORA-01422: Exakt hämtning returnerar fler än angivet antal rader
ORA-06512: vid "IST.GETGRUPPLARARE", rad 6

I attach a detailed description.

ora1422.pdf
marcguAsked:
Who is Participating?
 
flow01Commented:
To find the problem - row
Get the original query of the view:
remove the problem-function from the query

In pl/sql

declare
  -- key_colums definition;
  key1 ..;
  key2 ..;
  ..
  -- function arguments
  arg1 ..;
  arg2 ..;
 rsl_function ..;
Begin
   for r1 in (select .. modified_query) loop
      -- save key values
      key1 := r1.key1;
      key2 := r1.key2;
      arg1 := r1.columnx;
      arg2 := r1.columny;
 
    rsl_function := function(arg1,arg2,...);
   end loop;
exception when others then
    -- show keyvalues on the failing record
    dbms_output.put_line(key1);
    dbms_output.put_line(key2);
    -- show arguments of failing function call
    dbms_output.put_line(arg1);
    dbms_output.put_line(arg2);
    raise;
end;


0
 
slightwv (䄆 Netminder) Commented:
Can't get a great look at that pdf since I'm on mobile but you will need to take the view text and look at the sub-selects and using the where in your select on the view to figure out which one returns more than one row.
0
 
Swadhin RaySenior Technical Engineer Commented:
Please check this as similar question was asked by me , hope this will also help you:

http://www.experts-exchange.com/Database/Oracle/Q_27245771.html
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gajmpCommented:
Check the query within the function. In some condition that query may be return more than one row. you didnt handle any exception there. that's why this error coming.
two solution
1. check the query and correct the query to return only one row
2. handle exception TOO_MANY_ROWS and return 0 or some other values.
0
 
Swadhin RaySenior Technical Engineer Commented:
To identify for error line , you can query like :

 SELECT d.NAME NAME, d.line ERROR_LINE_NO, d.text ERROR_TEXT, us.text ERROR_REASON
  FROM dba_errors d, user_source us
  WHERE d.NAME = us.NAME
  AND d.line = us.line
  AND D.NAME = <<you function name>>;

0
 
marcguAuthor Commented:
Thanks for all comments so far, I am busy evaluating them now.

Best regards
Marcus
0
 
marcguAuthor Commented:
Thanks a lot! Sorry for my late answer. I really appreciated all the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.