Home » Infrastructure » Unix » problem in shell script involving sql statement and pl/sql
problem in shell script involving sql statement and pl/sql [message #259114] Tue, 14 August 2007 07:49 Go to next message
nashrul
Messages: 7
Registered: August 2007
Location: Indonesia
Junior Member
hi all,...
I am new to unix environment...
I would like to ask something. I want to execute sql statement from korn shell. here is the scenario. there is a table namely Report. I want to select maximum value of report_period field from that table (select max(report_period) from REPORT). I store the query result in var A. And I do the second query, selecting last day of current date (select last_day(sysdate) from dual) and store the result in var B. and then, I compare the values of A and B. If those values are the same.. I call a procedure to clean data with var A passed as the argument, clean_up(A). If they aren't the same I call another procedure to do calculation do_calculation(). I try to do this it doesn't work

#!/bin/ksh
# -----------------------------------------------------------------------
# Filename:   alIncentivesCalc.ksh
# Purpose:    Run PL/SQL to calculate Incentives for Telesales
# -----------------------------------------------------------------------


if [[ -f tmp/alIncentivesCalc.run ]]; then
   echo "Another job is already running"
   exit 1
fi

touch tmp/alIncentivesCalc.run

#testing only to be deleted
chmod 777 tmp/alIncentivesCalc.run

if [ ! -d logs ]; then
   mkdir logs
fi


max_date=`sqlplus -s scott/tiger@orcl<<eof
	set serveroutput on;
	set feedback off;
	set linesize 1000;
	select max(TO_CHAR(report_period,'YYYY-MM-DD')) from al_report_gen;
	EXIT;
	eof`


last_date=`sqlplus -s scott/tiger@orcl<<eof
	set serveroutput on;
	set feedback off;
	set linesize 1000;
	select to_char(last_day(sysdate), 'YYYY-MM-DD') from dual;
	EXIT;
	eof`


if [ $max_date -eq $last_date ]; then
	(
		sqlplus - /nolog <<-EOF	
		connect $TOCONNSTR
		set echo on feed on pages 50000
		set arraysize 1000 copycommit 1000 
		call pundism_incentive_al.cleanup_all($max_date);
		exit;
		EOF
	) >logs/AL_INCENTIVES_ONE
else
	(
		sqlplus - /nolog <<-EOF	
		connect $TOCONNSTR
		set echo on feed on pages 50000
		set arraysize 1000 copycommit 1000 
		call pundism_incentive_al.cleanup_all($max_date);
		call pundism_incentive_al.calculate_incentives($max_date);
		call pundism_incentive_al.calculate_incentives_current();
		exit;
		EOF
	) >logs/AL_INCENTIVES_TWO
fi


rm -f tmp/alIncentivesCalc.run


.. Could someone give me an insight ??
Re: problem in shell script involving sql statement and pl/sql [message #259234 is a reply to message #259114] Tue, 14 August 2007 15:42 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
You need to debug the scripts to see exactly where things go wrong.

Some tips:

1) User "echo" statements to tract progress and display variables;

2) Change the first line to "#!/bin/ksh -x" to see how the script executes.

Let us know once you've narrowed it down.
Re: problem in shell script involving sql statement and pl/sql [message #259237 is a reply to message #259114] Tue, 14 August 2007 15:53 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You are on the right track. "-eq" is for integer comparison in KSH - use "=". For your dates being passed into the procs try '$max_date' not just $max_date. Also - there is a subtle difference between "call" procedure and just the procedure name. Add "set -x" at the top of the script to see what's going on. Leave feedback and verify on in the sql until it's working. Make sure your closing eof is in column one - not indented.

You can optionally select both dates (and date1-date2) in a single call to sqlplus and then just use awk to isolate the return values.

host1>>cat t.ksh
#!/bin/ksh
unixvar=5
RETVAL=`sqlplus -s scott/tiger@dev <<EOF
whenever sqlerror exit sql.sqlcode
set verify off heading off pagesize 0 
select 'KeepThis', sysdate, sysdate-$unixvar from dual;
exit sql.sqlcode;
EOF`

echo $RETVAL
echo $RETVAL | grep KeepThis | read junk X Y
echo "X=$X"
echo "Y=$Y"


host1>>t.ksh    
KeepThis 14-AUG-07 09-AUG-07
X=14-AUG-07
Y=09-AUG-07
Re: problem in shell script involving sql statement and pl/sql [message #259263 is a reply to message #259114] Tue, 14 August 2007 21:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it doesn't work
My car dosn't work. Tell me how to make my car go.

You did do a very nice job of using "code tags" to post the formatted script.

In the future please include what happens when you invoke it (especially when following Andrew's advice of "sh -x t.ksh").
Re: problem in shell script involving sql statement and pl/sql [message #259349 is a reply to message #259114] Wed, 15 August 2007 03:02 Go to previous messageGo to next message
nashrul
Messages: 7
Registered: August 2007
Location: Indonesia
Junior Member
anyway, thanks a lot for the advice.
here's what the output looks like when i follow the advice (putting -x next to #!/bin/ksh and so on):

+ [[ -f tmp/alIncentivesCalc.run ]]
+ touch tmp/alIncentivesCalc.run
+ chmod 777 tmp/alIncentivesCalc.run
+ [ ! -d logs ]
+ + sqlplus -s scott/tiger@orcl
+ 0< /tmp/sh3567710.2
max_date=
TO_CHAR(MA
----------
2007-09-25
+ + sqlplus -s scott/tiger@orcl
+ 0< /tmp/sh3567710.4
last_date=
TO_CHAR(LA
----------
2007-08-31
+ [ TO_CHAR(MA ---------- 2007-09-25 = TO_CHAR(LA ---------- 2007-08-31 ]
tesScript.ksh[43]: ----------: 0403-012 A test command parameter is not valid.
+ 1> logs/AL_INCENTIVES_TWO
+ sqlplus -s scott/tiger@orcl
+ 0<<
set echo on feed on pages 50000
set arraysize 1000 copycommit 1000
call pundism_incentive_al.cleanup_all(to_date('
TO_CHAR(MA
----------
2007-09-25', 'YYYY-MM-DD'));
call pundism_incentive_al.calculate_incentives(to_date('
TO_CHAR(MA
----------
2007-09-25', 'YYYY-MM-DD'));
call pundism_incentive_al.calculate_incentives_current();
exit;
+ rm -f tmp/alIncentivesCalc.run

when the variables max_date and last_date are compared, it produces error "0403-012 A test command parameter is not valid" like printed above. It seems that those variables contain some other characters ([ TO_CHAR(MA ---------- 2007-09-25 = TO_CHAR(LA ---------- 2007-08-31 ]). So i made some changes:

var_max_date=`sqlplus -s scott/tiger@orcl<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select to_char(max(to_date(report_period,'YYYY-MM-DD')), 'YYYY-MM-DD') from al_report_gen;
EXIT;
eof`


var_last_date=`sqlplus -s scott/tiger@orcl<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select to_char(last_day(sysdate), 'YYYY-MM-DD') from dual;
EXIT;
eof`

max_date=$(echo $var_max_date | sed 's/.*\(.\{10\}\)$/\1/')
last_date=$(echo $var_last_date | sed 's/.*\(.\{10\}\)$/\1/')

and it works now...
Re: problem in shell script involving sql statement and pl/sql [message #259505 is a reply to message #259349] Wed, 15 August 2007 14:48 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
"TO_CHAR(LA" is the column heading
"----------" is the underline

It pains me to see cleaup of output (using REs if that's what you're doing) when the correct approach is to avoid the junk output in the first place...

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> set echo on
SQL> select sysdate from dual;

SYSDATE
---------
15-AUG-07

SQL> set pagesize 0;
SQL> select sysdate from dual;
15-AUG-07

SQL> set heading off;
SQL> select sysdate from dual;
15-AUG-07

SQL> 

Previous Topic: Oracle 10g Installation on Solaris x86
Next Topic: Oracle XE for Solaris/Unix
Goto Forum:
  


Current Time: Thu Mar 28 05:29:04 CDT 2024