Re: Printing extended ascii
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: gabriel.aragon@xxxxxx
- Date: Thu, 25 Mar 2010 18:19:59 +0100
Gabriel,
Le'ts take it another way. If you want to draw histograms with
SQL*Plus, why don't you generate HTML pages? It will definitely look
better than any fancy ASCII character, you can easily import it into a
Word document, etc, etc. I use this a lot.
I have suggested the following to Grant Allen for "Oracle SQL Recipes"
that he co-authored for Apress with Bob Bryla and Darl Kuhn, I hope he
won't mind my re-publishing it here - explanations are in the book if
you need them:
col salary_graph format A40
set pagesize 0
set linesize 80
set feedback off
spool ugly.txt
set recsep off
select employee_id, last_name, rpad('X', floor(salary/1000), 'X')
salary_graph
from hr.employees
order by salary desc
/
spool off
spool pretty.html
select case rownum
when 1 then
'<html>' || chr(10) ||
'<head>' || chr(10) ||
'<style type="text/css">' || chr(10) ||
' body {background-color: Lavender;}' || chr(10) ||
' table.outer {width: 80%; border: Black solid 1px;}' ||
chr(10) ||
' table.inner {width: 100%; border: none;}' || chr(10) ||
' th {background-color: LightSteelBlue; color:
WhiteSmoke;}' || chr(10) ||
' .bar {background-color: LightSlateGray;}' || chr(10) ||
' .empty {background-color: Lavender;}' || chr(10) ||
' .left {text-align: left; padding-left: 5px;}' || chr(10) ||
' .right {text-align: right; padding-right: 5px;}' ||
chr(10) ||
'</style>' || chr(10) ||
'</head>' || chr(10) ||
'<body>' || chr(10) ||
'<center>' || chr(10) ||
'<table class="outer">' || chr(10) ||
'<tr>' || chr(10) ||
'<th width="15%">Employee ID</th>' || chr(10) ||
'<th width="20%">Last Name</th>' || chr(10) ||
'<th>Salary Graph</th>' || chr(10) ||
'</tr>'
else ''
end ||
'<tr><td class="right">' || to_char(employee_id) || '</td>' ||
chr(10) ||
'<td class="left">' || last_name || '</td>' || chr(10) ||
'<td class="left">' ||
'<table class="inner">' || chr(10) ||
'<tr><td class="bar" width="' || to_char(round(100 * salary
/ maxsalary)) || '%">' || chr(38) || 'nbsp;</td>' || chr(10) ||
'<td class="empty" width="' || to_char(100 - round(100 *
salary / maxsalary)) || '%">' || chr(38) || 'nbsp;</td></tr>' || chr(10) ||
'</table></td></tr>' || chr(10) ||
case rownum
when cnt then
'</table>' || chr(10) ||
'</center>' || chr(10) ||
'</body>' || chr(10) ||
'</html>'
else ''
end
from (select employee_id,
last_name,
salary,
count(*) over () as cnt,
max(salary) over () as maxsalary
from hr.employees
order by salary desc)
/
spool off
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Aragon, Gabriel (GE, Corporate, consultant) wrote:
> No luck, I just cant get desired character, am I doing something wrong?
>
> sql >select parameter, value from v$nls_parameters where parameter
> in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET') ;
>
> PARAMETER VALUE
> ------------------ -------------------------------------------------------
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA
> NLS_CHARACTERSET UTF8
>
> $ NLS_LANG=AMERICAN_AMERICA.UTF8; export NLS_LANG
>
> $ echo $NLS_LANG
> AMERICAN_AMERICA.UTF8
>
> sql> declare
> x char(5);
> begin
> for i in 124..219
> loop
> select chr(i) into x from dual;
> dbms_output.put_line(i||'-'||x);
> end loop;
> end;
> /
>
> 2 3 4 5 6 7 8 9 10
> 124-|
> 125-}
> 126-~
> 127
> 128-
> 129-
> 130-
> 131-
> 132-
>
> 133-
>
> 134-
> 135-
> 136-
> 137-
> 138-
> 139-
> 140-
> 142-
> 143-
> 144-
> 145-
> 146-
> 147-
> 148-
> 149-
> 150-
> 151-
> 152-
> 153-
> 154-
> 155-
>
> 157-
>
> 159-
> 160-
> 161-¡
> 162-¢
> 163-£
> 164-¤
> 165-¥
> 166-¦
> 167-§
> 168-¨
> 169-©
> 170-ª
> 171-«
> 172-¬
> 173-
> 174-®
> 175-¯
> 176-°
> 177-±
> 178-²
> 179-³
> 180-´
> 181-µ
> 182-¶
> 183-·
> 184-¸
> 185-¹
> 186-º
> 187-»
> 188-¼
> 189-½
> 190-¾
> 191-¿
> 192-À
> 193-Á
> 194-
> 195-
> 196-
> 197-
> 198-
> 199-
> 200-
> 201-
> 202-
> 203-
> 204-
> 205-
> 206-
> 207-
> 208-
> 209-
> 210-
> 211-
> 212-
> 213-
> 214-
> 215-
> 216-
> 217-
> 218-
> 219-
>
>
>
> -----Original Message-----
> From: De DBA [mailto:dedba@xxxxxxxxxx]
> Sent: Miércoles, 24 de Marzo de 2010 07:59 p.m.
> To: Bobak, Mark
> Cc: Aragon, Gabriel (GE, Corporate, consultant)
> Subject: Re: Printing extended ascii
>
> My bad... should've exported NLS_LANG, not just set it ... I'll go back in my
> corner now and be ashamed..
>
> Cheers,
> Tony
>
> On 25/03/10 11:45 AM, Bobak, Mark wrote:
>
>> Ok, but what are you NLS_LANGUAGE, NLS_TERRITORY, and NLS_CHARACTERSET on
>> the database server?
>>
>> select * from v$nls_parameters where parameter
>> in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
>>
>> make sure that what you set NLS_LANG to matches the above, and then, if the
>> terminal is capable, it should work.
>>
>> Note that NLS_LANG should be set to:
>> <NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>
>>
>> Hope that helps,
>>
>> -Mark
>>
>> -----Original Message-----
>> From: oracle-l-bounce@xxxxxxxxxxxxx
>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of De DBA
>> Sent: Wednesday, March 24, 2010 9:36 PM
>> To: Bobak, Mark
>> Cc: gabriel.aragon@xxxxxx; oracle list
>> Subject: Re: Printing extended ascii
>>
>> I tried again, just to be sure, but no. My database is 10.2.0.4 for
>> MAC/OSX, NLS_LANG=AMERICAN.AMERICA-WE8ISO8895P1, the terminal is
>> capable of displaying the plus-minus sign (perl can do it..) but
>> SQL*Plus stubbornly refuses.
>>
>> Perhaps this differs between platforms. The OP uses SQL*Plus on SUN
>> and fails, I tried on MAC/OSX, fails too? My MAC/OSX terminal says it
>> behaves like an xterm-color. Sun's gnome-terminal should also behave
>> that way, I believe.
>>
>> Cheers,
>> Tony
>>
>> On 25/03/10 11:06 AM, Bobak, Mark wrote:
>>
>>> Works for me in SQL*Plus, but you need to consider the database character
>>> set and the value of NLS_LANG, as well as the capability of your terminal.
>>>
>>> If I don't have NLS_LANG set, I get the upside down question mark. If I
>>> set NLS_LANG to the database character set, then SQL*Plus doesn't attempt
>>> to do any kind of translation, and I get the plus/minus sign, "±".
>>> Assuming your terminal program is actually capable of displaying it, you
>>> should be able to do the same.
>>>
>>> -Mark
>>>
>>> -----Original Message-----
>>> From: oracle-l-bounce@xxxxxxxxxxxxx
>>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of De DBA
>>> Sent: Wednesday, March 24, 2010 8:54 PM
>>> To: gabriel.aragon@xxxxxx
>>> Cc: oracle list
>>> Subject: Re: Printing extended ascii
>>>
>>> Hi Gabriel,
>>>
>>> It seems to be a limitation of sql*plus. Some testing on my mac shows
>>> that even if the terminal encoding is set to ASCII and something like:
>>>
>>> $ perl -e 'print chr(177)'
>>>
>>> prints the expected plus-minus sign, a
>>>
>>> SQL> select chr(177) from dual;
>>>
>>> still displays the unwanted question mark. However the same query
>>> executed in tOra does return the plus-minus sign.
>>>
>>> If that's correct you have 2 options: execute your query in a goowee
>>> tool or use ASCII art (_-=+><) instead.
>>>
>>> Cheers,
>>> Tony
>>>
>>> On 25/03/10 4:58 AM, Aragon, Gabriel (GE, Corporate, consultant) wrote:
>>>
>>>> Hi list,
>>>> is there any way to print extended ascii? Let's say Im trying to
>>>> make a bar graph like:
>>>> select
>>>> ....., '|'||rpad(fieldn,30,'X')||'|'
>>>> from
>>>> ...
>>>> to display something like:
>>>> field1 field2 ... |XXXXXXXXXX|
>>>> but I want to replace the X for the square character, I think is
>>>> ascii 176, 177, 178 or 219 but cant process ascii code besides 126.
>>>> Is it possible to do this?
>>>> 10.2.0.4
>>>> Sun
>>>> UTF8
>>>> TIA
>>>> *Gabriel *
>>>>
>>> --
>>> //www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>>
>>
>>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
