Skip to Main Content
  • Questions

  • SqlPlus spool extract getting truncated

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, SAK.

Asked: July 10, 2017 - 2:48 pm UTC

Last updated: July 19, 2017 - 11:07 pm UTC

Version: Database 11g - 11.2.0.3.0, 64 bit

Viewed 10K+ times! This question is

You Asked

Hi,

I have a script to extract data from the Oracle EBS database. I have provided an extract of the script below for reference:

----------------------------------------------------------------------
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;



The issue is that in the spool file, each line gets truncated as shown:

11|RG_DRS|168|53920|AXIS_SET_TYPE="R"|FORM|Define Axis Set AXIS_SET="R"|AXI
12|RG_DCS|168|53920|AXIS_SET_TYPE="C"|FORM|Define Axis Set AXIS_SET="C"|AXI
43|FND_FNDCPQCR|0|51614|MODE="VIEW"|FORM|View All Concurrent Requests|Concu
54|FND_FFMHL_KEY|0|100100|FORM|APPLICATIONS|Applications Form|Roll up Group

-----------------------------------------------------------------------

Could you please advise what the issue could be. I also tried using SQL Developer with the same result.

Thanks

and Chris said...

Are you sure there's nothing setting the linesize to the number of characters you're seeing?

And what happens when you set wrap on?

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How do I reply to your question.

A reader, July 14, 2017 - 6:36 pm UTC

I don't see any setting that would override the linesize. Not aware of any setting at the database level perhaps ?
Connor McDonald
July 16, 2017 - 7:20 am UTC

Try

select dump( 
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;


and see what the content is coming out like.

Similarly, 

select length( 
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;

Output of SQLs you provided

Arun Kumar, July 18, 2017 - 2:27 pm UTC

The spool files are huge, so I will cut paste first few lines from both here:

Output of DUMP SQL:

Typ=1 Len=48: 49,48,48,49,56,57,53,124,52,48,124,124,49,48,48,50,53,48,57,124,89
,124,78,111,116,105,102,105,99,97,116,105,111,110,32,100,101,32,87,111,114,107,1
02,108,111,119,124,124
Typ=1 Len=50: 49,48,48,49,56,57,53,124,53,48,124,124,49,48,48,50,53,48,54,124,89
,124,68,101,102,105,110,105,114,32,117,110,32,101,116,97,116,32,81,117,105,99,10
7,80,97,105,110,116,124,124
Typ=1 Len=49: 49,48,48,49,56,57,53,124,54,48,124,124,49,48,48,50,53,48,55,124,89
,124,76,97,110,99,101,114,32,117,110,32,101,116,97,116,32,81,117,105,99,107,80,9
7,105,110,116,124,124
Typ=1 Len=50: 49,48,48,49,56,57,53,124,55,48,124,124,49,48,48,50,53,49,48,124,89
,124,71,101,110,101,114,101,114,32,117,110,101,32,108,101,116,116,114,101,32,77,
83,32,87,111,114,100,124,124
Typ=1 Len=54: 49,48,48,49,56,57,53,124,56,48,124,124,49,48,48,50,53,48,56,124,89
,124,76,97,110,99,101,114,32,100,101,115,32,69,116,97,116,115,32,112,101,114,115
,111,110,110,97,108,105,115,101,115,124,124
Typ=1 Len=43: 49,48,48,49,56,57,54,124,49,48,124,124,49,48,48,50,52,51,52,124,89
,124,68,101,102,105,110,105,114,32,100,101,115,32,76,101,116,116,114,101,115,124
,124

Output of LENGTH SQL:

48

50

49

50

54

43

44

43

31

46

56


Please let me know if you need any other information.

Thanks

Connor McDonald
July 19, 2017 - 11:07 pm UTC

Well, based on that, none of your (sample) rows are more than 60 bytes long...yet your question says they are being truncated at 75 chars.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.