Frequently Asked Questions, SQL*Plus Release 10.1
SQL*Plus -- General
SQL*Plus -- Display
SQL*Plus -- Syntax
SQL*Plus -- Security
SQL*Plus -- iSQL*Plus
Answers
What versions of the database will SQL*Plus 10.1 work with?
SQL*Plus 10.1.0.3 will work with Oracle 8.1.7 or later. Connections to earlier versions of the database will fail with the error "ORA-03134: Connections to this server version are no longer supported." Functionality of new SQL*Plus features may not be available unless SQL*Plus is used with a matching database release.
Modified: 17-AUG-04
Ref #: ID-5090
Where is Server Manager linemode (svrmgrl)?
The Server Manager linemode (svrmgrl) desupport letter
(September 1999) states that Oracle8i release 8.1.7 (the terminal release of Oracle8i)
is the last release that includes svrmgrl. SQL*Plus release 8.1 includes the database administration functionality of Server Manager. Customers migrating from svrmgrl
to SQL*Plus release 8.1 are offered a free upgrade to a restricted-use licence
for SQL*Plus. The use of SQL*Plus is limited to the following commands:
CONNECT
ARCHIVE LOG
RECOVER
SHUTDOWN
STARTUP
COLUMN <colname> FORMAT
DESCRIBE
@
@@
SPOOL
SET/SHOW commands:
AUTORECOVERY
INSTANCE
LOGSOURCE
COMPATIBILITY
ECHO
NUMWIDTH
SERVEROUTPUT
TERMOUT
SQLPROMPT
FEEDBACK
DEFINE
PAGESIZE
SQLBLANKLINES
AUTOTRACE
TIMING
SHOW ALL
SHOW ERRORS
REMARK, --, /* */
WHENEVER SQLERROR
WHENEVER OSERROR
SQL*Plus is bundled free with Oracle9i onwards,
so these restrictions do not apply from Oracle9i onwards.
Modified: 21-MAR-04
Ref #: ID-4688
Is it possible to find out the filename of the command file started with @ in SQL*Plus, like $0 in a UNIX shell script?
You can find the currently executing command file
using the SET APPINFO feature. Create this as a script and run it within
SQL*Plus
using the '@' or START commands.
set serveroutput on
set appinfo on
declare
ch_module_name varchar2(48);
ch_action_name varchar2(32);
begin
dbms_application_info.read_module(ch_module_name, ch_action_name);
dbms_output.put_line('APPINFO registered : ' || ch_module_name);
dbms_output.put_line('Current module is : ' || substr(ch_module_name,5));
end;
/
Similar code can be put in any script that you want
to track. The DBMS_APPLICATION_INFO package information is also visible
in
the V$SESSION view. If your database is read-only, the SET APPINFO information
will not be available.
Note that the default value of SET APPINFO changed to
OFF in SQL*Plus Release 9.0.1. This allows some privileged DBA operations
to be
executed when the database is not fully configured. Add SET APPINFO ON to
glogin.sql if you want the old behavior and will not be using SQL*Plus
for DBA work.
Modified: 22-MAR-04
Ref #: ID-4722
What does "Message file sp1<Lang>.msb not found" mean when I run SQL*Plus?
Sometimes when SQL*Plus is run, you see the error:
Message file sp1<Lang>.msb not found
Error 6 initializing SQL*Plus
This occurs when SQL*Plus was unable to find a message
file during program initialization. The most common cause is that ORACLE_HOME
has not been set. This prevents the message files being found and SQL*Plus
would be unable to display error messages or text required for normal operation.
Other causes are when the message file is corrupt or cannot be read.
Make sure that all the environment variables or registry
entries needed to run SQL*Plus are set. The variables are platform specific
but may include ORACLE_HOME, ORACLE_SID, NLS_LANG, ORA_NLS33, and LD_LIBRARY_PATH.
Also check that the file sp1XX.msb is present in the $ORACLE_HOME/sqlplus/mesg
or %ORACLE_HOME%\sqlplus\mesg directory. The "XX" stands for the country prefix
associated with your NLS_LANG environment variable. SQL*Plus reads only one
of the sp1XX.msb files. For example sp1ja.msb is read if NLS_LANG is JAPANESE_JAPAN.JA16EUC.
If NLS_LANG is not set, then the default (English language) sp1us.msb is used.
Check that the appropriate file is of non-zero size and that the file permissions
allow you to read it. Note the ".msb" files are binary. The contents may be
meaningless when viewed or printed. If you are unsure which language file
is being used, try unsetting NLS_LANG and run SQL*Plus to verify it can read
the sp1us.msb file.
The error message has changed in SQL*Plus Release 9.0.1
to be more informative:
Error 6 initializing SQL*Plus
Message file sp1<Lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Modified: 28-MAR-04
Ref #: ID-4828
How can I test if a database is running?
You can use the operating system return code from SQL*Plus to check
that a database is usable.
On UNIX or Linux create a script testdb.sh:
#! /bin/sh
sqlplus -s /nolog > /dev/null 2>&1 <<EOF
whenever sqlerror exit failure
connect username/password@connect_identifier
exit success
EOF
[ $? -ne 0 ] && { echo "Connection failed"; exit 1; }
echo "Connection succeeded"
Make the script executable:
chmod 700 testdb.sh
and run it:
./testdb.sh
The displayed message will indicate if the connection to the
database was successful. To minimize security problems you should use
a schema in your database that has minimal privileges.
Modified: 10-JUN-04
Ref #: ID-4834
Why is the EXIT status returned by SQL*Plus wrong under UNIX?
The problem is UNIX; the following is from 'man -s 2 wait':
If the child process terminated due to an _exit() call, the
low order 8 bits of status will be 0 and the high order 8 bits
will contain the low order 8 bits of the argument that the
child process passed to _exit(); see exit(2).
Only an eight bit number is ever returned from exit(n) to wait(pid). Mapping the sql.sqlcode to an exit status gives meaningless results. SQL*Plus may exit with SQLCODE of 942 however, the wait() command which is what your shell is using only gives you back the lower 8 bits of this number. For example:
SELECT * FROM XXX;
results in an ORA-942 error and sets the SQLCODE to 942. It is easier to calculate the final result in hexadecimal, since an eight bit value is represented by two hexadecimal digits. Decimal 942 is hexadecimal 0x3AE. If you strip off the high bits this is 0xAE or decimal 174, and the shell will return a status of 174. More subtly, an ORA-1536 which has a SQLCODE of 0x600, will return 0x00 (ie zero) to the shell. This may easily be confused with a successful return code.
For portability it is always safest to use only EXIT SUCCESS or EXIT FAILURE when terminating SQL*Plus and checking the return value.
Modified: 01-APR-04
Ref #: ID-4906
How do I create a batch file to run a script in SQL*Plus?
You can create a Windows batch script to run SQL*Plus scripts from your Windows desktop. The following batch file, runscript.bat gives an example of a batch file you could use.
runscript.bat
sqlplus your_username/your_password@your_sid @C:\temp\script.sql
notepad.exe C:\temp\output.txt
script.sql
set linesize 200
spool C:\temp\output.txt
select * from emp_details_view;
spool off
exit
This example contains a hard-coded username, password and connection information in the batch file. The batch file starts SQL*Plus, runs the script script.sql and loads the results into Notepad. The script.sql file spools the output to a file.
If you are using SQL*Plus for Windows Release 9.0.1, or SQL*Plus Release 9.2.0.1 or later on any platform, you can also store the script on a web server and run it from a URL, for example:
sqlplus your_username/your_password@your_sid
@http://my_machine/scripts/script.sql
Modified: 20-APR-04
Ref #: ID-4908
How do I change the default directory of SQL*Plus on Windows?
Start up SQL*Plus as a Windows shortcut, and set the script path:
Create a new Windows shortcut. Set the "Target" to the location of the SQL*Plus executable, for example
"C:\oracle\ora10g\bin\sqlplus.exe"
You can also include connection information, for example
"C:\oracle\ora10g\bin\sqlplus.exe scott/tiger@orcl"
This will automatically log in SCOTT into database ORCL. Set the "Start in" field to "C:\scripts". This will set the default path to run scripts.
Modified: 17-AUG-04
Ref #: ID-5092
How do I display the Euro symbol ("€") in SQL*Plus?
To check if the current font contains the Euro sign, enter the Euro sign's decimal number equivalent in SQL*Plus, Alt+0128. If it appears correctly, the chosen font contains the Euro sign, otherwise you need to try another font.
You can use the Windows Character Map utility to view the characters available in a font. Character Map also shows the decimal number equivalent for extended ASCII characters. You access the Character Map utility by selecting Start, Programs, Accessories and then clicking Character Map.
SQL*Plus Command Line
To change the font in SQL*Plus command line to a font that contains the Euro sign:
Right click in the command line interface title bar. Click Properties. The Window Preview box displays the current window's relative size on your monitor based on your font and font size selections. The Selected Font box displays a sample of the current font. Click the Font tab. Select the font size to use from the Size box. Raster font sizes are shown as width by height in pixels. TrueType font sizes are shown as height in pixels. Select the font to use from the Font box. Select the Bold Fonts check box to use a bold version of the font.
The procedure is the same for releases 8.1.7 onwards.
SQL*Plus for Windows
To change the font in SQL*Plus for Windows release 8.1.7 and 9.0.1 to a font that contains the Euro sign:
Select Run from the the Start menu and then enter regedit in the Open field. Click the OK button to start the Registry Editor. The Registry Editor is displayed. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. Click New String Value in the Edit menu. A new string value, with the default name, NewValue #1 is created at the bottom of the right pane of the Registry Editor. The default name of the new string value is selected ready for you to replace with the name you want. Enter SQLPLUS_FONT as the name of the new font face string value.
or
Enter SQLPLUS_FONT_SIZE as the name of the new font size string value.
Click Modify from the Edit menu or press Enter again to display the Edit String dialog. Enter the font name you want to use, such as Courier New, in the Value Data field. SQL*Plus will use the new font the next time you start the SQL*Plus Windows GUI. or enter the font size you want to use in pixels, such as 14, in the Value Data field. SQL*Plus will use the new font size the next time you start a SQL*Plus Windows GUI.
SQL*Plus release 9.2.0.1 contains a new registry entry, SQLPLUS_FONT_CHARSET. This entry allows you to select the subset of fonts. The values you can choose from are:
DEFAULT
SYMBOL
SHIFTJIS
HANGEUL
GB2312
CHINESEBIG5
OEM
JOHAB
HEBREW
ARABIC
GREEK
TURKISH
VIETNAMESE
THAI
EASTEUROPE
RUSSIAN
MAC
BALTIC
Create the registry entry for SQLPLUS_FONT_CHARSET in the same was as described for SQLPLUS_FONT and SQLPLUS_FONT_SIZE.
Modified: 01-APR-04
Ref #: ID-4912
Is it possible to change the displayed font type in SQL*Plus on Windows? How can I, for example, display Chinese fonts?
SQL*Plus command line and SQL*Plus for Windows rely on the operating system to determine the character set to be used. You should change the default locale of Windows to the language you want to display, or you can follow the steps below. Note:179133.1 on Metalink has further information.
Windows NT
Under Windows NT, locate the key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\FontSubstitutes
Remove existing mappings for Fixedsys.
Add a new String Value named "Fixedsys,0" (without quotes) with value "<Chinese font>,0" (without quotes). We assume the Chinese font is internally declared as an ANSI font. If it is declared as Big5 try:
Fixedsys,0=<Chinese font>,136
Windows 95
On Windows 95 do the same as described for Windows NT in the WIN.INI file in section [FontSubstitutes]. A database character set that supports Chinese characters and an appropriate value for NLS_LANG would be useful, for example, ZHT16BIG5 or ZHT16MSWIN950.
Warning
Modifying the registry can be dangerous. Microsoft do not recommend it, and Oracle takes no responsibility for problems arising from using Registry Editor.
This method maps a standard raster font to a non-standard TrueType font. Effect on various applications (including Oracle) is unpredictable.
You must reboot after each change to see any change.
Do not use a font which is not fixed-width. Replacing a fixed-width font with a proportional one will most probably have tragic effect on output alignment.
Modified: 26-SEP-04
Ref #: ID-4914
Why does the width of a column change when I run a report on two different databases?
In SQL*Plus it is recommended to use an explicit COLUMN
command to set a desired field width. This is specially true when the
column is a SQL function. The default column width displayed may not
be the same in all runtime situations.
SQL*Plus calculates the column display width from the buffer size
the RDBMS stores the data in. This may vary from database to
database.
The internal buffer size for SQL functions has been known to change
from version to version of the RDBMS for code optimization and after
bug fixes. (See bugs 900341, 173666, 2992405).
Setting CURSOR_SHARING in the init.ora can cause SQL function
columns to increase in width. The Oracle9i Database Performance
Tuning Guide and Reference says "Setting CURSOR_SHARING to SIMILAR or
FORCE causes an increase in the maximum lengths (as returned by
DESCRIBE) of any selected expressions that contain literals (in a
SELECT statement). However, the actual length of the data returned
does not change".
Some NLS environment settings also require the RDBMS and/or
SQL*Plus to allocate extra storage in case multibyte characters are
used. This commonly manifests itself as a double-width field when
NLS_LANG is set differently on a particular client machine.
Modified: 10-JUN-04
Ref #: ID-5508
How do I stop the "X rows selected" and "PL/SQL procedure successfully completed." messages appearing?
Use SET FEEDBACK OFF.
SET FEEDBACK can also be given a value indicating the number of rows a query must return before "X rows selected" is displayed. The default value is 6 rows.
Modified: 10-JUN-04
Ref #: ID-5512
How do I replace "CONNECT INTERNAL" with "/ AS SYSDBA"?
CONNECT INTERNAL has been desupported in Oracle9i and replaced with:
The "/ AS SYSDBA" and "/ AS SYSOPER" syntax can be used
with the CONNECT command within SQL*Plus. It can also be used on the command
line when SQL*Plus is started, as long as the complete connection string is
the first program argument. On many platforms this achieved by putting quotes
around it, for example:
SQLPLUS "/ AS SYSDBA" @myscript
Alternatively, you can run "SQLPLUS /NOLOG" and then use
the CONNECT command. The syntax "/NOLOG" is SQL*Plus specific, and is valid
only on the command line. It starts SQL*Plus, and gives you a prompt, but
does not create a connection to a database server. It cannot be used with
the CONNECT command. (If you are connected to a server and want to terminate
the connection, use the DISCONNECT command.) For example, if you want to run
a script that requires being connected AS SYSDBA you can use:
-
run "SQLPLUS /NOLOG @your_script.sql",
and then use "CONNECT / AS SYSDBA"
in the script
-
run the script "SQLPLUS @your_script.sql"
where the very first line of the script is"/
AS SYSDBA"
Modified: 20-APR-04
Ref #: ID-4826
What is the difference between a SQL*Plus command, a SQL command and a PL/SQL command?
SQL*Plus divides commands into three categories for processing and parsing:
Local commands
These are SQL*Plus commands that are implemented in the SQL*Plus program. Mostly, these commands are not sent to the server for processing. Examples are:
-
COMPUTE
-
COPY
-
RECOVER
-
REM
-
SET LINESIZE
These commands generally span one line, unless the SQL*Plus continuation character (-) is used at the end of the line. These commands are documented in the SQL*Plus User's Guide and Reference.
Server executed commands (no embedded semicolons)
This group covers most of the SQL commands. These are statements that are sent to the server for processing. They use the SQL language to manipulate data in the database. In SQL*Plus, these statements are terminated with either a semi-colon (;) or a slash (/) by itself or on a new line. Examples are:
-
CREATE TABLE
-
INSERT
-
SELECT
These commands are documented in the SQL Reference.
Server executed commands (allow embedded semicolons)
These are mostly PL/SQL statements using the PL/SQL language. They are sent to the server for processing. Because the PL/SQL language allows semi-colons, SQL*Plus requires each PL/SQL statement to be terminated with a slash (/).
Some other SQL statements that allow semicolons or have special requirements must also be be terminated with a slash. Examples are:
-
BEGIN ... END;
-
CREATE JAVA
These commands are documented in the PL/SQL User's Guide and Reference, or in the SQL Reference.
Modified: 28-MAR-04
Ref #: ID-4836
When should I use the COPY command?
There are many ways to copy data between tables. The combination of requirements and technical issues will make each method of copying data useful in specific circumstances. You should benchmark each method and use the one that satisfies you most.
For performance reasons, you may want to avoid using COPY if you are copying data from the same server that it is being inserted into. The COPY command requires copying data from the server to the client (that is, SQL*Plus) and back to the server. Depending on your network and machine capacity this could be much slower than using CREATE ... AS SELECT ... . When copying data to a second database, you may want to benchmark the direct path load capability of SQL*Loader.
The COPY command has not been enhanced to handle the new datatypes implemented by Oracle since Oracle8i release 8.0.
Modified: 20-APR-04
Ref #: ID-4838
How do I run a SELECT statement only if some condition is met?
The REF CURSOR feature of SQL*Plus allows you to do all your logic in PL/SQL, and still format the results in SQL*Plus. For example:
ACCEPT promptvar CHAR DEFAULT '1' -
PROMPT "Which report 1,2 or 3? [1]: "
ACCEPT datevar DATE DEFAULT '01-Sep-82' -
PROMPT "Enter start date [01-Sep-82]: "
var a refcursor
begin
if '&promptvar' = '1' then
open :a for select ename, hiredate
from emp
where hiredate > '&datevar';
elsif '&promptvar' = '2' then
open :a for select ename, empno, hiredate
from emp
where hiredate > '&datevar';
elsif '&promptvar' = '3' then
open :a for select ename, deptno, hiredate
from emp
where hiredate > '&datevar';
else
open :a for select 'No report run' NRR from dual;
end if;
end;
/
print a
For more information, see the chapter entitled "Manipulating Commands", or the VARIABLE command in the SQL*Plus User's Guide and Reference.
Modified: 28-MAR-04
Ref #: ID-4842
How can I format DBMS_OUTPUT since SQL*Plus strips leading spaces?
For example, the script:
set serveroutput on
begin
dbms_output.put_line('FIRST LINE');
dbms_output.put_line(' <- LEADING SPACE CHARACTERS');
dbms_output.put_line('LAST LINE');
end;
/
gives:
FIRST LINE
<- LEADING SPACE CHARACTERS
LAST LINE
Use '|' as the first character of every put_line() call, and create a 'border' or 'margin' for all output. Alternatively use a tab for the first character. This is not stripped.
SQL*Plus also allows your output to be formatted using the FORMAT clauses that are part of the SET SERVEROUTPUT command (that is, WRAPPED, WORD_WRAPPED, or TRUNCATED). For example, to set the output to WORD_WRAPPED, enter
SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SQL> SET LINESIZE 20
SQL> BEGIN
2 > dbms_output.put_line('If there is nothing left to do');
3 > dbms_output.put_line(' shall we continue with plan B?');
4 > end;
5 > /
If there is nothing
left to do
shall we continue
with plan B?
To set the output to TRUNCATED, enter
SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED
SQL> SET LINESIZE 20
SQL> BEGIN
2 > DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
3 > DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
4 > END;
5 > /
If there is nothing
shall we continue wi
For more information about using the SET SERVEROUTPUT command, see the SQL*Plus User's Guide and Reference.
Modified: 01-APR-04
Ref #: ID-4894
Why does the minus sign (-) in my SQL statement disappear in SQL*Plus command line?
SQL> select 200 -
> 100 from dual;
select 200 100 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
The minus sign (-) is the SQL*Plus line continuation character. SQL*Plus does not identify the statement as a SQL statement until after the input processing has joined the lines together and removed the minus/hyphen. A similar issue can occur if the continuation character appears at the end of a comment line. The solutions is to move the minus from the end of the line. (References: bugs 81520 and 15088.)
Modified: 01-APR-04
Ref #: ID-4896
Why can't I end a REM comment with a minus (-) sign in SQL*Plus command line?
If you end a REM comment with a minus (-) sign, SQL*Plus treats it as if it were a continuation character and displays the secondary prompt. An example is:
SQL> REM -------------------------------
>
SQL>
The preferred solution is to replace the REM comment with a '--' comment, for example:
SQL> -----------------------------------
SQL>
Modified: 01-APR-04
Ref #: ID-4898
Why can't I use '&' in a comment, put comments after a semicolon, end a comment with a semicolon, or put a comment in the middle of command keywords?
SQL*Plus does not have a SQL or PL/SQL command parser. This affects the way SQL*Plus identifies statement types, line continuation characters and command termination characters. SQL*Plus scans the first few keywords of each new statement to internally classify commands into one of three types:
SQL*Plus-type commands, such as SET, or SPOOL SQL-type commands such as INSERT, or DROP PL/SQL-type commands such as CREATE OR REPLACE PROCEDURE, or BEGIN (In reality this covers procedural commands that can contain embedded semicolons)
SQL*Plus uses these classifications to decide how to recognize when a command has been completely entered and is ready for processing. By default the terminators for each statement type are:
SQL*Plus-type commands end with a new line SQL-type commands end with either a semicolon ';' or a slash '/' PL/SQL-type commands end with a slash '/'
SQL*Plus also scans for the line continuation character ('-') and the substitution character (by default '&') before executing commands. For more information on the substitution and termination characters, see the SET DEFINE and SET SQLTERMINATOR commands in the SQL*Plus User's Guide and Reference.
After SQL*Plus has read the statement and handled substitution characters, then the statement is executed. SQL*Plus commands are executed in the SQL*Plus client program. All SQL and PL/SQL statements are sent to the database server for processing.
Here are some known issues with comments in SQL statements within SQL*Plus.
SQL> create or replace
2 /* hello */
3 procedure hello
4 as
5 begin
6 null;
Warning: Procedure created with compilation errors.
SQL*Plus doesn't realize this is a PL/SQL-type command and submits the block to the server when it sees the ';', not waiting for a '/'. The SHOW ERRORS command may not be able to display the errors for this package unless the name is explicitly entered, for example:
SHOW ERRORS PROCEDURE HELLO
Reference: bug 907385.
SQL> SELECT 'Y' FROM DUAL; -- Testing
2 ;
SELECT 'Y' FROM DUAL; -- Testing
*
ERROR at line 1:
ORA-00911: invalid character
Reference: bugs 92290 and 54897.
SQL> select *
2 -- comment;
-- comment
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
Reference: bug 404651.
SQL> select * from /* this & that */ dept;
Enter value for that:
old 1: select * from /* this & that */ dept
new 1: select * from /* this */ dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Use SET DEFINE OFF to prevent scanning for the substitution character. Reference: bug 13199.
Modified: 01-APR-04
Ref #: ID-4900
Why don't variables in my TTITLE substitute correctly?
If '&' is used inadvertently or incorrectly to prefix TTITLE variables, it is possible to get double substitution. This is dependent on the data entered, and is easily overlooked when scripts are written.
The resolution is to delete the prefix character '&' from substitution variables in TTITLE or BTITLE commands. For example, if your problem TTITLE command is:
TTITLE LEFT 'Urgent: ' &2 ' Days High: ' &myvar
you may need to change it to:
TTITLE LEFT 'Urgent: ' 2 ' Days High: ' myvar
The guidelines for variables in titles are:
If you want every title to have the same value for a variable, either use the '&' prefix and put the variable inside quotes:
ACCEPT mycustomer char prompt "Enter your company name: "
TTITLE LEFT 'Report generated for company &mycustomer'
SELECT last_name, job_id FROM employees ORDER BY job_id;
or do not use '&' and do not put it in quotes:
ACCEPT mycustomer char prompt "Enter your company name: "
TTITLE LEFT 'Report generated for company ' mycustomer
SELECT last_name, job_id FROM employees ORDER BY job_id;
The first option is marginally more efficient.
If you want each title to have data that is taken from that report page (for example, using COLUMN NEW_VALUE or OLD_VALUE variables), do not use the '&' prefix for the variable and do not put the variable inside quotes.
COLUMN job_id NEW_VALUE ji_nv NOPRINT
BREAK ON job_id SKIP PAGE
TTITLE LEFT 'Employees in job: ' ji_nv
SELECT last_name, job_id FROM employees ORDER BY job_id;
Using '&' in titles most commonly causes a problem with numeric variable names (that is, the SQL*Plus script parameters) that have small numeric values. SQL*Plus substitution variables (also known as '&' variables) are expanded before each command is executed. When this happens with a TTITLE or BTITLE command, the resulting string is stored as the title text. TTITLE variables need to be re-substituted for each page of query results. This is so the current COLUMN NEW_VALUE and OLD_VALUE substitution variables are displayed on each page, customizing each title for the results displayed on its page. Any non-quoted, non-keyword in a TTITLE is checked when the page is printed to see if it is a variable. If it is, its value is printed, if not then the word is printed verbatim.
This means that if you do use '&myvar' in a TTITLE command, and the text substituted for it can be interpreted as another variable name, then you will get double variable substitution. For example, the script:
DEFINE myvar = scottsvar
TTITLE left &myvar
DEFINE scottsvar = Hello
SELECT * FROM dual;
causes the text "left scottsvar" to be stored as the title. When the title is printed on each page of the query this string is re-evaluated. The word "scottsvar" is treated as a variable and the query output is:
Hello
D
-
X
Modified: 01-APR-04
Ref #: ID-4902
How do I put the current date in a spool file name?
column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol
from dual;
spool &mydate._report.txt
-- my report goes here
select * from mytable;
spool off
Note the first period in the SPOOL command tells SQL*Plus that the variable name has finished. You can use this technique to build up any string for the file name.
Modified: 01-APR-04
Ref #: ID-4904
How do I set the SQL*Plus command prompt to show the connect identifier of the database to which I'm connected?
In SQL*Plus 9.2.0.1, this is easy. There is a new DEFINE variable called _CONNECT_IDENTIFIER. This variable contains the connect identifier as supplied by you when you make the connection. You can use this variable to set the SQL*Plus prompt to the the connect identifier of the database you're connected to by entering:
SET SQLPROMPT '&_CONNECT_IDENTIFIER > '
You can also add this to the glogin.sql or login.sql files.
In SQL*Plus releases earlier than 9.2.0.1 it is a little more complicated, but it can be done. Here is one way.
Edit the SQL*Plus login file to contain the following.
define gname = "Unknown DB"
column global_name new_value gname
set termout off
select substr(global_name, 1,
decode(instr(global_name, '.',
0, length(global_name),
instr(global_name, '.')-1) global_name
from global_name;
set termout on
set sqlprompt '&gname> '
If you edit the glogin.sql file, this will effect all users when they first log in. If you edit your login.sql file instead, this will take effect only for you. Your SQL*Plus prompt should now show the global database name when you start up SQL*Plus.
The glogin.sql and login.sql files are only read once, when a user starts SQL*Plus. To refresh the global database name in the SQL*Plus prompt, you will need to run the login file again.
Modified: 01-APR-04
Ref #: ID-4910
How do I change the DOCTYPE tag using SET MARKUP HTML?
The SQL*Plus SET MARKUP HTML command generates the DOCTYPE tag set to HTML version 4.0 Transitional. You can make SQL*Plus generate your own HTML tags, but you cannot change the DOCTYPE tag.
Modified: 01-APR-04
Ref #: ID-4922
How do I see which output came from which statement in my script?
Use the SET ECHO ON command. In the output, each command will be displayed before its results.
Note: In iSQL*Plus Release 9.0.1, the first line of each SQL statement will be echoed twice. This problem does not occur in iSQL*Plus Release 9.2.0.1 or later.
Modified: 02-APR-04
Ref #: ID-4936
How do I use a semicolon in a text string?
If you try inserting
insert into mytable values ('begin
myprocedure();
end');
and get the error
ERROR:
ORA-01756: quoted string not properly terminated
a solution is to turn off recognition of semicolons as the statement terminator:
set sqlterminator off
insert into mytable values ('begin
myprocedure();
end')
/
When SQLTERMINATOR is OFF you must use a slash to execute or the
BLOCKTERMINATOR (e.g. a period) to stop entering statements.
Just make sure no embedded line contains only a
slash. Or use the ESCAPE character before the slash such as this example which divides three with two:
set sqlterminator off
set escape \
insert into mytable values ('begin
:c := 3
\/
2;
end')
/
Modified: 22-APR-04
Ref #: ID-5130
How can I stop being prompted "Enter value for X" when I use "&"?
Use SET DEFINE OFF.
More information and examples are in the SQL*Plus Substitution Variables examples
Modified: 10-JUN-04
Ref #: ID-5510
Is it possible for the INVALID COMMAND error message to occur when using standard SQL such as SELECT, INSERT, even though the PRODUCT_USER_PROFILE table is empty?
This indicates your configuration is not quite correct. These hints may help resolve the problem:
-
Make sure that all the synonyms etc. that lie behind the PRODUCT_USER_PROFILE
table are valid. (See pupbld.sql). Maybe you think the table is empty
but in fact the SYSTEM.PRODUCT_PRIVS table has data..
-
Triple check that you are looking at the PRODUCT_USER_PROFILE table (etc.)
in the same DB that the application connects to.
-
Make sure that pupbld.sql has only ever been run as SYSTEM (and not SYS),
and cleanup any mess made if not.
-
Rerun pupbld.sql (as SYSTEM).
If you find entries in the table, remove/update the rows corresponding
to the userid given by SELECT USER FROM DUAL; for your operating system
authenticated (formerly known as ops$) log ins.
Modified: 20-APR-04
Ref #: ID-4844
How do I restrict access to commands?
You can restrict access to some SQL*Plus and SQL commands and PL/SQL statements through the PRODUCT_USER_PROFILE table. For example, to disallow the user "HR" from entering the "DROP" command, enter:
INSERT INTO PRODUCT_USER_PROFILE
VALUES ('SQL*PLUS', 'HR', 'DROP', NULL, NULL, 'DISABLED', NULL, NULL);
See the "Security" Appendix of the SQL*Plus User's Guide and Reference for more information.
Modified: 28-MAR-04
Ref #: ID-4846
Which web browsers can I use with iSQL*Plus?
You can use iSQL*Plus with most web browsers. In all versions of iSQL*Plus, the web browser must support HTML 4.0 Transitional at a minimum. Note that users of iSQL*Plus may construct arbitrary output text. If this includes HTML tags then the version of HTML required to run any particular script may differ.
iSQL*Plus is supported in the following web browsers
in Oracle Database 10g:
| Operating System
|
| |
Windows
|
Solaris
|
Mac OS 9
|
Mac OS X
|
PocketPC
|
| Netscape Navigator 4.x |
4.7
|
4.7
|
|
|
|
| Netscape 7.x |
7.0.2
|
|
|
|
|
| Internet Explorer |
5.0, 5.5, 6.0
|
|
5.0
|
5.2.2
|
2000, 2002
|
| ICE Browser |
5.0
|
|
|
|
|
Modified: 20-APR-04
Ref #: ID-4716
Which web servers can I use with iSQL*Plus?
iSQL*Plus 10.1 is certified to run on Oracle Containers for Java (OC4J) delivered with Oracle Database 10g. You cannot use any other application server.
Modified: 20-APR-04
Ref #: ID-4720
How do I configure iSQL*Plus?
iSQL*Plus 10.x is automatically installed and configured. The URL for your iSQL*Plus server is:
http://<your_machine><port>/isqlplus
http://<your_machine><port>/isqlplus/dba
The port number is likely to be 5560.
If this URL does not display the iSQL*Plus log in page, check that iSQL*Plus has been started.
Modified: 28-MAR-04
Ref #: ID-4810
Why do some iSQL*Plus database sessions continue to exist?
If iSQL*Plus 10.x users don't use the "Log Out" button, sessions may be left open to the server for a short while. We recommend you set an IDLE_TIME in the Oracle schema profile of your users. See the Oracle ALTER PROFILE command for details on user profiles.
iSQL*Plus allows you to specify an idle period, independent of the database profile. iSQL*Plus sessions for users who have been idle for the specified time are removed.
The values are set in $ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/isqlplus/isqlplus/WEB-INF/web.xml. The default setting expires users who have been ide for 15 minutes. It can be set to any value from 1 to 1440 minutes.
Modified: 28-MAR-04
Ref #: ID-4814
How can I more easily copy and paste the results from iSQL*Plus?
iSQL*Plus can display results in an HTML table, or as preformatted text. Setting the output to be displayed in preformatted text will make the output easier to copy and paste. To set the output to be displayed in preformatted text, enter
SET MARKUP HTML PREFORMAT ON
To set the output to be displayed in an HTML table, enter
SET MARKUP HTML PREFORMAT OFF
You can also set whether the output is displayed in an HTML table or as preformatted text using Preferences > System Configuration > Script Formatting > Preformatted Output.
Modified: 04-APR-04
Ref #: ID-4954
How do I save or spool script output in iSQL*Plus?
You can save script output to a file by changing Preferences > Interface Options > Output Location. You cannot use the SPOOL command in iSQL*Plus.
Modified: 04-APR-04
Ref #: ID-4958
How do I start iSQL*Plus from the command line or a URL?
iSQL*Plus is a browser-based interface. You can start iSQL*Plus from a URL, for example
http://machine_name.domain:5560/isqlplus/dba/dynamic?
userid=hr/your_password@oracle10g%20as%20sysdba
&script=ftp://machine_name2.domain/script.sql&name=*&salary=12000
Note, the example given includes line breaks for display purposes only. There should be no line breaks in the URL to start iSQL*Plus.
See Starting SQL*Plus from a URL in the SQL*Plus User's Guide and Reference for more information.
Modified: 04-APR-04
Ref #: ID-4960
How do I start and stop iSQL*Plus?
To start iSQL*Plus, open a command line window and enter
$ORACLE_HOME/bin/isqlplusctl start
To stop iSQL*Plus, open a command line window and enter
$ORACLE_HOME/bin/isqlplusctl stop
On Windows platforms, you can also use the Windows service to start and stop iSQL*Plus. The service is set to start automatically on installation and when the operating system is started.
Modified: 04-APR-04
Ref #: ID-4962
How can I check whether iSQL*Plus is started?
To check whether the iSQL*Plus process is running on UNIX platforms, enter the following command
ps -eaf |grep java
The iSQL*Plus process looks something like the first process in the list.
oracle 18488 1 0 16:01:30 pts/8 0:36 $ORACLE_HOME/jdk/bin/java -Djava.awt.headless=true
-Doracle.oc4j.localhome=/ora
oracle 18497 25611 0 16:02:58 pts/5 1:08 $ORACLE_HOME/jdk/bin/java -server -Xmx512M
-XX:MaxPermSize=64m -XX:MinHeapFreeR
oracle 18514 18074 0 16:04:39 pts/9 0:00 grep java
To check whether the iSQL*Plus process is running on Windows platforms, open the Windows services dialog from the Control Panel, and check the status of the iSQL*Plus service. The iSQL*Plus service will be called OracleOracle_Home_NameiSQL*Plus.
Modified: 04-APR-04
Ref #: ID-4964
I cannot start iSQL*Plus on UNIX-based operating systems. What can I do to investigate the the problem?
It may be that iSQL*Plus did not shut down gracefully and the RMI and HTTP ports are still in use, even though the iSQL*Plus process has stopped.
To check whether the iSQL*Plus process is running, enter the following command:
ps -eaf |grep java
The iSQL*Plus process looks something like the first process in the list.
oracle 18488 1 0 16:01:30 pts/8 0:36 $ORACLE_HOME/jdk/bin/java -Djava.awt.headless=true
-Doracle.oc4j.localhome=/ora
oracle 18497 25611 0 16:02:58 pts/5 1:08 $ORACLE_HOME/jdk/bin/java -server -Xmx512M
-XX:MaxPermSize=64m -XX:MinHeapFreeR
oracle 18514 18074 0 16:04:39 pts/9 0:00 grep java
Try running $ORACLE_HOME/bin/isqlplusctl stop. If this still doesn't kill the process, then you'll need to stop it manually, using:
kill -9 <process_id>
Try running $ORACLE_HOME/bin/isqlplusctl start. If this still doesn't start the process, then you'll need to clear the RMI and HTTP ports manually. Here is one way of doing this, using the lsof utility. This utility many not be available on your machine.
First, see if the iSQL*Plus RMI and HTTP ports are being used by the old process.
lsof -i:5580
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 10872 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10874 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10876 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10878 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10880 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10882 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10884 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10886 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10888 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10890 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10972 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 10998 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 11002 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 11004 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
oracle 18069 oracle 13u IPv4 0x30001e3d578 0t0 TCP *:5580 (LISTEN)
lsof -i:5560
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 10872 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10874 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10876 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10878 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10880 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10882 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10884 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10886 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10888 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10890 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10972 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 10998 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 11002 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 11004 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
oracle 18069 oracle 15u IPv4 0x300031d7ba8 0t0 TCP *:5560 (LISTEN)
In this case, there are a number of processes using the ports. To kill them all, run:
lsof -i:5560 | grep -v PID | awk '{print $2}' | xargs kill -9
lsof -i:5580 | grep -v PID | awk '{print $2}' | xargs kill -9
Try running $ORACLE_HOME/bin/isqlplusctl start again. iSQL*Plus should now be running.
Modified: 04-APR-04
Ref #: ID-4966
Why do I get the error "The parameter is incorrect" when I start or stop the iSQL*Plus Windows service?
This only occurs in iSQL*Plus Release 10.1.0.2 on Windows NT 4.0. This is a known problem (bug 3451313) and will be fixed in an upcoming patch set. As a workaround, you can start and stop iSQL*Plus using the command line. To start iSQL*Plus, enter
%ORACLE_HOME%/bin/isqlplusctl start
To stop iSQL*Plus, enter
%ORACLE_HOME%/bin/isqlplusctl stop
Modified: 04-APR-04
Ref #: ID-4968
How do I connect as SYSDBA or SYSOPER in iSQL*Plus?
To log into iSQL*Plus as SYSDBA or SYSOPER, you need to set up a user in the OC4J user manager, and grant access to the webDba role for the user.
Start the JAZN shell from the correct directory:
cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus
$JAVA_HOME/bin/java -Djava.security.properties=$ORACLE_HOME/oc4j/j2ee/home/config/jazn.security.props
-jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user "iSQL*Plus DBA/admin" -password admin_password -shell
Create a user:
JAZN> adduser "iSQL*Plus DBA" username password
Grant the user access to the webDba role:
JAZN> grantrole webDba "iSQL*Plus DBA" username
Exit the JAZN shell:
JAZN>exit
Test iSQL*Plus DBA access by entering the iSQL*Plus DBA URL in your web browser:
http://[machine_name][:port]/isqlplus/dba
A dialog is displayed requesting authentication for the iSQL*Plus DBA URL. Log in as the user you created above.
Notes
$JAVA_HOME is the location of your JDK (1.4 or above). It is recommended to be set to $ORACLE_HOME/jdk, but you may use another JDK if you prefer.
admin_password is the password for the iSQL*Plus DBA realm administrator user, admin. The password for the admin user is set to 'welcome' by default. You should change this password as soon as possible.
You may need to restart iSQL*Plus for the changes to take effect. This is a known problem in iSQL*Plus 10.1.0.2 and should be fixed in an upcoming patch set.
Modified: 07-APR-04
Ref #: ID-5010
How do I get iSQL*Plus?
iSQL*Plus is available with:
- Oracle9i Release 9.0.1.1 for Windows only
- Oracle9i Release 9.2.0.1 on most operating systems,
including Windows, Solaris, HP-UX, and Linux
- Oracle10g Release 10.1 on all operating systems
You can download Oracle9i Database Server and Oracle
Database 10g
from the OTN Downloads area.
After installation connect to iSQL*Plus
using:
http://<your_machine><:port>/isqlplus
Modified: 04-APR-04
Ref #: ID-4708
Does iSQL*Plus run on Oracle9i Internet Application Server (9iAS) or Oracle10g Internet Application Server?
No, iSQL*Plus is currently only supported on the Oracle9i Database Server and Oracle Database 10g releases.
Modified: 20-APR-04
Ref #: ID-4710
I installed the Oracle Client. I can see command line SQL*Plus, but where is iSQL*Plus?
iSQL*Plus is only available on the Oracle9i or Oracle10g Server installations, not in the Client installations.
Modified: 20-APR-04
Ref #: ID-4712
Why can't I use @ or SPOOL in iSQL*Plus?
In iSQL*Plus you can use the '@' command with files that can be read over HTTP. You cannot reference files from a local file system. The basic security model of iSQL*Plus is the security model of the command line -RESTRICT option (introduced with SQL*Plus 8.1.7). See SQL*Plus Release 8.1.7 User's Guide and Reference. This prevents @ and a few other commands from accessing local files because in a 3-tier model (the tiers are: thin browser, iSQL*Plus engine, and database), the middle tier does not have access to a user's file system.
A number of other commands are not available in iSQL*Plus. These are generally very obsolete or have no place in a Web-based environment (for example the buffer editing commands).
Modified: 28-MAR-04
Ref #: ID-4840
Why does my iSQL*Plus Dynamic Report timeout?
If your network connection is slow or your report takes a long time to process, your browser may timeout the connection before any results are returned. It may be necessary to reduce the time that the script takes to execute, or for it to return output data sooner. Using SQL optimizer hints like FIRST_ROWS may help.
Modified: 01-APR-04
Ref #: ID-4890
How can I get my EXPLAIN PLAN to format correctly in iSQL*Plus?
Some scripts like the utlxpls.sql script for tuning query execution use custom whitespace padding for indentation. In HTML, multiple whitespaces are ignored. This makes some reports look awkward when run in iSQL*Plus. There are two ways around this:
Rewrite the report to take advantage of HTML formatting. Execute SET MARKUP HTML ON PREFORMAT ON before running the report.
In iSQL*Plus Releases 9.0.1 and 10.1, the latter workaround is useful with the SET AUTOTRACE command. Future versions of iSQL*Plus may not need this workaround for SET AUTOTRACE
Modified: 01-APR-04
Ref #: ID-4892
Is there a login.sql equivalent for iSQL*Plus?
The normal glogin.sql in the %ORACLE_HOME%\sqlplus\admin directory of the iSQL*Plus engine is read when you first connect, but there is no per-user login.sql equivalent.
Modified: 01-APR-04
Ref #: ID-4924
How can I run an iSQL*Plus Dynamic Report against a remote database?
If the remote server does not have an entry in the tnsnames.ora file on the machine with the iSQL*Plus server, you can use the full database connect string. For example to create a link in an HTML page that will run an iSQL*Plus dynamic report use:
<href='http://mymachine.domain/isqlplus?userid="Scott/tiger@
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.domain)
(PORT=1521)))(CONNECT_DATA=(SID=MYSID)))"
&script=http://mymachine.domain/scripts/report.sql'>
iSQL*Plus Dynamic Report</a>
Note a single quote is used for the href string and a double quote is used for the embedded userid string. There must not be any space in the userid or connection identifier.
Modified: 02-APR-04
Ref #: ID-4930
Why does my single column take the whole width of the screen in iSQL*Plus?
The default width for tables is set to 90% of the screen. You can change this by using the TABLE option of the SET MARKUP HTML command. For example to use bordered tables and allow the browser to choose the table width enter:
set markup html table 'border="1"'
select empno from emp;
Modified: 02-APR-04
Ref #: ID-4934
Is iSQL*Plus a separate executable that is distinct from command-line SQL*Plus?
iSQL*Plus is a web-based interface to the SQL*Plus command line engine. SQL*Plus performs the connection to the database and fetches the results, then returns the results to iSQL*Plus. Any SET commands you issue in SQL*Plus should have the same behaviour in iSQL*Plus. In fact, many of the SET commands are used to control the display of the output in iSQL*Plus.
Modified: 04-APR-04
Ref #: ID-4948
Is the HTML output created by SET MARKUP HTML the same as created by iSQL*Plus?
The output from SET MARKUP HTML in SQL*Plus Command Line is the same in iSQL*Plus. SQL*Plus receives the request from iSQL*Plus to access the database and return the results using SET MARKUP HTML.
---------------------------
| iSQL*Plus |
| |
| --------------------- |
| | Input | |
| | Sent to SQL*Plus | |
| --------------------- |
| |
| --------------------- |
| | Output | |
| | Generated by | |
| | SQL*Plus using | |
| | SET MARKUP HTML | |
| --------------------- |
| |
---------------------------
Modified: 04-APR-04
Ref #: ID-4950
Can I connect to remote databases using iSQL*Plus?
Yes, you can connect to remote databases using iSQL*Plus. Use the same connection indentifier as you would using SQL*Plus command line:
- Using an alias listed in the tnsnames.ora file
- Using a full connection identifier
- Using an abbreviated connection identifier (for example, //mymachine:1521/orcl)
iSQL*Plus can be configured to only allow connections to a restricted list of databases using the iSQLPlusConnectIdList parameter. Setting the iSQLPlusConnectIdList parameter creates a drop down list of connection identifiers on the iSQL*Plus login screen. See the SQL*Plus User's Guide and Reference for information on how to set the iSQLPlusConnectIdList parameter.
Modified: 04-APR-04
Ref #: ID-4952
| 
Bookmark
