OraFAQ Forum: Client Tools » SQL spool file, line gets cutoff at 80 characters

Home » Other » Client Tools » SQL spool file, line gets cutoff at 80 characters (Database 11g : 11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL spool file, line gets cutoff at 80 characters [message #664297]

Tue, 11 July 2017 09:45

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

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664301 is a reply to message #664297]

Tue, 11 July 2017 10:43

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664302 is a reply to message #664301]

Tue, 11 July 2017 10:50

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.

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664303 is a reply to message #664302]

Tue, 11 July 2017 10:58

BlackSwan
Messages: 26766
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.

Code: [Select all] [Show/ hide]

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>

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664304 is a reply to message #664297]

Tue, 11 July 2017 12:07

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664314 is a reply to message #664304]

Tue, 11 July 2017 14:39

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

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664316 is a reply to message #664314]

Tue, 11 July 2017 14:55

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664323 is a reply to message #664297]

Tue, 11 July 2017 16:39

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

SHOW ALL

To see individual values:

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

SET LINESIZE 5000;

SET LONG 10000

SET LONGCHUNKSIZE 10000

Report message to a moderator

Re: SQL spool file, line gets cutoff at 80 characters [message #664325 is a reply to message #664323]

Wed, 12 July 2017 01:02

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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:	sqlpptool SQL logging
Next Topic:	Oracle Error: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column (merged 2)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 03:02:07 CDT 2025