how to trim oracle sql sentence in unix
I have certain SQL sentence that dumps the result into an sql file, however somehow it retrieves the correct value but adds a lot of blank spaces before the value, actually the value apears at the end of the line within the output with no reason.
How can i get to trim the result of this query so i just get the value and no all the blank spaces that appear before it?
This is the SQLfile
It writes into the destination file something like this
They might be invisible characters or blankspaces or tabulations i really dont know what they are but they should't be there
thank you!
How can i get to trim the result of this query so i just get the value and no all the blank spaces that appear before it?
This is the SQLfile
PROMPT "Oldest items"
PROMPT "--------------------------------------------------------------------"
select round(24*to_number(sysdate-min(to_date((to_char(Ffranqueo,'DD-MM-YYYY')||
'/'||SUBSTR(REPLACE(HFRANQUEO,'.',':'),1,5)),'DD-MM-YYYY/hh24:mi'))))
from averias
where servicio not in ('AB')
and situation= 'F';
set feedback on
PROMPT hours without confirmation
PROMPTIt writes into the destination file something like this
VALUE
They might be invisible characters or blankspaces or tabulations i really dont know what they are but they should't be there
thank you!
ASKER
Id like to modify only the SQL sentence or the prompt, if the TO_CHAR is not necessary i can remove it.
I tried to cover this on your previous question:
https://www.experts-exchange.com/questions/28993808/ORA-00972-identifier-is-too-long-from-Unix-but-not-from-Windows-in-SQLPLUS.html
>>As you can see they appear many empty spaces before the number 10 (correct output)
By default Oracle uses the "formula" as the column header. You can turn off headers a few ways. Easiest is with: set pages 0
You can also set column formats in the script.
That it does is set the column called my_formatted_column with the format 999,999,999 then aliases your formula to have the column name my_formatted_column.
https://www.experts-exchange.com/questions/28993808/ORA-00972-identifier-is-too-long-from-Unix-but-not-from-Windows-in-SQLPLUS.html
>>As you can see they appear many empty spaces before the number 10 (correct output)
By default Oracle uses the "formula" as the column header. You can turn off headers a few ways. Easiest is with: set pages 0
You can also set column formats in the script.
col my_formatted_column 999,999,999
PROMPT "Oldest items"
PROMPT "--------------------------------------------------------------------"
select round(24*to_number(sysdate-min(to_date((to_char(Ffranqueo,'DD-MM-YYYY')||
'/'||SUBSTR(REPLACE(HFRANQUEO,'.',':'),1,5)),'DD-MM-YYYY/hh24:mi')))) my_formatted_column
from averias
where servicio not in ('AB')
and situation= 'F';
set feedback on
PROMPT hours without confirmation
PROMPTThat it does is set the column called my_formatted_column with the format 999,999,999 then aliases your formula to have the column name my_formatted_column.
set pages 0 would still leave the spaces (I tried it), padded to whatever you have set for numwidth. The number format would work, but if you don't fill the format, you are still going to get the leading spaces. The only reliable way I found to do it with only SQL was with TO_CHAR and no format mask.
ASKER
So what should i do? The most strange par is that in windows with the same sentence and same database i get the number without the blank spaces before.
Any hints?
Any hints?
As I posted in your other question:
Look for a glogin.sql or login.sql that will alter your environment.
Look for a glogin.sql or login.sql that will alter your environment.
ASKER
I understand but where should i find such files in both windows and unix?
On the UNIX side, glogin.sql should be in $ORACLE_HOME/sqlplus/admin
On Windows glogin.sql should be in the same location (sqlplus\admin under Oracle Home). I believe that login.sql should be somewhere specified by SQL_PATH variable. I'm not as familiar with the Windows locations, but those should be close.
On Windows glogin.sql should be in the same location (sqlplus\admin under Oracle Home). I believe that login.sql should be somewhere specified by SQL_PATH variable. I'm not as familiar with the Windows locations, but those should be close.
ASKER
I can't find either of them files, on windows, sqlplus executable is in the folder "C:\oracle8i\bin" but there isn't any sql file there.
in the header of the sql file im talking in the post i find these sentences:
Could this affect the output in the way of showing field names?
in the header of the sql file im talking in the post i find these sentences:
set feedback off
set echo off
column c noprint new_value fecha;
select to_char(sysdate,'yymmddhh24mi') c from dual;
column u noprint new_value usuario;
select osuser u from v$session s , V$MYSTAT m
where s.sid = m.sid
and m.STATISTIC# = 0;
SPOOL /home/user/abcd.log
set feedback on
set pagesize 100
set arraysize 5
set linesize 150Could this affect the output in the way of showing field names?
It would be in C:\oracle8i\sqlplus\admin If you have a glogin.sql that is where it should be.
You could also use the search function in Windows Explorer or the find command in UNIX.
ASKER
Thanks, now i've found it, this is my sql settings in windows, what could be the setting making the column name show up?
-- Used by Trusted Oracle
column ROWLABEL format A15
-- Used for the SHOW ERRORS command
column LINE/COL format A8
column ERROR format A65 WORD_WRAPPED
-- Used for the SHOW SGA command
column name_col_plus_show_sga format a24
-- Defaults for SHOW PARAMETERS
column name_col_plus_show_param format a36 heading NAME
column value_col_plus_show_param format a30 heading VALUE
-- For backward compatibility
set pagesize 14
-- Defaults for SET AUTOTRACE EXPLAIN report
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44
As I posted in he other question, just do a "show all" when logged into sqlplus. Find the differences and then we can figure out what is setting them.
>> login.sql should be in the users home directory
It can also be in the folder where you launch sqlplus.
>>Could this affect the output in the way of showing field names?
Those shouldn't affect column headings as far as making them not show up.
>> login.sql should be in the users home directory
It can also be in the folder where you launch sqlplus.
>>Could this affect the output in the way of showing field names?
Those shouldn't affect column headings as far as making them not show up.
ASKER
Oh good thank you I've been able to find it, these are the lines that are differente between both SHOW ALL attributes, this shows up in unix
While for the same variables this is what show sup in windows (its in spanish)
What could be causing this to happen?
arraysize 5
btitle OFF and is the first few characters of the next SELECT statement
editfile "afiedt.buf"
embedded OFF
escape OFF
FEEDBACK ON for 1 or more rows
linesize 150
lno 89
newpage 1
pagesize 100
pno 1
repfooter OFF and is NULL
repheader OFF and is NULL
sqlcode 1013
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)While for the same variables this is what show sup in windows (its in spanish)
arraysize 15
btitle ON y son los siguientes 64 caracteres:
OFF and is the first few characters of the next SELECT statement
editfile "afiedt.buf"
FEEDBACK ON para 6 o más filas
linesize 80
lno 14
pagesize 14
pno 0
repfooter ON y son los siguientes 15 caracteres:
OFF and is NULL
repheader ON y son los siguientes 15 caracteres:
OFF and is NULL
sqlcode 0
ttitle ON y son los siguientes 64 caracteres:
OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)What could be causing this to happen?
I don't think any of those affect column headers. If must be something else.
Can you post the complete list from the unix side? Make sure you are doing everything exactly the same as you do when you run the script. Remember, the login.sql is a local file in the folder you are running things from.
Can you post the complete list from the unix side? Make sure you are doing everything exactly the same as you do when you run the script. Remember, the login.sql is a local file in the folder you are running things from.
ASKER
I'm trying to find login.sql but haven't found it yet, ill try tomorrow again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
That makes it a character field being returned. Using SQL*Plus, numbers are right justified in a column with a width controlled by the NUMWIDTH parameter. You can change it, but it will always have leading spaces.If you don't want to use the TO_CHAR, then there is a way to do it within a shell script that is pretty easy.