Невозможно найти вызываемый блок программы oracle
This chapter explains how to handle PL/SQL compile-time warnings and PL/SQL runtime errors. The latter are called exceptions .
The language of warning and error messages depends on the NLS_LANGUAGE parameter. For information about this parameter, see Oracle Database Globalization Support Guide .
If you have problems creating or running PL/SQL code, check the Oracle Database trace files. The USER_DUMP_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER USER_DUMP_DEST . For more information about trace files, see Oracle Database Performance Tuning Guide .
Compile-Time Warnings
While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated PL/SQL feature.
To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS (described in Oracle Database Reference ) or, in the SQL*Plus environment, use the command SHOW ERRORS .
The message code of a PL/SQL warning has the form PLW- nnnnn . For the message codes of all PL/SQL warnings, see Oracle Database Error Messages .
Table 11-1 summarizes the categories of warnings.
Table 11-1 Compile-Time Warning Categories
Condition might cause unexpected action or wrong results.
Aliasing problems with parameters
Condition might cause performance problems.
Passing a VARCHAR2 value to a NUMBER column in an INSERT statement
Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable.
Code that can never run
By setting the compilation parameter PLSQL_WARNINGS , you can:
Enable and disable all warnings, one or more categories of warnings, or specific warnings
Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit)
You can set the value of PLSQL_WARNINGS for:
Your Oracle database instance
Use the ALTER SYSTEM statement, described in Oracle Database SQL Language Reference .
Use the ALTER SESSION statement, described in Oracle Database SQL Language Reference .
A stored PL/SQL unit
Use an ALTER statement from "ALTER Statements" with its compiler_parameters_clause . For more information about PL/SQL units and compiler parameters, see "PL/SQL Units and Compilation Parameters".
In any of the preceding ALTER statements, you set the value of PLSQL_WARNINGS with this syntax:
For the syntax of value_clause , see Oracle Database Reference .
Example 11-1 shows several ALTER statements that set the value of PLSQL_WARNINGS .
Example 11-1 Setting Value of PLSQL_WARNINGS Compilation Parameter
For the session, enable all warnings—highly recommended during development:
For the session, enable PERFORMANCE warnings:
For the procedure loc_var , enable PERFORMANCE warnings, and reuse settings:
For the session, enable SEVERE warnings, disable PERFORMANCE warnings, and treat PLW-06002 warnings as errors:
For the session, disable all warnings:
To display the current value of PLSQL_WARNINGS , query the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS , described in Oracle Database Reference .
DBMS_WARNING Package
If you are writing PL/SQL units in a development environment that compiles them (such as SQL*Plus), you can display and set the value of PLSQL_WARNINGS by invoking subprograms in the DBMS_WARNING package.
Example 11-2 uses an ALTER SESSION statement to disable all warning messages for the session and then compiles a procedure that has unreachable code. The procedure compiles without warnings. Next, the example enables all warnings for the session by invoking DBMS_WARNING.set_warning_setting_string and displays the value of PLSQL_WARNINGS by invoking DBMS_WARNING.get_warning_setting_string . Finally, the example recompiles the procedure, and the compiler generates a warning about the unreachable code.
Unreachable code could represent a mistake or be intentionally hidden by a debug flag.
Example 11-2 Displaying and Setting PLSQL_WARNINGS with DBMS_WARNING Subprograms
Disable all warning messages for this session:
With warnings disabled, this procedure compiles with no warnings:
Enable all warning messages for this session:
Check warning setting:
DBMS_WARNING subprograms are useful when you are compiling a complex application composed of several nested SQL*Plus scripts, where different subprograms need different PLSQL_WARNINGS settings. With DBMS_WARNING subprograms, you can save the current PLSQL_WARNINGS setting, change the setting to compile a particular set of subprograms, and then restore the setting to its original value.
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_WARNING package
Overview of Exception Handling
Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. You cannot anticipate all possible exceptions, but you can write exception handlers that let your program to continue to operate in their presence.
Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers. For example, an exception-handling part could have this syntax:
In the preceding syntax example, ex_name_ n is the name of an exception and statements_ n is one or more statements. (For complete syntax and semantics, see "Exception Handler".)
When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part. If ex_name_1 was raised, then statements_1 run. If either ex_name_2 or ex_name_3 was raised, then statements_2 run. If any other exception was raised, then statements_3 run.
After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:
If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.
If the exception handler is in an anonymous block, then control transfers to the host environment (for example, SQL*Plus)
If an exception is raised in a block that has no exception handler for it, then the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a block has a handler for it or there is no enclosing block (for more information, see "Exception Propagation"). If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome (for more information, see "Unhandled Exceptions").
Exception Categories
The runtime system raises internally defined exceptions implicitly (automatically). Examples of internally defined exceptions are ORA-00060 (deadlock detected while waiting for resource) and ORA-27102 (out of memory).
An internally defined exception always has an error code, but does not have a name unless PL/SQL gives it one or you give it one.
A predefined exception is an internally defined exception that PL/SQL has given a name. For example, ORA-06500 (PL/SQL: storage error) has the predefined name STORAGE_ERROR .
You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package. For example, you might declare an exception named insufficient_funds to flag overdrawn bank accounts.
You must raise user-defined exceptions explicitly.
Table 11-2 summarizes the exception categories.
Table 11-2 Exception Categories
Only if you assign one
Only if you assign one
For a named exception, you can write a specific exception handler, instead of handling it with an OTHERS exception handler. A specific exception handler is more efficient than an OTHERS exception handler, because the latter must invoke a function to determine which exception it is handling. For details, see "Error Code and Error Message Retrieval".
Advantages of Exception Handlers
Using exception handlers for error-handling makes programs easier to write and understand, and reduces the likelihood of unhandled exceptions.
Without exception handlers, you must check for every possible error, everywhere that it might occur, and then handle it. It is easy to overlook a possible error or a place where it might occur, especially if the error is not immediately detectable (for example, bad data might be undetectable until you use it in a calculation). Error-handling code is scattered throughout the program.
With exception handlers, you need not know every possible error or everywhere that it might occur. You need only include an exception-handling part in each block where errors might occur. In the exception-handling part, you can include exception handlers for both specific and unknown errors. If an error occurs anywhere in the block (including inside a sub-block), then an exception handler handles it. Error-handling code is isolated in the exception-handling parts of the blocks.
In Example 11-3, a procedure uses a single exception handler to handle the predefined exception NO_DATA_FOUND , which can occur in either of two SELECT INTO statements.
Example 11-3 Single Exception Handler for Multiple Exceptions
Invoke procedure (there is a DEPARTMENTS table, but it does not have a LAST_NAME column):
Invoke procedure (there is no EMP table):
If multiple statements use the same exception handler, and you want to know which statement failed, you can use locator variables, as in Example 11-4.
Example 11-4 Locator Variables for Statements that Share Exception Handler
You determine the precision of your error-handling code. You can have a single exception handler for all division-by-zero errors, bad array indexes, and so on. You can also check for errors in a single statement by putting that statement inside a block with its own exception handler.
Guidelines for Avoiding and Handling Exceptions
To make your programs as reliable and safe as possible:
Use both error-checking code and exception handlers.
Use error-checking code wherever bad input data can cause an error. Examples of bad input data are incorrect or null actual parameters and queries that return no rows or more rows than you expect. Test your code with different combinations of bad input data to see what potential errors arise.
Sometimes you can use error-checking code to avoid raising an exception, as in Example 11-7.
Add exception handlers wherever errors can occur.
Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors can also arise from problems that are independent of your code—for example, disk storage or memory hardware failure—but your code still must take corrective action.
Design your programs to work when the database is not in the state you expect.
For example, a table you query might have columns added or deleted, or their types might have changed. You can avoid problems by declaring scalar variables with %TYPE qualifiers and record variables to hold query results with %ROWTYPE qualifiers.
Whenever possible, write exception handlers for named exceptions instead of using OTHERS exception handlers.
Learn the names and causes of the predefined exceptions. If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them.
Have your exception handlers output debugging information.
If you store the debugging information in a separate table, do it with an autonomous routine, so that you can commit your debugging information even if you roll back the work that the main subprogram did. For information about autonomous routines, see "AUTONOMOUS_TRANSACTION Pragma".
For each exception handler, carefully decide whether to have it commit the transaction, roll it back, or let it continue.
Regardless of the severity of the error, you want to leave the database in a consistent state and avoid storing bad data.
Avoid unhandled exceptions by including an OTHERS exception handler at the top level of every PL/SQL program.
Make the last statement in the OTHERS exception handler either RAISE or an invocation of the RAISE_APPLICATION_ERROR procedure. (If you do not follow this practice, and PL/SQL warnings are enabled, then you get PLW-06009.) For information about RAISE or an invocation of the RAISE_APPLICATION_ERROR , see "Raising Exceptions Explicitly".
Internally Defined Exceptions
Internally defined exceptions (ORA- n errors) are described in Oracle Database Error Messages . The runtime system raises them implicitly (automatically).
An internally defined exception does not have a name unless either PL/SQL gives it one (see "Predefined Exceptions") or you give it one.
If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them. Otherwise, you can handle them only with OTHERS exception handlers.
To give a name to an internally defined exception, do the following in the declarative part of the appropriate anonymous block, subprogram, or package. (To determine the appropriate block, see "Exception Propagation".)
Declare the name.
An exception name declaration has this syntax:
For semantic information, see "Exception Declaration".
Associate the name with the error code of the internally defined exception.
For semantic information, see "EXCEPTION_INIT Pragma".
An internally defined exception with a user-declared name is still an internally defined exception, not a user-defined exception.
Example 11-5 gives the name deadlock_detected to the internally defined exception ORA-00060 (deadlock detected while waiting for resource) and uses the name in an exception handler.
Example 11-5 Naming Internally Defined Exception
Predefined Exceptions
Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD . The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.
Если захожу в процедуру в PL/SQL Developer через Debug по шагам, то процедура выполняется нормально и отрабатывает корректно. Все используемые пакеты имеют статус VALID .
Подскажите, в чем может быть проблема?
посмотрите , может что-то неверно скомпилировалось из объектов или пакетов, которые используются у вас в процедуре: select comp_id, comp_name, version, status, namespace, schema from dba_registry;
2 ответа 2
Это поведение свазано с состоянем пакета. Обратимся к подглаве Package State:
The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state.
If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.
Если в пакете определена как минимум одна переменная, константа или курсор, то пакет после инициализаци получает состояние, которое сохраняется до окончания сессии. Состояние пакета может стать недействительным, одной из причин этого является рекомпиляция пакета:
The package body is recompiled.
If the body of an instantiated, stateful package is recompiled (either explicitly, with the "ALTER PACKAGE Statement", or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.
After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost.
После возникновения ошибки, пакеты автоматически реинициализируются, старое их состояние теряется и появляется новое.
Возможное решение, пересмотреть дизайн кода и убрать все переменные, константы или курсоры, объявленные вне функций или процедур, другими словами создать пакет без состояния. Но ничего страшного, если это не предоставляется возможным, при повторном запуске ошибка не должна возникнуть.
В другой сессии заново скомпилируем пакет:
Теперь, в первой сессии вызов вызовет ошибку, так как состояние пакета pack1 потеряно:
I am using oracle 10g and toad 11.5. I am trying to call an api from an anonymous block.
If I recompile the api after adding dbms_output.put_line and then try to execute the anonymous block, it shows error as:
However if I end current session and open a new session, then the anonymous block will execute with out the error.
Due to this issue, i am made to reconnect the session everytime i make a change to API. Can anyone help if this issue can be resolved by making any configurations in toad or database level.
Are you also getting something like 'existing package state had been discarded'? If so running it a second time in the same session ought to work. But that would suggest your package has some state, i.e. a variable declared in the package rather than in a procedure (and nothing to do with the dbms_output ).
4 Answers 4
I suspect you're only reporting the last error in a stack like this:
The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.
When you recompile the state is lost:
If the body of an instantiated, stateful package is recompiled (either explicitly, with the "ALTER PACKAGE Statement", or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.
After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it.
You can't avoid this if your package has state. I think it's fairly rare to really need a package to be stateful though, so you should revisit anything you have declared in the package, but outside a function or procedure, to see if it's really needed at that level. Since you're on 10g though, that includes constants, not just variables and cursors.
But the last paragraph from the quoted documentation means that the next time you reference the package in the same session, you won't get the error and it will work as normal (until you recompile again).
Не понимаю в чем дело, понятного объяснения не нашел.
В PL/SQL Developer при попытке открыть процедуру или пакет (пишем имя в SQL окно и кликаем с зажатым Ctrl) падает эта ошибка.
ORA-03113: принят сигнал конца файла по коммуникационному каналу. Идентификатор процесса: такой-то. Идентификатор сеанса: такой-то. Порядковый номер:такой-то
После нажатия OK выпадает следующая:
При этом обычные селекты из таблиц продолжают работать. Селекты из представлений так же дают ошибку "нет связи". Например:
Пробовал перезапускать PL/SQL Developer (даже весь комп ребутил). База не локальная, в сети.
Гугл говорит, что может кончиться место, но сейчас нет возможности подключиться под линуксовым пользователем. Можно как-то оценить это из командной строки оракла?
@sanmai Логи почистил (удалил лишние файлы). Места освободилось гигов двадцать. Но ошибка сохраняется. Причем, её нет если заходить с лягухи или SQL Developer, только в PLSQL Developer. У коллеги с другой машины ситуация аналогичная. Ошибка сохраняется и при установке в соседний каталог 13-й версии.
1 ответ 1
Эта ошибка возникает, если соединение с серверным процессом оборвалось. Самая частая причина - серверный процесс завершился ненормально. Причин этого ненормального завершения может быть очень-очень много и тут без анализа лог-файлов не обойтись.
Узнать, куда серверный процесс пишет логи можно так:
По-умолчанию примерно такое:
Серверный процес обычно не пишет логи (только самые критические ошибки). Включить логгирование можно так:
Ошибку как в вопросе я воспроизвести конечно же не могу, но как примерно искать причину подобных ошибок см. далее.
Запускаю сессию с любого клиента и нахожу к нему серверный процесс, в моём случае:
Предлагаю ему закрыться (провоцирую ошибку):
В клиенте получаю ошибку, что соединения больше нет:
В папке trace/, в файле тревог alert_.log нахожу причину:
Где pid: 7532, uid: 1004 это оболочка и учётка с которых был выполнен kill .
Hi All, I know, most of us would have faced the similar error "ORA-06508: PL/SQL: could not find program unit being called" . And this type of error will way off after compiling its dependent objects successfully . In my case no invalid objects , but still i am getting the same error . Is there any other thing should I take care in order to way off the error , please share your views Any more inputs on the issue , please let me know Thanks in advance Regards, Shamed H
Answers
Your program is calling some executable in some other schema, for which a public synonym is created in your schema.
You tried to call a stored procedure, but the stored procedure could not be found. It's possible the procedure may exist or might be invalid. If it's invalid, then you'll be able to fix the error by compiling it. Also, you might need to append schema name in front of your procedure, so that you code might know inside which specific schema does your procedure belong. For example.
Find the procedure that has error and append the schema that it belongs in in front of it like below. Then try to run your script again and see if that fixes your issue.
It could be error or commission or error of omission, but without more details we can't be sure why the error gets thrown.
Thanks for your inputs . .And i was able to recreate the issue . Please find below what i have done to recreate
1. Open the package "A" in SQL developer (Go to package tree on the left panel and select the package "A")
2. Change something (make sure the change will not cause any error in the package) in package "A"
3. And compile the package (When you select the package , the package will open in an editor) in the same editor. (make sure no errors in the Package A and no invalid objects.)
4. And when you try calling the package "A" from a program/client application/plsql block . The error shows "ORA-06508: PL/SQL: could not find program unit being called"
To solve (looks vie-rd but it really solved)
1. Copy the package body on an SQL editor (either from unix sqlplus / SQL developer SQL worksheet)
2. Execute the same (compile) . And make sure no errors / no invalid objects
3. Try executing the package "A" and absolutely no issues and it executes as expected Can anyone help me to understand why such behavior (Do the editor has any default settings)
Читайте также: