klyles95
asked on
Passing values between Bash Shell (Oracle Linux 6.5) and Oracle 11g procedures and functions
This is a long post but bear with me. It has 3 parts.
I am writing a bash shell script that will load and process a flat file.
This script needs to be able to :
1. pass single variables from the script to an oracle procedure or function <----- This I am able to do without issue
2. pass a shell array to an oracle procedure or function <----- this I need help with
3. assign a single value to a shell variable FROM a procedure or a function <----- this I need help with
4. assign a multiple values to a shell array variable FROM a procedure <----- this I need help with
_0004.csv contains 10 lines of which:
1st Line = H,20140318,09583200,0004
10th Line = T,1234567
In my script I have the following snipet of code
I then want to be able to pass the array variables arr_hdr and arr_trl whether to a procedure or function to do further validation, something like this:
ename that returns 0 for success and 1 for failure. Intent is to validate the parts of the filename against parts of the header record plus other business rules.
I want to capture that return value in a shell variable.
In my script I have the following snipet of code
http://asktom.oracle.com/pls/asktom/f/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:430819636473
which apparently returns multiple values to a shell array variable.
-----
Any assistance on solutions for 2, 3, 4 would be greatly appreciated.
I am writing a bash shell script that will load and process a flat file.
This script needs to be able to :
1. pass single variables from the script to an oracle procedure or function <----- This I am able to do without issue
2. pass a shell array to an oracle procedure or function <----- this I need help with
3. assign a single value to a shell variable FROM a procedure or a function <----- this I need help with
4. assign a multiple values to a shell array variable FROM a procedure <----- this I need help with
Challenge 2 -------
if we assume in the file named testfile_20140318_095832001st Line = H,20140318,09583200,0004
10th Line = T,1234567
In my script I have the following snipet of code
#!/bin/bash
myfile='testfile_20140318_09583200_0004.csv'
hdr=`sed '1q;d' $myfile`
trl=`sed '$q;d' $myfile`
arr_hdr=($( echo "$hdr" | awk 'BEGIN{FS=","} { for (i=1; i<=NF; i++) print $i }'))
arr_trl=($( echo "$trl" | awk 'BEGIN{FS=","} { for (i=1; i<=NF; i++) print $i }'))
cnt_hdr_fields=${#arr_hdr[@]}
cnt_trl_fields=${#arr_trl[@]}
I then want to be able to pass the array variables arr_hdr and arr_trl whether to a procedure or function to do further validation, something like this:
sqlplus -s /nolog << EOF
SET TIMING OFF SET FEEDBACK OFF SET SERVEROUTPUT ON
BEGIN
pkg_file_process.check_hdr_trl(p_hdr => $arr_hdr , p_trl => $arr_trl );
END;
/
exit;
EOF
}
I can not get this work.Challenge 3 -------
Assume my oracle package has another function pkg_file_process.check_filI want to capture that return value in a shell variable.
In my script I have the following snipet of code
#!/bin/bash
myfile='testfile_20140318_09583200_0004.csv'
hdr=`sed '1q;d' $myfile`
trl=`sed '$q;d' $myfile`
arr_hdr=($( echo "$hdr" | awk 'BEGIN{FS=","} { for (i=1; i<=NF; i++) print $i }'))
arr_trl=($( echo "$trl" | awk 'BEGIN{FS=","} { for (i=1; i<=NF; i++) print $i }'))
cnt_hdr_fields=${#arr_hdr[@]}
cnt_trl_fields=${#arr_trl[@]}
ret_val=`sqlplus -s /nolog << EOF| grep ^l_status | sed "s/^l_status:= //"
SET TIMING OFF SET FEEDBACK OFF SET SERVEROUTPUT ON
DECLARE
l_status NUMBER;
BEGIN
l_status:= pkg_file_process.check_filename(p_filename => '$myfile', p_hdr => '$hdr' );
END;
/
exit;
EOF`
}
if [ $ret_val -eq 0 ]; then
echo "File name validates successfully"
else
echo "File name invalid"
fi
I can not get this work.Challenge 4 -------
I saw this snipet fromhttp://asktom.oracle.com/pls/asktom/f/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:430819636473
which apparently returns multiple values to a shell array variable.
#!/bin/ksh
echo "------------------------------"
echo "using an array..."
echo "------------------------------"
## Max 4095 in Sun OS 5.6!
set -A my_arr `sqlplus -s scott/tiger@dev <<EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 4;
exit;
EOF`
echo "there are ${#my_arr[*]} elements in the array"
element=0
while [ $element -lt ${#my_arr[*]} ]
do
echo "==>"${my_arr[$element]}
let element=$element+1;
done
I have attempted to get this concept to work in my bash environment for my specific needs but unsuccessfully. Is it because this is for a ksh shell and certain commands like 'set -A' are not valid in bash shell?-----
Any assistance on solutions for 2, 3, 4 would be greatly appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
3) Its {dollar}?:
ret_val=$?
External Table is perfect substitute for sql loader.
If you only need to "view" the csv file, check out my article:
Quick and Dirty Unix CSV File Browser
ret_val=$?
External Table is perfect substitute for sql loader.
If you only need to "view" the csv file, check out my article:
Quick and Dirty Unix CSV File Browser
ASKER
Hello Mike,
I got 3 to work. Thank you. But now I want to enhance a little. I attempted the following:
func_result remains empty even though I have confirmed that $ret_val was assigned a value and subsequently assigned to func_result. Any ideas?
I got 3 to work. Thank you. But now I want to enhance a little. I attempted the following:
#!/bin/bash
# global variable
func_result=''
function do_check () {
sqlplus -s /nolog << EOF
SET TIMING OFF SET FEEDBACK OFF SET SERVEROUTPUT ON
VAR l_status NUMBER;
DECLARE
BEGIN
:l_status:= pkg_file_process.check_filename(p_filename => '$myfile', p_hdr => '$hdr' );
END;
/
exit :l_status;
EOF
ret_val=$?
func_result=$ret_val
}
## call function
do_check
if [ $func_result -eq 0 ]; then
echo "File name validates successfully"
else
echo "File name invalid"
fi
func_result remains empty even though I have confirmed that $ret_val was assigned a value and subsequently assigned to func_result. Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Mike,
I have 3 & 4 working as I desire. I will discuss external tables with my manager but gut feeling is that he wants to stick with SQL Loader.
I will also look at that script you posted on the link.
Thank you for your quick and useful (i.e. it works) responses!!!
I have 3 & 4 working as I desire. I will discuss external tables with my manager but gut feeling is that he wants to stick with SQL Loader.
I will also look at that script you posted on the link.
Thank you for your quick and useful (i.e. it works) responses!!!
ASKER
Hello Mike - I have one last question.
I had done the following:
Adding the redirection to the function has now masked the result from the function. Any idea on how to resolve? If I should open this as a new question please let me know and I will do so.
I had done the following:
. . .
END;
/
exit :l_status;
EOF
return $?
}
## call function
do_check | tee -a ${LOGFILE} ## add redirection to file
func_result=$?
if [ $func_result -eq 0 ]; then
echo "File name validates successfully"
else
echo "File name invalid"
fi
Adding the redirection to the function has now masked the result from the function. Any idea on how to resolve? If I should open this as a new question please let me know and I will do so.
Why would you want to re-direct a result code ( 0 - 255 ) to a file?
You already display a message, redirect that messaeg to the logfile.
You already display a message, redirect that messaeg to the logfile.
ASKER
The procedure I am writing is doing dbms_output.put_line statements and those are what I am attempting to capture in the log file.
The side affect however is that the result code is also being redirected. If I remove the redirection, I have the result code but then the dbms_output.put_line from the proc are not being captured in the file.
The side affect however is that the result code is also being redirected. If I remove the redirection, I have the result code but then the dbms_output.put_line from the proc are not being captured in the file.
then try this:
END;
/
exit :l_status;
EOF
return $?
}
## call function
do_check >> ${LOGFILE} ## add redirection to file
func_result=$?
if [ $func_result -eq 0 ]; then
echo "File name validates successfully"
else
echo "File name invalid"
fi
ASKER
Thank you Mike...that did it
ASKER
For 1, 2)
use External Table : That would be good however we are using sql loader. And so before I go through the exercise of inserting in some cases millions of rows into a staging table I want to verify some things first.
3)
I get the error message:
Open in new window
Any ideas on this?
4) Works perfectly! Thank you