RE: split file from UNIX or SQL

From: Kevin Lange <kgel_at_ppoone.com>
Date: Wed, 13 Jun 2001 11:38:17 -0700
Message-ID: <F001.00328379.20010613112544@fatcity.com>

If you
are using SQLPlus to generate your report (which can easily be called from a shell script), then you could use the built-in BREAK command.
<FONT face=Arial color=#0000ff

size=2> 
Here
is a simple sample:
<FONT face=Arial color=#0000ff

size=2> 
My
query:
 
<SPAN

class=560111818-13062001>    select <SPAN class=560111818-13062001>table_name, column_name, data_type from user_tab_columns<SPAN
class=560111818-13062001>;
<SPAN

class=560111818-13062001> 
<FONT

face=Arial color=#0000ff>The Original Data:
<FONT

face=Arial color=#0000ff> 
<FONT face=Courier

color=#0000ff>TABLE_NAME                     
COLUMN_NAME                    

DATA_TYPE        

------------------------------ ------------------------------ 
-----------------TEMP                           
TABLESPACE_NAME                
VARCHAR2          
TEMP                           
OWNER                          
VARCHAR2          
TEMP                           
TABLE_NAME                     
VARCHAR2          
TEMP                           
INITIAL_EXTENT                 
NUMBER            
TEMP                           
NEXT_EXTENT                    
NUMBER            
TEMP2                          
TABLESPACE_NAME                
VARCHAR2          
TEMP2                          
OWNER                          
VARCHAR2          
TEMP2                          
TABLE_NAME                     
VARCHAR2          
TEMP2                          
INITIAL_EXTENT                 
NUMBER            
TEMP2                          
NEXT_EXTENT                    

NUMBER             
<FONT face=Arial

color=#0000ff size=2>10 rows selected.
 
After running
the BREAK command<FONT
face=Courier>:
<FONT face=Courier color=#0000ff

size=2> 
<FONT

size=2>    break on table_name skip
page;
<FONT face=Arial color=#0000ff

size=2> 
T<SPAN
class=560111818-13062001>he returned data is
<SPAN

class=560111818-13062001> 
<FONT face=Courier color=#0000ff

size=2>TABLE_NAME                     
COLUMN_NAME                    

DATA_TYPE        

------------------------------ ------------------------------ 
-----------------TEMP                           
TABLESPACE_NAME                
VARCHAR2          
                               
OWNER                          
VARCHAR2          
                               
TABLE_NAME                     
VARCHAR2          
                               
INITIAL_EXTENT                 
NUMBER            
                               
NEXT_EXTENT                    

NUMBER             
<FONT face=Courier color=#0000ff

size=2>TABLE_NAME                     
COLUMN_NAME                    

DATA_TYPE        

------------------------------ ------------------------------ 
-----------------TEMP2                          
TABLESPACE_NAME                
VARCHAR2          
                               
OWNER                          
VARCHAR2          
                               
TABLE_NAME                     
VARCHAR2          
                               
INITIAL_EXTENT                 
NUMBER            
                               
NEXT_EXTENT                    

NUMBER             
<FONT face=Courier color=#0000ff

size=2>10 rows selected.

<SPAN

class=560111818-13062001> 
You could then have
them pick up the correct pages. 
<SPAN

class=560111818-13062001> 
Look into the other
SQLPlus report options.
<SPAN

class=560111818-13062001> 
<SPAN

class=560111818-13062001>Kevin
<SPAN

class=560111818-13062001> -----Original Message-----From: Helen rwulfjeq [mailto:lannyue_at_yahoo.com]Sent: Wednesday, June 13, 2001 2:02 PMTo: Multiple recipients of list ORACLE-LSubject: split file from UNIX or SQL

  Hello,   Being not a UNIX expert, I'm write unix & SQL   scripts to generate report. But have difficulty to split the file according   the report ID. I'm wondering IF UNIX (sh script) can split a big text file   into several different files according to a certain field (from posX to posY),   when that field is not in sequence, but from rowA to rowB will be exactly   the same.
  ie: check text file, if from rowA to rowB, when posX-posY   are equal, generate report AB;
  if from rowC to rowD, when posX-posY are equal, generate report   CD; ....
  Here are more info:
  I have a table to store report request info from user, like this:   REPORT_ID  DI PL BUI FL ROOM---------- -- -- --- --   ------REPORT 1   RC SF DAL ndREPORT 2   AD   LEREPORT 3   RC DD LKF 01 RENTALREPORT 4     *REPORT 5   CSREPORT 6   *  DD LKF 01   RENTALREPORT 7   RC DD LKF 01 ABCDEREPORT 8     AB AC LKF 01
  After I do error checking and then run the query against DB, I can get a   huge temp table to store info, like this:   REPORT_ID 
  KOMP_IDENT----------               

  ----------REPORT 1   C-773280558REPORT 1   
  C-773281754REPORT 1   C-773281755REPORT 1   
  C-773281753REPORT 3   C-0773280100REPORT 8   
  C-063444REPORT 8   C-063442

  Due to invalid records, certain report ID are throw away during the query,   therefore nothing in my temp table are in sequence. How could I spool out the   file according to group by the report ID??   Maybe this can be done in SQL script but I just running out of my brain for   this complex report. (I'm not doing it in Dynamic SQL).   Thanks for advise
  Helen      

  Do You Yahoo!?<A
  href="http://personal.mail.yahoo.com/?.refer=mailiyfoot">Yahoo! Mail Personal   Address - Get email at your own domain with Yahoo! Mail. Received on Wed Jun 13 2001 - 13:38:17 CDT