To pass one month range in sql script

Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users To pass one month range in sql script
# 1  
Old 01-15-2016
To pass one month range in sql script

Hi Guys,

i am having .sql script which inserts data from one table to another table based on date condition, i need to pass range on based on how many number of months, for e.g
set timing on;
whenever sqlerror exit failure;
spool myscript.log append
accept start_date char prompt 'Enter start date ( DD-MON-YYYY ) : '
accept   end_date char prompt 'Enter end date ( DD-MON-YYYY ) : '
prompt start date: &start_date
prompt   end date: &end_date
PROMPT >> Inserting records into master
INSERT INTO master t
SELECT /*+ PARALLEL(t1,8) */ * FROM T1 t1 where date_col between '&start_date' and '&end_date';
undef start_date
undef  end_date

i need run this script by passing start date and end date for eg 01-JAN-2005 to 30-APR-2005 this range will be fixed, only thing is i need to pass in sql script like this it should split accordingly to one month , the script should process only one month data once completed next month

@myscript 01-JAN-2005 31-JAN-2005
@myscript 01-FEB-2005 28-FEB-2005
@myscript 01-MAR-2005 31-MAR-2005
@myscript 01-APR-2005 30-APR-2005

, also if i give 2 months gap then it should consider first range as 01-JAN-2005 28-FEB-2005 and 01-MAR-2005 to 30-APR-2005, by default the gap should be always one month difference.
# 2  
Old 01-15-2016
If it was me, I would do "where TRUNC(date_col, 'MM') = '1-APR-2005' ". That way you only need to pass in one date. Also if you use Oracle you can create a function based index on TRUNC(date_col) if you want. You then don't need to know the last day of the month. Another way to do that is to add a month to the first of the month and subtract a day. "ADD_MONTH('1-APR-2005', 1)-1 ". still you only need to pass in one date and will get a range scan if you have that column as the leading edge of an index.
# 3  
Old 01-15-2016
i have achieved something like below
SELECT /*+ PARALLEL(t1,8) */ * FROM T1 t1 where date_col between ADD_MONTHS(TO_DATE('&start_date','DD-MON-YYYY'),(i-1)) and ADD_MONTHS(TO_DATE('&start_date','DD-MON-YYYY'),i);

But i wanted to achieve in scripting something like passing how month difference the range should be along with that after each insert i.e range it should prompt from the user that so on so range is completed should it proceed with next range displaying the range and start processing the ranges. Like this till it reaches the final date
# 4  
Old 01-18-2016
Guys can any one advise??
# 5  
Old 01-19-2016
You can try to use getopts where someone
passes in a date and a flag. Such as:

bash:getopts command help
  • -d '01-01-2016' -- one day
  • -w '01-01-2016' -- one week
  • -m '01-01-2016' -- one month
  • -y '01-01-2016' -- one year

You would then make sure that only one of the flags was passed in, and calculate two
dates based on the flag and use those dates as your low and high dates. You can do
that using the date functions in Oracle.
# 6  
Old 01-19-2016
Thanks, can you guide me how i can achieve that
# 7  
Old 01-20-2016
Here is some sample code.


setDateParam() {
if [ ! -z "$2" ]
   echo "You can only pass in one parameter: -d, -w, -m, -y"
   exit 1

export DATE_FLAG=$1

while getopts "dwmy" Option
   case $Option in
      d ) setDateParam "D"    "${DATE_FLAG}"  ;;
      w ) setDateParam "W"    "${DATE_FLAG}"  ;;
      m ) setDateParam "MM"   "${DATE_FLAG}"  ;;
      y ) setDateParam "YYYY" "${DATE_FLAG}"  ;;
      * )
         echo "You must pass in one parameter: -d, -w, -m, -y"
         exit 1 ;;


Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Unable to pass value from .Shell script to .SQL file

Hi All, I am new to shell script. I am trying to pass value from .sh file to .sql file . But I am able to run the .sql file from .sh file with values in sql file. But I am unable to pass the values from .sh file. can some one please help to resolve this. here is my .sh file ... (4 Replies)
Discussion started by: reddy298599
4 Replies

2. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./ users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

3. Shell Programming and Scripting

How to add decimal month to some month in sql, php, perl, bash, sh?

Hello, i`m looking for some way to add to some date an partial number of months, for example to 2015y 02m 27d + 2,54m i need to write this script in php or bash or sh or mysql or perl in normal time o unix time i`m asking or there are any simple way to add partial number of month to some... (14 Replies)
Discussion started by: bacarrdy
14 Replies

4. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
Discussion started by: megha2525
11 Replies

5. Shell Programming and Scripting

How to put date range from a perl & sql script

Hi Guys, Can someone please help me on adding/inserting a variable date to an sql scipt? Basically I want to assign a 7 days date range. As shown below.. #!/usr/bin/perl use strict; use Env qw(ORACLE_HOME); my $SQLPLUS='/opt/oracle/product/10.1.0/db_1/bin/sqlplus -S... (1 Reply)
Discussion started by: pinpe
1 Replies

6. UNIX for Advanced & Expert Users

How to use parameter in sql script pass from unix script?

Hi, I am unable to use parameter in sql script passed from unix script. my sql script CREATE_SBI_LIST_GROUP.sql is like this - ------------------------------- SELECT SDS.ID "SO_ID", SDS.SO a1, sgp.sga__code SGA_CODE, FROM sga sga,sales_genl_provision sgp , comm_product_condn cpc... (2 Replies)
Discussion started by: apskaushik
2 Replies

7. Shell Programming and Scripting

Pass a variable to SQL script

Hi Guys, I like to pass a variable to a sql file in a unix script.. I tried a below code.. var=200903 db2 -vf test.sql 200903 test.sql is as below. select * from db2.users where quarter = $1; Please tell me where i go wrong.. Thanks in advance, Magesh (2 Replies)
Discussion started by: mac4rfree
2 Replies

8. Shell Programming and Scripting

How to pass pl/sql table values to shell script

Hello, i am using '#!/bin/bash', i want to make a loop in pl/sql, this loop takes values from a table according to some conditions, each time the loop choose 3 different variables. What i am not able to do is that during the loop i want my shell script to read this 3 variables and run a shell... (1 Reply)
Discussion started by: rosalinda
1 Replies

9. Shell Programming and Scripting

pass input arguements to DB2 SQL script

Hi all, I have a shell script which invoke a sql script using command db2 -tf /home/me/db_housekeep.sql -z /home/me/db_housekeep.log however, this mentioned sql script requires several input arguments, I wonder if one can pass variables from shell script to sql script? thanks! (0 Replies)
Discussion started by: mpang_
0 Replies

10. Shell Programming and Scripting

Pass multiple variables to SQL script

I am trying to close of multiple users in an Oracle database. Each users has records in multiple tables what I need to do is use a script that call each SQL seperately passing either CLI arguments or gathered arguments from the users during run time. ## Accept variable(s) from the command line... (1 Reply)
Discussion started by: jagannatha
1 Replies
Login or Register to Ask a Question

Featured Tech Videos