Home » Other » Client Tools » Only values a select-result (Oracle 11g, SUSE Linux Enterprise Server 11)
Only values a select-result [message #628348] Mon, 24 November 2014 07:02 Go to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Hi,
I am about to make a kind of homemade logging-tool for my oracle-installation(s).
Therefore I want to perform some checks via SQL and store the results in a plain textfile, which is being analysed via Javascript later.

First thing I want to have is the information whether the DB is up and running.

I have an sql-file calles suche.sql, containing the following code:
CONNECT user/password
SET LINESIZE 100
SET PAGESIZE 50
SELECT 'Alive' FROM dual;
EXIT;


And I have a bashscript called suche.sh, containing the following code
sqlplus /nolog @suche.sql > message.log


That works fine so far, except that the result is:

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 24 13:37:32 2014

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

Connect durchgefuhrt.

'ALIVE'
--------------------------------
Alive

Verbindung zu Oracle Database 11g Release 11.2.0.3.0 - 64bit Production beendet


Unfortunately I only want the value "Alive".

Is there a way to manage that?

I am sorry for stupid questions, I am very new to Oracle and SQL.
Regards
Christian
Re: Only values a select-result [message #628350 is a reply to message #628348] Mon, 24 November 2014 07:06 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, you can switch off the column headers with "SET PAGESIZE 0".

But when the database is down, there might be some other error messages that get logged.
Re: Only values a select-result [message #628353 is a reply to message #628350] Mon, 24 November 2014 07:21 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Thanks for the quick answer,

unfortunately it didn't help.

My code now looks as follows:
CONNECT user/password
SET PAGESIZE 0
SELECT 'Alive' FROM dual;
EXIT;


and the result still looks like:
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 24 14:16:44 2014

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

Connect durchgefuhrt.
Alive

Verbindung zu Oracle Database 11g Release 11.2.0.3.0 - 64bit Production beendet



That there will be other errors, when Oracle is down, that is okay.
This alive-query is only a first test, if my idea of logging works at all.
There will be much more interesting queries later.
Re: Only values a select-result [message #628354 is a reply to message #628353] Mon, 24 November 2014 07:35 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
sqlplus -s /nolog @suche.sql > message.log
Re: Only values a select-result [message #628355 is a reply to message #628354] Mon, 24 November 2014 07:40 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Purrfect, that worked and was exactly, what I wished.
Thank you Smile
Re: Only values a select-result [message #628356 is a reply to message #628353] Mon, 24 November 2014 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use "sqlplus -s" to remove the banners.

Re: Only values a select-result [message #628357 is a reply to message #628356] Mon, 24 November 2014 07:49 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Is there a way to mark this topic as solved?
thx Smile
Re: Only values a select-result [message #628358 is a reply to message #628357] Mon, 24 November 2014 07:52 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
No. I suspect Michel's post and mine simply overlapped Smile

Many thanks for your feedback though.
Re: Only values a select-result [message #628362 is a reply to message #628358] Mon, 24 November 2014 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, someone talked to me when I was writing my post and so yours was not on my screen when I clicked to reply. Smile

Re: Only values a select-result [message #628464 is a reply to message #628362] Tue, 25 November 2014 08:38 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Hi,

based on your suggestions I built an SQL-file:
CONNECT user/password@IP/instancename

set pagesize 0

SELECT ROUND( SUM(bytes) /1024 / 1024,1 ) fsize FROM V$DATAFILE;
SELECT 'Alive' FROM dual;

SELECT ROUND ( Space_Limit / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
SELECT ROUND ( Space_Used / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
SELECT ROUND ( Space_Reclaimable / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;

EXIT;


This does, what I wanted - almost.
The output looks like this:

SP2-0103: SQL-Puffer enthalt keine auszufuhrenden Befehle.
     43895

Alive

Alive

                        215040

                     157750,5

                             33021,6



This is kinda funny. The values are okay, but I can not imagine, where these empty spaces come from.
And the second "Alive"
Any idea?

[Updated on: Tue, 25 November 2014 08:41]

Report message to a moderator

Re: Only values a select-result [message #628465 is a reply to message #628464] Tue, 25 November 2014 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What tool do you use?
If SQL*Plus then actually copy and paste your session like:
SQL> SELECT ROUND( SUM(bytes) /1024 / 1024,1 ) fsize FROM V$DATAFILE;
     FSIZE
----------
      3130

1 row selected.

SQL> SELECT 'Alive' FROM dual;
'ALIV
-----
Alive

1 row selected.

SQL>
SQL> SELECT ROUND ( Space_Limit / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
ROUND(SPACE_LIMIT/1024/1024,1)
------------------------------
                           550

1 row selected.

SQL> SELECT ROUND ( Space_Used / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
ROUND(SPACE_USED/1024/1024,1)
-----------------------------
                        663.2

1 row selected.

SQL> SELECT ROUND ( Space_Reclaimable / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
ROUND(SPACE_RECLAIMABLE/1024/1024,1)
------------------------------------
                                  27

1 row selected.

Re: Only values a select-result [message #628466 is a reply to message #628464] Tue, 25 November 2014 08:41 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
How do you call/run that SQL script?
Re: Only values a select-result [message #628470 is a reply to message #628466] Tue, 25 November 2014 09:06 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
I do it via sqlplus.
And I found the reason for the whitespace. The headers are not visible, but the space is used:

Call with s-option:
                             33021,6


Call without s-option:
ROUND(SPACE_RECLAIMABLE/1024/1024,1)
------------------------------------
                             33021,6


This seems quite strange, but it can be suppressed, I guess.

[Updated on: Tue, 25 November 2014 09:07]

Report message to a moderator

Re: Only values a select-result [message #628471 is a reply to message #628470] Tue, 25 November 2014 09:09 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
TRIMSPOOL
LINESIZE

HTH
-g
Re: Only values a select-result [message #628478 is a reply to message #628470] Tue, 25 November 2014 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

show all

This will give you your session settings.

SQL*PlusĀ® User's Guide and Reference
This will give you the meaning of all these parameters/options.

Re: Only values a select-result [message #628555 is a reply to message #628478] Wed, 26 November 2014 04:43 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Sorry for bothering with such basics, but I don't get it.

With "show all" I got the following:
appinfo ist OFF und auf "SQL*Plus" eingestellt
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFFund entspricht den ersten Zeichen der nachsten SELECT-Anweisung
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK ist ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
exitcommit ON
FEEDBACK ON fur 6 oder mehr Zeilen
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; backgroun                                           d:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,                                           Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {fon                                           t:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16                                           pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; ma                                           rgin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; m                                           argin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; ma                                           rgin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90                                           %' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE ist OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1102000300
repfooter OFF und ist NULL
repheader OFF und ist NULL
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 11.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFFund entspricht den ersten Zeichen der nachsten SELECT-Anweisung
underline "-" (hex 2d)
USER ist "SYSTEM"
verify ON
wrap : Zeilen werden umbrochen
xmloptimizationcheck OFF
errorlogging is OFF


So, reagrding to http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch6.htm#i1082570 I added the following to my SQL-script:

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 20
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF


That deleted the empty lines, but there is still whitespace in front of the values.
But I guess, they can be ignored later, so it's fine.

To be complete the result of this query:
     43895
Alive
Alive
                        215040
                     157750,5
                             33021,6


Regards
Christian

[Updated on: Wed, 26 November 2014 04:47]

Report message to a moderator

Re: Only values a select-result [message #628556 is a reply to message #628555] Wed, 26 November 2014 04:46 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Can you show us your amended script and how you are running it in its entirety, please? Copy and paste the entire session. Additionally, you could format your numbers using TO_CHAR function, amongst other things.
Re: Only values a select-result [message #628564 is a reply to message #628556] Wed, 26 November 2014 05:34 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
I have a bashscript with the following content:

sqlplus -s /nolog @suche.sql > message.log


The sql-file looks as follows:

CONNECT User/password@IP/instance

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 20
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF

SELECT ROUND( SUM(bytes) /1024 / 1024,1 ) fsize FROM V$DATAFILE;
SELECT 'Alive' FROM dual;

SELECT ROUND ( Space_Limit / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
SELECT ROUND ( Space_Used / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;
SELECT ROUND ( Space_Reclaimable / 1024 / 1024, 1 ) FROM V$recovery_File_Dest;

EXIT;


Thats it, folks
Re: Only values a select-result [message #628566 is a reply to message #628564] Wed, 26 November 2014 05:37 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
And that causes this output?

     43895
Alive
Alive
                        215040
                     157750,5
                             33021,6
Re: Only values a select-result [message #628569 is a reply to message #628566] Wed, 26 November 2014 05:40 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Incidentally, do you know about formatting your column headings with:

COLUMN <column_name> FORMAT <format_mask>


See here for examples.
Re: Only values a select-result [message #628592 is a reply to message #628564] Wed, 26 November 2014 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Can you show us your amended script and how you are running it in its entirety, please?


You didn't show anything, you just repeat the same.
Post the SQL*Plus session as I did.
Is this so difficult?

Re: Only values a select-result [message #628596 is a reply to message #628555] Wed, 26 November 2014 06:47 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
chricken wrote on Wed, 26 November 2014 04:43


That deleted the empty lines, but there is still whitespace in front of the values.
But I guess, they can be ignored later, so it's fine.

To be complete the result of this query:
     43895
Alive
Alive
                        215040
                     157750,5
                             33021,6



TRIMSPOOL (which you didn't use) only trims white space from the end of the line. Such that, if you set LINESIZE 128 but a given line only has 50 bytes of data, that line won't get padded out to 128.
Except for the "Alive" text string literal, everything you are selecting is a NUMBER. As such it will be right aligned within the specific column width and left-padded with blanks. Alignment issues could be either/or
1) every number you select was a seperate SELECT, and the nature of the data in a particular select makes for a different column width
2) You are viewing it in an editor/display using a proportional font, so spaces are narrower than other characters and so a different number of leading spaces makes for different right-alignment.
Re: Only values a select-result [message #628623 is a reply to message #628596] Wed, 26 November 2014 08:21 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Thank you all for your grateful help.
Now I have understood everything important for the moment.

The reason for the unsatisfying alignment was, like EdStevens said, that the columns had different widths and the numbers were aligned to the right.
Now I have added
COLUMN ROUND(SPACE_LIMIT/1024/1024,0) HEADING H1
COLUMN ROUND(SPACE_USED/1024/1024,0) HEADING H2
COLUMN ROUND(SPACE_RECLAIMABLE/1024/1024,0) HEADING H3


to make the headings smaller and it worked.
All columns have the same size now.

I am satisfied with the results.

@ Michel Cadot: I am using a bash-script to run an sql-file and I don't know, how to log that. Therefore it is indeed difficult to post the session.
Sorry for that inconvenience.
Re: Only values a select-result [message #628625 is a reply to message #628623] Wed, 26 November 2014 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>@ Michel Cadot: I am using a bash-script to run an sql-file and I don't know, how to log that.

set -x

or

man script
Re: Only values a select-result [message #628627 is a reply to message #628625] Wed, 26 November 2014 08:27 Go to previous message
chricken
Messages: 13
Registered: November 2014
Junior Member
BlackSwan wrote on Wed, 26 November 2014 15:23
>@ Michel Cadot: I am using a bash-script to run an sql-file and I don't know, how to log that.

set -x

or

man script



OK, but this topic can be closed now.
Previous Topic: SP2-0552: Bind variable "V_D" not declared.
Next Topic: SQL Developer connection error
Goto Forum:
  


Current Time: Thu Mar 28 18:24:43 CDT 2024