Sponsored Content
Top Forums Shell Programming and Scripting Need to pass Oracle SQL output to Linux and back... Help! Post 302922839 by Corona688 on Tuesday 28th of October 2014 03:38:54 PM
Old 10-28-2014
Quote:
Originally Posted by exm
Thanks all for the help. The code below is doing what I am trying to accomplish. I figure to share this in case it helps someone, and I am open for suggestions for any further tuning Smilie
Hmm.

It's cleaner to use a here-document for large multi-line strings.

I'm guessing xargs was to strip off quotes? That's cool. That can be moved outside the loop so it only needs to be run once instead of n times.

It's simple to skip blank lines in the loop instead of editing the temp file with a grep and a mv.

If you're editing the string with awk, don't bother with sed | cut | kitchen | sink -- solve the whole thing in one awk. It'd be nice if we could strip the quotes off too, but xargs really is better at that.

The sed was unnecessary with the code I originally gave you. It split the data on "=" into NAME and VAL. When you got rid of one of those variables you ended up having to do the splitting yourself again, with sed. But never mind, since we need awk, it can do that too.

Code:
# Section 1: Obtain list of user names from EBS
cat > $CLONING_LOG/ldap.sql <<EOF

set echo off;
set heading off;
set feedback off;
set pagesize 3000;
set linesize 200;
set termout off
set trimout on;
set trimspool on;
spool $CLONING_LOG/ldap.txt
select user_name from fnd_user where user_id<>3304
and (END_DATE > SYSDATE or END_DATE is null)
and user_name <> 'ASADMIN'
order by user_name;
spool off
exit
EOF

sqlplus apps/$APPS_PASSWORD @$CLONING_LOG/ldap.sql

# Section 2 obtain the orclguid from OID for these users
while read -r U
do
        [ -z "$U" ] && continue # Skip blank lines

        ldapsearch -h $OIDHOST -p $OIDPORT  -w $OIDPWD "(sn=$U)" orclguid sn | awk -F"=" 'NR==2,NR==3 { print $2 }'
done < $CLONING_LOG/ldap.txt | xargs | while read NAME 
do
# Section 3 updating the GUID in EBS from OID 
 echo "Now Updating" $NAME
 sqlplus -s apps/$APPS_PASSWORD @$CLONING_SCRIPTS/ebsclone_target_guid.sql $NAME
done

 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

How to pass a oracle variable back to the shell script

Hi, I am calling an oracle function that returns a number (either 0 or 2), how do I pass that pass to the wrapping shell script as I would like to do other things based on the value returned by the oracle function. Your help will be appreciated. -------------------------- sqlplus / <<... (3 Replies)
Discussion started by: Jtrinh
3 Replies

2. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

how to pass values from oracle sql plus to unix shell script (2 Replies)
Discussion started by: trichyselva
2 Replies

3. Filesystems, Disks and Memory

in Oracle Enterprise Linux not able to redirect pvscan output

hi, In Oracle Enterprise Linux I'm not able to redirect output of commands pvscan and vgscan into a file. File is coming blank Please suggest something Thanx. (1 Reply)
Discussion started by: discover
1 Replies

4. Linux

In Oracle Enterprise Linux, not able redirect pvscan output

Hi, I'm not able to redirect output of ovscan and vgscan commands to a file in Oracle Enterprise Linux. Please suggest something. Thanks Mayank (1 Reply)
Discussion started by: discover
1 Replies

5. Shell Programming and Scripting

How to Pass the Output Values from the PL/SQL Procedure to Shell Script?

hi, Could anyone tell me how to pass the output values of the PL/SQL procedure to Shell script and how to store that values in a shell script variable... Thanks in advance... (5 Replies)
Discussion started by: funonnet
5 Replies

6. UNIX for Dummies Questions & Answers

Pass value back to unix variable

i had this unix korn shell code that connects to oracle database and execute the oracle procedure. i need to add a variable that indicates the oracle procedure failed. basically the variable is to check if the oracle procedure failed it will assign 1 and when the variable is equal to 1 it will not... (4 Replies)
Discussion started by: wtolentino
4 Replies

7. Shell Programming and Scripting

Pass perl array to SQL oracle

Hello, Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg : #!/usr/bin/perl @v_array = (1,2,4,5,6,8); $db_userid = 'ni71/ni711'; $bufTPO = qx{ sqlplus -s << EOF $db_userid set verify off set feedback off set... (1 Reply)
Discussion started by: arunshankar.c
1 Replies

8. UNIX for Dummies Questions & Answers

Removing unnecessary eol ($) character from Oracle sql query output

Hi All, I am fetching oracle query result in shell variable. As columns numbers are more the output wraps in unix terminal .i.e one complete record in db gets store in multiple lines. with each line ends with $ character. I want to remove these unnecessary $ character but to keep required $... (8 Replies)
Discussion started by: Harshal22
8 Replies

9. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off... (14 Replies)
Discussion started by: megha2525
14 Replies

10. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies
regex(1F)							   FMLI Commands							 regex(1F)

NAME
regex - match patterns against a string SYNOPSIS
regex [-e] [ -v "string"] [ pattern template] ... pattern [template] DESCRIPTION
The regex command takes a string from the standard input, and a list of pattern / template pairs, and runs regex() to compare the string against each pattern until there is a match. When a match occurs, regex writes the corresponding template to the standard output and returns TRUE. The last (or only) pattern does not need a template. If that is the pattern that matches the string, the function simply returns TRUE. If no match is found, regex returns FALSE. The argument pattern is a regular expression of the form described in regex(). In most cases, pattern should be enclosed in single quotes to turn off special meanings of characters. Note that only the final pattern in the list may lack a template. The argument template may contain the strings $m0 through $m9, which will be expanded to the part of pattern enclosed in ( ... )$0 through ( ... )$9 constructs (see examples below). Note that if you use this feature, you must be sure to enclose template in single quotes so that FMLI does not expand $m0 through $m9 at parse time. This feature gives regex much of the power of cut(1), paste(1), and grep(1), and some of the capabilities of sed(1). If there is no template, the default is $m0$m1$m2$m3$m4$m5$m6$m7$m8$m9. OPTIONS
The following options are supported: -e Evaluates the corresponding template and writes the result to the standard output. -v "string" Uses string instead of the standard input to match against patterns. EXAMPLES
Example 1: Cutting letters out of a string To cut the 4th through 8th letters out of a string (this example will output strin and return TRUE): `regex -v "my string is nice" '^.{3}(.{5})$0' '$m0'` Example 2: Validating input in a form In a form, to validate input to field 5 as an integer: valid=`regex -v "$F5" '^[0-9]+$'` Example 3: Translating an environment variable in a form In a form, to translate an environment variable which contains one of the numbers 1, 2, 3, 4, 5 to the letters a, b, c, d, e: value=`regex -v "$VAR1" 1 a 2 b 3 c 4 d 5 e '.*' 'Error'` Note the use of the pattern '.*' to mean "anything else". Example 4: Using backquoted expressions In the example below, all three lines constitute a single backquoted expression. This expression, by itself, could be put in a menu defini- tion file. Since backquoted expressions are expanded as they are parsed, and output from a backquoted expression (the cat command, in this example) becomes part of the definition file being parsed, this expression would read /etc/passwd and make a dynamic menu of all the login ids on the system. `cat /etc/passwd | regex '^([^:]*)$0.*$' ' name=$m0 action=`message "$m0 is a user"`'` DIAGNOSTICS
If none of the patterns match, regex returns FALSE, otherwise TRUE. NOTES
Patterns and templates must often be enclosed in single quotes to turn off the special meanings of characters. Especially if you use the $m0 through $m9 variables in the template, since FMLI will expand the variables (usually to "") before regex even sees them. Single characters in character classes (inside []) must be listed before character ranges, otherwise they will not be recognized. For exam- ple, [a-zA-Z_/] will not find underscores (_) or slashes (/), but [_/a-zA-Z] will. The regular expressions accepted by regcmp differ slightly from other utilities (that is, sed, grep, awk, ed, and so forth). regex with the -e option forces subsequent commands to be ignored. In other words, if a backquoted statement appears as follows: `regex -e ...; command1; command2` command1 and command2 would never be executed. However, dividing the expression into two: `regex -e ...``command1; command2` would yield the desired result. ATTRIBUTES
See attributes(5) for descriptions of the following attributes: +-----------------------------+-----------------------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +-----------------------------+-----------------------------+ |Availability |SUNWcsu | +-----------------------------+-----------------------------+ SEE ALSO
awk(1), cut(1), grep(1), paste(1), sed(1), regcmp(3C), attributes(5) SunOS 5.10 12 Jul 1999 regex(1F)
All times are GMT -4. The time now is 05:30 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy