# Calling Oracle function from script

**URL:** <https://community.unix.com/t/calling-oracle-function-from-script/368720>\
**Category:** Shell Programming and Scripting\
**Created:** [October 9, 2017, 3:00pm UTC](https://community.unix.com/t/calling-oracle-function-from-script/368720 "2017-10-09T15:00:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kev94](https://community.unix.com/letter_avatar/kev94/32/5_5575768a8748004e209b776fc1b2916d.png) [@kev94](https://community.unix.com/u/kev94)\
**Post date:** [October 9, 2017, 3:00pm UTC](https://community.unix.com/t/calling-oracle-function-from-script/368720/1 "2017-10-09T15:00:02Z")

</div>

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

```nohighlight
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.

```nohighlight
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

---

<div class="post-metadata">

**Author:** ![bakunin](https://community.unix.com/letter_avatar/bakunin/32/5_5575768a8748004e209b776fc1b2916d.png) [@bakunin](https://community.unix.com/u/bakunin)\
**Post date:** [October 9, 2017, 4:17pm UTC](https://community.unix.com/t/calling-oracle-function-from-script/368720/2 "2017-10-09T16:17:11Z")

</div>

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

> [@kev94](#):
>
> test.csv
> 
> ```plaintext
> 1,2,3,,5,,,8,9,10
> 1,2,3,4,5,,,8,9,10
> 1,2,3,,,,,8,9,10
> 
> ```

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

> [@kev94](#):
>
> In the above file I want to replace column 2 with a value extracted from database  
> like ( `select student_id from stud_master` )

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

```plaintext
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?

> [@kev94](#):
>
> Normally I could able to connect to db and and get the value.
> 
> ```plaintext
> 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.

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

```plaintext
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

---

<div class="post-metadata">

**Author:** ![kev94](https://community.unix.com/letter_avatar/kev94/32/5_5575768a8748004e209b776fc1b2916d.png) [@kev94](https://community.unix.com/u/kev94)\
**Post date:** [October 9, 2017, 5:26pm UTC](https://community.unix.com/t/calling-oracle-function-from-script/368720/3 "2017-10-09T17:26:39Z")

</div>

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:

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

```

Output

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

```

Thanks

---

<div class="post-metadata">

**Author:** ![bakunin](https://community.unix.com/letter_avatar/bakunin/32/5_5575768a8748004e209b776fc1b2916d.png) [@bakunin](https://community.unix.com/u/bakunin)\
**Post date:** [October 10, 2017, 5:15am UTC](https://community.unix.com/t/calling-oracle-function-from-script/368720/4 "2017-10-10T05:15:49Z")

</div>

> [@kev94](#):
>
> Oralce Function will always return one value, I want to replace that value with second field in csv file.
> 
> Input:
> 
> ```plaintext
> 1,2,3,,5,,,8,9,10
> 
> ```
> 
> Output
> 
> ```plaintext
> 1,<<replaced value from function>>,3,,5,,,8,9,10
> 1,<<replaced value from function>>,3,4,5,,8,9,10
> 
> ```

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

```plaintext
$ /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:

```plaintext
$ 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:

```plaintext
$ myvar=$(echo "hello world")

```

is ultimately the same as

```plaintext
$ 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:

```plaintext
#! /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