오라클 Oracle SQL*Plus

Version 11.1

General Note: Oracle in its near infinite wisdom dropped sqlplusw.exe from the initial release of 11gR1. If want it you can copy in the executable from 10.2.0.1 and in most cases rename the DLL oraclient11.dll to oraclient10.dll. Then you will again have a usable interface. If you are as thrilled as we are about this send us an email and we will pass it along to Oracle. Constants Constant Usage Example SQL.LNO Line Number SELECT COUNT(*) FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; show lno SQL.PNO Page Number SELECT object_name FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; show pno SQL.RELEASE Oracle Version show release SQL.SQLCODE Current error code show sqlcode SQL.USER Currently connected user show user Startup Parameters: Usage 1 Flags Description -H Displays the SQL*Plus version and the usage help -V Displays the SQL*Plus version sqlplus -C | -H Startup Parameters: Usage 2 Flags Description -C<version> Sets the compatibility of affected commands to the version specified. The version has the form "x.y[.z]. For example -C 10.2.0 -L Attempts to log on just once, instead of reprompting on error -M<option>   -R<level> Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be 1, 2, or 3 with R3 being the most restrictive and disables all such commands -S Sets the silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands sqlplus [ [<option>] [<logon>] [start>] ] Logon Parameters sqlplus <user_name>[/password][@<connect_identifier>] | / [AS SYSDBA | AS SYSOPER | /NOLOG sqlplus system/manager@orabase AS SYSOPER /NOLOG Start Parameters @<url>|<filename>[.<extension>] [<parameter> ....] TBD   View All Parameters List SQL*Plus Parameters show all SQL> show all   Column Formatting Character COL <column_name> FORMAT a<integer> SELECT object_name, object_type FROM all_objects WHERE ROWNUM < 6; COL object_name FORMAT a30 SELECT object_name, object_type FROM all_objects WHERE ROWNUM < 6; Money COL <column_name> FORMAT $99,999 COL salary FORMAT $99,999 Number Element Example Description 9 9999 Number of significant digits returned COL test FORMAT 99.99 SELECT 100/3 TEST FROM dual; COL test FORMAT 99.9999 SELECT 100/3 TEST FROM dual; 0 0999 9990 Display a leading zero or a value of zero in this position as 0 COL test FORMAT 099.999 SELECT 100/3 TEST FROM dual; $ $9999 Prefixes with dollar sign CREATE TABLE t ( test NUMBER(10,4)); INSERT INTO t VALUES (1234); INSERT INTO t VALUES (-234); INSERT INTO t VALUES (0); COL test FORMAT $9999 SELECT * FROM t; B B9999 Display a zero value as blank COL test FORMAT B9999 SELECT * FROM t; MI 9999MI Display "-" after a negative value COL test FORMAT 9999MI SELECT * FROM t; S S9999 Display "+" for positive values and "-" for negative values COL test FORMAT S9999 SELECT * FROM t; PR 9999PR Displays a negative value in <angle brackets> COL test FORMAT 9999PR SELECT * FROM t; D 99D99 Display the decimal character COL test FORMAT 9999D99 SELECT * FROM t; G 9G999 Display the group separator COL test FORMAT 9G999 SELECT * FROM t; C C999 Display the ISO currency symbol COL test FORMAT C9999 SELECT * FROM t; L L999 Display the local currency symbol COL test FORMAT L9999 SELECT * FROM t; , 9,999 Display a comma COL test FORMAT 9,999 SELECT * FROM t; . 99.99 Display a period COL test FORMAT 9999.99 SELECT * FROM t; V 999V99 Multiplies value by 10n, where n is number of "9"s after "V" COL test FORMAT 9999V99 SELECT * FROM t; EEEE 9.999EEEE Display value in scientific notation COL test FORMAT 9999.99EEEE SELECT * FROM t; RN or rn RN Display upper or lowercase Roman numerals. Value can be an integer between 1 and 3999 COL test FORMAT RN SELECT * FROM t; DATE DATE Format a NUMBER columns that represent Julian dates as MM/DD/YY COL test FORMAT DATE SELECT * FROM t;   Arraysize Array Size Demo ARRAYSIZE nnn The default value of nnn is 15, which is too small for large data transfers. Try larger and larger values of nnn until response improvements become marginal. CREATE TABLE t AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; SELECT COUNT(*) FROM t; CREATE INDEX t_idx ON t(object_id) PCTFREE 0; set autotrace traceonly show arraysize SELECT * FROM t; SELECT * FROM t; SELECT * FROM t; set arraysize 10 SELECT * FROM t; set arraysize 100 SELECT * FROM t; set arraysize 250 SELECT * FROM t;   Connect CONN as SYS CONN <logon> AS <SYSDBA | SYSOPER> conn / as sysdba conn sys@orabase AS SYSDBA CONN as a user CONN <logon> / <password> @ <instance> conn uwclass/uwclass@orabase conn uwclass@orabase   Define / Undefine SQL*Plus: Release 10.1.0.2.0 - Production on Fri Nov 19 15:20:10 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> DEFINE _CONNECT_IDENTIFIER DEFINE _CONNECT_IDENTIFIER = "ORABASE" (CHAR) SQL> DEFINE _DATE DEFINE _DATE = "19-NOV-04" (CHAR) SQL> DEFINE _EDITOR DEFINE _EDITOR = "Notepad" (CHAR) SQL> DEFINE _O_VERSION DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR) SQL> DEFINE _PRIVILEGE DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) SQL> DEFINE _SQLPLUS_RELEASE DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR) SQL> DEFINE _USER DEFINE _USER = "SYS" (CHAR) SQL>UNDEFINE _USER   Describe Describe a function desc <function_name> CREATE OR REPLACE FUNCTION dayofweek (stringin VARCHAR2) RETURN VARCHAR2 IS BEGIN   NULL; END dayofweek; / desc dayofweek Describe a package desc <package_name> CREATE OR REPLACE PACKAGE demopkg IS PROCEDURE demoproc; FUNCTION demofunc RETURN BOOLEAN; END; / desc demopkg CREATE OR REPLACE PACKAGE BODY demopkg IS PROCEDURE demoproc IS BEGIN   NULL; END; FUNCTION demofunc RETURN BOOLEAN IS BEGIN   RETURN TRUE; END; END demopkg; / desc demopkg Describe a procedure desc <procedure_name> CREATE OR REPLACE PROCEDURE demoproc (numbin NUMBER, stringin IN OUT VARCHAR2, tfin OUT BOOLEAN) BEGIN   NULL; END demoproc; / desc demoproc Describe a table desc <table_name> CREATE TABLE demotable ( Describe an object table set describe depth all {linenum <ON | OFF>} {INDENT <ON | OFF>} CREATE OR REPLACE TYPE rectangle_t AS OBJECT ( h  NUMBER, w  NUMBER, x  NUMBER, y  NUMBER); / CREATE TABLE rectable ( rectangle_name VARCHAR2(20), rectangle      rectangle_t); desc rectable set describe depth all desc rectable set describe depth all linenum on indent on desc rectable Describe a view desc <view_name> CREATE OR REPLACE VIEW demoview AS SELECT * FROM demotable; desc demotable Describe a synonym desc <synonym_name> CREATE SYNONYM demosyn FOR rectable; desc demosyn   Display Clear Screen clear scr SELECT object_name, created FROM all_objects WHERE ROWNUM < 2; clear scr Column Separators SET COLSEP <column separator> set colsep ',' SELECT table_name, column_name, data_type FROM user_tab_cols WHERE rownum < 10; Display Headers SET HEAD <OFF | ON> SELECT table_name FROM all_tables; set head off SELECT table_name FROM all_tables; set head on Line Size SET LINESIZE <integer> SELECT text FROM all_source WHERE rownum < 21; set linesize 121 SELECT text FROM all_source WHERE rownum < 21; Page Size SET PAGESIZE <integer> SELECT object_name FROM all_objects WHERE rownum < 60; set pagesize 20 SELECT object_name FROM all_objects WHERE rownum < 60; set pagesize 0 SELECT object_name FROM all_objects WHERE rownum < 60; Page Break BREAK ON <column_name> [SKIP PAGE] break on overload skip page set pagesize 20 set linesize 121 col overload format a8 SELECT overload, position, argument_name, in_out, data_type FROM all_arguments WHERE object_name = 'CREATE_TUNING_TASK' ORDER BY overload, position; Pause SET PAUSE <OFF | ON> SELECT object_name FROM all_objects WHERE rownum < 60; set pause on SELECT object_name FROM all_objects WHERE rownum < 60; set pause off Timing SET TIMING <OFF | ON> SELECT COUNT(*) FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; set timing on SELECT COUNT(*) FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; set timing off Title ttitle {LEFT <text>} {CENTER <text>} {RIGHT <text>} set pagesize 25 ttitle LEFT '01-Jan-2005' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO SELECT * FROM dual; ttitle LEFT '01-Jan-2005' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER - 'Oracle Application Development' SELECT object_name FROM all_objects WHERE rownum < 60; Display Output From DBMS_OUTPUT.PUT_LINE built-in package set serveroutput <ON | OFF> DECLARE  x VARCHAR2(20) := 'This is a test'; BEGIN   dbms_output.put_line(x); END; / set serveroutput on DECLARE  x VARCHAR2(20) := 'This is a test'; BEGIN   dbms_output.put_line(x); END; /   Edit / Editor Define An Editor _editor define _editor=vi Edit The Last Command ed ed   Error Handling OS Errors WHENEVER OSERROR <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK> WHENEVER OSERROR EXIT @c:\temp\nofile.sql SQL Errors WHENEVER SQLERROR <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK> WHENEVER OSERROR EXIT SQL.SQLCODE   Execute Run a stored procedure exec <procedure_name> CREATE OR REPLACE PROCEDURE demoexec IS BEGIN   dbms_output.put_line('*** Executed ***'); END demoexec; / set serveroutput on exec demoexec   Help Display SQL*Plus Help help <command> help index help variable   Host Shell to the operating system host host exit Unix Shell ! SQL> ! exit Windows Shell $ SQL> $ exit   Password Change Password password password   Quit Exit exit exit Quit quit quit   Run Run RUN set serveroutput on BEGIN   dbms_output.put_line('test'); END; / run   Run Script @ @ <path_and_script_name> @c:\oracle\product\ora102\rdbms\admin\catplan.sql -- in a directory under $ORACLE_HOME @?\rdbms\admin\catplan.sql Get get <path_and_script_name> get c:\oracle\product\ora102\rdbms\admin\catplan.sql -- in a directory under $ORACLE_HOME get ?\rdbms\admin\catplan.sql   Save Save the most recently executed SQL statement save <file_name> SELECT tablespace_name, status FROM all_tablespaces; save ts SQL> host $ more ts $ exit   Spool Spool Short version: SPO spo[ol] [path_and_file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT] spool c:\temp\zzyzx.txt SELECT table_name FROM all_tables; spo off spo c:\temp\zzyzx.txt app SELECT object_id FROM user_objects spool off Termout termout <OFF | ON> termout is one of those sqlplus settings which tend to cause confusion. It only applies to output from running script files. abc.sql: set termout off select 'abc' from dual and run it like this in sqlplus: @abc.sql Trimspool trimspool <OFF | ON > spool c:\temp\trimspool.txt SELECT table_name FROM all_tables WHERE rownum < 11; trimspool on SELECT table_name FROM all_tables WHERE rownum < 11; spool off   SQL Prompt Set the SQL Prompt sqlprompt <value> Predefined Variable Description _CONNECT_IDENTIFIER Connection identifier used to make connection, where available. _DATE Current date, or a user defined fixed string. _EDITOR Specifies the editor used by the EDIT command. _O_RELEASE Full release number of the installed Oracle Database. _O_VERSION Current version of the installed Oracle Database. _PRIVILEGE Privilege level of the current connection. _SQLPLUS_RELEASE Full release number of installed SQL*Plus component. _USER User name used to make connection. SQL> SQL> set sqlprompt "_user'@'_connect_identifier>" SYS@orabase>conn uwclass/uwclass Connected. UWCLASS@orabase> SQL> SET SQLPROMPT '_CONNECT_IDENTIFIER> ' SQL> set sqlprompt 'SQL> '   Variable Define Variable variable <variable_name> <data_type> variable x VARCHAR2(20) variable x BEGIN   :x := 'ABC'; END; / View Variable Value print <variable_name> print x Undefine Variable undefine <variable_name> undefine x   Save Settings glogin.sql   login.sql   State File SQL> show linesize SQL> set linesize 120 SQL> show linesize SQL> STORE SET statefile REPLACE SQL> EXIT SQL> show linesize SQL> @statefile SQL> show linesize

Related Topics SHOW

출처 : http://www.psoug.org/reference/sqlplus.html