tips

What is the difference between @ and @@?

The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.

SQL> @myscript.sql

A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in your SQLPATH or ORACLE_PATH).

@@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script).

What is the difference between & and &&?

“&” is used to create a temporary substitution variable that will prompt you for a value every time it is referenced.

“&&” is used to create a permanent substitution variable. Once you have entered a value (defined the variable) its value will used every time the variable is referenced.

What is the difference between ! and HOST?

Both “!” and “HOST” will execute operating system commands as child processes of SQL*Plus. The difference is that “HOST” will perform variable substitution (& and && symbols), whereas “!” will not.

Note: use “$” under OS/390, VMS, and Windows environments, not “!”.

How does one trap errors in SQL*Plus?

Use the “WHENEVER SQLERROR …” command to trap SQL and PL/SQL errors, and the “WHENEVER OSERROR …” to trap operating system errors. Eg:

SQL> WHENEVER OSERROR  EXIT 9
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
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 var3
Parameter 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;
Example passing the Windows User's Temp Path Location to sqlplus:
sqlplus username/password @cmdfile.sql %TEMP%
within @cmdfile.sql -
SPOOL &1\myscript.log
-- Your script commands
SPOOL OFF
Can one generate HTML reports from SQL*Plus?
set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off
http://www.orafaq.com/wiki/SQL*Plus_FAQ
			

			
				

sql*plus autocommit and exit

A transaction is demarkated by savepoints or commit or rollback not by statements.

A transaction ends when any of the following occurs:

• You issue a COMMIT or ROLLBACK (without a SAVEPOINT clause) statement.
• You execute a DDL statement (such as CREATE, DROP, RENAME, ALTER). If the current transaction contains any DML statements, Oracle first commits the transaction, and then executes and commits the DDL statement as a new, single statement transaction.
• A user disconnects from Oracle. (The current transaction is committed.)
• A user process terminates abnormally. (The current transaction is rolled back.)

exit commits all pending changes by default, logs out of Oracle, terminates SQL Plus and returns control to the operating system. If we wanted to exit SQL Plus without committing, we should have either issued a rollback then an exit or just simply exit rollback. Note that both SQL Plus commands exit and quit are identical and have the same behavior.

EXIT defaults to COMMIT the current transactions.

Autocommit causes a commit to be issued after every statement.

links

Starting SQL*Plus

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/ch3.htm#sthref718

Use sqlplus/rman可以直接修改命令和查看命令历史

http://www.linux010.cn/UniX/Shi-sqlplusrman-KeYiZhiJieXiuGaiMingLingHeChaKanMingLingLiShi-zhsw89.htm

Sqlplus doesn’t have a command history

http://linux.omnipotent.net/article.php?article_id=11901#Oracle