Sybernet / SQLPlus
Release 3.00
Dec 7, 2002

SQLPlus

This UNIX application can be used in lieu of for Oracle's sqlplus. You might want to use this instead if you are executing stored procedures written for Sybernet. Sybernet stored procedure send their output to http.write or http.writeln. Oracle's sqlplus does not understand how to interpret this output and will discard it.

Oracle's sqlplus allows you to write your output to the package DBMS_OUTPUT. The maximum number of bytes that can be written is one million bytes, and you can't retrieve this output until your stored procedure has completed. Although SQLPlus recognizes output sent to DBMS_OUTPUT (and suffers from the same limitations), it also recognizes output sent to http.write and http.writeln. These procedures have no such limitation on the output size and can send you these results asynchronously, even while your procedure is still executing. If your stored procedure completes abnormally, your output is not lost because it has already been written to your terminal window.

Oracle's sqlplus requires you to enter a password. SQLPlus doesn't require a password. Known passwords are stored encrypted in the .SQLPlusPasswords file. If the password is not known, you will be prompted for this password before connection can take place.

Command Line Arguments

The following command line arguments can be entered in any order except for [logon] which must be the last thing specified:

Option	Description
-SILENT	This option tells SQLPlus to run in silent mode. It inhibits (among other things) gratuitous output such as the SQL prompt. If you are interested in only the output from Oracle, -SILENT should be specified.
-VERBOSE	This option is used for internal purposes only and causes SQLPlus to generate extra debugging information.
-MAXPIPESIZE	This option followed by a number allows you to set the buffer size for the DBMS_OUTPUT package. This should be set if you want to use PUT or PUT_LINE.
-DEPTH	This option followed by a number determines how many input lines will be remembered by SQLPlus.
-INPUTFILE	This option followed by a valid filename allows you to specify the input file to SQLPlus.
-OUTPUTFILE	This option followed by a valid filename allows you to specify the output file that SQLPlus will write its results. Normally, you would also specify -SILENT when using this options.
-NLS_DATE_FORMAT	This option allows you to set the default date format.
[logon]	This option allows you to specify the connect string on the command line. If specified, the logon option uses this syntax: username[/password][@connect_identifier] If you omit the password, you will be prompted for its value. If you omit the connect_identifier, the default instance is used.

Example

The following example illustrates how to connect to ORADEV as user DELTEK. Notice that no password is required:

SQLPlus deltek@oradev

Connected to oradev as deltek from ORADEV
Your sessionid is 313854
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production

SQL>

Once you are connected any command allowed by Oracle (as opposed to Oracle's sqlplus) should work the same. Oracle's sqlplus has a several options for formatting your output results. SQLPlus has none of these. SQLPlus does attempt to format your output, but you (essentially) have no control on how the results are going to look. Since the default output created by Oracle's sqlplus is nearly unreadable, this shouldn't be a problem.

SQLPlus assumes you are executing a stored procedure if your command is not a valid SQL statement or anonymous block. This means preceding your command with the keyword EXECUTE is not required, but is allowed if you wish to do so. It only assumes this if the command is not a keyword recognized by SQLPlus.

Keywords

The following keywords are recognized by SQLPlus after you have connected to Oracle:

Command	Description
CONNect	Disconnects from your current session and logs you in with new logon specs. Disconnecting also means your save text queue is saved to disk.
DEPTH	Sets or interrogates the current save text depth.
DO	Runs the specified command file.
EDIT	Allows you to edit the current save text entry.
EXECute	Allows you to execute a procedure.
EXIT	Exit from SQLPlus.
HELP	List commands recognized by SQLPlus.
QUIT	Exit from SQLPlus.
SHOW	Shows the current save text entry, a particular save text entry or all save text entries. The current save entry is denoted as 0. The entry prior to this entry entry is denoted as 1 (and so on).
REHASH	This command allows you to change the password for a username at an instance recognized by SQLPlus. You are presented with a list of username and instance combinations. To leave the password untouched, just press the enter key. To change that password, enter a new password. This command does not change the password in Oracle. Rather it allows you to change the password that is known by SQLplus. To work, you must change the password in both Oracle and SQLPlus.
REPeat	Repeats your last command. You may edit that command using <editspecs> which edits the entry prior to executing.
RESET	Terminates command input for the current save text entry.
RETrieve	Retrieve a prior command and optionally edits it and repeats it.
SQLPlus	Displays a list of command line arguments recognized by SQLPlus.
?	Executes a host operating system command without leaving SQLPlus.

Example 1

The following example illustrates how to execute a SQL command from SQLPlus:

SQL> select * from HTTP.HTTP_COLORS;

Example 2

The following example illustrates how to repeat this command:

SQL> repeat

Example 3

The following example illustrates how to edit and repeat this command:

SQL> repeat /*/name

Example 4

The following example illustrates how to retrieve the prior command, edit it and repeat it:

SQL> ret rep/*/color

Example 5

The following example illustrates how to edit the current (unfinished) command:

SQL> select *
  1> from HTTP_COLORS
  2> edit/*/NAME

SQL> select NAME
  1> from HTTP_COLORS
  2>

Example 6

The following example illustrates how to change the depth of save-text entries from the default of 10 to 30:

SQL> depth 30

External Files

The following external files are used by SQLPlus. Except for the .SQLPlusPasswords file, SQLPlus looks in your current working directory. The .SQLPlusPasswords file searches your current working directory first then looks in $ORACLE_HOME/bin.

.SQLPlusHistory             (history file)
.SQLPlusPasswords           (encrypted password file)
.SQLPlusSumlog              (log file)