Altering Oracle's SQL*Plus Help Facility

Wednesday Jun 4th 2003 by James Koopmann
Share:

Everyone needs a little help now and then. James Koopmann finds new ways to provide information to users of SQL*Plus through the Help Facility.

Everyone needs a little help now and then. If you have never used Oracle's help facility, venture with me and find new ways you can provide benefit to your users of SQL*Plus through this simple interface.

Invoking Help

The SQL*Plus facility has a very simple syntax.

HELP [topic]

In order to get the appropriate help information, you need only issue the HELP or '?' command on the command line within SQL*Plus, followed by the command or subject matter you need help on. If you do not know what you want or just want to see what is available, then for the subject matter supply the global 'TOPICS' or 'INDEX' keyword and get a listing of everything available for HELP.

SQL> HELP INDEX

Enter Help [topic] for help.

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET
 CONNECT       PASSWORD     SHOW

Not only can you supply a single topic for the HELP command, you may also supply an abbreviated topic. If the abbreviated topic also covers multiple topic areas, all of the topics will be reported. For example, if I supplied the topic 'H', under the base installation of HELP, I would get results for both HELP and HOST.

SQL> ? H

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX for a list
 of topics.
 In iSQL*Plus, click the Help button to display iSQL*Plus help.

 HELP [topic]


 HOST
 ----

 Executes a host operating system command without leaving
 SQL*Plus.

 HO[ST] [command]

 Not available in iSQL*Plus

Possible errors you may encounter are an indication of the HELP facility not being installed or an invalid topic.

SP2-0171 HELP not accessible
Cause: On-line SQL*Plus help is not installed in this Oracle instance.
Action: Contact the Database Administrator to install the on-line help.

SP2-0172 No HELP available
Cause: There is no help information available for the specified command.
Action: Enter HELP INDEX for a list of topics.

Installing Help

Typically, the HELP facility is installed along with the database during normal installation. However, if for some reason you are unable to invoke the HELP facility, it has more than likely not been installed or has been dropped from the installation. In order to install it, follow these simple steps. If these do not quite work, look inside the files within your particular HELP directory with any editor as they will be self-documenting and have changed from version to version.

1.      Change to the directory where the help install scripts reside:

cd $ORACLE_HOME/sqlplus/admin/help

2.      Log into your database with the SYSTEM account:

sqlplus system/<password>

3.      Drop any lingering help objects:

@helpdrop

4.      Create the help tables and load help information:

@helpbld helpus.sql

Beefing Up HELP

I cannot quite remember the version of Oracle, but it used to be that the HELP facility also contained quite a few topics that dealt with the DDL, DML, and other administration commands. Please e-mail me if you remember the version that this was de-supported. Anyway, I really miss some of this simple text based help and have since decided to start porting some of the more widely used command syntax back into my HELP facility on the databases that I use. There is really nothing you can't put into this HELP facility and you should seriously start thinking of giving your customers or developers as others call them, the ability to quickly access information that they need without having to page through manuals.

Adding HELP content is really just a four-step process.

1.      Load your new content through a SQL script.

2.      Rebuild the 'TOPICS' information

3.      Rebuild the 'INDEX' information

4.      Validate your information

Follow along with the simple formula.


Load Help

The content I wish to add is for the CREATE TABLESPACE command. All that is required is the TOPIC ('CREATE TABLESPACE'), a unique line number and the text for that line. After you build your topic I would suggest that you save it into its own file, in my case I called it create_tablespace.sql and then if you ever need to rebuild or port this it is very simple.

Remember you must be connected as the SYSTEM user.


Download create_tablespace.sql

Rebuild TOPICS

In order for the 'HELP TOPICS' command to work and show the new content you just added, you must rebuild the whole TOPIC content. Here are the four simple steps:

1.      Rebuild the HELP_TEMP_VIEW

CREATE OR REPLACE VIEW HELP_TEMP_VIEW (TOPIC) AS
   		SELECT DISTINCT UPPER(TOPIC) FROM HELP;

2.      Delete the TOPICS content

DELETE FROM HELP WHERE TOPIC = 'TOPICS';

3.      Rebuild the TOPICS content

INSERT INTO HELP
  SELECT 'TOPICS', ROWNUM + 10, TOPIC FROM HELP_TEMP_VIEW;

4.      Commit your work

COMMIT;

Rebuild INDEX

Unfortunately, the rebuilding of the INDEX content is not quite as easy as the TOPICS and you may decide not to provide this since the same information is available through the TOPICS content that we just rebuilt. If you do want to keep up on changing the INDEX content you should first cut and paste the content from the helpus.sql script into a file called index_content.sql and maintain all your future changes there.

Here is my current index_content.sql, after I have added the 'CREATE TABLESPACE' content. Notice that all I have added is an 'EXTRA' area at the bottom from lines 19 thru 22. You may call it anything you wish or rearrange the lines so that you can convey it to your users in the method best for them.


Download Index_content.sql

After you have built your index_content.sql script you are now ready to install it. This is a simple two step process.

1.      Delete the old INDEX content

DELETE FROM HELP WHERE TOPIC = 'INDEX';

2.      Rebuild the INDEX content through index_content.sql script.

@index

3.      Commit Your work

COMMIT:

Validate Help

Once everything is loaded and rebuilt, it is time to make sure it all works. Here is our newly built content:

SQL> HELP create tablespace

 CREATE TABLESPACE (9.2)
 ---------------------------------------
CREATE [UNDO] TABLESPACE tablespace
[DATAFILE datafile_tempfile_spec [, datafile_tempfile_spec]... ]
[{ MINIMUM EXTENT integer [ K | M ]
 | BLOCKSIZE integer [K]
 | logging_clause
 | FORCE LOGGING
 | DEFAULT [data_segment_compression] storage_clause
 | { ONLINE | OFFLINE }
 | { PERMANENT | TEMPORARY }
 | extent_management_clause
 | segment_management_clause
 }
 [ MINIMUM EXTENT integer [ K | M ]
 | BLOCKSIZE integer [K]
 | logging_clause
 | FORCE LOGGING
 | DEFAULT [data_segment_compression] storage_clause
 | { ONLINE | OFFLINE }
 | { PERMANENT | TEMPORARY }
 | extent_management_clause
 | segment_management_clause
 ]...
]
;

datafile_tempfile_spec
['filename'] [SIZE integer [ K | M ]] [REUSE] [autoextend_clause]

autoextend_clause
AUTOEXTEND { OFF | ON [ NEXT integer [ K | M ] ] [maxsize_clause] }

logging_clause
{LOGGING | NOLOGGING}

data_segment_compression
{ COMPRESS | NOCOMPRESS }

storage_clause
STORAGE
( { INITIAL integer [ K | M ]
  | NEXT integer [ K | M ]
  | MINEXTENTS integer
  | MAXEXTENTS { integer | UNLIMITED }
  | PCTINCREASE integer
  | FREELISTS integer
  | FREELIST GROUPS integer
  | OPTIMAL [ integer [ K | M ] | NULL ]
  | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
  }
  [ INITIAL integer [ K | M ]
  | NEXT integer [ K | M ]
  | MINEXTENTS integer
  | MAXEXTENTS { integer | UNLIMITED }
  | PCTINCREASE integer
  | FREELISTS integer
  | FREELIST GROUPS integer
  | OPTIMAL [ integer [ K | M ] | NULL ]
  | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
  ]...
)

extent_management_clause
EXTENT MANAGEMENT
{ DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM [SIZE integer [ K | M ]]] }

segment_management_clause
SEGMENT SPACE MANAGEMENT { MANUAL | AUTO }

ISQL*PLUS

If you like using iSQL*Plus, the HELP facility is also available through it. Just enter the HELP command in the statement area and hit the EXECUTE button.

Having to work in command line all day and not having access to any manuals is sometimes the way we must work. If you are like me and forget the exact syntax to some of the commands the HELP facility is a very quick and easy way to store the information you need so that it is readily available. In addition, you can also supply your development staff with other insightful information about the use of the Oracle database that they would not normally have documentation on. For instance, just think of the possibilities for you to develop a HELP ENV command that describes the current machine or settings needed for a particular process. The possibilities are endless.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved