Need to pass Oracle SQL output to Linux and back... Help!
Hi all,
Hopefully you can help. This is what I'm trying to achieve:
Obtain a list of usernames out of an Oracle Database
Based on this list, link each username with an Oracle Internet Directory (OID) GUID
Using the username and GUID perform a database update for all users
Here are the commands:
This is SQL which is not the scope of this forum. Let's say I'll have a flat text file that has each username listed on a single line as the output
Now I have to run this list against OID to obtain the so so called GUID. For this I'll run:
The output looks like (used jdoe as a username example):
3. Now I need to pass this back to SQL
Ideally I have one file that takes the output above and prints it like
Any thoughts how to do this easily? Remember that the output of step 1 will be a list of app 100 users, and the final output of step 3 should basically have the GUID printed next to this (I assume in a new flat file)
Use/include an awk script to parse for "^orclguid=" and "^sn="...i.e.
something like that
awk can be piped to our script specified inline using ' ', or use -f to specify a awk script file.
If your ldap results in multiple lines, I would save that entire output to a file, then run it through awk.
the key here is awk since you have multiple lines that convey a single context.
This may also work
Last edited by blackrageous; 10-24-2014 at 06:01 PM..
So let's see I have a flat text file ldap.txt which contains
JDOE
HCLINTON
JBUSH
I will need to loop through those names like this
The output will look like
And then THIS output has to be put in a flat file like this
I'm not sure based on your advise how to incorporate that?
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
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
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.
awk can be moved completely outside the loop, so awk and xargs only need to be run once instead of n times. Since you're only using sn= (I think!) that simplifies it a lot too.
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)
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)
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)
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)
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)
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)
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)
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)
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)