Common setup parameters in Sqlplus

One, various settings parameter explanation

Transfer from http://baike.baidu.com/view/1239908.htm

Sql*plus is one of the most commonly used tools, with strong features, mainly:

1. Database maintenance, such as startup, shutdown, etc., which is generally operated on the server.
2. Execute the SQL statement to execute/PL.
3. Execute the SQL script.
4. Export of data, report.
5. Application development, Test Sql/plsql.
6. Generate a new SQL script.
7. For application calls, such as installation of scripts in the Setup program.
8. User management and rights maintenance.

Second, the following is the introduction of some common Sql*plus commands:
　　1. Execute a SQL script file
Sql>start file_name
sql>@ file_name
We can save multiple SQL statements in a text file so that when all the SQL statements in this file are executed, any of the above commands can be used, similar to the batch processing in DOS.
What is the difference between @ and @@ 的
@ equals the start command, which is used to run a SQL script file.
The @ command invokes the current directory, or specifies a full path, or a script file that can be searched through the SQLPATH environment variable. This command is used to specify the full path of the file to be executed, otherwise the specified file is read from the default path (as specified by the SQLPath variable).
@@ 用 used in SQL script files to indicate that the SQL script file executed with @@ 用 in the same directory as the @@ 用, without specifying the full path to execute the SQL script file, or looking for the SQL script file from the path specified by the SQLPATH environment variable, which is typically used in a script file.
For example, the contents of the file Start.sql and Nest_start.sql,start.sql script files in the C:\Temp directory are:
@ @nest_start. SQL--equivalent @ c:\temp\nest_start.sql
Then we do this in Sql*plus:
sql> @ c:\temp\start.sql

Note: You can also run in the shell, for example Sqlplus '/as sysdba ' @c:\temp\start.sql

2. Edit the current input
Sql>edit
3. Rerun the last run of the SQL statement
sql>/
4. Output the displayed content to the specified file
Sql> SPOOL file_name
Everything on the screen is included in the file, including the SQL statement you entered.
5. Turn off the spool output
Sql> SPOOL OFF
Only the spool output is turned off to see the output in the output file.
6. Show the structure of a table
Sql> DESC TABLE_NAME

1). Set whether the current session automatically commits the modified data ("[...]" Indication can be omitted)
Sql>set Auto[commit] {on| Off| imm[ediate]| N

2). Whether to display the SQL statement that is executing in the script when executing an SQL script with the start command
Sql> SET ECHO {on| OFF}

3). Whether to display the number of rows queried or modified by the current SQL statement
Sql> SET Feed[back] {6|n| on| OFF}
By default, the number of rows that result is displayed only if the result is greater than 6. If set feedback 1, it returns regardless of how many rows are queried. When off, the number of rows for the query is not displayed

4). Whether to display column headings
Sql> SET hea[ding] {on| OFF}
When set heading off, the column headings are not displayed on the top of each page, instead of a blank line

5). Set the number of characters a row can hold
Sql> SET lin[esize] {80|n}
If the output of a row is greater than the number of characters that can fit in the set line, the line is displayed.

6). Set the separation between page and page
Sql> SET Newp[age] {1|n| NONE}
When set newpage 0 o'clock, there is a small black box at the beginning of each page.
When set newpage n, n blank lines are separated between the page and the page.
When set newpage none, there are no gaps between the page and the page.

7). When displayed, replace the null value with the text value
sql> SET NULL Text

8). Set the number of rows on a page
Sql> SET pages[ize] {24|n}
If set to 0, all output is one page and column headings are not displayed

9). Whether to display with Dbms_output. Put_Line the information that the package outputs.
Sql> SET Serverout[put] {on| OFF}
When writing a stored procedure, we sometimes use dbms_output.put_line to output the necessary information in order to debug the stored procedure, and the information can be displayed on the screen only if the Serveroutput variable is set to ON.

10). When the length of the SQL statement is greater than linesize, the SQL statement is intercepted when it is displayed.
Sql> SET Wra[p] {on| OFF}
When the length of the output line is greater than the length of the set line (set with the Set linesize n command), when set wrap is on, more characters from the output line are displayed on a different line, otherwise, more than the characters of the output line will be cut off and not displayed. (the default is on state)

11). Whether the content of the output is displayed on the screen, mainly used in conjunction with spool.
Sql> SET Term[out] {on| OFF}
When you use the spool command to output the contents of a large table to a file, it takes a lot of time to output the content on the screen, and after setting set Termspool off, the output will only be saved in the output file and will not be displayed on the screen, greatly improving the speed of the spool.

12). Remove the extra space after each line in the spool output
Sql> SET Trims[out] {on| OFF}

13) shows the execution time spent on each SQL statement
Set TIMING {on| OFF}

14). When a blank row is encountered, the statement is not considered to have ended and is read from subsequent lines.
SET Sqlblanklines on
Sql*plus, it is not allowed to have a blank line in the middle of the SQL statement, which can be cumbersome when copying scripts from other places to Sql*plus. For example, the following script:
Select Deptno, Empno, ename
From EMP
where empno = ' 7788 ';
If the copy is executed in Sql*plus, an error occurs. This command can resolve the problem

15). Set the output of the Dbms_output (only for use in terminal output)
SET Serveroutput on BUFFER 20000
Use Dbms_output.put_line (' strin_content '); You can output information in stored procedures and debug stored procedures
If you want to have Dbms_output.put_line (' abc '), the output is shown as:
Sql> ABC, instead of SQL>ABC, adds the format wrapped parameter after set serveroutput on.

16). Output data is in HTML format
Set markup HTML
In the 8.1.7 version (perhaps 816? unsure), Sql*plus has a set markup HTML command that can display the output of Sql*plus in HTML format.
Note that the spool on, when the output on the screen, we do not see the difference with no spool on, but when we use spool filename output to the file, you will see the spool file appears in the tag.

3. Modify the default values for page display and rows

But this method, when you go to SQL plus next time to re-set, very troublesome. So you can modify the default settings.

found in Oracle's installation directory Glogin.sql,

I am installing Oracle 11, which is easy to find with a search under D:\oracle\product\11.2.0\dbhome_1\sqlplus\admin. Then open this file,

Get:

--

--Copyright (c) 1988, 2005, Oracle. All rights Reserved.

--

--NAME

--Glogin.sql

--

--DESCRIPTION

--Sql*plus Global Login "site profile" file

--

--Add Any sql*plus commands here, is to is executed when a

--User starts Sql*plus, or uses the Sql*plus CONNECT command.

Set Linesize 300;

Set pagesize 100;

For more detailed information, see: http://blog.csdn.net/kkdelta/article/details/7178890

        

4. Discussions between CLOB and BLOBs and To_char and To_clob

(1) Introduction to CLOB and blobs

1. BLOBs are all called binary Large objects (binary Large object). It is used to store large binary objects in the database. The maximum size that can be stored is 4G bytes
2. CLOB CLOB is all called a character large object (Character Large object). It is similar to the Long data type except that CLOB is used to store large single-byte character blocks in the database and does not support character sets of varying widths. The maximum size that can be stored is 4G bytes.
3. NCLOB the NCLOB data type based on the national language character set is used to store large chunks of fixed-width single-byte or multibyte characters in the database and does not support character sets of varying widths. The maximum size that can be stored is 4G bytes
4. BFILE when large binary objects are large in size and 4G bytes, the BFILE data type is used to store them in operating system files outside the database, and when they are less than 4G bytes, they are stored in the operating system files inside the database, and the BFILE column stores the file locator. This locator points to a large binary file on the server.
5. The summary is: BLOBs are large object types (typically file pictures, office files, and so on.) CLOB is a large text/long string

(2) Problems encountered in the actual project

In the Sqlplus query CLOB type of data is not displayed by default, the default line displays 80 characters (many will be cut off), regardless of whether set linesize or not, this is the limit of CLOB, for example:

Select Sql_fulltext from V$sql; you will see that only 80 characters are displayed per line.

There are several ways to show all:

Method One: Select Dbms_lob.substr (Sql_fulltext) from V$sql;

#注意, although the display is full, but in the back of the problem, although not cut, will be at the end of the error: Error:ora-06502:pl/sql: Number or value error: String buffer is too small
ORA-06512: On line 1

So it doesn't actually work out. haha haha

Method Two: In fact, you can set the parameters to solve the above error

Sqlplus output CLOB Type of data is often encountered when the data is truncated and can not fully output the problem, in fact, these are due to the Sqlplus parameter settings, this article will discuss several main parameters.

1, set long 2000000000

The first is the long parameter, the maximum value of which is 2000000000 (2G), in bytes. The meaning is: sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; And for copying LONG values. The Chinese meaning is probably the maximum number of display bytes to set CLOB, LONG, Nlob, and XmlType types.

2, set Longchunksize 255

Related to the long parameter is the longchunksize parameter, which should be smaller than the value of the long parameter, and the unit is also a byte. The meaning is: sets the size (in bytes) of the increments Sql*plus uses to retrieve a CLOB, LONG, NCLOB or XMLType value. That Sqlplus will follow the parameter to get the data of the above type for a period of time until a long value is reached or the data is obtained.

Example:

There are 10 bytes of data: 0123456789

If set Longchunksize 2, then the effect shown is

01

23

45

67

89

If set Longchunksize 4, then the effect shown is

0123

4567

89

3, set Linesize 255

This parameter is used very frequently, and it is believed that the meaning is known to all: Sets the total number of characters this sql*plus displays on one line before beginning a new line. This The parameters are used to control the Sqlplus display, and longchunksize is used to control the Sqlplus to get the data, so if this parameter is not properly set, it cannot be fully displayed.

Example:

There are 10 bytes of data: 0123456789

Set Longchunksize 4

Set Linesize 3

The display effect is as follows

012--3 cannot be displayed

456--7 cannot be displayed

89

So want to display the select Sql_fulltext from v$sql correctly; The method is to set a long 2000000000, the true maximum 2G, for the other is really not, hehe. Set Longchunksize 255.

Then can be normal query, pay attention not to use dbms_lob.substr Oh, direct select Sql_fulltext from V$sql; it's okay.

Method Three: Write the program (I didn't read it anyway)

I'll give you a reference to the Plsql I wrote to read the LOB:
Create or replace procedure Haozhu_getblob (
table_name in VARCHAR2,
field_id in Varchar2,
Field_name in Varchar2,
v_id in Varchar2,
V_pos in number
)
Is
Lobloc Clob;
Buffer varchar2 (32767);
Amount Number: = 60;
Offset number: = 1;
Query_str VARCHAR2 (1000);
Create_str VARCHAR2 (1000);
Bloblength number;
Ddlcursor integer;
Begin
QUERY_STR: = ' Select Dbms_lob. GetLength (' | | field_name| | ') From ' | | table_name| | ' WHERE ' | | field_id| | ' =: ID ';
--dbms_output.put_line (' Query_str is ' | | QUERY_STR);
EXECUTE IMMEDIATE query_str into Bloblength USING v_id;
--dbms_output.put_line (' The length of blob is ' | | Bloblength);
QUERY_STR: = ' SELECT ' | | field_name| | ' From ' | | table_name| | ' WHERE ' | | field_id| | ' =: ID ';
EXECUTE IMMEDIATE query_str into Lobloc USING v_id;
offset:=offset+ (v_pos-1) *amount;
while (Offset<=bloblength) loop
--dbms_output.put_line (' offset: ' | | Offset);
--read VARCHAR2 from the buffer
Dbms_lob.read (Lobloc,amount,offset,buffer);
Dbms_output.put_line (buffer);
Offset:=offset+amount;
End Loop;
End
/

O (∩_∩) o haha ~, this end, the back will also use with CLOB related content, then tangled dead