Calling Oracle function from script

Shell Programming and Scripting
1

Hi
I need to call a function in database and update the return value of that function with a value in csv file.

test.csv

1,2,3,,5,,,8,9,10
1,2,3,4,5,,,8,9,10
1,2,3,,,,,8,9,10

In the above file I want to replace column 2 with a value extracted from database
like ( select student_id from stud_master )

The output can be updated the same file or ideally into another file.

Normally I could able to connect to db and and get the value.

awk 'BEGIN -F, -v OFS=, {print $1,$2,$3}' test.csv

Now I want to know to call a oracle function and replace the output for $2 variable.

Thanks

2

Bear with me but i don't quite get what you want:

OK, what do you want to do with this file?

ahem - yes? From a select statement like the above you likely get a list in return, say, this:

99
100
101
...

What exactly do you want to do to "column two" and what has has this to do with the list the select-statement returned? Could you show an example of the outcome you have in mind?

Now i am even more confused: if you replace "$2" (the second field) with the output i envisioned above you would get this:

1,99
100
101
...,3
1,99
100
101
...,3
1,99
100
101
...,3

I suppose this is not what you want, but i can't figure out what it actually is. Please enlighten us.

bakunin

3

Hi Thanks for your message.

Oralce Function will always return one value, I want to replace that value with second field in csv file.

Input:

1,2,3,,5,,,8,9,10

Output

1,<<replaced value from function>>,3,,5,,,8,9,10
1,<<replaced value from function>>,3,4,5,,8,9,10

Thanks

4

OK so far, this makes it clearer. If i get you correctly the value this function returns will not change throughout a run of this script and therefore "<<replaced value from function>>" will always be the same, yes?

Next: you want to replace the (second) column in the CSV-file with this value you have retrieved and then store the changed outcome to either (preferably?) a new file or the same file, yes?

If so:

Here is how you query the DB and store the result in a variable: this is done via a procedure called "process substitution". Normally a command would have some output which lands on <stdout>:

$ /some/command -xyz
hello world

$ _

In shell scripts you can "catch" this outcome into a variable instead of displaying it. Try out the following yourself:

$ echo "hello world"
hello world

$ myvar=$(echo "hello world")

$ echo $myvar
hello world

$ _

What the "$(...)" does is: the command (or pipeline - "command" is not limited to a single command but everything you can issue on a command line) is executed in a sub-shell. Then whatever the output on <stdout> is, is treated as if it would be a fixed string. Therefore:

$ myvar=$(echo "hello world")

is ultimately the same as

$ myvar="hello world"

but with the difference that echo "hello world" is really executed and the output of that is used.

You can do the same for your problem. I don't know which database you use, so this example is how one would do it in Oracle with sqlplus , but you can adapt that to your DB:

#! /bin/ksh

typeset chRepl="$(sqlplus -s /nolog <<-EOF
                  connect / as sysdba
                  set lines=132
                  set trims on
                  set tabs off
                  set pages 2000
                  select select student_id from stud_master;
                  exit
                  EOF
                 )"
typeset fInput="/path/to/input.file"
typeset fOutput="/path/to/output.file"

if sed 's/[^,]*,/'"$chRepl",/2' "$fInput" > "$fInput".tmp ; then
     mv "$fInput".tmp "$fOutput"
else
     print -u2 - "error working on $fInput, aborting."
     exit 1
fi

exit 0

I hope this helps.

bakunin