We help IT Professionals succeed at work.

sqlplus returning data from user function not spooling correctly

1,315 Views
Last Modified: 2013-12-18
I'm having a very strange problem occur.  It is problaby due to a sqlplus setting, but I haven't been able to figure out what it is.  I have a package of a bunch of user functions that  requesting data from the database.   Outside of the package, I am running some SQL that calls the user function and in sqlplus, this is being spooled to a file.   But when I look at the file, the data being returned from the function is spooled with a CRLF both before and after the field. (I looked at the file in Hex and saw 0D0A's.  

this is something close to what I'm doing:
select fielda,fieldb,
          myfuncs.getdata(parm1,parm2) fieldc,  
         fieldd
from tb1
where blah,etc...
fieldc only returns a single Character and I know that works properly, yet when looking at the spooled file, there is a 0D0A both before and after the field.  Other fields are ok,only the ones returned from user functions. (I have several functions I do this for)
I have the following set in sqlplus:  
set term on
set heading off  
set feedback off  
set verify off
set pagesize 0
set echo off
set serveroutput off
set trimout on
set trimspool on
set linesize 1000

the PL/SQL functions are defined to return CHAR, but I have also tried with VARCHAR and VARHCAR2, just trying anything.  In the sql, I also did a select of the length(funcreturnvalue) and it shows the correct length.

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
0D0A seems to be a carriable return + line feed in hex ....
so, I would check out in that direction ...

could you show the function's code, please

Author

Commented:
yes, I know 0D0A is a CR/LF, I'm just not sure why its putting it there.  I have stripped all the code out to the following in the function to this and it still happens:   (I get 0D0A590D0A in the spooled file)  - 59 is Y

      FUNCTION GetData (pMIN IN NUMBER, pRequestDate IN VARCHAR)
      RETURN       CHAR
      IS
         BEGIN
              return 'Y';
                  END GetData;;

Author

Commented:
One other thing to note.  If I use substr(myfuncs.getdata...1,1), the od0a is not there.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please try this:
FUNCTION GetData (pMIN IN NUMBER, pRequestDate IN VARCHAR)
RETURN       CHAR
IS
  v_res CHAR(1);
BEGIN
  v_res := 'Y';          
  return v_res;
END GetData;;

Open in new window

Author

Commented:
That is what I did.  I just took out the variable before posting.   The other wierd thing happening is that I also am selecting a number field from the database that is 7 digits.  It spools with a leading space.  I tried using Column with format 9999999, as well as, numwidth 7.   I am trying now without any sqlplus command and see what is displayed on screen.  I was suppressing display before and only spooling.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
It's the column itself causing the problem.  If SQL*Plus doesn't know how wide to make the column, it defaults to some large number (I think 80).

To see this set heading on and set pagesize to a non-zero number and run the query.

You need to set the column width:
column fieldc format a1

Author

Commented:
I will try that.

Author

Commented:
That fixed it for the one column.  Thanks.  It still happens on some other fields.  How would I do that if I dont know the width that a field should be.  If I make the format longer, will it pad or truncate ?  The other issue is the field field I select is a NUMBER in the database (7 digits), but when spooled, it puts a leading space in.  I tried column fielda format 9999999, but that didn't work.  I tried trim(fielda) even though that doesn't make sense and it trimmed the first char, but put trailing spaces (I guess because of the reason you mentioned before).  I also tried setting numwidth to 7, but no luck.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
My guess is you are looking to come up with a fixed layout or CSV for loading by another application.

The easiest way is to just concatinate all the fields together:

select fielda || fileldb || myfuncs.getdata(parm1,parm2) || fieldc from table;
or
select fielda || ',' || fileldb || ',' || myfuncs.getdata(parm1,parm2) || ',' || fieldc from table;

Author

Commented:
Actually, I'm trying to recreate a tab delimited file that we created in the past.  However, because of a backup issue where we cant restore the file anymore, I had to write user functions to get the correct data from some archive tables and rebuild best we can.  The tab delimited part is not the issue.  I was just using SQLplus to make it easier for the group that will run this. but since having format issues, I have gone back to using SQLDeveloper and just using a Export from the grid to a text file.  I would like to figure out my formatting issues eventually though, if not just for my own knowledge.  I still dont know why it spools a number field with a leading space.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Without concatinating the columns Oralce places a space between columns so doing 'select fielda, fieldb from table', ther eshould be a space between fielda and fieldb.

To generate a tab delimietd file use concat like I did above.

select fielda || chr(9) || fieldb || chr(9) ...;

Author

Commented:
I know the default numwidth is 10 and without a column format 9999999, I get 3 leading spaces, but with the column, there is just 1 leading space.  thanks for your help by the way on the other stuff.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I'm confused.  There is a leading space under the column header or between the columns?  If the space is between the columns, I explained that.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I just remembered the default column seperator is a space. You can set it to whatever you want: set colsep = ""

You can set it to a tab character but you need an editor that will allow you to insert a hard tab.

Author

Commented:
Sorry for the confusion.  I did something very simple like this: select numberfield from table.
all my data is spooled to a file.  the number in this case is 1000115, but in the spooled file, when you look at it, it has a leading space.  maybe I need to to_char it first and be done with it.

so:

column fielda format 9999999
spool test
select fielda from table;
spool off
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.