sqlplus 使用小技巧

1、sqlplus显示语言设置
export NLS_LANG=”AMERICAN_AMERICA.ZHS16GBK”
export NLS_LANG=”SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
注意数据库编码：ZHS16GBK/UTF8

2、dbms_output.put_line显示最前面空格
SQL> set serveroutput on
SQL> exec dbms_output.put_line(‘     abc’);
abc

PL/SQL procedure successfully completed.

SQL> set serveroutput on format wrapped
SQL> exec dbms_output.put_line(‘   abc’);
   abc

PL/SQL procedure successfully completed.

3、sqlplus 语句中间查询对象结构
SQL> select owner
2 # desc input
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

2 ,status from input where rownum<10;

no rows selected
下一行以#开头, 就可以执行一条sql*plus命令, 执行完后, 刚才的语句可以继续输入

4、Sql*plus中sql语句中间有空行
SQL> select owner
2
SQL> ,status from input where rownum<10; SP2-0734: unknown command beginning “,status fr…” – rest of line ignored. 原因是sqlplus遇到空行就认为是语句结束了. 其实要改变这种现象, 只要使用SQLBLANKLINES参数就可以了 SQL> SET SQLBLANKLINES ON
SQL> select owner
2
3 ,status from input where rownum<10;

no rows selected