Home » SQL & PL/SQL » SQL & PL/SQL » carriage return and semi colon in a quoted string
carriage return and semi colon in a quoted string [message #341412] Mon, 18 August 2008 17:15 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
For a part of a 2 step spool process I want to be able to get carriage returns into a string.

For instance, as a very simplistic example:
select 'abc
def' from dual;

This works fine.

However I also want a ';' in there.

select 'abc;def' from dual;

That also works fine. However when I combine the two.

select 'abc;
def' from dual;

This does NOT work fine. Or rather it works fine from my Toad window, but not from the sqlplus prompt.

My work around is:

select 'abc' || chr(59) || '
def' from dual;

But I'm not particularly fond of it.

My actual query:
SELECT        'spool '
           || lower(table_name)
           || '.dat
select '
           || SUBSTR (SYS_CONNECT_BY_PATH (column_name, '||''    ''||'), 11)
           || ' from '
           || table_name
           || chr(59) -- for some reason having a ';' causes an error
           || '
spool off'
      FROM (SELECT table_name, column_name, column_id,
                   COUNT (*) OVER (PARTITION BY table_name) cnt
              FROM all_tab_columns
             WHERE owner = '<INSERT TABLE OWNER HERE>')
     WHERE column_id = cnt
START WITH column_id = 1
CONNECT BY PRIOR column_id + 1 = column_id AND PRIOR table_name = table_name;


Re: carriage return and semi colon in a quoted string [message #341413 is a reply to message #341412] Mon, 18 August 2008 17:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


CHR(10) and/or CHR(13) depending upon Operating Ssystem; which you neglected to specify,
Re: carriage return and semi colon in a quoted string [message #341414 is a reply to message #341413] Mon, 18 August 2008 17:48 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod

As to the OS, I know it is Linux RedHat, however I connect via cygwin *shudders*, and I don't know the exact version number.

I think I agree that using a CHR code for the new line (it's CHR(10)) is slightly more elegant than using a new line inside quotes, and a CHR code for the semi colon. Ideally there would be a method to do this without either, but such is life.

Re: carriage return and semi colon in a quoted string [message #341470 is a reply to message #341412] Mon, 18 August 2008 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select 'abc;'||'
def' from dual;

Regards
Michel

[Updated on: Mon, 18 August 2008 23:49]

Report message to a moderator

Re: carriage return and semi colon in a quoted string [message #341484 is a reply to message #341470] Tue, 19 August 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or also:
SQL> set sqlterminator '/'
SQL> select 'abc;
  2  def' from dual
  3  /
'ABC;DEF
--------
abc;
def

1 row selected.

Regards
Michel
Re: carriage return and semi colon in a quoted string [message #341634 is a reply to message #341412] Tue, 19 August 2008 11:57 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Thanks, problem solved, now I just need to find out why spool is taking forever and a day.
Re: carriage return and semi colon in a quoted string [message #341635 is a reply to message #341634] Tue, 19 August 2008 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good luck in your investigation.
With what you posted we can't help.

Regards
Michel
Re: carriage return and semi colon in a quoted string [message #341652 is a reply to message #341412] Tue, 19 August 2008 16:41 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Is 6 minutes an expected time to spool a 143M file?

If not is there any recommended procedures, or a FAQ or other resource to read on how to go about improving spool performance?

[Updated on: Tue, 19 August 2008 16:43]

Report message to a moderator

Re: carriage return and semi colon in a quoted string [message #341685 is a reply to message #341652] Tue, 19 August 2008 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First use "dd" (or equivalent on your system) to know the speed of your io subsystem.
Then it depends on your queries.
In the end, 6 minutes for 143MB is not surprising, it means 400KB per second, assuming there is concurrent workload, this seems quite good.

Regards
Michel
Re: carriage return and semi colon in a quoted string [message #341701 is a reply to message #341652] Wed, 20 August 2008 00:43 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If you are displaying the output to the screen while you are spooling, that can slow things down. Use SET TERMOUT OFF from SQL*Plus to eliminate that.
Previous Topic: Getting ORA-03114: not connected to ORACLE error
Next Topic: How to Stop insertion of specific transactions in a table in ORACLE
Goto Forum:
  


Current Time: Sat Dec 03 22:39:38 CST 2016

Total time taken to generate the page: 0.10425 seconds