Login or Register to Ask a Question and Join Our Community


Programming

To invoke pass parameters in Oracle file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming To invoke pass parameters in Oracle file
# 1  
Old 01-06-2016
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:
Mod Comment 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.
# 2  
Old 01-06-2016
Quote:
Originally Posted by rohit_shinez
...

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.
This User Gave Thanks to durden_tyler For This Post:
rohit_shinez
# 3  
Old 01-07-2016
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
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

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pass Parameters to awk command

I need to pass values at runtime for the below awk command where l is the length and partial.txt is the file name. awk -v l=285 '{s="%-"l"s\n";printf(s,$0);}' partial.txt > temp1.txt; (5 Replies)
Discussion started by: Amrutha24
5 Replies

2. Emergency UNIX and Linux Support

Pass two parameters

Hi I have a batch file aaa.exe which needs two input parameters: Usually the command's format likes aaa 555 10000 But I want to use parameters to do it. aaa $1 $2 These two parameters come from a text file list.txt 41800497 41801375 41814783 41816135 41814930 41816135 41819987 41820843... (4 Replies)
Discussion started by: zhshqzyc
4 Replies

3. Shell Programming and Scripting

pass shell parameters to awk does not work

Why does this work for myfile in `find . -name "R*VER" -mtime +1` do SHELLVAR=`grep ^err $myfile || echo "No error"` ECHO $SHELLVAR done and outputs No error err ->BIST Login Fail 3922 err No error err ->IR Remote Key 1 3310 err But... (2 Replies)
Discussion started by: alan
2 Replies

4. Shell Programming and Scripting

Can't get shell parameters to pass properly to sqlplus

Gurus, The issue I'm having is that my Shell won't accept SQL parameters properly...... Here's they way I'm running it.... applmgr@ga006hds => sh CW_MigrationDeployScript.sh apps <appspwd> <SID> '01-JAN' '31-MAR' The process just hangs not submitting the SQL job... ... (3 Replies)
Discussion started by: WhoDatWhoDer
3 Replies

5. Shell Programming and Scripting

Pass parameters to function

Hi, for example I have this function: function get_param () { test=echo "some string" test2=echo "someother string" } I want to call this function and get test or test2 result, how do I do that ? Thank you (2 Replies)
Discussion started by: ktm
2 Replies

6. Shell Programming and Scripting

Read Oracle Username password SID from single file and pass it to shell

Dear All I am trying to write one shell which will be running through Cron which contain one SQL query. But I want to draw/fetch the Username password and Instance name (required to loging to the database) from one single file to run that SQL query . Also this file contain details of multiple... (2 Replies)
Discussion started by: jhon
2 Replies

7. Shell Programming and Scripting

How to pass parameters transparently into a sub script

Hi, I am trying to write a script like this: #!/bin/ksh #script name: msgflow #The awk commands for Solaris and Linux are incompatible if ] then msgflow-solaris $* elif ] then msgflow-linux $* fi This script is shared by a file system which is visible to both... (3 Replies)
Discussion started by: danielnpu
3 Replies

8. Shell Programming and Scripting

How to pass parameters to an awk file?

I have an awk file where I need to pass a filename and a value as a parameter from a sh script. I need to know how to pass those values in the sh script and how to use the same in the awk file. Thanks in advance!!! Geetha (3 Replies)
Discussion started by: iamgeethuj
3 Replies

9. Shell Programming and Scripting

invoke oracle package error?

My sh scripts is : sqlplus -S "user/pwd@server" <<!! SET SQLPROMPT exec IN_PROC_TEST; !! It prompt 'PL/SQL procedure successfully completed.' but not execute actually :mad: I run this procedure in sqlplus, no problem and I also could execute simple DDL in this sh script, but why... (2 Replies)
Discussion started by: andrewknight
2 Replies

10. UNIX for Advanced & Expert Users

How can i pass a file from unix to oracle??

I am having a file which has the following data 123424 2354235 142424 43423 1434 . . . . etc i want to pass these values to oracle at a time by using a file.. can someone suggest me a answer... waiting...... (1 Reply)
Discussion started by: vidyadhar85
1 Replies
Login or Register to Ask a Question