To invoke pass parameters in Oracle file

Applications Programming
1

Hi Guys,

I am having a sql script file which does below operations

ALTER TABLE M1
EXCHANGE PARTITION FOR (TO_DATE('&1','dd-MON-yyyy'))
WITH TABLE &2
INCLUDING INDEXES;

i need to pass variables in such a way that if i pass start date and end date as parameter , something like my start and end dates as 01-JAN-2015 - 04-JAN-2015, i need to call this .sql file

calling test.sql for 01-JAN-2015
Test.sql 01-JAN-2015 T1 #establish connection using connect user/user_name@db
completed for 01-JAN-2015
calling test.sql for 02-JAN-2015
Test.sql 02-JAN-2015 T2
completed for 02-JAN-2015 
calling test.sql for 03-JAN-2015
Test.sql 03-JAN-2015 T3
completed for 03-JAN-2015 
calling test.sql for 04-JAN-2015
Test.sql 04-JAN-2015 T4
completed for 04-JAN-2015

Here T is constant value where T1,T2,T3,T4 needs to be appended accordingly based in one day interval

2

Pass variables to what - a shell script or SQL script?
I have assumed you want to pass the dates to a shell script.

If you have GNU date, then here's an idea:

$ 
$ cat -n run_exchange_partition.sh
     1	#!/bin/bash
     2	from_date="$1"
     3	to_date="$2"
     4	echo "From_date = $from_date"
     5	echo "To_date   = $to_date"
     6	diff_days=$((($(date -d $to_date "+%s") - $(date -d $from_date "+%s")) /60/60/24))
     7	echo "Diff_days = $diff_days"
     8	echo
     9	
    10	iter=0
    11	while [ "$iter" -le "$diff_days" ]
    12	do
    13	    date_to_process=$(date "+%d-%b-%Y" -d "$from_date +$iter days")
    14	    suffix=$(expr $iter + 1)
    15	    echo "Calling test.sql for $date_to_process"
    16	    echo "sqlplus -s username/passwd@db @test.sql $date_to_process T${suffix}"
    17	    echo "Completed for $date_to_process"
    18	    echo
    19	    iter=$(expr $iter + 1)
    20	done
    21	
$ 
$ . run_exchange_partition.sh "01-JAN-2015" "04-JAN-2015"
From_date = 01-JAN-2015
To_date   = 04-JAN-2015
Diff_days = 3

Calling test.sql for 01-Jan-2015
sqlplus -s username/passwd@db @test.sql 01-Jan-2015 T1
Completed for 01-Jan-2015

Calling test.sql for 02-Jan-2015
sqlplus -s username/passwd@db @test.sql 02-Jan-2015 T2
Completed for 02-Jan-2015

Calling test.sql for 03-Jan-2015
sqlplus -s username/passwd@db @test.sql 03-Jan-2015 T3
Completed for 03-Jan-2015

Calling test.sql for 04-Jan-2015
sqlplus -s username/passwd@db @test.sql 04-Jan-2015 T4
Completed for 04-Jan-2015

$ 
$

If you do not have GNU date, then doing date arithmetic entirely in the shell is kind of complicated.
Search this site for date functions posted by Perderabo a while ago.
Or you could use scripting languages like gawk, Perl or Python to do calculations like date difference and adding days to a date.

1 Like
3

Hi,

yeah i would like to pass the two parameters in .sql script

My test.sql will be something like this

Alter table m1 exchange with &1 with table &2 using index

so from start date and end date i need to pass like this

@test.sql 01-Jan-2015 T1
@test.sql 02-Jan-2015 T2
.
.

---------- Post updated at 05:28 AM ---------- Previous update was at 04:45 AM ----------

Thanks is there way to wait until first execution .sql completes then proceed with next .sql

Calling test.sql for 01-Jan-2015
sqlplus -s username/passwd@db @test.sql 01-Jan-2015 T1
Completed for 01-Jan-2015

wait for the above to complete then go for next execution

Calling test.sql for 02-Jan-2015
sqlplus -s username/passwd@db @test.sql 02-Jan-2015 T2
Completed for 02-Jan-2015