Home » SQL & PL/SQL » SQL & PL/SQL » SQL capture the output statement
SQL capture the output statement [message #187633] Mon, 14 August 2006 14:23 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
I have a shell script that calls a .sql statement. The sql statement is this:

select to_char(sysdate, 'MM') -1 from dual;

I want to be able to capture the output of this statement which will be a char and passing it as the parameter into another program.

like: sqr $progname.sqr $user $mnth_dt

Thanks.
Re: SQL capture the output statement [message #187634 is a reply to message #187633] Mon, 14 August 2006 14:25 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Why would you subtract a number from a character string? That's asking for trouble.

But if you're passing the value off into another SQL script, why not just call it from within the first script and use the value using the NEW_VALUE method.

[Updated on: Mon, 14 August 2006 14:28]

Report message to a moderator

Re: SQL capture the output statement [message #187644 is a reply to message #187633] Mon, 14 August 2006 15:40 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
The output of this statement which will be a number:

select to_number(to_char(sysdate, 'MM')) -1 mnth_prev from dual;

The output of this statement will pass as the parameter into another shell script, not sql:

./bal_calc.sh $p1


Thanks.
Re: SQL capture the output statement [message #187664 is a reply to message #187644] Mon, 14 August 2006 17:38 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
What happens when it is January?

In any event you could do this trivially with:

#!/bin/bash

dt=`sqlplus -s user/pass <<EOF
set head off
set pagesize 0
select to_char(to_number(to_char(sysdate, 'MM')) -1) from dual;
quit
EOF`
echo dt is -$dt-


Or perhaps more appropriately with:

#!/bin/bash

dt=`sqlplus -s user/pass <<EOF
set head off
set pagesize 0
select to_char(to_number(to_char(add_months(sysdate, -1), 'MM'))) from dual;
quit
EOF`
echo dt is -$dt-



I am assuming you wanted to trim off the leading 0, so I left the to_number in there, but converting it back to to_char otherwise there is padding that ends up in the $dt variable. When it is a numeric, sqlplus throws in a tab & space then the number -- when it is a char it doesn't.
Re: SQL capture the output statement [message #187805 is a reply to message #187664] Tue, 15 August 2006 13:49 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
If sql query return two values and both will be passing as parameters into shell script.

select to_char(to_number(to_char(sysdate, 'MM')) -1) "mnth",
to_number(ceil(to_char(sysdate,'mm')/3) - 1) "qtr"
from dual;

./bal_calc.sh $p1 $p2
Re: SQL capture the output statement [message #188425 is a reply to message #187633] Fri, 18 August 2006 08:59 Go to previous messageGo to next message
westuss
Messages: 4
Registered: August 2006
Location: Pittsburgh
Junior Member
Is there a special reason for using the sysdate returned by database over the UNIX "date" command?

currmo=`if [ \`date +"%m"\` -le 1 ]; then echo 12; else date +"%m"; fi`
lastmo=`expr $currmo - 1`

Just an option, there's probably a good reason you have to do it in SQL.

If you are compatible with KSH, you can use arrays to get these variables:

set -A returnvals `sqlplus -s user/pass <<EOF
set head off
set pagesize 0
select to_char(to_number(to_char(sysdate, 'MM')) -1),
to_number(ceil(to_char(sysdate,'mm')/3) - 1) "qtr"
from dual;
quit
EOF`

./bal_calc.sh ${returnvals[0]} ${returnvals[1]}

** EDIT ** - arrays start with 0, not 1.

[Updated on: Fri, 18 August 2006 09:15]

Report message to a moderator

Re: SQL capture the output statement [message #188805 is a reply to message #187633] Mon, 21 August 2006 14:00 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I also think ,if your going to use sql, that you want to do the following.

select to_char(add_months(sysdate,-1), 'MM')) mnth_prev from dual;
Previous Topic: to_date problem
Next Topic: Getting LONG columns out of Oracle 8i and into Access (eek!)
Goto Forum:
  


Current Time: Mon Dec 05 08:42:25 CST 2016

Total time taken to generate the page: 0.09134 seconds