Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> generate fixed length txt file
itzkashi
post May 3 2017, 02:20 PM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 3-May 17
Member No.: 51,957



hi,

i am using below query to generate the fixed length txt file. this sql is being called from shell script.
This is supposed to be a fixed record file with the below definitions. There must be 2 byte filler after the CAT_ID AND each line should have total of 270 bytes.

field length
EMAIL_ADDR 1-255
CAT_ID 256-268
FILLER 269-270

issues
-------
1) i am not getting 270 byte file for one record.
2) 3rd col doesn't have 2 byte length as it is null.

please advise.


test.sql

set linesize 1000;
set newpage 0;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET VERIFY OFF;
whenever sqlerror exit failure;
whenever oserror exit failure;
alter session enable parallel dml;
SELECT DISTINCT rpad(EMAIL_ADDR,255),rpad(CAT_ID,13),rpad(' ',3) filler
FROM T1
Go to the top of the page
 
+Quote Post
burleson
post May 6 2017, 09:32 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi Itzkashi, and welcome to the forum!

>> This is supposed to be a fixed record file with the below definitions

First, I would set the linesize to 270

Then see this example, noting the col parameter:

http://dbaforums.org/oracle/index.php?showtopic=1259




--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd October 2017 - 09:41 PM