Loading SQL*Plus HELP into the Database

Oracle provides scripts to load the HELP command for SQL*Plus.

See $ORACLE_HOME/sqlplus/admin/help

The schema to use is SYSTEM, not SYS.

I demonstrate
(a) How to load SQLPlus Help  into the database
(b) How to customise the Help (e.g. add new commands)

[oracle@ora11204 help]$ cd $ORACLE_HOME/sqlplus/admin/help
[oracle@ora11204 help]$ ls -l
total 84
-rwxrwxrwx. 1 oracle oracle   265 Feb 17  2003 helpbld.sql
-rwxrwxrwx. 1 oracle oracle   366 Jan  4  2011 helpdrop.sql
-rwxrwxrwx. 1 oracle oracle 71817 Aug 17  2012 helpus.sql
-rwxrwxrwx. 1 oracle oracle  2154 Jan  4  2011 hlpbld.sql
[oracle@ora11204 help]$ sqlplus -S system/oracle @helpbld.sql `pwd` helpus.sql
...
...
...
View created.


58 rows created.


Commit complete.


PL/SQL procedure successfully completed.

[oracle@ora11204 help]$ 

The 'pwd`  (note the back-quote character, not the single quote character) is a way of specifying the current directory in Unix and Linux shells.   This specifies where the help datafile is located.  helpus.sql is the help data in English (US-English).

The scripts create a table called "HELP" in the SYSTEM schema.  SQL*Plus's "HELP" command then uses this table.

Examples :

SQL> connect hemant/hemant
Connected.
SQL> help

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics.

 You can view SQL*Plus resources at
     http://www.oracle.com/technology/documentation/

 HELP|? [topic]


SQL> 
SQL> help set  

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n}
   AUTORECOVERY {OFF|ON}                    PAGES[IZE] {14|n}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         PAU[SE] {OFF|ON|text}
     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED]|EA[CH]|OFF}
   BLO[CKTERMINATOR] {.|c|ON|OFF}           RECSEPCHAR {_|c}
   CMDS[EP] {;|c|OFF|ON}                    SERVEROUT[PUT] {ON|OFF}
   COLSEP {_|text}                            [SIZE {n | UNLIMITED}]
   CON[CAT] {.|c|ON|OFF}                      [FOR[MAT]  {WRA[PPED] |
   COPYC[OMMIT] {0|n}                          WOR[D_WRAPPED] |
   COPYTYPECHECK {ON|OFF}                      TRU[NCATED]}]
   DEF[INE] {&|c|ON|OFF}                    SHIFT[INOUT] {VIS[IBLE] |
   DESCRIBE [DEPTH {1|n|ALL}]                 INV[ISIBLE]}
     [LINENUM {OFF|ON}] [INDENT {OFF|ON}]   SHOW[MODE] {OFF|ON}
   ECHO {OFF|ON}                            SQLBL[ANKLINES] {OFF|ON}
   EDITF[ILE] file_name[.ext]               SQLC[ASE] {MIX[ED] |
   EMB[EDDED] {OFF|ON}                        LO[WER] | UP[PER]}
   ERRORL[OGGING] {ON|OFF}                  SQLCO[NTINUE] {> | text}
     [TABLE [schema.]tablename]             SQLN[UMBER] {ON|OFF}
     [TRUNCATE] [IDENTIFIER identifier]     SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
   ESC[APE] {\|c|OFF|ON}                    SQLPRE[FIX] {#|c}
   ESCCHAR {@|?|%|$|OFF}                    SQLP[ROMPT] {SQL>|text}
   EXITC[OMMIT] {ON|OFF}                    SQLT[ERMINATOR] {;|c|ON|OFF}
   FEED[BACK] {6|n|ON|OFF}                  SUF[FIX] {SQL|text}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  TAB {ON|OFF}
   FLU[SH] {ON|OFF}                         TERM[OUT] {ON|OFF}
   HEA[DING] {ON|OFF}                       TI[ME] {OFF|ON}
   HEADS[EP] {||c|ON|OFF}                   TIMI[NG] {OFF|ON}
   INSTANCE [instance_path|LOCAL]           TRIM[OUT] {ON|OFF}
   LIN[ESIZE] {80|n}                        TRIMS[POOL] {OFF|ON}
   LOBOF[FSET] {1|n}                        UND[ERLINE] {-|c|ON|OFF}
   LOGSOURCE [pathname]                     VER[IFY] {ON|OFF}
   LONG {80|n}                              WRA[P] {ON|OFF}
   LONGC[HUNKSIZE] {80|n}                   XQUERY {BASEURI text|
   MARK[UP] HTML [OFF|ON]                     ORDERING{UNORDERED|
     [HEAD text] [BODY text] [TABLE text]              ORDERED|DEFAULT}|
     [ENTMAP {ON|OFF}]                        NODE{BYVALUE|BYREFERENCE|
     [SPOOL {OFF|ON}]                              DEFAULT}|
     [PRE[FORMAT] {OFF|ON}]                   CONTEXT text}


SQL> 
SQL> help show

 SHOW
 ----

 Shows the value of a SQL*Plus system variable, or the current
 SQL*Plus environment. SHOW SGA requires a DBA privileged login.

 SHO[W] option

 where option represents one of the following terms or clauses:
     system_variable
     ALL
     BTI[TLE]
     ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
        | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
     LNO
     PARAMETERS [parameter_name]
     PNO
     RECYC[LEBIN] [original_name]
     REL[EASE]
     REPF[OOTER]
     REPH[EADER]
     SGA
     SPOO[L]
     SPPARAMETERS [parameter_name]
     SQLCODE
     TTI[TLE]
     USER


SQL> 
SQL> help connect

 CONNECT
 -------

 Connects a given username to the Oracle Database. When you run a
 CONNECT command, the site profile, glogin.sql, and the user profile,
 login.sql, are processed in that order. CONNECT does not reprompt
 for username or password if the initial connection does not succeed.

 CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA|SYSASM}] [edition=value]]

 where logon has the following syntax:
     username[/password][@connect_identifier]

 where proxy has the syntax:
     proxyuser[username][/password][@connect_identifier]
 NOTE: Brackets around username in proxy are required syntax


SQL> 

Remember !  These are SQL*Plus commands, not SQL Language commands.  So you won't see help about CREATE or ALTER or SELECT and other such commands.

Since, it uses a plain-text file (helpus.sql in this case) to load the help information, it is possible to extend this.

For example, I copy helpus.sql as helpcustom.sql and add these lines into the scrip file :

INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 2, 'This Hemant''s Test Database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 3, 'A Playground database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 4, 'Running 11.2.0.4 on Linux');

INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 2, 'Test Database owned by Hemant');
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 2, 'Various Experiments by Hemant');

INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 2, 'Hemant K Chitale');
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 3, 'https://hemantoracledba.blogspot.com');

COMMIT;

and then I run the command :

sqlplus -S system/oracle @helpbld.sql `pwd` helpcustom.sql

And view the results :

SQL> connect hemant/hemant
Connected.
SQL> help dbinfo

This Hemant's Test Database
A Playground database
Running 11.2.0.4 on Linux

SQL> help ownerinfo

Test Database owned by Hemant

SQL> help who is hemant

Hemant K Chitale
https://hemantoracledba.blogspot.com

SQL>         
SQL> help startup

 STARTUP
 -------

 Starts an Oracle instance with several options, including mounting,
 and opening a database.

 STARTUP options | upgrade_options

 where options has the following syntax:
    [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
    [ OPEN [open_options] [dbname] ] |
    NOMOUNT ]

 where open_options has the following syntax:
    READ {ONLY | WRITE [RECOVER]} | RECOVER

 and where upgrade_options has the following syntax:
    [PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]


SQL> help shutdown

 SHUTDOWN
 --------

 Shuts down a currently running Oracle Database instance, optionally
 closing and dismounting a database.

 SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]


SQL> 

And, so, the SQL*Plus HELP command can be customised !

.
.
.