Avatar of Swadhin Ray
Swadhin Ray
Flag for United States of America asked on

Error message information on oracle DB

I found few invalid package body in my schema.
When check in DBA_ERROR table I see three error lines with line no and error message .

How to find what is the line no referring to.

As when I use SQl PLUS like if the line no  8823 * then I can find what is the actual error  for the table or reference causing the errors

Oracle Database

Avatar of undefined
Last Comment
Swadhin Ray

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
OP_Zaharin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Swadhin Ray

ASKER
is there any other way where I can debug for my invalid objects for what is the reason they are invalid ?
Like when i do alter to recompile for each error details and the reason for errors

OP_Zaharin

- you can use GUI tool to write and debug you procedure/package. your original question is on finding the line in your procedure, this is a new question and should be ask separately.
Swadhin Ray

ASKER

What I was expecting is to get all the details for the line level error details along with the reason for invalid objects.

For example :
One package is compiled and got 40 errors on BODY.

So there should be something which will give me all the list of the 40 error details with line level information but if the errors are repeated for the same issue should not come in the report or by some query .

Hope this time i am clear.  
Your help has saved me hundreds of hours of internet surfing.
fblack61
OP_Zaharin

- if you compiling the package in sqlplus, you can run SHOW ERROR to display all the errors with the error description such as follows:


SQL> show error;
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/81     PLS-00103: Encountered the symbol "." when expecting one of the
         following:
         begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe
OP_Zaharin

- or you can query to USER_ERRORS tables, specify the procedure/package name and do a group by the columns if you want to avoid the repetition. if this is what you mean.

 error
Swadhin Ray

ASKER
so if i do a join on dba_errors and user_source then I can get the details for the line but only problem is the same error duplicates
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
OP_Zaharin

- as i mention, you can use GROUP BY in your syntax but again, you need to look into the error data if its really a duplicate error or a same error message for a different line of your code.
Swadhin Ray

ASKER
Ops yes true it same error message but from different line .
SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Swadhin Ray

ASKER
Thanks for all your respond..
 But think if a package is having more errors and the package is of more than 10k lines then its really bit difficult by running select for each line errors.

Is there any other way ?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
slightwv (䄆 Netminder)

I would probably spool or capture all the errors and use an editor with line numbers to modify the code.

A lot of the time errors cascade.  If fix one above, some of the lower ones go away.
Swadhin Ray

ASKER
thanks