Home » SQL & PL/SQL » Client Tools » SQL spool file, line gets cutoff at 80 characters (Database 11g : 11.2.0.3.0)
SQL spool file, line gets cutoff at 80 characters [message #664297] Tue, 11 July 2017 09:45 Go to next message
OracleGuy123
Messages: 10
Registered: September 2016
Junior Member
Hi,

I have a SQL script to extract data to a spool file. The following initialization parameters and the SQL to extract the data:
---------------------------------------
set feedback off
set trims on
set linesize 5000
set pagesize 0
set TERMOUT OFF
set AUTOTRACE OFF
set VERIFY off
set ECHO off
set SERVEROUTPUT off
set term off
set colsep '|'
set escape /
set heading on
set trimspool on
set headsep on

spool C:\temp\FP_Menu_Entries.txt

select
MENU_ID || '|' ||
ENTRY_SEQUENCE || '|' ||
SUB_MENU_ID || '|' ||
FUNCTION_ID || '|' ||
GRANT_FLAG || '|' ||
replace(PROMPT,chr(10)) || '|' ||
replace(DESCRIPTION, chr(10)) || '|'
from APPS.fnd_menu_entries_vl;



spool C:\temp\FP_Functions.txt

select
FUNCTION_ID || '|' ||
FUNCTION_NAME || '|' ||
APPLICATION_ID || '|' ||
FORM_ID || '|' ||
replace(PARAMETERS, chr(10)) || '|' ||
TYPE || '|' ||
replace(USER_FUNCTION_NAME, chr(10)) || '|' ||
replace(DESCRIPTION, chr(10)) || '|'
from APPS.fnd_form_functions_vl;
----------------------------------------------------------------

When the spool file is created, each line gets truncated after 80 characters. I have set the linesize to 5000 as shown above, but the truncation issue persists.

Appreciate any insight and a solution to this issue.

Thanks
Re: SQL spool file, line gets cutoff at 80 characters [message #664301 is a reply to message #664297] Tue, 11 July 2017 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 25719
Registered: January 2009
Location: SoCal
Senior Member
from sqlplus standpoint you are producing a single column report.
so you need to convince sqlplus that this column is larger than 80 characters.
Re: SQL spool file, line gets cutoff at 80 characters [message #664302 is a reply to message #664301] Tue, 11 July 2017 10:50 Go to previous messageGo to next message
OracleGuy123
Messages: 10
Registered: September 2016
Junior Member
I understand, isn't what we do when I set linesize 5000. Is there anything else that needs to be done.
Re: SQL spool file, line gets cutoff at 80 characters [message #664303 is a reply to message #664302] Tue, 11 July 2017 10:58 Go to previous messageGo to next message
BlackSwan
Messages: 25719
Registered: January 2009
Location: SoCal
Senior Member
OracleGuy123 wrote on Tue, 11 July 2017 08:50
I understand, isn't what we do when I set linesize 5000. Is there anything else that needs to be done.
SQL>  select table_name, sum(blocks) from user_tables group by table_name;

TABLE_NAME
--------------------------------------------------------------------------------
SUM(BLOCKS)
-----------
EMP
	  0

A
	  5

B
	  5


TABLE_NAME
--------------------------------------------------------------------------------
SUM(BLOCKS)
-----------
C
	  5

EVENT
	  0


SQL> column table_name format a30
SQL> /

TABLE_NAME		       SUM(BLOCKS)
------------------------------ -----------
EMP					 0
A					 5
B					 5
C					 5
EVENT					 0

SQL> 

Re: SQL spool file, line gets cutoff at 80 characters [message #664304 is a reply to message #664297] Tue, 11 July 2017 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65202
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ What are the type of each select column?
2/ Why do you "set colsep '|'" then manually truncate the columns? Why don't you let SQL*Plus format the column as speficied?
3/ In your previous topic John asked you to explain your solution, why didn't you do it? Or do you estimate if you have a solution with the help of others you have no reason to help them and others in return?

Re: SQL spool file, line gets cutoff at 80 characters [message #664314 is a reply to message #664304] Tue, 11 July 2017 14:39 Go to previous messageGo to next message
OracleGuy123
Messages: 10
Registered: September 2016
Junior Member
With respect to 3. Regarding my previous topic, I did not see John's reply as there was no automated email. I was also a new to orafaq.com and not familiar with how it works. Will provide that information on that thread. There is no need to doubt my intentions.

Thanks
Re: SQL spool file, line gets cutoff at 80 characters [message #664316 is a reply to message #664314] Tue, 11 July 2017 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65202
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is an automating email or not depending on what you choose in your account profile (see " Control Panel" at top of page).

What about points 1 and 2?

Re: SQL spool file, line gets cutoff at 80 characters [message #664323 is a reply to message #664297] Tue, 11 July 2017 16:39 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Researching the Web I found some similar issues with the truncation of the output to a Spool File. It
appeared that the issue was related to the data type of the output when it is related to a LONG or CLOB.

Of the various SQLPLUS SET Variables, the following 3 variables modify data output widths. Some of this info
is a bit dated so I encourage further research specific to the Version of Oracle you are using. Not sure if
will solve your issue, but it is worth a try to add them to your script.

Variables:
LINESIZE      - The width of the SQLPLUS Output before the line wraps. Defaults to 80 Characters. 
                Maximum Width of 32767.

LONG          - Maximum width for displaying a LONG value. Defaults to 80 Characters.

LONGCHUNKSIZE - The fetch size in Characters for retrieving a LONG Value. Defaults to 80 Characters.

To Display All the Values of all the SET Variables you can use:
SHOW ALL

To see individual values:
SHOW LINESIZE

SHOW LONG

SHOW LONGCHUNKSIZE

From the articles I reviewed most were setting the LONG and LONGCHUNKSIZE settings were generally fairly large.
I did not see any optimal settings for these variable and it would most likely depend on your environment and requirements.

SET LINESIZE 5000;

SET LONG 10000

SET LONGCHUNKSIZE 10000
Re: SQL spool file, line gets cutoff at 80 characters [message #664325 is a reply to message #664323] Wed, 12 July 2017 01:02 Go to previous message
Michel Cadot
Messages: 65202
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is why I asked:
Quote:
1/ What are the type of each select column?
An answer from OP will give him/her (and you) the answer 4 hours earlier with no effort.

When you quote the documentation, please the revevant link to it.

[Updated on: Wed, 12 July 2017 01:04]

Report message to a moderator

Previous Topic: Oracle Error: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column (merged 2)
Next Topic: Generate DDL using Sql Developer
Goto Forum:
  


Current Time: Wed Nov 22 21:45:56 CST 2017

Total time taken to generate the page: 0.02957 seconds