Toad's Export Dataset For "Fixed Field Spacing" format Is Adding Spaces

Toad for Oracle
1

I’m using Toad Version 12.0.0.61

I have a SELECT statement that contains RPAD for each of the piped columns to pull records of fixed length columns from Oracle tables. When I export the result from the Data Grid to a .txt file, however, the export adds a bunch of spaces after the last field. My solution was to preceed it with a “Create table”, first creating a table, and then use the “alter table modify” to change the record length to equal the sum of all of the RPAD’ed columns…then simple do a “SELECT *” and export from the grid using “fixed field spacing” format as before. The results give me good column by column spacing (as always), but even though I’ve modified the record length down to 164, I can stil see 2 spaces add to the last field before the carriage return/line feed. How can I resolve this? The receiving system my extract feeds needs the record length to be 164. Thanks!

2

Try right clicking in the grid and choosing “Export to flat file”.

Set the “Spaces between fields” option to whatever you want (sounds like you want 0).

Click ‘Generate columns’

Then go to the Options tab and put in your output file name and click “Execute”

3

John:

Worked like a charm. Thanks much. Now I’m wondering if I even needed to create temp table just so I could alter the size of each record down to where I needed it to be. I’m going do some additional testing, but I think if I’m RPAD’ing all of my piped fields (into a record by record extraction) and just use what you’ve shown me here, I should end up with each field with its proper length and the record length equalling the sum of all of the fields. Make sense?

4

Yeah you should test it to be sure, but I don’t think the RPAD is needed in your query/temp table.