The ORA-00000 normal, successful completion in Oracle means there is no error in SQL Queries or whatever transactions performed are successful. You can simply ignore this message.
As per Oracle error message guide,
Cause: An operation has completed normally, having met no exceptions.
Action: No action required
Let’s reproduce it,
Create an anonyms block as shown below.
DECLARE
l_value VARCHAR(30);
BEGIN
SELECT 1
INTO l_value
FROM dual;
dbms_output.put_line('Error ' || sqlerrm);
END;
It runs a query against the dual table and prints the value of SQLERRM using dbms_output.put_line procedure.
Run it to reproduce ORA-0000.
Error ORA-0000: normal, successful completion
Solution,
Ther is no error here. Whatever query we ran was successful, hence Oracle responds back with ORA-0000: normal, successful completion and stores this value in SQLEEM. Just ignore the error.
Today, a friend asked me for a help in a copy of one database to a new server… this error was on 11g RDBMS, but it can be reproduced in version 9i, 10g, 12c, 18c or 19c. This is a very common misconfiguration on DNS entries or /etc/hosts files and instead to startup or shutdown the instance, Oracle rdbms does nothing and show up the message ORA-00000: normal successful completion.
[root@oracle01 ~]# su - oracle
[oracle@oracle01 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 5 02:33:40 2019Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected.
SQL>
SQL>
SQL>
SQL> shutdown abort
ORACLE instance shut down.SQL>
SQL>SQL> startup nomount
ORA-00000: normal, successful completion
SQL> exit
Disconnected
In this case, there is no entry oracle01 on /etc/hosts pointing to a local ip on their network. Below you can see the format of a /etc/hosts files:
::1 localhost.localdomain localhost
127.0.0.1 localhost.localdomain localhost
192.168.1.1 oracle01.mydomain.local oracle01
192.168.1.2 oracle02.mydomain.local oracle02
After fixed on the issue on /etc/hosts the Oracle database started normally, as expected:
[root@oracle01 ~]# su - oracle[oracle@oracle01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 5 02:48:23 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 2.1379E+10 bytes
Fixed Size 2237776 bytes
Variable Size 2751466160 bytes
Database Buffers 1.8589E+10 bytes
Redo Buffers 36098048 bytes
I hope this small articled helped you if you have the same issue than my friend!!!
ORA-00000: normal, successful completion
This error may occur in many circumstances you need to check environment variables:
$ORACLE_HOME
$ORACLE_BASE
$ORACLE_SID
also check /etc/hosts file it must contains correct hostname and ip.
example:
[oracle@oel6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 1 9:12:17 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected.
SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-00000: normal, successful completion
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-00000: normal, successful completion
After that i checked environment variables:
[oracle@server1 ~]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@server1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@server1 ~]$ echo $ORACLE_SID
prim
Then i checked /etc/hosts
cat /etc/hosts
#192.168.2.102 server1.soumya.com server1
So i found the issue. The hostname and ip was commented inside /etc/hosts file.
Then i started up the database and everything was right.
SQL> startup
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 331352056 bytes
Database Buffers 75497472 bytes
Redo Buffers 4308992 bytes
Database mounted.
Database opened.
P.S.Generally ORA-00000 comes under «Oracle Database Server Messages». These messages are generated by the Oracle database server when running any Oracle program.
Im getting Report error: ORA-0000: normal, successful completion Oracle Apex in a Tabular Form Report.
I cant able to proceed with any operations in the Tabular Forms such as Add Rows and Delete Checked Operation.
Is this an error or some kind of notification exception?
Few columns in this tabular form is done through cascading from another column in the same tabular form using Application Process. Will this be a reason for this error?
I checked many forums, but I couldnt get a proper solution to solve this. Kindly help me out in this. Thanks in Advance!
asked Jul 11, 2014 at 13:02
4
This actually means there is no error i.e An operation has completed normally, having met no exceptions.
Action: No action required.
answered Oct 10, 2016 at 13:46
The same problem we faced, there is a firewall between db server and application server, but later we noticed that. When we listen to tcp network we notice FIN ACK flags. This flag closes the connection for a long time without request, I mean if it was idle for a long time. However, in the db server side, there was no check to see if there was a connection. The situation was corrected by restarting again. We continued by removing the firewall between db and application server.
answered Aug 2, 2018 at 12:24
elifekizelifekiz
1,44613 silver badges26 bronze badges
This error Error (ORA-0000: normal, successful completion) Error while xyz prc, In side the xyz.prc I am update date into a table. To that table data range partition is there for that data partition are not there.
Once created it got processed.
answered Feb 15, 2021 at 13:28
MantuMantu
1331 silver badge5 bronze badges
Have you gotten the ORA-20000 error when working with Oracle PL/SQL? Learn what causes it and how to resolve it in this article.
The ORA-20000 error code is displayed when there is some PL/SQL code that calls RAISE_APPLICATION_ERROR.
Also, the code is displayed along with another, more helpful, error code.
Similar to the ORA-06550 error, the ORA-20000 error often has another error that is the cause of this error.
There are a few common errors that appear when you see the ORA-20000 error, so I’ll mention them here.
ORA-20000 Solution: ORA-10027
Often, the ORA-20000 error is accompanied by the ORA-10027 error:
ORA-20000: ORA-10027: buffer overflow, limit of 2000 bytes
The ORA-20000 error occurs when using the DBMS_OUTPUT package. This package has all kinds of default buffer sizes, the values of which depend on your version of Oracle.
This example shows that the buffer limit is 2000 bytes, which means you can only output 2000 bytes at a time.
When the ORA-10027 error happens, it means that you have tried to output something that is more than the buffer limit.
To resolve this error, you can increase the buffer limit:
DBMS_OUTPUT.ENABLE(10000);This increases it to 10,000 bytes. You can increase it up to 1,000,000 bytes:
DBMS_OUTPUT.ENABLE(1000000);Other Solutions of ORA-20000
Because ORA-20000 is such a generic error and is always accompanied by another error, focus on those errors first.
You can read my guide to the Oracle errors here to find out how to resolve all of the Oracle errors.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Programs that rely on PL/SQL can often be hit with run-time errors that occur due to faults in design, problems with coding and a number of other issues. However, one of the great aspects of working with PL/SQL in Oracle is that the user can plan for the errors that frequently arise by creating warnings, or exceptions, to signal them.
The user can have exceptions for items in a database such as “insufficient_budget” that signal when more funding is allocated to a particular budget category than what is owned. When the error occurs, an exception is raised and users can write routines called ‘exception handlers’ that essentially skip over the procedure to allow continuous running. The ORA-20000 concerns these type of user-defined errors as well as other errors that are artificially tacked onto a program to facilitate a database manager’s needs.
The Problem
The ORA-20000 is a generic error that almost always accompanies another error or a stack of errors. It is part of the reserved section of PL/SQL user-defined errors. The error is caused when a stored procedure (‘raise_application_error’) is called upon. Oracle raises exceptions from the innermost to the outermost error, so when the ORA-20000 is seen in front of a stack of errors, the user knows that the innermost error, or bottom, is the block that can serve as the catalyst.
The amount of information available on the ORA-20000 is minimal due primarily to its open-endedness. Essentially, when a user sees an ORA-20000, their goal is not necessarily to correct the ORA-20000. Instead, they need to resolve the error accompanying an ORA-20000, regardless of whether it is a user-created error or a reserved error. Because the error accompanies several other error messages, let us look at some of the more common combinations for the ORA-20000.
The Solution
One example of the ORA-20000 conjoined with another set of errors is shown below. Suppose the following stack of exceptions are thrown together:
ORA-20000: ORA-20000: ORA-0000: normal, successful completion
Update failed for the ch_clnt_mast
Line: 632 Execution of ap_old_ib_terms_xfer_dr failed Line: 1045
ORA-06512: at “AEPRDFCRH.ORA_RAISERROR”, line 16
ORA-06512: at “AEPRDFCRH.AP_OL_IB_TERMS_XFER_DR”, line 935
To review, the ORA-06512 is an error caused when the stack is unwound by unhandled exceptions in the code. As previously mentioned, the ORA-06512 error and ORA-20000 error are often triggered together. To fix these errors, the user would need to correct the condition causing the errors or write an exception handler.
To begin correcting the stack of errors, check the code in the lines indicated in the error message. In this particular case, the user-defined error likely occurred due to being place in a WHEN OTHERS exception. Check over the code in line 632 (update failed for the ch_clnt_mast) as well as line 1045 (ap_old_ib_terms_xfer_dr failed). The user will have to remove or work with the exception handlers that are masking the real error message so they can rerun the code to discover what is occurring in the system.
Another common error combination is the ORA-20000: ORU-10027: buffer overflow. DBMS_OUTPUT has various default buffer sizes that all depend on the user’s version of Oracle. In the system, the buffer size limit is 2000 bytes. The user can extend the buffer all the way to 1,000,000 bytes by issuing the statement below:
DBMS_OUTPUT.ENABLE(1000000);
The comparable SQL*Plus statement looks like this:
set serveroutput on size 1000000
If the user is working with Oracle’s 10g release or something more recent, unlimited buffer settings can be set with the following:
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
And the SQL*Plus version:
set serveroutput on size unlimited
This should offset the ORA-20000: ORU-10027, but, if the user conducts this approach and is still triggering the error, it is recommended to look back through the code in full to see if any items are overriding the buffer settings.
Looking forward
The ORA-20000 can be confusing and has such a wide range of responses that it would be impossible to cover them all here. If you find that you are having a difficult time managing the stack, contact your database manager or a licensed Oracle consultant to receive further instruction on correcting the error.
I use Oracle 11g express. I try to install sample database HR. From cmd
sqlplus
system
123456
Error:
Comment created.
Commit complete.
BEGIN dbms_stats.gather_schema_stats( 'HR' , granularity => 'ALL' , cascade => TRUE , block_sample => TRUE ); END;
*
ERROR at line 1:
ORA-20000: Schema "HR" does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 3701
ORA-06512: at "SYS.DBMS_STATS", line 24470
ORA-06512: at "SYS.DBMS_STATS", line 24435
ORA-06512: at line 1
How I install sample database HR correctly?
asked Apr 12, 2016 at 4:38
2
Apparently the statement to create the user hr was not executed correctly, and despite that the execution of the hr_main.sql script is not stopped.
This worked for me:
Once as sysdba:
SQL> alter session set «_ORACLE_SCRIPT»=true;
Session altered.
SQL> create user hr identified by hr;
User created.
SQL> drop user hr cascade;
User droped.
SQL> @?/demo/schema/human_resources/hr_main.sql
…
User created.
…
answered Jul 28, 2019 at 1:20
Navigate to the PDB container as SYS user before executing the script
[oracle@af18354c958e /]$ sqlplus sys as sysdba
Enter password: password
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter session set container = ORCLPDB1
SQL> @hr_main.sql
specify password for HR as parameter 1:
Enter value for 1: hr
specify default tablespeace for HR as parameter 2:
Enter value for 2: users
specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp
specify log path as parameter 4:
Enter value for 4: $ORACLE_HOME/demo/schema/log/
answered Nov 12, 2018 at 17:36
NandithaNanditha
531 silver badge6 bronze badges
4
The problem is the line
create user hr identified by 123456a@
Because user is not created, you are getting other errors.
To resolve it do either of below
-
Remove special character from password. Or use underscores
_in password.create user hr identified by 123456aOR
-
Try enclosing password in double quotes. (I am not able to test it now. But if it doesn’t work, try first option. I referred this link)
create user hr identified by "123456a@"
answered Apr 12, 2016 at 5:44
UtsavUtsav
7,8342 gold badges16 silver badges37 bronze badges
0
When you want to gather stat of a table and oracle throw ORA-20000:
begin
dbms_stats.gather_table_stats(
ownname=> ‘your_username’,
tabname=> ‘jbm’ ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> ‘AUTO’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
ERROR at line 1:
ORA-20000: Unable to analyze TABLE «your_username».»jbm», insufficient
privileges or does not exist
ORA-06512: at «SYS.DBMS_STATS», line 24270
ORA-06512: at «SYS.DBMS_STATS», line 24332
ORA-06512: at line 2
SQL> grant analyze any to your_username;
SQL> exec dbms_stats.unlock_table_stats(ownname => ‘your_username’,tabname => ‘jbm’);
SQL> begin
dbms_stats.gather_table_stats(
ownname=> ‘your_username’,
tabname=> ‘jbm’ ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> ‘AUTO’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
/
PL/SQL procdure successfully completed.
ORA-20000: Insufficient privileges to analyze an object in Schema
Today I have experienced
a new error in my database. We have oracle jobs scheduled to analyze the tables
in a schema(PNYDV) , runs in frequent intervals. The job is created in the system schema. The code is as follows:
SQL>
sho user
USER
is «SYSTEM»
SQL> DECLARE
2
X NUMBER;
3
BEGIN
4
SYS.DBMS_JOB.SUBMIT
5
( job => X
6
,what => ‘GATHER_ PNYDV_STATS;’
7
,next_date => to_date(’24/11/2011 07:22:18′,’dd/mm/yyyy hh24:mi:ss’)
8
,interval =>
‘TRUNC(SYSDATE+7)+8/24’
9
,no_parse => TRUE
10
);
11
SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is: ‘ || to_char(x));
12
END;
13 /
commit;
We
created a procedure (GATHER_ PNYDV _STATS) to analyze the schema PNYDV in the system schema. It is
as follows
SQL>
CREATE OR REPLACE PROCEDURE «GATHER_PNYDV_STATS» AS
2
BEGIN
3
EXECUTE IMMEDIATE ‘ALTER SESSION SET HASH_AREA_SIZE=2147483647’;
4
EXECUTE IMMEDIATE ‘ALTER SESSION SET SORT_AREA_SIZE=2147483647’;
5
DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘PNYDV’,method_opt =>
‘FOR ALL INDEXED COLUMNS SIZE AUTO’, CASCADE => TRUE);
6 END;
7 /
Procedure
created.
Error:
The job ran as per the scheduled
time. We got an alert in the logfile when the job ran as per the scheduled
time, an error occurred in the alert log file as in the below format.
ORA-20000:
Insufficient privileges to analyze an object in Schema
ORA-06512:
at «SYS.DBMS_STATS», line 13578
ORA-06512:
at «SYS.DBMS_STATS», line 13937
ORA-06512:
at «SYS.DBMS_STATS», line 14015
ORA-06512:
at «SYS.DBMS_STATS», line 13974
ORA-06512:
at «SYSTEM.GATHER_ PNYDV_STATS», line 5
ORA-06512:
at line 1
I
researched on the error but I did not get the result. Then I googled for the solution
and got to know that the system user should have the privilege ANALYZE ANY to
analyze the non system tables(other schema tables). I granted the ANALYZE ANY privilege to system user and rescheduled the job. It ran successfully.
SQL>
conn / as sysdba
Connected.
SQL>
grant ANALYZE ANY to system;
Grant
succeeded.
SQL>
conn system/*****
Connected.
SQL>
exec GATHER_PNYDV_STATS;
PL/SQL
procedure successfully completed.
Cause:
SYSTEM user doesn’t have the
privilege to analyze any non system table(Other schema’s table).
Solution:
·
Grant
the ANALYZE ANY privilege to the SYSTEM user.
·
Create
the procedure and the job under the particular schema which has to be analyzed (under PNYDV schema in my case)
Oracle 11g Error Codes and Solution Suggestions from ORA-20000 to ORA-22000
- ORA-20000: string
- Cause: The stored procedure ‘raise_application_error’ was called which causes this error to be generated.
- Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.
- ORA-21300: objects option not installed
- Cause: The objects option is not installed at this site. object types and other object features are, therefore, unavailable.
- Action: Install the objects option. The objects option is not part of the Oracle Server product and must be purchased separately. Contact an Oracle sales representative if the objects option needs to be purchased.
- ORA-21301: not initialized in object mode
- Cause: This function requires the OCI process to be initialized in object mode.
- Action: Specify OCI_OBJECT mode when calling OCIInitialize().
- ORA-21500: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]
- Cause: This is the generic error number for the OCI environment (client-side) internal errors. This indicates that the OCI environment has encountered an exceptional condition.
- Action: Report as a bug – the first argument is the internal error number.
- ORA-21501: program could not allocate memory
- Cause: The operating system has run out of memory.
- Action: Take action to make more memory available to the program.
- ORA-21503: program terminated by fatal error
- Cause: A program is in an unrecoverable error state.
- Action: Report as a bug.
- ORA-21520: database server driver not installed
- Cause: User attempted to access a database server through an object-mode OCI environment but the necessary driver for supporting such access to the database server is not installed or linked in.
- Action: Check if the driver corresponding to the database server has been installed/linked in and entered in the server driver table.
- ORA-21521: exceeded maximum number of connections in OCI (object mode only)
- Cause: User exceeded the maximum number of connections (255) that can be supported by an OCI environment in object mode.
- Action: Close some of existing and unused connections before opening more connections.
- ORA-21522: attempted to use an invalid connection in OCI (object mode only)
- Cause: User attempted to use an invalid connection or a connection that has been terminated in an OCI environment (object mode), or user attempted to dereference a REF obtained from a connection which has been terminated.
- Action: Ensure that the connection exists and is still valid.
- ORA-21523: functionality not supported by the server (object mode only)
- Cause: User attempted to use a functionality that the server does not support.
- Action: Upgrade the server
- ORA-21524: object type mismatch
- Cause: The object type of the object is different from what is specified.
- Action: Check the type of the object and correct it.
- ORA-21525: attribute number or (collection element at index) string violated its constraints
- Cause: Attribute value or collection element value violated its constraint.
- Action: Change the value of the attribute or collection element such that it meets its constraints. The constraints are specified as part of the attribute or collection element’s schema information.
- ORA-21526: initialization failed
- Cause: The initialization sequence failed. This can happen, for example, if an environment variable such as NLS_DATE_FORMAT is set to an invalid value.
- Action: Check that all NLS environment variables are well-formed.
- ORA-21527: internal OMS driver error
- Cause: A process has encountered an exceptional condition. This is the generic internal error number for Oracle object management services exceptions.
- Action: Report this as a bug to Oracle Support Services.
- ORA-21528: internal error, image could not be understood
- Cause: The format of the image passed to the pickler is incorrect and cannot be understood.
- Action: Contact Oracle Support Services.
- ORA-21560: argument string is null, invalid, or out of range
- Cause: The argument is expecting a non-null, valid value but the argument value passed in is null, invalid, or out of range. Examples include when the LOB/FILE positional or size argument has a value outside the range 1 through (4GB – 1), or when an invalid open mode is used to open a file, etc.
- Action: Check your program and correct the caller of the routine to not pass a null, invalid or out-of-range argument value.
- ORA-21561: OID generation failed
- Cause: The handles passed in may not be valid
- Action: Check the validity of the env, svc handles
- ORA-21600: path expression too long
- Cause: The path expression that is supplied by the user is too long. The path expression is used to specify the position of an attribute in an object. This error occurs when one of the intermediate elements in the path expression refers to an attribute of a built-in type. Thus, the OCI function cannot proceed on to process the rest of the elements in the path expression.
- Action: User should pass in the correct path expression to locate the attribute.
- ORA-21601: attribute is not an object
- Cause: The user attempts to perform an operation (that is valid only for an object) to an attribute of a built-in type. An example of such an illegal operation is to dynamically set a null structure to an attribute of a built-in type.
- Action: User should avoid performing such operation to an attribute of built-in type.
- ORA-21602: operation does not support the specified typecode
- Cause: The user attempts to perform an operation that does not support the specified typecode.
- Action: User should use the range of valid typecodes that are supported by this operation.
- ORA-21603: property id [string] is invalid
- Cause: The specified property id is invalid.
- Action: User should specify a valid property id. Valid property ids are enumerated by OCIObjectPropId.
- ORA-21604: property [string] is not a property of transient or value instances
- Cause: Trying to get a property which applies only to persistent objects.
- Action: User should check the lifetime and only get this property for persistent objects.
- ORA-21605: property [string] is not a property of value instances
- Cause: Trying to get a property which applies only to persistent and transient objects.
- Action: User should check the lifetime and only get this property for persistent and transient objects.
- ORA-21606: can not free this object
- Cause: Trying to free an object that is persistent and dirty and the OCI_OBJECTFREE_FORCE flag is not specified.
- Action: Either flush the persistent object or set the flag to OCI_OBJECTFREE_FORCE
- ORA-21607: memory cartridge service handle not initialized
- Cause: Attempt to use the handle without initializing it.
- Action: Initialize the memory cartridge service handle.
- ORA-21608: duration is invalid for this function
- Cause: Attempt to use a duration not valid for this function.
- Action: Use a valid duration – a previously created user duration or OCI_DURATION_STATEMENT or OCI_DURATION_SESSION. For callout duration or external procedure duration, use OCIExtProcAllocCallMemory.
- ORA-21609: memory being resized without being allocated first
- Cause: Attempt to resize memory without allocating it first.
- Action: Allocate the memory first before resizing it.
- ORA-21610: size [string] is invalid
- Cause: Attempt to resize memory with invalid size.
- Action: Pass in a valid size (must be a positive integer).
- ORA-21611: key length [string] is invalid
- Cause: Attempt to use an invalid key length.
- Action: Key length is invalid and valid range is 0 to 64
- ORA-21612: key is already being used
- Cause: Attempt to use a key that is already used.
- Action: Use a new key that is not yet being used.
- ORA-21613: key does not exist
- Cause: Attempt to use a non-existent key
- Action: Use a key that already exists.
- ORA-21614: constraint violation for attribute number [string]
- Cause: Constraints on the attribute were violated
- Action: Correct the value (of the attribute) so that it satisfies constraints
- ORA-21615: copy of an OTS (named or simple) instance failed
- Cause: see following message
- Action: Check that no attribute value violates constraints.
- ORA-21700: object does not exist or is marked for delete
- Cause: User attempted to perform an inappropriate operation to an object that is non-existent or marked for delete. Operations such as pinning, deleting and updating cannot be applied to an object that is non-existent or marked for delete.
- Action: User needs to re-initialize the reference to reference an existent object or the user needs to unmark the object.
- ORA-21701: attempt to flush objects to different servers
- Cause: User attempted to flush objects to different servers in one function call. These objects are obtained by calling a callback functions provided by the program.
- Action: User should avoid performing such operation.
- ORA-21702: object is not instantiated or has been de-instantiated in cache
- Cause: User attempted to perform an inappropriate operation to a transient object that is not instantiated in the object cache. Operations that cannot be applied to a not-instantiated transient object include deleting or pinning such an object.
- Action: User should check their code to see if they are performing such an operation without instantiating the object first, or performing such an operation after the allocation duration of the object has expired.
- ORA-21703: cannot flush an object that is not modified
- Cause: See the error message.
- Action: The object should not be flushed.
- ORA-21704: cannot terminate cache or connection without flushing first
- Cause: See the error message.
- Action: The transaction should be aborted or committed before terminating the cache or connection.
- ORA-21705: service context is invalid
- Cause: The service context that is supplied by the user is not valid.
- Action: User needs to establish the service context.
- ORA-21706: duration does not exist or is invalid
- Cause: The duration number that is supplied by the user is not valid.
- Action: User needs to establish the duration or use a correct predefined duration.
- ORA-21707: pin duration is longer than allocation duration
- Cause: The pin duration supplied by the user is longer than the allocation duration. This affects operations such as pinning and setting default parameters.
- Action: User should use a shorter pin duration or use the null duration.
- ORA-21708: inappropriate operation on a transient object
- Cause: User attempted to perform an inappropriate operation on a transient object. Operations that cannot be applied to a transient object include flushing and locking.
- Action: User should avoid performing such operation on a transient object.
- ORA-21709: cannot refresh an object that has been modified
- Cause: User attempted to refresh an object that has been marked for delete, update or insert (new).
- Action: User should unmark the object before refreshing it.
- ORA-21710: argument is expecting a valid memory address of an object
- Cause: The object memory address that is supplied by the user is invalid. The user may have passed in a bad memory address to a function that is expecting a valid memory address of an object.
- Action: User should pass in a valid memory address of an object to the function.
- ORA-21779: duration not active
- Cause: User is trying to use a duration that has been terminated.
- Action: User should avoid performing such operation.
- ORA-21780: Maximum number of object durations exceeded.
- Cause: This typically happens if there is infinite recursion in the PL/SQL function that is being executed.
- Action: User should alter the recursion condition in order to prevent infinite recursion.
Вопрос:
Я создал триггер, который позволяет пользователю иметь 10 текущих размещенных заказов. Итак, теперь, когда клиент пытается разместить номер заказа 11, база данных оракула возвращает ошибку. Ну, 3 ошибки.
ORA -20000: В настоящее время у вас 10 или более заказов.
ORA-06512: в строке “C3283535.TRG_ORDER_LIMIT”, строка 12
ORA-04088: ошибка во время запуска триггера C3283535.TRG_ORDER_LIMIT ‘
Верхняя ошибка – это то, что я создал, используя:
raise_application_error (-20000: “В настоящее время у вас 10 или более заказов”.);
Я просто задался вопросом после поиска и пытался много способов изменить сообщения об ошибках для двух других ошибок или даже не показать их всем вместе с пользователем?
Вот код, который я использовал
create or replace trigger trg_order_limit
before insert on placed_order for each row
declare
v_count number;
begin
-- Get current order count
select count(order_id)
into v_count
from placed_order
where fk1_customer_id = :new.fk1_customer_id;
-- Raise exception if there are too many
if v_count >= 10 then
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'You currently have 10 or more orders processing.');
end if;
end;
Большое спасибо Ричард
Лучший ответ:
Происхождение исключения идет от внутреннего к внешнему блоку, в отличие от переменной области, которая идет от внешнего к внутреннему блоку. Для получения дополнительной информации об этом, прочитайте “Макрофлин” “Программирование с помощью PL/SQL”, глава 5.
То, что вы получаете здесь, представляет собой стек исключений – исключения, полученные от самых внутренних блоков до самых внешних блоков.
Когда вы вызываете исключение из триггера, оператор raise_application_error возвращает ошибку.
Затем он распространяется на триггерный блок, который говорит ORA-06512: at "C3283535.TRG_ORDER_LIMIT", line 12. Это связано с тем, что триггер рассматривает повышенное исключение как ошибку и останавливается для продолжения.
Затем ошибка распространяется на сеанс, который вызывает ORA-04088: error during execution of trigger 'C3283535.TRG_ORDER_LIMIT'. Эта ошибка сообщает нам о том, где, как в какой части программы, была поднята ошибка.
Если вы используете внешнюю программу, такую как страницы сервера Java или PHP, вы сначала поймаете поднятую ошибку – 20000. Таким образом, вы можете отобразить то же самое для своего конечного пользователя.
РЕДАКТИРОВАТЬ :
О первой ошибке – ORA-20000, вы можете изменить ее в самой инструкции RAISE_APPLICATION_ERROR.
Если вы хотите обрабатывать ORA-06512, вы можете использовать ответ Uday Shankar, который поможет в ORA-06512 этой ошибки и отображает соответствующее сообщение об ошибке.
Но вы все равно получите последний ORA-04088. Если бы я был у вас на месте, я бы не стал беспокоиться, так как после получения ORA-20000 я бы поднял ошибку приложения на самой передней панели, скрывая все остальные данные от пользователя.
Фактически, это характер стека исключений Oracle. Возникают все ошибки от самого внутреннего до самого внешнего блока. Это очень полезно для нас, чтобы определить точный источник ошибок.
Ответ №1
В триггере вы можете добавить часть обработки исключений, как показано ниже:
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'You currently have 10 or more orders processing.');
Ответ №2
Я вижу, что это довольно старый пост, но я думаю, что читатели должны знать, что
- Это фактически не обеспечивает соблюдение бизнес-правила (максимум 10 заказов). Если это просто “некоторый” номер, чтобы избежать слишком больших сумм, и вам все равно, если иногда у людей есть 12 заказов, тогда это может быть хорошо. Но если нет, подумайте о сценарии, когда у вас уже 9 заказов, а затем заказы для одного и того же клиента вставляются из двух разных сеансов/транзакций одновременно. В этом случае вы получите 11 заказов, не обнаружив эту ситуацию с переполнением. Таким образом, вы не можете полагаться на этот триггер на самом деле.
- Кроме того, вам может потребоваться перезапустить этот триггерный огонь при обновлении, если fk1_customer_id может быть обновлен (я видел реализации, где сначала NULL помещается в столбец FK, а затем обновляется до фактического значения). Вы можете подумать, реалистичен ли этот сценарий.
- В триггере есть основной недостаток. Вы находитесь внутри транзакции и внутри оператора, который в настоящее время выполняется, но еще не завершен. Итак, что, если вставка не является одной вставкой строки, а что-то вроде
insert into placed_order (select... from waiting_orders...)что вы ожидаете от триггера?
Подобное бизнес-правило непросто обеспечить. Но если вы решите сделать это в триггере, вам лучше сделать это в триггер после инструкции (таким образом, не в триггере перед строкой). Триггер after after по-прежнему не будет видеть результаты других незафиксированных транзакций, но, по крайней мере, текущий оператор находится в определенном состоянии.
Фактически бизнес-правило МОЖЕТ принципиально применяться только в момент фиксации; но в базе данных Oracle нет такой вещи, как триггер ON-COMMIT. Вы можете сделать денормализацию количества записей в таблице клиентов (добавить столбец ORDER_COUNT) и поместить в эту таблицу отложенное ограничение (ORDER_COUNT <= 10). Но тогда вы все еще полагаетесь на правильное поддержание этого поля в своем коде.
Полностью надежная альтернатива, но несколько громоздкая, заключается в создании материализованного представления (что-то вроде SELECT fk_customer_id, count(*) order_count from placed_orders group by fk_customer_id, с FAST REFRESH ON COMMIT в таблице place_order и создания контрольного ограничения order_count <= 10 на материализованном виде. Это единственный способ надежно применять этот тип ограничений, не задумываясь о всех возможных ситуациях, таких как параллельные сеансы, обновления и т.д. Обратите внимание, что FAST REFRESH ON COMMIT замедлит вашу фиксацию, поэтому это решение не пригодно для больших объемов (вздох… Почему Oracle не предоставляет триггер ON COMMIT…)
Jul
31
Those days when I was trying to apply the Apr 2018 Release Update Revision 12.2.0.1.180717, I got the error below during datapatch roolback execution of the previous patch:
Patch 27013506 rollback (pdb PDB01): WITH ERRORS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27013506/21779816/27013506_rollback_ORCL_PDB01_2018Jul31_00_12_34.log (errors) Error at line 32785: ORA-20000: Oracle Text Error: CTXSYS.JSONREST_GENERIC_STOPLIST not created
And in logfile there was this entry:
3410 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','ustedes');
3411 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','varias');
3412 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','varios');
3413 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','vosotras');
3414 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','vosotros');
3415 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','vuestra');
3416 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','vuestras');
3417 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','vuestro');
3418 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','vuestros');
3419 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','y');
3420 sys.default_add_stopword('CTXSYS.JSONREST_MEX_SP_STOPLIST','yo');
3421 end;
3422 end;
3423 /
declare
*
ERROR at line 1:
ORA-20000: Oracle Text Error: CTXSYS.JSONREST_GENERIC_STOPLIST not created
ORA-06512: at "SYS.DEFAULT_CREATE_STOPLIST", line 27
ORA-06512: at line 35
After doing some investigation I’ve found the «Oracle Support Document 2358409.1 (Oracle Database / Grid Infrastructure / OJVM Release Update» saying this is an expected issue:
1.2 ORA-20000: Oracle Text Error: CTXSYS.JSONREST_GENERIC_STOPLIST not created
Problem: In the (unlikely) scenario that you are rolling back one of these Apr 2018 12.2.0.1 patches to a previous quarterly 12.2.0.1 patch, and then rolling back that previous quarterly 12.2.0.1 patch, the second rollback can fail with the error:
ORA-20000: Oracle Text Error: CTXSYS.JSONREST_GENERIC_STOPLIST not created reported by datapatch. This is due to the script ctx/admin/bug24899895_rollback.sql being run twice during this operation.
Workaround: If this is the only error reported by datapatch, the error can be ignored, and the status column of the sql registry can be manually updated from WITH ERRORS to SUCCESS to reflect that.
for example:
SQL> UPDATE dba_registry_sqlpatch SET status = ‘SUCCESS’ WHERE status = ‘WITH ERRORS’;
SQL> COMMIT;
Those rollbacks are happening automatically by datapatch to apply the latest RUR. After applying the workaround, I’ve just re-executed «datapatch -verbose» to ensure nothing was missing. It worked like a charm.
Have you enjoyed? Please leave a comment or give a 👍!