Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Calling Oracle function from script

Shell Programming and Scripting


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 1 Week Ago
kev94 kev94 is offline
Registered User
 
Join Date: Oct 2017
Last Activity: 9 October 2017, 5:26 PM EDT
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Calling Oracle function from script

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

Code:
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.


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


Moderator's Comments:
Calling Oracle function from script Please use CODE tags as required by forum rules!

Last edited by RudiC; 1 Week Ago at 03:18 PM.. Reason: Added CODE tags.
Sponsored Links
    #2  
Old Unix and Linux 1 Week Ago
bakunin bakunin is offline Forum Staff  
Bughunter Extraordinaire
 
Join Date: May 2005
Last Activity: 22 October 2017, 8:09 PM EDT
Location: In the leftmost byte of /dev/kmem
Posts: 5,614
Thanks: 106
Thanked 1,598 Times in 1,176 Posts
Bear with me but i don't quite get what you want:

Quote:
Originally Posted by kev94 View Post
test.csv

Code:
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?

Quote:
Originally Posted by kev94 View Post
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:


Code:
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?

Quote:
Originally Posted by kev94 View Post
Normally I could able to connect to db and and get the value.


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


Code:
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
Sponsored Links
    #3  
Old Unix and Linux 1 Week Ago
kev94 kev94 is offline
Registered User
 
Join Date: Oct 2017
Last Activity: 9 October 2017, 5:26 PM EDT
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
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:

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

Output

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

Thanks

Last edited by rbatte1; 1 Week Ago at 05:47 AM.. Reason: Added CODE tags
    #4  
Old Unix and Linux 1 Week Ago
bakunin bakunin is offline Forum Staff  
Bughunter Extraordinaire
 
Join Date: May 2005
Last Activity: 22 October 2017, 8:09 PM EDT
Location: In the leftmost byte of /dev/kmem
Posts: 5,614
Thanks: 106
Thanked 1,598 Times in 1,176 Posts
Quote:
Originally Posted by kev94 View Post
Oralce Function will always return one value, I want to replace that value with second field in csv file.

Input:

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

Output

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


Code:
$ /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:


Code:
$ 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:


Code:
$ myvar=$(echo "hello world")

is ultimately the same as


Code:
$ 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:


Code:
#! /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

Last edited by rbatte1; 1 Week Ago at 05:48 AM.. Reason: Retro fitted CODE tags into quote to match
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Calling function from another bash script kristinu Shell Programming and Scripting 11 04-05-2012 10:32 AM
SHELL SCRIPT Function Calling Another Function Please Help... omkar.sonawane Shell Programming and Scripting 2 04-13-2010 10:20 AM
Question for calling oracle function from perl ili Shell Programming and Scripting 0 03-29-2008 09:51 PM
calling one function from another shell script trichyselva UNIX for Dummies Questions & Answers 1 01-16-2008 11:28 PM
calling c++ function from script Lebamb Programming 3 06-24-2003 09:53 AM



All times are GMT -4. The time now is 10:29 PM.