Home » Infrastructure » Unix » query to get a value from database
query to get a value from database [message #288424] Mon, 17 December 2007 08:42 Go to next message
dvivekd
Messages: 3
Registered: November 2007
Location: Milton Keynes
Junior Member
Hi friends,

I have written a script which retrieves one value from the database.
It takes time for the sql query to get the desired output. So the query runs fine from TOAD or from UNIX console.

However if I put this in a script it doesn't work. The script is as below.

#! /bin/ksh
DB_VALID_CDR=`sqlplus -s username/password<<!EOF | grep -v "^Connected" 2>&1
set termout off echo off feedback off pause off timing off time off
set pages 0
clear breaks
clear compute
set termout on
select a.VALUE_INTEGER from client_statistics a, statistic_type b where a.STATISTICS_TYPE=b.Statistic_type_id and a.STATISTICS_OBJECT like '%20071217102445_9673324_AG40_20071217101738_0000002118%' and b.STATISTIC_TYPE='valid_cdrs'
EXIT
!EOF`
echo $DB_VALID_CDR

I strongly believe that the issue is with the query taking time to return the value.

Please let me know how to tackle the issue.

Many thanks
Re: query to get a value from database [message #288431 is a reply to message #288424] Mon, 17 December 2007 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

it doesn't work.

What do you think this means to us?

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: query to get a value from database [message #288435 is a reply to message #288431] Mon, 17 December 2007 09:13 Go to previous messageGo to next message
dvivekd
Messages: 3
Registered: November 2007
Location: Milton Keynes
Junior Member
Hi Michel,

Sorry for that, please consider this as a mistake from a naive user to this forum.

#! /bin/ksh
DB_VALID_CDR=`sqlplus -s username/password<<!EOF | grep -v "^Connected" 2>&1
set termout off echo off feedback off pause off timing off time off
set pages 0
clear breaks
clear compute
set termout on
select a.VALUE_INTEGER 
from client_statistics a, statistic_type b 
where a.STATISTICS_TYPE=b.Statistic_type_id 
and a.STATISTICS_OBJECT like '%20071217102445_9673324_AG40_20071217101738_0000002118%' 
and b.STATISTIC_TYPE='valid_cdrs'
EXIT
!EOF`
echo "valid cdrs is: "$DB_VALID_CDR


I tried redirecting the output to a log, and it says

valid cdrs is:
logout


[Updated on: Mon, 17 December 2007 10:18] by Moderator

Report message to a moderator

Re: query to get a value from database [message #288447 is a reply to message #288435] Mon, 17 December 2007 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What about "Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button."?
Can you do this next time?

Execute the sqlplus part without the grep and redirection and post the result.

Regards
Michel
Re: query to get a value from database [message #288481 is a reply to message #288447] Mon, 17 December 2007 14:05 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
dvivekd, I can understand your posting just fine without any fancy formatting. Try removing "termount off" from your code.
set echo on
select sysdate from dual;
set termout off
select sysdate from dual;

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> select sysdate from dual;

SYSDATE
---------
17-DEC-07

SQL> set termout off
SQL>

[Updated on: Mon, 17 December 2007 14:15]

Report message to a moderator

Re: query to get a value from database [message #288669 is a reply to message #288424] Tue, 18 December 2007 04:54 Go to previous message
dvivekd
Messages: 3
Registered: November 2007
Location: Milton Keynes
Junior Member
Many thanks Andrew

Its now working and I have also missed a semicolon at the end of the query

Cheers
Previous Topic: ls
Next Topic: Cannot load module libjox9.a(shr.o).
Goto Forum:
  


Current Time: Sat Dec 14 15:45:23 CST 2024