RE: split file from UNIX or SQL

[Kevin Lange]

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.

 

Here is a simple sample:

 

My query:

 

    select table_name, column_name, data_type from user_tab_columns;

 

The Original Data:

 

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           

 

10 rows selected.

 

After running the BREAK command:

 

    break on table_name skip page;

 

The returned data is

 

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

 

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

 

10 rows selected.

 

You could then have them pick up the correct pages. 

 

Look into the other SQLPlus report options.

 

Kevin

 -----Original Message-----
From: Helen rwulfjeq [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 13, 2001 2:02 PM
To: Multiple recipients of list ORACLE-L
Subject: 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 nd
REPORT 2   AD LE
REPORT 3   RC DD LKF 01 RENTAL
REPORT 4   *
REPORT 5   CS
REPORT 6   *  DD LKF 01 RENTAL
REPORT 7   RC DD LKF 01 ABCDE
REPORT 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-773280558
REPORT 1   C-773281754
REPORT 1   C-773281755
REPORT 1   C-773281753
REPORT 3   C-0773280100
REPORT 8   C-063444
REPORT 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!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.