Sql*plus Connector Stitching output

In your daily work, you may need to use duplicate commands, modify the values of a different field, and use the connection string for stitching

#本篇文档:

I. Splicing SQL with connectors

Second, spool output query results

Third, spool output xml/html format content

1.1 Connector

Example a: Drop a table under a user

>select ' Drop table ' | | owner| | '. ' | | table_name| | '; ' from dba_tables where owner= ' HR '
and table_name not in (' EMPLOYEES ', ' departments '); Droptable ' | | owner| | '. ' | | table_name----------------------------------------------------Drop table HR. LOCATIONS;

1.2 Connector ' s special usage

>Select'| | department_name| | Q'['is]'| | salary| | '; ' From hr.employees e,hr.departments D where e.department_id=d.department_id; Department Name Administration ' s Sal is4400; Department name Marketing's Sal is13000;

2,1 Spool Content

Parameters:

Set heading off           setting display column name: The display field name of the query cancels the set feedback off          setting displays "Selected row": The last query row result is canceled

Spool

Query results output to a file: can be edited, pasted, used:

Spool/home/oracle/drop_hr_table.sqlselect ' drop table ' | | owner| | '. ' | | table_name| | '; ' from dba_tables where owner= ' HR ' and table_name not in (' EMPLOYEES ', ' departments '); >spool  off

More Spool

[Email protected] ~]$ more drop_hr_table.sql01:20:39 [email protected] >select ' drop table ' | | owner| | '. ' | | table_name| | '; ' from dba_tables where owner= ' HR ' 01:20:41   2 and   table_name No in (' EMPLOYEES ', ' departments ');D ROP Table HR. LOCATIONS;                                                                                                Drop table hr.jobs;  #有一个缺陷: SQL statement executed as above query

2.2 Spool Output XML/HRML format content-using Gai teacher book Learning

Parameter: linesize 200term  Whether the output content is displayed verify   output variable content feedback The number of record rows returned markup HTML  output HTML format content Main.sql set the environment, Call specific execution script set linesize 200set term off verify off feedback off pagesize 999set markup html on ENTMAP on spool on Preformat o Ffspool tables.xls@get_tables.sqlspool offexit get_tables.sql Specific execution script select Owner,table_name,tablespace_name,blocks, Last_analyzed from All_tables ORDER by 1, 2; Execute: Sqlplus "/As SYSDBA" @main

#以上是XML格式: Modify Sppol Table.xls Modify to HTML to output HTML format

" /As Sysdba " @main. html

#显示输出分隔符号

Sql> Set Colsep | Sql> Select Username,password,default_tablespace from Dba_usersusername  | PASSWORD  | Default_tablespace----------|----------|------------------------------perfstat  |          | Statspack

Sql*plus Connector Stitching output