Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Passing values between Bash Shell (Oracle Linux 6.5) and Oracle 11g procedures and functions

Posted on 2014-03-18
11
1,447 Views
Last Modified: 2014-03-19
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

Challenge 2 -------

if we assume in the file named testfile_20140318_09583200_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

#!/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[@]}

Open in new window


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
}

Open in new window

I can not get this work.

Challenge 3 -------

Assume my oracle package has another function pkg_file_process.check_filename 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

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

Open in new window

I can not get this work.

Challenge 4 -------

I saw this snipet from

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.  
#!/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

Open in new window

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.
0
Comment
Question by:klyles95
  • 6
  • 5
11 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 500 total points
ID: 39937524
1,2) Create external table for the csv file and just "select" what you need for your procedure.

3)
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=#?
if [ $ret_val -eq 0 ]; then
   echo "File name validates successfully"
else
   echo "File name invalid"
fi 

Open in new window


4) Yes, arrays may be different for bash, try this:

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

Open in new window

0
 

Author Comment

by:klyles95
ID: 39938033
Hello Mike.  Thank you for responding

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:

mytest.sh: line 11: [: #?: integer expression expected

Open in new window


Any ideas on this?

4)  Works perfectly!  Thank you
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39938039
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
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:klyles95
ID: 39938171
Hello Mike,

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 

Open in new window


func_result remains empty even though I have confirmed that $ret_val was assigned a value and subsequently assigned to func_result.  Any ideas?
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 39938216
Try this:
. . .
 END;
 /
 exit :l_status;
EOF
return $?
}

## call function
do_check
func_result=$?
if [ $func_result -eq 0 ]; then
   echo "File name validates successfully"
else
   echo "File name invalid"
fi 

Open in new window

0
 

Author Closing Comment

by:klyles95
ID: 39938282
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!!!
0
 

Author Comment

by:klyles95
ID: 39938926
Hello Mike - I have one last question.

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 

Open in new window


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.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39939510
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.
0
 

Author Comment

by:klyles95
ID: 39940461
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.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39940481
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 

Open in new window

0
 

Author Comment

by:klyles95
ID: 39941073
Thank you Mike...that did it
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Samba Question 11 74
having some issue on pl sql procedure 1 23
return value in based on value passed 6 27
Renaming with batch file 9 48
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question