{"id":321,"date":"2013-09-24T00:12:56","date_gmt":"2013-09-24T00:12:56","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=321"},"modified":"2014-02-13T02:33:53","modified_gmt":"2014-02-13T02:33:53","slug":"sqlplus-newline-character","status":"publish","type":"post","link":"http:\/\/www.theSQLReport.com\/?p=321","title":{"rendered":"SQLPlus Newline Character"},"content":{"rendered":"

Not exactly one character will produce a new line in Oracle’s SQLPlus.\u00a0 By piping the ASCII CHR(13) then piping the ASCII CHR(10) in that order (\u00a0 || CHR(13) || CHR(10) || ) gives the query its desired result.\u00a0 This works on the Unix, Linux, and the Windows operating systems.\u00a0\u00a0 ASCII CHR(13) is the carriage return character.\u00a0 ASCII CHR(10) is the line feed character.<\/p>\n

Wikipedia provides a nice explanation on both the carriage return<\/a> and the line feed<\/a>.<\/p>\n

Shown in the example below are what the two characters together look like, and how each act when used independently.<\/p>\n

A simple query for SQLPlus:<\/p>\n

select ‘A system view is: ‘ || view_name || ‘\u00a0 ‘ || CHR(13) || CHR(10) || ‘– Line for Comments or Commit –‘ as COMMAND from all_views where owner = ‘SYS’ and view_name like ‘ALL%’;<\/em><\/p>\n

\"Simple<\/a><\/p>\n

ASCII CHR(13) ||\u00a0 ASCII CHR(10) together:<\/strong><\/p>\n

Below is what the top three lines of the result set looks like on Unix\/Linux system:<\/p>\n

\"Unix<\/a><\/p>\n

By using the ASCII characters 13 & 10 in that order, the result data can be moved between files among different operating systems and the appearance will not change.<\/p>\n

\"Notepad<\/a><\/p>\n

ASCII CHR(10) alone:<\/strong><\/p>\n

Now if only the Line Feed character ASC(10) is used , it will display normally in SQLPlus on a Unix\/Linux system (example shown below).<\/p>\n

\"ascii<\/a><\/p>\n

However, when the result data is opened in a Windows operating system program, like notepad, the data will still be all on the same line.<\/p>\n

\"Windows<\/a><\/p>\n

ASCII CHR(13) alone:<\/strong><\/p>\n

If only the Line Feed (ASC(13)) is used , the results on a Unix\/Linux system overwrite the beginning of the line.<\/p>\n

\"thriteen_only\"<\/a><\/p>\n

The result set also has the same results as using the ASC (10) character when view on the Windows operating system.<\/p>\n

\"thirteen_notepad\"<\/a><\/p>\n

Please note that the results may vary using different programs.<\/p>\n","protected":false},"excerpt":{"rendered":"

Not exactly one character will produce a new line in Oracle’s SQLPlus.\u00a0 By piping the ASCII CHR(13) then piping the ASCII CHR(10) in that order (\u00a0 || CHR(13) || CHR(10) … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":335,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-321","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle"],"_links":{"self":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/321"}],"collection":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=321"}],"version-history":[{"count":21,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/321\/revisions"}],"predecessor-version":[{"id":365,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/321\/revisions\/365"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/335"}],"wp:attachment":[{"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=321"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=321"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=321"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}