Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to pass parameter to User defined function in shell script?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to pass parameter to User defined function in shell script?
# 1  
Old 10-11-2010
How to pass parameter to User defined function in shell script?
Hello,

Can anyone guide me tin passing parameters into user defined function of shell script (KSH).
Here is my code,
Code:
InsertRecord()
{
DB_TBL=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
INSERT INTO TBL1 ( OLD_VAL,
    NEW_VAL,
    DATE)
    VALUES (
    $Old_VAL,
    $New_VAL,
    $DATE
    );
SELECT COUNT (*) FROM TBL1;
commit;
/ 
EOF)
echo $DB_TBL
}
 
OLD_VAL=blabla
NEW_VAL=blabla
DATE=blabla
value=$(InsertRecord "$OLD_VAL" "$NEW_VAL" "$DATE")

Please let me know the correct way to pass parameters to function? also let me know how can I use it in INSERT statement?
Could you please help me out.
Last edited by Poonamol; 10-11-2010 at 07:38 AM.. Reason: spell correction
# 2  
Old 10-11-2010
getting parameters in a function is the same as in a script. If you call the script like
Code:
InsertRecord "$OLD_VAL" "$NEW_VAL" "$DATE"

you can retrieve the values inside the function (as in your example)
Code:
...
    VALUES (
    $1,
    $2,
    $3
    );
...

But the variables in the script are also visible from inside the function, so you can refer directly to them by their names "$OLD_VAL" "$NEW_VAL" "$DATE"
This User Gave Thanks to frans For This Post:
usha rao
# 3  
Old 10-11-2010
Thanks a lot for reply.
I had called function as
Code:
InsertRecord

now its working fine.
Could you please let me know, how can I pass multiple values to INSERT statement?
I am parsing a file line by line and adding required field values into
Code:
"$OLD_VAL" "$NEW_VAL" "$DATE"

But for each line I am connecting to DB and doing insertion and disconnecting.
Is there any way that I can store all values of file lines and collectively insert them into database?
Please help me out.
# 4  
Old 10-11-2010
Quote:
Originally Posted by Poonamol
Is there any way that I can store all values of file lines and collectively insert them into database?
I think you should read the man of sqlplus.
# 5  
Old 10-11-2010
In sqlplus you should can use
Code:
&Old_VAL,
&New_VAL,
&DATE

instead of

Code:
$Old_VAL,
$New_VAL,
$DATE

This will prompt you to enter the values, but this would suppose an interactive mode.

You'd better go for an alternative using the eval command to force the $variable to be evaluated before you give it to sqlplus.
You also can dynamically build an file.sql that you can then pass to your sqlplus with @file.sql
# 6  
Old 10-12-2010
Thanks for reply.
Can you please let me know the correct syntax using
Code:
&Old_VAL,
&New_VAL,
&DATE

into INSERT statement.

Also let me know, How can I check the TBL1 is exist in database or not. If it exist then only insert the record.
Code:
SELECT COUNT (*) FROM TBL1;

How can I check the return val of this SETECT statement in database?

Thanks in advance.
# 7  
Old 10-12-2010
Maybe sql loader would be of some interest?

---------- Post updated at 09:56 PM ---------- Previous update was at 09:15 PM ----------

maybe something like this :

Code:
InsertRecord()
{
echo "set head off 
set feed off 
set serveroutput on
INSERT INTO TBL1 ( OLD_VAL,
    NEW_VAL,
    DATE)
    VALUES (
    $1,
    $2,
    $3
    );
SELECT COUNT (*) FROM TBL1;
commit;
/ 
exit" > db_tbl.sql

DB_TBL=$(sqlplus $USERID/$PASSWORD@$DATABASE @db_tbl.sql )
rm db_tbl.sql
}
 
OLD_VAL=blabla
NEW_VAL=blabla
DATE=blabla

value=$(InsertRecord "$OLD_VAL" "$NEW_VAL" "$DATE")

Note that for massive load, i would not recommend this approach.
You should look sql plus and pl/sql synthax.
If you run oracle, you have a lot of example under admin directory (look the code of the AWR report generator $ORACLE_HOME/rdbms/admin/awrrpt.sql, and see how it uses bind variables)
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Homework & Coursework Questions

How to Dynamically Pass Parameter to plsql Function & Capture its Output Value in a Shell Variable?

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: 2. Relevant commands, code, scripts, algorithms: #! /bin/ksh v="ORG_ID" ... (2 Replies)
Discussion started by: sujitdas2104
2 Replies

2. Shell Programming and Scripting

How to pass the parameter in xml file in UNIX shell script?

Hi, I have an XML file like the following... <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ONDEMAND_JOB VERSION="5.1" LOCALE="en_US"> <IMPORT_JOBSET TC_CONNECTION_NAME="default" ENVIRONMENT="PRD" USERNAME="Administrator" PASSWORD="AdminPassword" CALENDAR="Main Monthly Calendar"... (3 Replies)
Discussion started by: Debalina Roy
3 Replies

3. Shell Programming and Scripting

How to pass the parameter in xml file in UNIX shell script?

Hi, I have an XML file like the following... <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ONDEMAND_JOB VERSION="5.1" LOCALE="en_US"> <IMPORT_JOBSET TC_CONNECTION_NAME="default" ENVIRONMENT="PRD" USERNAME="Administrator" PASSWORD="AdminPassword" CALENDAR="Main Monthly Calendar"... (2 Replies)
Discussion started by: Debalina Roy
2 Replies

4. Post Here to Contact Site Administrators and Moderators

Unable to pass shell script parameter value to awk command in side the same script

Variable I have in my shell script diff=$1$2.diff id=$2 new=new_$diff echo "My id is $1" echo "I want to sync for user account $id" ##awk command I am using is as below cat $diff | awk -F'~' ''$2 == "$id"' {print $0}' > $new I could see value of $id is not passing to the awk... (0 Replies)
Discussion started by: Ashunayak
0 Replies

5. Shell Programming and Scripting

Pass parameter to nawk from shell script

I need to parse log files using nawk, but I'm not able to pass script input argument (date) to nawk, for example: ------------ #!/bin/ksh read date nawk -F, '{if($1==date) print $4" "$5}' ------------- Is there a way to pass an argument to nawk from shell script. Many thanks... (8 Replies)
Discussion started by: samer.odeh
8 Replies

6. Shell Programming and Scripting

What is the maximum number of parameter we can pass to a shell script function?

what is the maximum number of parameter we can pass to a shell script function (8 Replies)
Discussion started by: alokjyotibal
8 Replies

7. Shell Programming and Scripting

How to pass a variable as a parameter to DB2 database from shell script

I need to pass a variable as a parameter from shell script into a DB2 database. var=bhuk_1123_Q_11/22/09 select * from tbl1 where serial_id='$var'; I have tried executing it using db2 -tvf scriptname Somebody please help me out with this. It is throwing an error. Please tell me how... (2 Replies)
Discussion started by: ss3944
2 Replies

8. Shell Programming and Scripting

Shell Script to display function names (called & defined) in a C++ Source Code

Hello to all, I am looking for a way to display only the names of function (calls & definition) of a C++ source code.There is already a post related to this, but the script is to find the functions using a specific variable, and the replies are not that convincing since they cannot be used for... (2 Replies)
Discussion started by: frozensmilz
2 Replies

9. Shell Programming and Scripting

pass parameter to function

HI all I have a code like ############################################## minyear() { curryear=$1 echo $curryear } ##Main Program ## minyear exit ####### when i execute "sh scriptname 2005" output should be like 2005 but the output is blank. I guess i need to pass parameter to... (3 Replies)
Discussion started by: vasuarjula
3 Replies

10. Shell Programming and Scripting

How to pass parameter from sqlplus(procedure completed) to your shell script

if then # mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com echo "FILE does not exist" exit 1 fi echo "FILE EXIST" size=-1 set $(du /export/home/oracle/nas/scott21.dmp.gz) while do echo "Inside the loop" size=$1 set $(du... (1 Reply)
Discussion started by: sanora600
1 Replies
Login or Register to Ask a Question