SQL*Plus New Features - Oracle 11g

The SQL*Plus interface has several interesting 11g release innovations, including the new error logging feature and the incorporation of default SQL*Plus settings in the SQL*Plus executable itself instead of in the traditional glogin.sql file.

SQL*Plus Error Logging

When you’re troubleshooting code errors,it’s common to use the show errors command in SQL*Plus to identify the errors in a PL/SQL statement. Other than this, there was no way tocheck code errors, and the errors were not stored for later examination. In Oracle Database 11g, there’s a new SQL*Plus command called seterror logging, which stores all errors resulting from the execution of any SQL, PL/SQL, and even SQL*Plus commands in a special error logging table.

By default the set errorlogging command causes any query errors to be written to the default table SPERRORLOG.You can specify your own table name for the error logging table, instead of using this default table name. For each error the error logging feature logs the following bits of information:

  • The username.
  • The time when the error occurred.
  • The name of the script that contains the query, if a script was used.
  • A user-defined identifier.
  • The ORA,PLS,or SP2 error message.
  • The query statement that caused the error.

By default, error logging is turned off, as you can see from the following query:

You can turn error logging on with the set errorlogging command,as shown here:

SQL>set errorlogging on;

If you issue the show errorlogging command again to ensure that error logging has been successfully turned on,you’ll see some thing interesting:

Not only does the database turn error logging on, but it also creates a new table called hr.sperrorlog to hold the error messages.The prefix to the error table is the same as the name of the schema owner who sets error logging on. In this case,we logged in as the user hr, so the error log is created in the hr schema.

The following example shows how to query the error logging table,sperrorlog, to retrieve the error messages and the SQL statements or PL/SQL code that generated those error messages.

select names from employees ORA-00904: "NAMES":
invalid identifier SQL>

Default Settings in the SQL*Plus Executable

In previous versions of the Oracle database,a site profile script for SQL*Plus,called glogin.sql,was automatically created while installing the server.DBAs used this script to configure environmental variables for all users who logged into SQL*Plus.The glogin.sql script would run first whenever any user attempted a connection to SQL*Plus, followed by the user profile script, login. sql.

In Oracle Database 11g, the glogin.sql site profile file is still installed as usual and called by SQL*Plus, but it’s blank now, as shown by the following output:

For the SQL*Plus Instant Client, you no longer need a glogin.sql file.

New SQL*Plus Connection Syntax

In Oracle Database 11g, the SQL*Plus connect command is enhanced, as shown here:

CONN[ECT] [{ logon | / } [AS {SYSOPER | SYSDBA | SYSASM}]]

Notice that the as clause now permits privileged connections to SQL*Plus from users who are given the new system privilege, Oracle has consciously separated automatic storage management (ASM) administration from regular database administration.

Enhanced SQL*Plus BLOB Support

In Oracle Database 11g, you can query and print tables and objects that contain the BLOB and BFILE datatypes.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics