Shulou(Shulou.com)06/01 Report--

In some specific cases, we want to do some kind of experiment, we need to execute a sql statement, but we don't want to print out the result of the sql statement on the screen (too long)

It can be done in the following ways.

1 write the statement you want to execute into a sql script, for example:

[oracle@irac01 scripts] $more sqlt.sql

Set term off

Alter session set sql_trace=true

Select * from v$latch

Alter session set sql_trace=false

2 notice that the beginning of the script is set

Set term off

The explanation for the set command is as follows:

Column

Modify column width

Column C1 format A20-adjust the maximum width of column C1 (character type) display to 20 characters

Column C1 format 9999999-adjust the maximum width of column C1 (numtype) display to 7 characters

Modify column headings

Column C1 heading c2-- outputs the column name of C1 as c2

Set colsep''- the delimiter of the header column of the row

Set linesize (line)-- sets the maximum line width of the sqlplus output

Set pagesize-sets the maximum number of rows of a page to 24 by default, and can be set to 0 to avoid paging

Set serveroutput on | off

Set echo on-displays each command in the file and its execution result. Default is on.

Set echo off-does not display the commands in the file, only the results of their execution

Set term on-query results are displayed both in the spooled file (the output file specified by spool) and in SQLPLUS

Set term off-query results are displayed only in spooled files (spool specifies the output file)

Set heading off-so that the title of the result row is not displayed, default is on

Set heading on-- make the title of the result row display

Set trimout on-removes trailing spaces from each line of standard output and defaults to off

Set trimspool on-removes trailing spaces for each line of spool output, defaults to off

Set timing off-displays the time spent for each sql command, defaults to off

Set verify off-whether to display statements before and after the substitution variable is replaced.

Exit-exit

Set feedback

There are three ways: the default is on

Set feedback on;set feedback off;set feedback n

By default, when a sql is issued, oracle will give you feedback, for example, when creating a table, if successful, the command line will return feedback similar to: Table created, or if the execution of a pl/sql is successful, the command line will return PL/SQL procedure successfully completed. When many statements are run in a loaded script, feedback is generally set to off.

Set feedback n: when a sql statement returns n or more than n rows of records, there will be feedback, such as set feedback 5, and similar 8 rows selected when the returned records are equal to or more than 5 rows. Feedback. If the return row is less than 5, there is no such feedback.

Set define off

The default "&" in SQL*Plus indicates an alternative variable, that is, whenever the symbol appears in the command, SQL*Plus will ask you to enter the alternate value. This means that you cannot enter a string containing the symbol into the database or assign it to a variable, such as the string "SQL&Plus" the system will understand as a string that begins with "SQL", it will prompt you to enter the value of the alternative variable Plus, and if you enter ABC, the final string will be converted to "SQLABC".

Set define off disables this function, and "&" will be used as an ordinary character. As in the example above, the final character will be "SQL&Plus".

Set define off-turn off the alternate variable function

Set define on-enable the function of alternative variables

Set define $(not alphanumeric characters and spaces) will replace the default variable marker & "$", that is, the original & marker will be inserted in the form of ordinary characters, while the characters after $will be prompted for input.

SPOOL command

Spool filename-writes the contents of the buffer to the specified file filename

Spool off-terminates the write command

MARKUP

MARKUP is an environment setting for sql*Plus that controls the type of markup language output. By default, MARKUP defines HTML as a markup language, but the tag itself is turned off. The HTML tag set is predefined; you must open the tag with the following statement:

SET MARKUP HTML ON

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.