Home » Infrastructure » Unix » CHR(10) does not work from shell script (Oracle 11G)
CHR(10) does not work from shell script [message #617138] Wed, 25 June 2014 07:45 Go to next message
sinpeak
Messages: 59
Registered: January 2011
Location: india
Member
Hi,

Here is a snippet from my ksh script :

MV_CREATION=`sqlplus -S $DATABASE <<-_END
SET SERVEROUTPUT ON;
SET PAGES 0;
SET LINESIZE 255;
SET FEEDBACK OFF;
SET NEWLINE ON;

EXEC DBMS_OUTPUT.PUT_LINE('INFORM----FCFmv:'||'PLSQLLINE' ||':'|| TO_CHAR(SYSDATE,'DD/MM/YY HH24:MI:SS') || ' > Dropping public synonym.'||CHR(10));
EXEC DBMS_OUTPUT.NEW_LINE;


Both CHR(10) and DBMS_OUTPUT.NEW_LINE do not seem to work.
The output does not have a new line.
Tried CHR(13) as well. Does not work.

Any advise ?

Thanks.
Re: CHR(10) does not work from shell script [message #617145 is a reply to message #617138] Wed, 25 June 2014 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Both CHR(10) and DBMS_OUTPUT.NEW_LINE do not seem to work.


What does this mean? In what they do not work?
Copy and paste your session.

Re: CHR(10) does not work from shell script [message #617146 is a reply to message #617138] Wed, 25 June 2014 08:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
see a picture of my car that does not work.
any advice?

How will you or I decide when it is OK?
Re: CHR(10) does not work from shell script [message #617154 is a reply to message #617138] Wed, 25 June 2014 09:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I suggest you have a log file to capture the details. Else you might just lose the output details and also do the manual activity of copy pasting from what you see on the screen.

I bet neither korn shell nor Oracle is the culprit. Try it once again with a log file and copy paste the log file contents alongwith your script. That would show a crystal clear picture.
Re: CHR(10) does not work from shell script [message #617158 is a reply to message #617154] Wed, 25 June 2014 10:16 Go to previous messageGo to next message
sinpeak
Messages: 59
Registered: January 2011
Location: india
Member
My bad, I did not specify the problem.

I meant none of these options were leading to a new line.

chr(10)
chr(13)
DBMS_OUTPUT.NEW_LINE


Re: CHR(10) does not work from shell script [message #617159 is a reply to message #617158] Wed, 25 June 2014 10:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You are yet to post the log file content.

And logically you don't need put_line when you already have serveroutput enabled.
Re: CHR(10) does not work from shell script [message #617160 is a reply to message #617159] Wed, 25 June 2014 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Any advise ?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/


How can we reproduce what you report?
Re: CHR(10) does not work from shell script [message #617162 is a reply to message #617160] Wed, 25 June 2014 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>SET LINESIZE 255;

Hmmmm, I do not think that SET NEWLINE is a valid sqlplus option

SQL> help set

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n}
   AUTORECOVERY {OFF|ON}                    PAGES[IZE] {14|n}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         PAU[SE] {OFF|ON|text}
     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED]|EA[CH]|OFF}
   BLO[CKTERMINATOR] {.|c|ON|OFF}           RECSEPCHAR {_|c}
   CMDS[EP] {;|c|OFF|ON}                    SERVEROUT[PUT] {ON|OFF}
   COLSEP {_|text}                            [SIZE {n | UNLIMITED}]
   CON[CAT] {.|c|ON|OFF}                      [FOR[MAT]  {WRA[PPED] |
   COPYC[OMMIT] {0|n}                          WOR[D_WRAPPED] |
   COPYTYPECHECK {ON|OFF}                      TRU[NCATED]}]
   DEF[INE] {&|c|ON|OFF}                    SHIFT[INOUT] {VIS[IBLE] |
   DESCRIBE [DEPTH {1|n|ALL}]                 INV[ISIBLE]}
     [LINENUM {OFF|ON}] [INDENT {OFF|ON}]   SHOW[MODE] {OFF|ON}
   ECHO {OFF|ON}                            SQLBL[ANKLINES] {OFF|ON}
   EDITF[ILE] file_name[.ext]               SQLC[ASE] {MIX[ED] |
   EMB[EDDED] {OFF|ON}                        LO[WER] | UP[PER]}
   ERRORL[OGGING] {ON|OFF}                  SQLCO[NTINUE] {> | text}
     [TABLE [schema.]tablename]             SQLN[UMBER] {ON|OFF}
     [TRUNCATE] [IDENTIFIER identifier]     SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
   ESC[APE] {\|c|OFF|ON}                    SQLPRE[FIX] {#|c}
   ESCCHAR {@|?|%|$|OFF}                    SQLP[ROMPT] {SQL>|text}
   EXITC[OMMIT] {ON|OFF}                    SQLT[ERMINATOR] {;|c|ON|OFF}
   FEED[BACK] {6|n|ON|OFF}                  SUF[FIX] {SQL|text}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  TAB {ON|OFF}
   FLU[SH] {ON|OFF}                         TERM[OUT] {ON|OFF}
   HEA[DING] {ON|OFF}                       TI[ME] {OFF|ON}
   HEADS[EP] {||c|ON|OFF}                   TIMI[NG] {OFF|ON}
   INSTANCE [instance_path|LOCAL]           TRIM[OUT] {ON|OFF}
   LIN[ESIZE] {80|n}                        TRIMS[POOL] {OFF|ON}
   LOBOF[FSET] {1|n}                        UND[ERLINE] {-|c|ON|OFF}
   LOGSOURCE [pathname]                     VER[IFY] {ON|OFF}
   LONG {80|n}                              WRA[P] {ON|OFF}
   LONGC[HUNKSIZE] {80|n}                   XQUERY {BASEURI text|
   MARK[UP] HTML [OFF|ON]                     ORDERING{UNORDERED|
     [HEAD text] [BODY text] [TABLE text]              ORDERED|DEFAULT}|
     [ENTMAP {ON|OFF}]                        NODE{BYVALUE|BYREFERENCE|
     [SPOOL {OFF|ON}]                              DEFAULT}|
     [PRE[FORMAT] {OFF|ON}]                   CONTEXT text}


SQL> 


Re: CHR(10) does not work from shell script [message #617163 is a reply to message #617158] Wed, 25 June 2014 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me and I can prove it showing you my session:
SQL> begin
  2     dbms_output.put_line('A');
  3     dbms_output.put_line(chr(10));
  4     dbms_output.put_line('B');
  5     dbms_output.new_line;
  6     dbms_output.put_line('C');
  7  end;
  8  /
A


B

C

PL/SQL procedure successfully completed.

Now do the same thing for yours.
Re: CHR(10) does not work from shell script [message #617164 is a reply to message #617163] Wed, 25 June 2014 11:24 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
[oracle@localhost ~]$ sqlplus user1/user1 @nl.sql

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 25 09:20:10 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SPOOL NL.LOG
SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('LINE1');
  3  DBMS_OUTPUT.NEW_LINE;
  4  DBMS_OUTPUT.PUT_LINE('LINE2');
  5  DBMS_OUTPUT.NEW_LINE;
  6  DBMS_OUTPUT.NEW_LINE;
  7  DBMS_OUTPUT.PUT_LINE('LINE3');
  8  DBMS_OUTPUT.NEW_LINE;
  9  DBMS_OUTPUT.NEW_LINE;
 10  DBMS_OUTPUT.NEW_LINE;
 11  DBMS_OUTPUT.PUT_LINE('LINE4');
 12  DBMS_OUTPUT.PUT_LINE(CHR(10)||CHR(10));
 13  DBMS_OUTPUT.PUT_LINE('LINE5');
 14  DBMS_OUTPUT.PUT_LINE(CHR(13)||CHR(13));
 15  DBMS_OUTPUT.PUT_LINE('LINE6');
 16  END;
 17  /
LINE1
LINE2
LINE3
LINE4



LINE5

LINE6
SQL> SPOOL OFF
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ od -c NL.LOG
0000000   S   Q   L   >       B   E   G   I   N  \n           2        
0000020   D   B   M   S   _   O   U   T   P   U   T   .   P   U   T   _
0000040   L   I   N   E   (   '   L   I   N   E   1   '   )   ;  \n    
0000060       3           D   B   M   S   _   O   U   T   P   U   T   .
0000100   N   E   W   _   L   I   N   E   ;  \n           4           D
0000120   B   M   S   _   O   U   T   P   U   T   .   P   U   T   _   L
0000140   I   N   E   (   '   L   I   N   E   2   '   )   ;  \n        
0000160   5           D   B   M   S   _   O   U   T   P   U   T   .   N
0000200   E   W   _   L   I   N   E   ;  \n           6           D   B
0000220   M   S   _   O   U   T   P   U   T   .   N   E   W   _   L   I
0000240   N   E   ;  \n           7           D   B   M   S   _   O   U
0000260   T   P   U   T   .   P   U   T   _   L   I   N   E   (   '   L
0000300   I   N   E   3   '   )   ;  \n           8           D   B   M
0000320   S   _   O   U   T   P   U   T   .   N   E   W   _   L   I   N
0000340   E   ;  \n           9           D   B   M   S   _   O   U   T
0000360   P   U   T   .   N   E   W   _   L   I   N   E   ;  \n       1
0000400   0           D   B   M   S   _   O   U   T   P   U   T   .   N
0000420   E   W   _   L   I   N   E   ;  \n       1   1           D   B
0000440   M   S   _   O   U   T   P   U   T   .   P   U   T   _   L   I
0000460   N   E   (   '   L   I   N   E   4   '   )   ;  \n       1   2
0000500           D   B   M   S   _   O   U   T   P   U   T   .   P   U
0000520   T   _   L   I   N   E   (   C   H   R   (   1   0   )   |   |
0000540   C   H   R   (   1   0   )   )   ;  \n       1   3           D
0000560   B   M   S   _   O   U   T   P   U   T   .   P   U   T   _   L
0000600   I   N   E   (   '   L   I   N   E   5   '   )   ;  \n       1
0000620   4           D   B   M   S   _   O   U   T   P   U   T   .   P
0000640   U   T   _   L   I   N   E   (   C   H   R   (   1   3   )   |
0000660   |   C   H   R   (   1   3   )   )   ;  \n       1   5        
0000700   D   B   M   S   _   O   U   T   P   U   T   .   P   U   T   _
0000720   L   I   N   E   (   '   L   I   N   E   6   '   )   ;  \n    
0000740   1   6           E   N   D   ;  \n       1   7           /  \n
0000760   L   I   N   E   1                                            
0001000                                                                
*
0001340                                                              \n
0001360   L   I   N   E   2                                            
0001400                                                                
*
0001740                                                              \n
0001760   L   I   N   E   3                                            
0002000                                                                
*
0002340                                                              \n
0002360   L   I   N   E   4                                            
0002400                                                                
*
0002740                                                              \n
0002760  \n  \n                                                        
0003000                                                                
*
0003340                                                              \n
0003360   L   I   N   E   5                                            
0003400                                                                
*
0003740                                                              \n
0003760  \r  \r                                                        
0004000                                                                
*
0004340                                                              \n
0004360   L   I   N   E   6                                            
0004400                                                                
*
0004740                                                              \n
0004760   S   Q   L   >       S   P   O   O   L       O   F   F  \n
0004777
[oracle@localhost ~]$ cat nl.sql
SET SERVEROUTPUT ON
SET PAGES 0
SET LINESIZE 255
SET FEEDBACK OFF
SET TERM ON ECHO ON
SPOOL NL.LOG
BEGIN
DBMS_OUTPUT.PUT_LINE('LINE1');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('LINE2');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('LINE3');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('LINE4');
DBMS_OUTPUT.PUT_LINE(CHR(10)||CHR(10));
DBMS_OUTPUT.PUT_LINE('LINE5');
DBMS_OUTPUT.PUT_LINE(CHR(13)||CHR(13));
DBMS_OUTPUT.PUT_LINE('LINE6');
END;
/
SPOOL OFF
EXIT
[oracle@localhost ~]$ 

Previous Topic: Turn off output on the terminal for sql script in Unix shell
Next Topic: syntax error near unexpected token
Goto Forum:
  


Current Time: Thu Mar 28 04:52:25 CDT 2024