When you have multiple Oracle APEX environments, e.g., development, test, UAT and production, and for multiple groups, one of the confusing things is that sometimes developers are lost on which environment they’re working on. Most of technically proficient developers can make a distinction easily from the URLs they’re currently using. However, most of less technically proficient (e.g., business users) sometimes cannot.

A quick and simple solution here is to make a visual distinction on the APEX workspace pages especially on the logo area. Instead of using plain vanilla logo images, they are replaced with more distinct and informative images.

This is the default APEX workspace logon page.

This is a sample of the personalized workspace logon page with a company logo and it is showing that this is the development environment.

This is the main workspace page after logon showing the default logo image.

You can change it to be like this.

With just this minor change, our users feel more comfortable knowing that they’re working the right environment.

How to change it?

With a quick investigation, you can easily find the paths of those image files as follows:

Workspace logon page:

Image path: /i/apex/builder/apex-logo-white.gif
Image size: 300px x 30px
Transparent background

Image path:/i/apex/builder/apex-db-apps.png
Image size: 240px x 200px
Transparent background

Workspace main page after logon:
Image path: /i/htmldb/apex_logo.gif
Image size: 300px x 30px
White background

• Use Photoshop or any image editing tools to recreate those image files.
• Copy and replace those image files on the web server. Please make the backup of those files before replacing them.

Note that this approach will NOT work if you’re using a shared web server for multiple APEX database environments because they’re using the same image files.

Update: Using ApexLib script in the login message (under Manage Instance) as mentioned by Peter in the comment section is another solution for customization. I like this approach as not only you can customize the logo, but you can also change the description text on the workspace logon page. In addition, since the change is on the APEX instance itself, it can definitely be used on a shared web server.

When I was offered to review this book, I was so excited. The topic is probably one of the least popular topics in the Android development circle based on the number of books written on Android. Most of the books in the market tend to cover solely on the area of actual development because it gives readers an instant gratification when creating something. However, it is unfortunate (but true) that the application testing might be something most developers least think about, or if they do, they not do it systematically. (I’m guilty of this as well.) I would like to expand my horizon by learning from the pros. That’s why I’m so excited for a chance to review this book.

The “Android Application Testing Guide” is a very practical book introducing available frameworks and most widely used tools & techniques to improve the qualify of the application by engaging in the quality control and testing throughout the development cycle (not just start testing at the end). This agile development concept called the Test Driven Development (TDD) relies on repeatable short cycle to trying to catch and take care of potential issues (i.e., bugs) as early as possible.

The first chapter explains what involves in the different stages of testing in the Android development including unit test, integration test, functional or acceptance test, system test, and performance test. It starts introducing the Android testing framework extending JUnit which provides the complete testing framework suitable for the end-to-end testing strategies.

The chapter 2 starts working on the actual testing using JUnit which is the default framework for Android testing project and is supported by Eclipse which it the most widely-used IDE for Android development. The chapter jumps right into the step-by-step on how to create the Android test project which is a separate from its development project being tested. The test project will have independent structure and a set of its own components. Having a separate project is the best practice because from the production build’s standpoint, testing codes will not be included in the actual build, thus it will be not be in the APK.

Chapter 3 dives into individual building block in the Android SDK tool.  This covers Assertions, TouchUtils class (to simulate the touch events), Mock objects (to simulate mock objects in order to isolate the tests), TestCase class, and Instrumentation. There is an extensive explanation of individual component accompanying by code samples.

Chapter 4 talks about the concept of Test Driven Development. Again, it is the strategy of performing tests along the development process – not at the end as in the traditional approach. This even includes writing test cases (and test codes) first right after studying the requirements, and then writing the actual codes to satisfy (having the “pass” results) the test cases. Author claims that this approach of creating test cases this early will ensure that tests will be performed instead of, if left until the end, it is highly possible that they would have been forgotten or ignored. I agree with the concept. But in reality this may not work with all types of the projects, and this is confirmed by author in a few paragraphs later to use your judgement and expertise in applying this approach to wherever suitable. The latter part of the chapter shows samples on how to apply the TDD in the step-by-step sample application and tests, which I found this makes the case very compelling.

Chapter 5 introduces the Android Virtual Device (AVD) which is the next best thing to the real devices. The obvious advantage of using AVD is ability to create different Android configurations to run tests. The Headless Emulator (no window displayed) is also mentioned here allowing the automated tests via command line with no windows. I really enjoy many samples of what you can do with command-lines especially when you can simulate different device’s conditions including network bandwidth throttling, or different locales, etc. Later in the chapter, it talks about the support of the Monkey application (you can read more about Monkey Theorem here) which allows random event generation. It also demos the server-client setup and test scripting with Monkneyrunner.

Chapter 6 talks about the Behavior Driven Development which, according to author, is considered the evolution of the Test Driven Development and a need of Acceptance Testing. In summary, the approach emphasizes not only to include the business or end users in testing, but also to use non-technical terms in the test cases that business or end users would understand. Personally I think the concept is too abstract than practical. However, the introduction of Fitnesse and Slim tools makes the point to see its usefulness especially the part of “Wiki”-style test cases and test result documentation.

Chapter 7 contains all practical real-world samples of the disciplines and techniques you can use in the different situations. This includes testings on activities and applications, databases and Content Providers, UIs, exceptions, parsers and memory leaks. Couple tools are introduced here including EasyMock which provides mock objects for interfaces, and Hamcrest which is a library of matcher objects for comparison.

Chapter 8 continues the introduction of Agile technique with Continuous integration. Similar to continuous testing, it is suggested that integration should be done frequently early in the process and in small steps. The most common practice, as stated by author, is to trigger the build process after every commit to the source code repository. The chapter goes in details how to setup the automate building process using ant, the version control repository using Git, the continuous integration with Hudson, and test result analysis using nbandroid-utils.

Chapter 9 discusses one of the most critical components in designing Android application which is performance. As we all know, with a device which has limited computing power and battery life, balancing between performance and effective use of resources is something developers should keep in mind when designing an Android application. The chapter again introduces us with a series of tools and techniques for performance testing. This includes using traditional LogCatlogging, extending Instrumentation, Traceview, dmtracedump, and Caliper microbenchmarks.

Chapter 10 – alternative testing tactic is not for the most Android developers as it involves building Android from source code. Again, more tools are introduced. Even though it is very technical, it is still a good read.

Summary:

I have to say that for a novice Android developer like I am, I learn a lot from reading the Android Application Testing Guide. Best-in-class testing techniques are covered in this book which are commonly practiced in the agile development. These include Test Driven Development, Behavior Driven Development, Continuous Integration, etc. They in generally oppose to the traditional approach of doing test and integration at the end of development cycle. However, as author states in the book, there is no silver bullets in software development – the suggested testing approaches might not apply to certain needs or projects. But I can see that for a large complex project, applying these methodologies and techniques will likely increase in the productivity and quality.

Numerous tools are introduced in this book. This may be one of the big issues for those who are less-advanced in Android development. Since there is no common repository of these tools, extended knowledge of a varieties of open-source tools, and ability to incorporate them with the development process are required. I wish that there would be consolidated repository for all testing tools or even better all required testing functionality would be built-in in the SDK. Well, before when that would happen, this Android Application Testing Guide book is probably one of a few reference books that has the complete collection of Android testing topics.

Disclosure: I received the Android Application Testing Guide e-book from Pubkt publishing with no charge. I’m not compensated for this book review. The content expressed in this post is personal opinion.

Oracle APEX provides multiple ways for authentication. The most commonly used one is the LDAP authentication. Configuring it is very straight-forward in most cases – just providing the LDAP host, port, whether or not to use SSL, and finally the DN string. See here and here for posts about APEX LDAP configuration.

Sometime you want to test the LDAP configuration before deploying it. You can either use the 3rd-party tools to validate the configurations, or create an APEX application to test the logon, or use the built-in LDAP test tool in APEX.

The built-in APEX LDAP test tool can be launched when the authentication is being configured as seen below.

It will pop-up a new window as shown below.

By accident, I found out that you can even launch the APEX LDAP Test Tool as the stand-alone application using Application ID 4000 and Page Number 3890.

For example,

http://localhost:8888/apex/f?p=4000:3890

It will obviously prompt you to log on to the workspace first, then the LDAP test tool will just show just like an application, not the pop-up window.

Additional resources:

• LDAP Authentication Function http://www.oracle.com/technetwork/developer-tools/apex/how-to-ldap-authenticate-099256.html

Oracle Real Application Cluster (RAC)  becomes the technology of choice for highly available and highly scalable deployment of enterprise Oracle database environment. With its innovative technology it comes with no surprise its complexity. The complexity is not only with the underlying database technology itself, but also how well you design and implement it to work with other components including operating system, storage subsystem, etc. In order to setup and maintain a successful RAC environment, it not only requires technical knowledge of database administrator (DBA), but also well collaboration and extended planning between all IT and business partners.

Few years back, there has been a movement of DBA 2.0 which says that, in addition to database knowledge,  an extended understanding of operating system, storage and/or networking is encouraged due to more tightly integrated of Oracle database technologies such as Clusterware, RAC, ASM with the underlying subsystems. I agree with this notion but not to the extent that DBAs should perform all works in those areas. (Yes, I’m aware that some DBAs do it all.) However, I think at least they should have enough understandings of all-related technologies to collaborate with those who are responsible for in order to achieve the desired setup. In many cases especially in the large companies where traditional organization division of the job description of administrators is clear cut and no overlapping, being well-rounded with all-related technologies will allow DBA to better discuss the requirements and designs and even assist with the implementation details.

Most Oracle RAC books in the market today tend to focus only on the detailed technical aspects of this database technology. This mainly is good for those Oracle professionals who at least have worked with RAC before. Few books really provide a good foundation for those who are new to this technology. One of those few which I’ve just read recently is Oracle 11g R1/R2 Real Application Clusters Essentials authored by Ben Prusinski and Syed Jaffer Hussain published by Packt Publishing.

What I really like about this book is the fact that it does not jump into the nuts and bolts of this Oracle technology right away but instead it is trying to build up good foundation starting from concept (especially high availability), architecture (of all components not just of database), design, implementation and then even some of the real-world samples. This stepping-stone approach helps readers to understand what is involved and how they should deploy this technology for the high available database.

In the chapter 1, the emphasis of the high availability concept is crucial. The authors precisely points out that high availability should be looked from users’ perspective. Many DBAs or system administrators especially novice ones have a tendency to focus solely on their respective areas. The provided sample of the availability percentage in the “high availability interpretations” section is a good reminder for a need to have an end-to-end high availability design to avoid a single point of failure (SPOF). The chapter goes on to give the definitions of many acronyms (BIA, BCP, DRP, etc.) commonly-used during the planning phase. It is quite useful to be aware of them especially when discussing with business end-users. The chapter ends with providing a whole picture of the high availability solutions (RAC, Data Guard, Streams, Application Server Clustering) from Oracle as a part of the Maximum Availability Architecture (MAA). It is worth to point out that RAC is just one (not the only) of key components for a complete high availability solution.

Chapter 2 looks into the hardware components of Oracle RAC architecture including network, storage, etc. Again, I think the authors reinforce a need to understand all these underlying technologies (at least at high level) even though DBAs might not involve in the actual implementation. But it is crucial to have this knowledge under their belts in order to have the productive discussions of the requirements and design with system administrators.

Chapter 3 goes into the clusterware installation of 11g R1/R2. Even though readers may to be able to find similar information on many web sites and blogs, this book has some extra information which makes an attempt to explain what each action does, for example, what happen when the orainstRoot.sh and root.sh run?

Chapter 4 dedicates to the Automatic Storage Management (ASM) which becomes the only clear choice for storage management in the 11g deployment. The chapter covers all aspects of ASM in both R1 and R2 including new features, configurations, management, etc.

Chapter 5 covers Clusterware management and (some) troubleshooting samples. Instead of showing all possible troubleshooting scenarios (which is impossible to include everything in a chapter or even in a book), the authors did an excellent job of giving an overview of all necessary Clusterware utilities in order to troubleshoot the issues.

Chapter 6 turns our attention to RAC database administration. The structure is similar to that of Clusterware in the chapter 3. The latter part of the chapter also covers the automatic workload management including the topic of the service which is highly recommended to utilize it (not using the default database service.)

Chapter 7 covers all aspects of backup and recovery of Oracle database including the best practices. In addition, new features of RMAN in Oracle 11g R1 and 11g R2 along with OCR and voting disk backup and recovery are discussed.

Chapter 8 tries to give an overview of performance tuning. The intention here seems to just give an overview of the concept and methods of performance tuning as well as new 11g performance features, for example, Real Application Testing (RAT) which has the Database Replay and SQL Performance Analyzer (SPA). This high-level overview focus is understandable because the scope of performance tuning is generally vast. Some of tuning metrics especially relate to the wait events are explained here.

Chapter 9 focuses on the upgrade scenarios. As we all know it is impossible to cover all possible scenarios. But the authors gives sufficient information about overall upgrade processes from 10g R2 to 11g R1 and then to 11g R2 in this chapter. Definitely consulting with Oracle support for more details on individual upgrade case is still highly recommended.

Chapter 10 is very informative on the node addition and removal. The chapter steam-lines the steps pretty well (comparing to the official Oracle document which is very confusing at the first read with the flow of information). (We just went through adding two nodes into the existing two nodes of Oracle 11g R1 two months ago. That’s why I can say that.)

Chapter 11 shows the readers the importance of the end-to-end high availability implementation. The high availability should not stop at the Oracle RAC database, but it must extend to application layer on top of it. In addition to an overview of Oracle EBS (E-Business Suite), this chapter shows the readers how to take full advantage of high availability and performance features in Oracle RAC database.

The last chapter wraps up nicely with information about other tools in the MAA tool suite namely Streams and Data Guard. It covers all various topics of thes tools including concepts, new features, best practices, and overview configuration.

The last section of this book on the additional resources and tools is a nice read and very informative. It is just like a nice dessert at the end of a good meal.

In conclusion, if you’re new to Oracle RAC technology, this is THE book for you. It should give the readers enough understanding to jump on Oracle RAC bandwagon. Advanced DBAs may find that most of the contents in the book are too familiar, but they might be attracted to the new features, comparisons between R1 and R1 as well as some of real-world samples mentioned in this book. Overall I appreciate a very well effort of Ben ad Syed for one of the most complete informative books on Oracle RAC in the market today. Well-deserved kudos to them.

Thanks to Packt Publishing for the opportunity to review this book.

I encountered this error “I/O Error: SSO Failed: Native SSPI library not loaded” when using Oracle SQL Developer connecting to a MS SQL database using Windows Authentication. Fortunately, couple web sites (here and here) already mentioned about this.

Basically, from the  jTDS distribution downloaded files, I have to copy a DLL file named ntlmauth.dll (which is for NT authentication) under the jtds-x.x.x-dist\x86\SSO\ or jtds-x.x.x-dist\x64\SSO\, to any directories in the PATH environment. I copied it to the bin directory of the JDK (which I already have it in the PATH). Then after restarting the SQL Developer, I can now connect to MS SQL database using Windows Authentication.

Learning Android is a collection of many years’ experience of the author, Marko Gargenta, who has been teaching the Android bootcamp training to thousands of software developers. It covers all necessary topics about Android starting from a high-level overview (history and concept) until the nuts and bolts (writing codes for Twitter-like application called Yamba application).

As an avid user of both Android phone and tablet myself, I have an interest in learning about this platform and hopefully writing some applications on it. I’ve read many Android books before. Most cover a lot of topics but without coherence – you feel like you learn a lot about many things but you still can’t put them together to make them work well. This book is one of the few books I really like. It walks you through creating a reasonably complex application from scratch. Not only the author explains the concepts and sample codes really well, but he also guides you through his thinking and design process on how to write a good application. The latter, I think, is more important for laying a good foundation for writing other applications in the future. The tips, suggestions, and notes throughout the book show you that Marko really understands what works and what does not.

I highly recommend this book to everyone who has an interest in learning about programming on Android and would like to build a good foundation on good programming practices. People who know Java programming language should have no problem following the sample codes presented in the book. But for those who don’t, I suggest getting yourself familiar with Java first. (Obviously if you don’t plan to use any other mobile cross-platform or rapid development tools, you must write in Java codes.) Knowing it a bit will help you tremendously with understanding the sample codes. Needless to say, you can still pretty much read for concepts, tips, and even some codes because the explanation from the author is very precise and clear.

It took me a longer time to finish this book because my plan is not to just read through it, but also write the sample application along with it. At the end I’m proud to say that I’ve successfully coded the Yamba application.

Here is the list of useful links on this book:

This is not new but I’ve learned about it last week from one of developers when reviewing codes with them for the plan to change the sequence’s current value. Normally I would drop the sequence and create it with START WITH to a new desired number. However, my approach will obviously invalidate all dependencies which commonly refer to triggers.

The trick is to change the INCREMENT BY value to the difference between the current value and the needed value, then use it once, finally switch the INCREMENT BY back to 1.

Here is a quick demo:

This TR_TAB_A_BI trigger depends on the SQ_TAB_A sequence.

create or replace trigger TR_TAB_A_BI
before insert on TAB_A
for each row
when (new.id is null)
begin
	select SQ_TAB_A.nextval
	into :new.id
	from dual;
end;
/

Start with trigger valid.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

Let’s drop trigger.

SQL> drop sequence SQ_TAB_A;

Sequence dropped.

As expected, the trigger became invalid.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          INVALID

After the sequence is recreated, you can compile this trigger before use, or you can just execute an insert into the table, Oracle will recompile this trigger automatically before actual use.

SQL> create sequence SQ_TAB_A start with 243 cache 20;

Sequence created.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          INVALID

SQL> insert into TAB_A (B) values ('AA');

1 row created.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

Neat!

The other way to avoid triggers being invalidated at all, as mentioned in the first paragraph, is to change the INCREMENT BY value.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

Assuming the current value is 300, we want a new current value to be 500. So the difference is 200.

SQL> alter sequence SQ_TAB_A increment by 200 nocache;

Sequence altered.

SQL> select SQ_TAB_A.nextval from dual;

   NEXTVAL
----------
       500

SQL> alter sequence SQ_TAB_A increment by 1 cache 20;

Sequence altered.

With all changes in sequence, the trigger will remain valid.

SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI';

OBJECT_NAME          STATUS
-------------------- -------
TR_TAB_A_BI          VALID

If the Developer Toolbar (shown below) in your Oracle APEX application is not displayed, you can control its display by changing the Status attribute on the application’s Edit Definition page.

I’ve learned about this when somehow one of our applications suddenly did not display the Developer Toolbar. We found out later that during environment refresh, the deployment team switched the Application’s Availability Status to Unavailable before the refresh and then back to Available after it’s done – not realizing that it was initially Available with Edit Links.

No harm done – just temporary inconvenience from Developer’s standpoint.

Related Topic:

• Delayed Message When URL redirect for Oracle APEX application

Quick bullet points from reading the Transparent Performance Improvement: Real Native Compilation from the PL/SQL Enhancements in Oracle Database 11g white paper:

• The PL/SQL is an interpreted language but it is not just like other interpreted languages , e.g. BASIC, where the code is processed statement by statement at the run time. The PL/SQL code is compiled into machine code called M-Code with a target virtual machine called PL/SQL Virtual Machine (PVM), just like Java to Java Virtual Machine (JVM).
• The PVM is implemented as a set of subroutines in Oracle executables, and scans the M-Code at run time.
• The scanning detects each successive OPCODE and its OPERANDS, then calls the subroutine that implements this OPCODE with actual arguments.
• This run-time scanning of the M-Code takes some resources. This is where the PL/SQL native compilation will help with improvement.
• Compilations of both follow the same path:
  • In the interpreted mode, the M-Code is produced.
  • In the native mode, a platform-specific dynamically linkable library (DDL) (similar to .dll in Windows or .so in Unix) is produced.
    • This platform-specific DLL, at run time, calls exactly the same PVM subroutines with same arguments as would have been called by scanning the M-Code.
• In short, the performance improvement is due to the fact that the scanning effort has been moved from run time (when in interpreted mode) to compile time (when in native mode).
• Since exactly the same PVM subroutines are called with exactly the same arguments in both interpreted and native modes, the native mode is guaranteed to have exactly the same semantics as the interpreted mode.

We’re working on the installation scripts for an internal PL/SQL applications. Multiple scripts are called from the main one. To capture errors from script execution, normally the spool syntax will be used in the scripts to pipe out all executions into log files, and then later the deployment team members will examine them using find/search ORA- for any errors. This would work fine if there are only a few scripts but it becomes cumbersome when multiple scripts are involved. In addition we’d like to be able to run the scripts on either Windows and Unix platforms, handling OS file paths for multiple platforms using spool adds unnecessary layer.

The SQL Error Logging is a new feature in 11g. This simplifies the way we capture and locate error messages as information now will be stored in database table instead of OS files.

To check whether or not the Error Logging is enabled.

SQL> show errorlogging

errorlogging is OFF

To enable the Error Logging. The default table SPERRORLOG is created.

SQL> set errorlogging on

By default, the SPERRORLOG will be created under current user. In this sample, the current schema is TEST1_USER.

SQL> show errorlogging

errorlogging is ON TABLE TEST1_USER.SPERRORLOG

Here is the structure of this table SPERRORLOG.

SQL> desc SPERRORLOG
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(256)
TIMESTAMP                                          TIMESTAMP(6)
SCRIPT                                             VARCHAR2(1024)
IDENTIFIER                                         VARCHAR2(256)
MESSAGE                                            CLOB
STATEMENT                                          CLOB

To enable the Error Logging to an user defined table instead of the default SPERRORLOG table.

SQL> set errorlogging on table [schema].[table]

If non-default, the table has to be created in advance, otherwise you will get an error.

SQL> set errorlogging on table my_sperrorlog;

SP2-1507: Errorlogging table, role or privilege is missing or not accessible

Here is the syntax to create an user-defined table. If this table is created on a different schema, an insert grant to is needed for the current user.

SQL> CREATE TABLE my_sperrorlog (
username     VARCHAR(256),
timestamp    TIMESTAMP,
script       VARCHAR(1024),
identifier   VARCHAR(256),
message      CLOB,
statement    CLOB
);

SQL> set errorlogging on table my_sperrorlog

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.my_sperrorlog

Demo
Generate error #1.

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

Generate error #2.

SQL> alter table EMP add new_column VARCHR2(1);
alter table EMP add new_column VARCHR2(1)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Check the SQL Error Logging.

SQL> column username format A10
SQL> column message format A30 wrap
SQL> column statement format A30 wrap

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

TEST1_USER alter table EMP add new_column ORA-01735: invalid ALTER TABLE
VARCHR2(1)                     option

Without commit, other sessions won’t see this information.

SQL> commit;

Commit complete.

Set an unique identifier to make it easier to identify the logging record. In sample here, the identifier is set to REL1.

SQL> set errorlogging on identifier 'REL1'

Generate error #3.

SQL> alter table EMP modify ABC NOT NULL;
alter table EMP modify ABC NOT NULL
*
ERROR at line 1:
ORA-00904: "ABC": invalid identifier

Check the SQL Error Logging with the identifier=’REL1′.

SQL> SELECT username, statement, message, IDENTIFIER
FROM sperrorlog where IDENTIFIER='REL1';

USERNAME   STATEMENT                      MESSAGE                        IDENT
---------- ------------------------------ ------------------------------ -----
TEST1_USER alter table EMP modify ABC NOT ORA-00904: "ABC": invalid iden REL1
NULL                          tifier

Truncate the Error Logging to clear all existing rows in the error log table. This will clear out all records regardless of who creates them.

SQL> set errorlogging on truncate

SQL> SELECT * FROM sperrorlog;
No rows selected

There is no set errorlogging truncate only a specified identifier. Doing below is the same set errorlogging on truncate. So basically the identifier is ignored.

SQL> set errorlogging on truncate identifier 'REL1'

But you can just delete records as the regular table.

SQL> delete sperrorlog where IDENTIFIER='REL1';

SQL> commit;

Disable Error Logging. (Log off will automatically disable it.)

SQL> set errorlogging OFF

SQL> show errorlogging
errorlogging is OFF

Will it work with previous versions of Oracle database?

Since the SQL Error Logging is the feature on client, I have no reason to believe that it would not work with pre-11g databases.

The 11g SQL Plus Client to 10.2.0.1 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 10.1.0.4 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 9i Database
Unfortunately I don’t have any Oracle 9i databases. 

The 11g SQL Plus Client to 8i Database

Surprisingly, I still have one 8i database left. But, well, the 11g client no longer supports the 8i database.

ERROR:
ORA-03134: Connections to this server version are no longer supported.

Related topics:

• The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g
• 11gR2 New Feature – Alter Database Link to Change Password