SQLPLUS问题集

1. windows下sqlplus乱码问题

因为sqlplus客户端和ORACLE数据库中的字符集使用的不一样

查看ORACLE数据库的字符集

SQL> select * from nls_database_parameters where parameter in ('NLS_LANGUAGE','NLS_CHARACTERSET');

 

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_CHARACTERSET               WE8ISO8859P1

检查SQLPLUS客户端使用的字符集（就是ORACLE软件使用的字符集）

打开注册表
regedit

hkey_local_machine-->software-->oracle-->key_OraDb10g_home1

查看参数nls_lang

现在正在使用的是SIMPLIFIED CHINESE_CHINA.ZHS16GBK

将他修改成跟ORACLE数据库中的一致，既AMERICAN.WE8ISO8859P1即可

 

另：

SQL> select * from nls_database_parameters where parameter in ('NLS_LANGUAGE','NLS_CHARACTERSET');

 

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_CHARACTERSET         AL32UTF8

 

注册表：

查看参数nls_lang

现在正在使用的是AMERICAN.AL32UTF8

 

但是SQLPLUS显示中文乱码，求解决方法！

 

 

问题已经解决，由于系统是日文的，所以换成JAPANESE_JAPAN.JA16SJIS就可以了，日文汉字没有问题。

要想正常显示中文汉字，我想要把系统的内码改成中文喽！

 

 

2.unix 下 sqlplus中途出错的中断退出问题

首先有以下程序：

 

当第八行的commit失败的时候，程序还会继续往下走，直到执行完毕，由于第一个commit失败，所以我需要停止整个sql的运行，如何解觉呢？

经过大量的网上搜索，终于找到了答案：

WHENEVER SQLERROR exit failure rollback

顾名思义，不用多说了吧，现执行这句话就ok了。

 

看到结果了吧，第二次执行失败了，结果后面的语句就不在被执行了！！

另：还有一种更牛的方法，不说了，想知道请找我！

 

详解：

WHENEVER SQLERROR

Syntax

WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING |n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK |NONE]}

Performs the specified action (exits SQL*Plus by default) if a SQL command or PL/SQL block generates an error.

In iSQL*Plus, performs the specified action (stops the current script by default) and returns focus to the Workspace if a SQL command or PL/SQL block generates an error.

Terms

[SUCCESS | FAILURE | WARNING | n |variable | :BindVariable]

Directs SQL*Plus to perform the specified action as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error.

EXIT [SUCCESS | FAILURE | WARNING | n |variable | :BindVariable]

Directs SQL*Plus to exit as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error. The EXIT clause of WHENEVER SQLERROR follows the same syntax as the EXIT command. See EXIT for more information.

CONTINUE

Turns off the EXIT option.

COMMIT

Directs SQL*Plus to execute a COMMIT before exiting or continuing and save pending changes to the database.

ROLLBACK

Directs SQL*Plus to execute a ROLLBACK before exiting or continuing and abandon pending changes to the database.

NONE

Directs SQL*Plus to take no action before continuing.

Usage

The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.

Examples

The commands in the following script causeiSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace if theSQL UPDATE command fails:

The commands in the following script cause SQL*Plus to exit and return the SQL error code if theSQL UPDATE command fails:

WHENEVER SQLERROR EXIT SQL.SQLCODE
UPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1;

The following SQL command error causes iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace if theSELECT command fails:

WHENEVER SQLERROR EXIT SQL.SQLCODE
select column_does_not_exiSt from dual;

select column_does_not_exist from dual * ERROR at line 1: ORA-00904: invalid column name Disconnected from Oracle.....

The following examples show that the WHENEVER SQLERROR command is not executed after errors with SQL*Plus commands, but it is executed if SQL commands or PL/SQL blocks cause errors:

WHENEVER SQLERROR EXIT SQL.SQLCODE
column LAST_name headIing "Employee Name"

Unknown COLUMN option "headiing" SHOW non_existed_option

The following PL/SQL block error causes SQL*Plus to exit and return the SQL error code:

WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
/

SELECT COLUMN_DOES_NOT_EXIST FROM DUAL; * ERROR at line 2: ORA-06550: line 2, column 10: PLS-00201: identifier 'COLUMN_DOES_NOT_EXIST' must be declared ORA-06550: line 2, column 3: PL/SQL: SQL Statement ignored Disconnected from Oracle.....