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

Shell Programming and Scripting
1

Hello,

Can anyone guide me tin passing parameters into user defined function of shell script (KSH).
Here is my 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.

2

getting parameters in a function is the same as in a script. If you call the script like

InsertRecord "$OLD_VAL" "$NEW_VAL" "$DATE"

you can retrieve the values inside the function (as in your example)

...
    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"

1 Like
3

Thanks a lot for reply.
I had called function as

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

"$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

I think you should read the man of sqlplus.

6

In sqlplus you should can use

&Old_VAL,
&New_VAL,
&DATE

instead of

$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

7

Thanks for reply.
Can you please let me know the correct syntax using

&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.

SELECT COUNT (*) FROM TBL1;

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

Thanks in advance.

9

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 :

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)

10

Thanks a lot for reply. :slight_smile: