[20191204]sqlplus特殊定義導致的問題.txt

[20191204]sqlplus特殊定義導致的問題.txt

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> set numformat "999,999,999"
SCOTT@book> select * from emp where rownum<=2;
       EMPNO ENAME      JOB                MGR HIREDATE                     SAL         COMM       DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
       7,369 SMITH      CLERK            7,902 1980-12-17 00:00:00          800                        20
       7,499 ALLEN      SALESMAN         7,698 1981-02-20 00:00:00        1,600          300           30

--//這樣顯示的資料有逗號.這樣一些指令碼可能執行報錯.

2.例子:
SCOTT@book> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1337401710 BOOK                1 book


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1337401710        1 BOOK         book         gxqyydg4

Using ############ for database Id
Using            1 for instance number
  :dbid      :=  ############;
                 *
ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00103: Encountered the symbol "#" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively


declare
*
ERROR at line 1:
ORA-20200: Database/Instance / does not exist in DBA_HIST_DATABASE_INSTANCE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-06512: at line 23

--//我定義太短,輸出資訊是# .如果設定:
SCOTT@book> set numformat "999,999,999,999"
SCOTT@book> @?/rdbms/admin/awrrpt.sql
Current Instance

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1337401710 BOOK                1 book


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1337401710        1 BOOK         book         gxqyydg4

Using    1,337,401,710 for database Id
Using                1 for instance number
  :dbid      :=     1,337,401,710;
                     *
ERROR at line 2:
ORA-06550: line 2, column 22:

--//但是這樣無法執行的.取消設定後ok.