Unix/Linux Go Back    


Programming Post questions about C, C++, Java, SQL, and other programming languages here.

To invoke pass parameters in Oracle file

Programming


Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 01-06-2016   -   Original Discussion by rohit_shinez
rohit_shinez's Unix or Linux Image
rohit_shinez rohit_shinez is offline
Registered User
 
Join Date: Jul 2013
Last Activity: 8 December 2017, 2:18 AM EST
Posts: 192
Thanks: 59
Thanked 0 Times in 0 Posts
To invoke pass parameters in Oracle file

Hi Guys,

I am having a sql script file which does below operations


Code:
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


Code:
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
Moderator's Comments:
To invoke pass parameters in Oracle file Please use CODE tags for sample input, sample output, AND sample code segments; not just for code segments.

Last edited by Don Cragun; 01-06-2016 at 06:29 PM.. Reason: Add more CODE tags.
Sponsored Links
    #2  
Old Unix and Linux 01-06-2016   -   Original Discussion by rohit_shinez
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 9 September 2017, 1:30 PM EDT
Posts: 2,083
Thanks: 21
Thanked 383 Times in 346 Posts
Quote:
Originally Posted by rohit_shinez View Post
...

I am having a sql script file which does below operations


Code:
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 , ...
...
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:

Code:
$ 
$ 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.
The Following User Says Thank You to durden_tyler For This Useful Post:
rohit_shinez (01-07-2016)
Sponsored Links
    #3  
Old Unix and Linux 01-07-2016   -   Original Discussion by rohit_shinez
rohit_shinez's Unix or Linux Image
rohit_shinez rohit_shinez is offline
Registered User
 
Join Date: Jul 2013
Last Activity: 8 December 2017, 2:18 AM EST
Posts: 192
Thanks: 59
Thanked 0 Times in 0 Posts
Hi,

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

My test.sql will be something like this

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

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


Code:
@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 ----------

Quote:
Originally Posted by durden_tyler View Post
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:

Code:
$ 
$ 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.
Thanks is there way to wait until first execution .sql completes then proceed with next .sql

Code:
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

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

Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Pass two parameters zhshqzyc Emergency UNIX and Linux Support 4 06-30-2011 02:13 AM
Read Oracle Username password SID from single file and pass it to shell jhon Shell Programming and Scripting 2 06-10-2009 06:37 PM
How to pass parameters to an awk file? iamgeethuj Shell Programming and Scripting 3 10-17-2008 09:39 AM
invoke oracle package error? andrewknight Shell Programming and Scripting 2 07-29-2008 08:11 AM
How can i pass a file from unix to oracle?? vidyadhar85 UNIX for Advanced & Expert Users 1 06-28-2008 08:01 PM



All times are GMT -4. The time now is 12:08 PM.