0

So, I have a script:

set heading off;
set feedback off;
set linesize 10000;
set newpage NONE;
set colsep '~'
whenever sqlerror exit 1;
WHENEVER OSERROR EXIT 255;
select TO_CHAR(je.key_date, 'YYYY-MM-DD HH24:MI:SS'), je.message, je.id from esslog_job_errors je order by key_date desc fetch first 10 rows only;
exit;

Which produces:

2023-11-18 23:59:55~Execution error for request 6358639. Reason: ESS-07034 Job logic indicated a business error occurred while executing an asynchronous java job for request 6358639. Job error is: BIP job failed.                                                                                                                                                                                                                ~ 194852628
2023-11-18 23:59:55~Execution error for request 1976388. Reason: ESS-07034 Job logic indicated a business error occurred while executing an asynchronous java job for request 1976388. Job error is: BIP job failed with delivery error.                                                                                                                                                                                            ~ 195438040

So, somehow the field has either a lot of space between it, or \n~ in it. Can someone explain what's going on?

I want these fields packed in. The equivalent of:

select TO_CHAR(je.key_date, 'YYYY-MM-DD HH24:MI:SS') ||'~'|| je.message ||'~'|| je.id from esslog_job_errors je order by key_date desc fetch first 10 rows only;

Which produces what I need (so, there are no spacing issues in the message field)

2023-11-18 23:59:55~Execution error for request 6358639. Reason: ESS-07034 Job logic indicated a business error occurred while executing an asynchronous java job for request 6358639. Job error is: BIP job failed.~194852628
2023-11-18 23:59:55~Execution error for request 1976388. Reason: ESS-07034 Job logic indicated a business error occurred while executing an asynchronous java job for request 1976388. Job error is: BIP job failed with delivery error.~195438040


without having to do this in every script

EDIT

Here is a table description:

DESCRIBE esslog_job_errors;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KEY_DATE                                           DATE
 POD                                                VARCHAR2(64)
 JOB_DEFINITION                                     VARCHAR2(256)
 MESSAGE                                            VARCHAR2(4000)
 COMPONENT                                          VARCHAR2(256)
 ID                                        NOT NULL NUMBER(38)
 ERROR_TYPE                                NOT NULL NUMBER
 CREATED_DATE                              NOT NULL TIMESTAMP(6)
 UPDATED_DATE                                       TIMESTAMP(6)

EDIT

I have since determined that it's actually inserting tabs:

0000230  \t  \t  \t                   ~       1   9   4   5   7   3   3
0000240   7   8  \n   2   0   2   3   -   1   1   -   1   8       2   3
0000250   :   5   9   :   5   9   ~   E   x   e   c   u   t   i   o   n
0000260       e   r   r   o   r       f   o   r       r   e   q   u   e
0000270   s   t       8   0   1   3   8   1   .       R   e   a   s   o
0000280   n   :       n   u   l   l  \t  \t  \t  \t  \t  \t  \t  \t  \t
0000290  \t  \t  \t  \t  \t  \t  \t  \t  \t  \t  \t  \t  \t  \t  \t  \t

1 Answer 1

0
select TO_CHAR(je.key_date, 'YYYY-MM-DD HH24:MI:SS'), 
       je.message,                           --> a lot of spaces between this ...
       je.id                                 --> ... and this 
from esslog_job_errors je 
order by key_date desc fetch first 10 rows only;

It looks as if message column's datatype is char; is it? If so, all values are right-padded with spaces up to the maximum column length.

Example:

SQL> create table test (message char(20));

Table created.

SQL> insert into test values ('abc');

1 row created.

And now:

SQL> select message,
  2         length(message) len,
  3         '#' || message || '#' message_2,
  4         '#' || trim(message) ||'#' message_3
  5  from test;

MESSAGE                     LEN MESSAGE_2              MESSAGE_3
-------------------- ---------- ---------------------- ----------------------
abc                          20 #abc                 # #abc#

SQL>
  • message is original value; you don't actually see anything strange
  • len is column's length; although I put only 3 letters in there, length is the same as column's declared length
  • message_2: that's visible if you concatenate some character to beginning and end of that column; see the final # character and how far right it is?
  • message_3 might be your savior - trim superfluous spaces

Or, applied to your code:

select TO_CHAR(je.key_date, 'YYYY-MM-DD HH24:MI:SS'), 
       trim(je.message) as message,                    --> here
       je.id      
from esslog_job_errors je 
order by key_date desc fetch first 10 rows only;
3
  • This doesn't fix it. The data type is VARACHAR2 (I updated the question). When I apply your trim, along with all the directives, I get the same result. Your example is missing the directives, which I think is where the issue is Commented Nov 24, 2023 at 21:07
  • Aha; OK then. Try to include SET TAB OFF. If it doesn't help either, then - I'm afraid - you'll have to concatenate separator between every column you select, i.e. use code you already know: select TO_CHAR(je.key_date, 'YYYY-MM-DD HH24:MI:SS') ||'~'|| je.message ||'~'|| je.id from esslog_job_errors je order by key_date desc fetch first 10 rows only;
    – Littlefoot
    Commented Nov 25, 2023 at 19:21
  • I'll give some time for others to chime in. Unfortunately, that also didn't work. I ended up applying some sed post-facto to clean it up. Commented Nov 27, 2023 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.