Ora 06512 ошибка oracle
I am receiving this error atleast twice a week in Production, however I simply re-start the process ( after 2 -3 hours) and the error goes away.
Can someone please help me as to what this error means?
ORA-06512: at "REPORTS.PA_1000_KOI_DM", line 699
ORA-06512: at "REPORTS.PA_1000_KOI_DM", line 39
ORA-06512: at line 4
Comments
I am not sure if you already did check this out or not but anyway,
[Oracle documentation says
ORA-06512: at str line num
Cause: This is usually the last of a message stack and indicates where a problem occurred in the PL/SQL code.
Action: Fix the problem causing the exception or write an exception handler for this condition. It may be necessary to contact the application or database administrator. ]
I would suggest you to debug the pl/sql function or procedure code with the name PA_1000_KOI_DM in the REPORTS schema at those specified lines (39 and 699)
I have tried debugging it, but It never fails on me in test, is it because I don't have big enough data.
Is this error something to do with Overflow?
What does ORA-20000 and ORA-06512 mean anyways.
ORA-20000 is a user-defined error number which has been raised at "REPORTS.PA_1000_KOI_DM", line 699
ORA-6512 is the error given for each line as the call-stack is unwound. Each line number here indicates where "REPORTS.PA_1000_KOI_DM" was called from and where that procedure was called from so on.
In your previous mail, check further down the error message and you will see.
pa_1000_koi_banner_rep - Retrieve_Registers - -2068 - ORA-02068: following seve
. which appears to be the following (perhaps you can prevent the error message from being truncated?).
ORA-02068 following severe error from stringstring
Cause: A severe error (disconnect, fatal Oracle error) was received from the indicated database link. See following error text.
Action: Contact the remote system administrator.
Thanx for your reply.
What you are saying is. ORA-06512 is not a real error, is just a notification?? Is that true?
From what I have been told here, ORA-02068 is due to the application NOT AVAILABLE. So that is not an issue, I think.
So what I can conclude is that because the application is down, I am receiving these errors in order:
ORA-20000 (RAISED_APPLICATION_ERROR), and then
ORA-02068, (stating the application is down) and then
ORA-06512 (call-stack been unwound).
True??
Well nearly. Actually the ORA-02068 comes first, this is the triggering error. Oracle may have included the error which caused the disconnection but because your error text is truncated we cannot see it. In any case this causes the current procedure to stop executing and control is passed to its exception section. The exception is caught by an EXCEPTION WHEN OTHERS at somewhere around "REPORTS.PA_1000_KOI_DM", line 699, at which point it is re-raised using RAISE_APPLICATION_ERROR with error code -20000 and a string containing what appears to be a call stack and the current SQLERRM (SQL error message, i.e. ORA-02068). This error is not caught so the call stack is unwound (cue ORA-06512 errors) and the error stack is returned to the client. Something like this. With the result that the triggering error is actually the second error in your list. Am I making any sense?
Oracle — это система управления базами данных, которая существует в течение сорока лет в различных формах. Первоначально он использовал нечто, называемое схемой SCOTT, названное в честь одного из первоначальных сотрудников Oracle. Вы даже впервые зашли в Oracle под именем пользователя «scott» и паролем «тигр», который назван в честь кота Скотта. Теперь есть несколько схем, используемых в зависимости от того, для чего вы используете Oracle.
Если вы хотите узнать больше об Oracle с нуля, эта страница очень полезна.
2 Answers 2
Before getting into your code: if you are planning to write that kind of logging in every procedure you write, maybe you should simply avoid doing it and keep at reach of your hand this trigger and enable it just when you need it: this trigger might be handy in a testing environment or if you are in deep trouble and you need to collect all possible error that happen in a given time:
this trigger will log ALL errors that will happen in your system. it is not a good idea to keep it enabled permanently: you can use it for doing some troubleshooting in emergency and you might want adjust its code to log only some kind of errors, but it is a starting point. if you do some research you will discover that you can even log the SQL text of the statement that caused the error. keep in mind that there are some errors that are not catched by this trigger (i am referring to NO_DATA_FOUND and TOO_MANY_ROWS errors): there is simply too much code that normally uses these exceptions during its normal lifetime, so the guys at oracle decided not to trap these errors.
Now let's get back to your code: your approach, as other have pointed out, is not neutral to the normal execution of the program:
you are logging the error, right, but you are hiding the error to the program calling your procedure. It is not a good idea: the calling program surely would like to know that something wrong has happened and it would like to issue a "rollback" in order to cancel all previous work, instead of continuing like nothing wrong did happen.
Not only you are hiding the error, but you are also issuing a commit whenever an error happens: this is most likely the opposite of what your calling program would do in case of an error: surely the caller would issue a rollback. Instead you are making permanent all the partial work done until the error happened.
It generally is a bad practice to commit or rollback inside a procedure, unless you are more than sure that such procedure will never be called as part of a bigger transaction (for example: it is ok to commit if your procedure is the main body of a database job)
(As you can see, I used an autonomous transaction also in the above trigger)
your code would be more "kind", to the calling program, if written this way:
Learn the cause and how to resolve the ORA-06512 error message in Oracle.
Cause
This error is caused by the stack being unwound by unhandled exceptions in your PLSQL code.
- Fix the condition that is causing the unhandled error.
- Write an exception handler for this unhandled error.
- Contact your DBA for help.
The ORA-06512 error message indicates the line number of the unhandled error in the PLSQL code. This is quite useful when troubleshooting.
Исправление ошибок ORA-06512
В Oracle ошибка ORA-06512 — это общая ошибка исключения, которая указывает, где что-то идет не так. Это одна из наименее специфических ошибок, производимых Oracle, поскольку она говорит вам только о том, что есть проблема, но не то, что идет не так.
«ORA-01422: точная выборка возвращает больше запрошенного количества строк
ORA-06512: в «DATABASE_NAME», строка 66
ORA-06512: в строке 1 ″
Первая строка сообщает вам, какой тип ошибки происходит, в этом случае запрос возвращает больше данных, чем ожидал запрос, поэтому он не знает, как ее обработать. Код «ORA-01422» — это фактический код ошибки, на который вам нужно обратить внимание. ORA-06512 — это просто общий код ошибки.
Третья строка в синтаксисе ошибки сообщает вам, откуда поступил вызов. Проверьте первую строку, и вы увидите звонок DATABASE_NAME.
Есть две вещи, которые вы можете сделать. Если вы ожидаете, что запрос вернет более одной строки, вы можете изменить его, чтобы он не удивился. Если вы ожидаете, что запрос вернет только одну строку, вы также можете изменить его.
Ожидается более одной строки:
для X в (выберите * из т, где …)
— обработать X запись здесь
Это должно устранить ошибку в запросах к базе данных, когда будет возвращено более одной строки.
Если вы ожидаете, что будет возвращена только одна строка, вы можете попробовать:
когда NO_DATA_FOUND тогда
код обработки ошибок, когда запись не найдена
когда TOO_MANY_ROWS тогда
код обработки ошибок при обнаружении слишком большого количества записей
Этот второй метод должен доставлять только одну строку, не выбрасывая ORA-01422: точная выборка возвращает ошибку, превышающую запрошенное количество строк, и, следовательно, исходную ошибку ORA-06512.
Вы также можете настроить запрос так, чтобы он возвращал только первую строку многострочного ответа. Это может сработать, если у вас нет полного контроля над базой данных или вы не хотите слишком много возиться с вещами, но вам все еще нужен ответ.
c1 курсор для выбора * из т, где …
if (c1% notfound) тогда
обработка ошибок для записи не найдена
(Если вы знаете SQL, вы можете быть немного смущены этими командными строками … Oracle не использует Transact-SQL, а скорее является собственным процедурным языковым расширением SQL, PL / SQL. Хотя PL / SQL похож на Transact-SQL, много умных вещей и сам по себе очень мощный инструмент. Этот FAQ по PL / SQL может оказаться полезным при попытке узнать об Oracle.)
Есть ли у вас какие-либо советы или рекомендации Oracle? Дайте нам знать о них в комментариях!
The structure base for the table where the insert is made:
What is the full error stack? ORA-06512 is just the line number (it would be helpful to include that), the actual error is in the error stack.
You can and should edit your question to include additional information instead of posting it as comments, where it is easy to miss and hard to read.
Description
When you encounter an ORA-06512 error, the following error message will appear:
Resolution
Let's look at an example of how to resolve an ORA-06512 error by fixing the error condition.
For example, if you created a procedure called TestProc as follows:
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you've tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by redefining the v_number variable as number(3).
And now when we execute our TestProc procedure, the ORA-06512 error has been resolved.
Let's look at an example of how to resolve an ORA-06512 error by writing an exception handler.
For example, if you created a procedure called TestProc as follows:
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06512 error as follows:
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you've tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this by writing an exception handler to set the v_number variable to 99 (so that it is only 2 digits) when this error occurs.
And now when we execute our TestProc procedure, the ORA-06512 error has been resolved.
3 Answers 3
ORA-06512 is part of the error stack. It gives us the line number where the exception occurred, but not the cause of the exception. That is usually indicated in the rest of the stack (which you have still not posted).
In a comment you said
"still, the error comes when pNum is not between 12 and 14; when pNum is between 12 and 14 it does not fail"
Well, your code does this:
That is, it raises an exception when pNum is not between 12 and 14. So does the rest of the error stack include this line?
ORA-06510: PL/SQL: unhandled user-defined exception
If so, all you need to do is add an exception block to handle the error. Perhaps:
The documentation covers handling PL/SQL exceptions in depth.
The variable pCv is of type VARCHAR2 so when you concat the insert you aren't putting it inside single quotes:
Additionally the error ORA-06512 raise when you are trying to insert a value too large in a column. Check the definiton of the table M_pNum_GR and the parameters that you are sending. Just for clarify if you try to insert the value 100 on a NUMERIC(2) field the error will raise.
Can´t do this since it goes on pairs; VALUES('''||pCv||''', ' still, the error comes when pNum is not between 12 and 14; when pNum is between 12 and 14 it does not fail
Could you attach the DDL of one of your M_pNum_GR tables? And are you sure that all have the same structure?
CREATE TABLE "DB"."M12GR" ("IDM12GR" NUMBER(10,0) NOT NULL ENABLE, "CV" VARCHAR(5) NOT NULL ENABLE, "SUP" FLOAT(126) NOT NULL ENABLE, "IDM12" NUMBER(10,0) NOT NULL ENABLE, CONSTRAINT "PRIMARY_30" PRIMARY KEY ("IDM12GR") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DB" ENABLE, CONSTRAINT "M12SUELORM12" FOREIGN KEY ("IDM12") REFERENCES "DB"."M12" ("IDM12") ENABLE)
I created a mock table an purposely induced the ORA-06512 error by inserting a character string in a timestamp field using a procedure. here is the procedure which inserts dummy data into my mock table with the purpose of inducing an error and logging it into my error_log table.
when I attempt to run/compile it I get the following error.
I am a complete beginner at pl/sql so any help is appreciated.
And you would never, ever, ever in real life have a when others clause that did not include a raise , right? (or that raised a new custom exception)?
In the Real World (ain't never been there - they tell me it's nice :-) you might want do the logging in a procedure with PRAGMA AUTONOMOUS TRANSACTION so that you could commit your log messages without affecting the main transaction. Oh - and always, Always, ALWAYS use a field list with an INSERT statement. Thou Shalt Not Count Upon Thy Table Layout Remaining Stable Unto Eternity - Don't Ask Me How I Know. Best of luck.
another issue: . (current_timestamp,sqlcode,'sqlerrm',
Читайте также: