OraCmd - Oracle access without client tool »
What is SQL*Plus and where does it come from? SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database. If you are familiar with other databases, sqlplus is equivalent to "sql" in Ingres, "isql" in Sybase and SQLServer, "db2" in IBM DB2, "psql" in PostgresQL, and "mysql" in MySQL. SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first Oracle releases up to Oracle v4. The UFI interface was extremely primitive and, in today's terms, anything but user friendly. If a statement was entered incorrectly, UFI issued an error and rolled back the entire transaction (ugggh). -------------------------------------------------------------------------------- How does one use the SQL*Plus utility? Start using SQL*Plus by executing the "sqlplus" command-line utility. Valid options are: userid/password@db -- Connection details /nolog -- Do not login to Oracle. You will need to do it yourself. -s or -silent -- start sqlplus in silent mode. Not recommended for beginners! @myscript -- Start executing script called "myscript.sql" Look at this example session: sqlplus /nolog SQL> connect scott/tiger SQL> select * from tab; SQL> disconnect SQL> exit Please note that one must prepare the environment before starting sqlplus. Linux/ Unix example: $ . oraenv ORACLE_SID = [orcl] ? orcl $ sqlplus scott/tiger Windows Example: Click on "Start" -> "Run" and enter "cmd" C:> set ORACLE_SID=orcl C:> sqlplus scott/tiger or C:> sqlplus scott/tiger@orcl -------------------------------------------------------------------------------- What commands can be executed from SQL*Plus? One can enter three kinds of commands from the SQL*Plus command prompt: SQL*Plus commands - SQL*Plus commands are used to set options for SQL*Plus, format reports, edit files, edit the command buffer, and so on. SQL*Plus commands do not interact with the database. These commands do not have to be terminated with a semicolon (;), as is the case with SQL commands. The rest of this page is dedicated to SQL*Plus commands, eg. SHOW USER SQL commands - for more information see the Oracle SQL FAQ. Eg: SELECT * FROM TAB; PL/SQL blocks - for more information see the Oracle PLSQL FAQ. Eg: BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END; / -------------------------------------------------------------------------------- What are the basic SQL*Plus commands? The following SQL*Plus commands are available: ACCEPT Get input from the user DEFINE Declare a variable (short: DEF) DESCRIBE Lists the attributes of tables and other objects (short: DESC) EDIT Places you in an editor so you can edit a SQL command (short: ED) EXIT or QUIT Disconnect from the database and terminate SQL*Plus GET Retrieves a SQL file and places it into the SQL buffer HOST Issue an operating system command (short: !) LIST Displays the last command executed/ command in the SQL buffer (short: L) PROMPT Display a text string on the screen. Eg prompt Hello World!!! RUN List and Run the command stored in the SQL buffer (short: /) SAVE Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql SET Modify the SQL*Plus environment eg. SET PAGESIZE 23 SHOW Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc. SPOOL Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst START Run a SQL script file (short: @) -------------------------------------------------------------------------------- How does one trace (and explain) SQL statements from SQL*Plus? When the AUTOTRACE setting is enabled, SQL*Plus will print an EXPLAIN PLAN and execution statistics after each SQL statement. Look at this example: SQL> set autotrace on SQL> select * from dept where deptno = 40; DEPTNO DNAME LOC ---------- -------------- ------------- 40 OPERATIONS BOSTON Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=18) 2 1 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 499 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Follow these steps to enable this feature: Run the PLUSTRCE.SQL script from the SYS database user. This script is located in the $ORACLE_HOME/sqlplus/admin directory. Create a PLAN_TABLE table by running the UTLXPLAN.SQL script. This script is in $ORACLE_HOME/rdbms/admin. Use the "SET AUTOTRACE ON" command to trace SQL execution. This will print an explain plan and high level trace information after your query results. -------------------------------------------------------------------------------- Can one pass operating system parameters to SQL*Plus? One can pass operating system variables to sqlplus using this syntax: sqlplus username/password @cmdfile.sql var1 var2 var3Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example: sqlplus scott/tiger @x.sql '"test parameter"' dual Where x.sql consists of: select '&1' from &2; exit 5; -------------------------------------------------------------------------------- Something's wrong with my CLOB/ NCLOB/ LONG column. Where is my data? SQL*Plus by default only shows the first 80 bytes of any LONG, CLOB and NCLOB datatypes. The data is there, but since sqlplus is a command-line tool it tries not to print out too much data. You can override this to tell sqlplus exactly how many bytes you want to see: SET LONG 32000 SELECT text FROM user_views WHERE rownum = 1; -------------------------------------------------------------------------------- How does one copy data from one database to another in SQL*Plus? The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. Look at this example: COPY FROM SCOTT/TIGER@LOCAL_DB TO SCOTT/TIGER@REMOTE_DB - CREATE IMAGE_TABLE USING - SELECT IMAGE_NO, IMAGE - FROM ../images;
Download Sql*Plus Alternative (not need Oracle client)