Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-19
7
Medium Priority
?
781 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:marcgu
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36996786
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
 
LVL 16

Assisted Solution

by:Swadhin Ray
Swadhin Ray earned 332 total points
ID: 36997126
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
 
LVL 3

Assisted Solution

by:gajmp
gajmp earned 332 total points
ID: 36997234
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 21

Accepted Solution

by:
flow01 earned 336 total points
ID: 36998284
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
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 36998595
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
 

Author Comment

by:marcgu
ID: 37003067
Thanks for all comments so far, I am busy evaluating them now.

Best regards
Marcus
0
 

Author Closing Comment

by:marcgu
ID: 37188466
Thanks a lot! Sorry for my late answer. I really appreciated all the help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question