No question at this time
DBA Top 10
1 M. Cadot 9300
2 B. Vroman 7100
3 A. Kavsek 4600
4 P. Wisse 2400
5 J. Péran 1700
6 J. Schnackenberg 1400
7 T. Boles 800
8 D. Dave 400
9 S. Spb 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48613
Total active users1400
Act. users last 24h3
Act. users last hour0
Registered user hits last week107
Registered user hits last month432
Go up

How to read table columns into Shell variables?
Next thread: RAC database stop and start shell script
Prev thread: Right Join is not working properly

Message Score Author Date
Hi Experts, Need some help on reading the oracl...... S SPB May 16, 2021, 14:12
Hello Shiva, - first of all your statement is n...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman May 17, 2021, 13:40
Thanks Bruno for your time on this, Finally I r...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts S SPB May 17, 2021, 14:42
Hi Shiva, thanks for the feedback. A first rema...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman May 18, 2021, 08:23
Hi Bruno, you are correct! We dont need those ...... S SPB May 18, 2021, 12:55

Follow up by mail Click here


Subject: How to read table columns into Shell variables?
Author: S SPB, India
Date: May 16, 2021, 14:12, 39 days ago
Os info: Any
Oracle info: Any
Message: Hi Experts,

Need some help on reading the oracle table rows into shell script variables.

I m trying to read username,account_status,locked_date,expiry_date into shell variables from the below query in loop But its getting wrong results due to column lenghts and line length limitations.

Do you have any alternative to get my results in expected way?

{
sqlplus -s /@GAPMASTER_VSDEV <<ENDOFSQL
SET HEADING OFF;
SET FEEDBACK OFF;
SET LINESIZE 300;
SELECT USERNAME USERNAME, ACCOUNT_STATUS, to_char(lock_date,'DD-MON-YYYY HH24:MI:SS') LOCK_DATE,
to_char(expiry_date,'DD-MON-YYYY HH24:MI:SS') EXPIRY_DATE,
to_char(last_login,'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN from DBA_USERS
ENDOFSQL
}|while read line
do
if [ "$line" ] # Line not NULL
then
set $line
USERNAME="$1"
ACCOUNT_STATUS="$2"
LOCK_DATE="$3"
EXPIRY_DATE="$4"
LAST_LOGIN="$5"
echo $USERNAME
echo $ACCOUNT_STATUS
echo $LOCK_DATE
echo $EXPIRY_DATE
echo $LAST_LOGIN
fi
done

Thanks,
Shiva
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to read table columns into Shell variables?
Author: Bruno Vroman, Belgium
Date: May 17, 2021, 13:40, 38 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Shiva,

- first of all your statement is not executed because there is no ending ";" or lines with "/"...

- then note that if a column is NULL it would give you issues (for example: if an OPEN account has no lock_date and expiry_date but a last_login, you will misinterpret last_login as lock_Date)

- then there are issues because of spaces... if account_status is "EXPIRED & LOCKED", you don't want thsi to be seen as three variables...

- (maybe the most important) you want to do something with this; maybe there is a better way to achieve your goal... You just ask us "how to set the variables" but maybe it would be bettter to know your problem, another solution might be proposed.

Well, I made quite some "trials and errors" and end up with the following, but maybe there are details that depend on the OS version...
{

sqlplus -s / AS SYSDBA <<ENDOFSQL
SET HEADING OFF;
SET FEEDBACK OFF;
SET LINESIZE 300;
SELECT '\\"' || USERNAME || '\\"' ||
' \\"' || REPLACE( ACCOUNT_STATUS, ' ', '\\ ' ) || '\\"' ||
' \\"' || NVL( to_char(lock_date,'DD-MON-YYYY\\ HH24:MI:SS'), '..-...-....\\ ..:..:..' ) || '\\"' ||
' \\"' || NVL( to_char(expiry_date,'DD-MON-YYYY\\ HH24:MI:SS'), '..-...-....\\ ..:..:..' ) || '\\"' ||
' \\"' || NVL( to_char(last_login,'DD-MON-YYYY\\ HH24:MI:SS'), '..-...-....\\ ..:..:..' ) || '\\"'
FROM dba_users
;
ENDOFSQL
}|while read us st lo ex la
do
if [ "$us" ] # Line not NULL
then
USERNAME="$us"
ACCOUNT_STATUS="$st"
LOCK_DATE="$lo"
EXPIRY_DATE="$ex"
LAST_LOGIN="$la"
echo usr=$USERNAME stat=$ACCOUNT_STATUS loc=$LOCK_DATE exp=$EXPIRY_DATE log=$LAST_LOGIN
fi
done
HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to read table columns into Shell variables?
Author: S SPB, India
Date: May 17, 2021, 14:42, 38 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Thanks Bruno for your time on this,

Finally I redirected all the output with a deli metered text into a flat file and used cut to read for each line.

rm -rf /home/oracle/test/stale_users.lst

sqlplus -s /as sysdba <<ENDOFSQL
SET HEADING OFF;
SET FEEDBACK OFF;
SET LINESIZE 600;
set pages 500;
spool /home/oracle/test/stale_users.lst
SELECT APPLICATION_NAME||':'||DBNAME||':'||USERNAME||':'||ACCOUNT_STATUS||':'||LOCK_DATE||':'||EXPIRY_DATE||':'||LAST_LOGIN||':'||USER_EMAIL||':'||MANAGERNAME||':'||MANAGER_MAIL||':'||STAT_COLLECT_DATE FROM (
SELECT replace(trim(d.APPLICATION),' ','_') APPLICATION_NAME, trim(a.DBNAME) DBNAME, trim(a.USERNAME) USERNAME, trim(ACCOUNT_STATUS) ACCOUNT_STATUS, trim(to_char(lock_date,'DD-MON-YYYY_HH24_MI_SS')) LOCK_DATE, trim(to_char(expiry_date,'DD-MON-YYYY_HH24_MI_SS')) EXPIRY_DATE, trim(to_char(last_login,'DD-MON-YYYY_HH24_MI_SS')) LAST_LOGIN,trim(b.EMAIL) USER_EMAIL,trim(MANAGERNAME) MANAGERNAME, trim(MANAGER_MAIL) MANAGER_MAIL, trim(STAT_COLLECT_DATE) STAT_COLLECT_DATE from GAPMASTER.VS_STALE_USERS a,VS_AD_USERS b, (select USERNAME,email MANAGER_MAIL from vs_ad_users) c, VS_AD_CONTACTS d where profile in ('END_USER_PRFL','READ_ONLY_PRFL') and upper(a.username)=upper(b.username) and upper(b.managername)=upper(c.username) and a.dbname=d.dbname and a.dbname='IWP1' order by 2,1);
ENDOFSQL


for ea in `cat /home/oracle/test/stale_users.lst`
do

APPLICATION_NAME="$(echo $ea|cut -d : -f 1)"
DBNAME="$(echo $ea|cut -d : -f 2)"
USERNAME="$(echo $ea|cut -d : -f 3)"
ACCOUNT_STATUS="$(echo $ea|cut -d : -f 4)"
LOCK_DATE="$(echo $ea|cut -d : -f 5)"
EXPIRY_DATE="$(echo $ea|cut -d : -f 6)"
LAST_LOGIN="$(echo $ea|cut -d : -f 7)"
USER_EMAIL="$(echo $ea|cut -d : -f 8)"
MANAGERNAME="$(echo $ea|cut -d : -f 9)"
MANAGER_MAIL="$(echo $ea|cut -d : -f 10)"
STAT_COLLECT_DATE="$(echo $ea|cut -d : -f 11)"

echo $APPLICATION_NAME
echo $DBNAME
echo $USERNAME
echo $ACCOUNT_STATUS
echo $LOCK_DATE
echo $EXPIRY_DATE
echo $LAST_LOGIN
echo $USER_EMAIL
echo $MANAGERNAME
echo $MANAGER_MAIL
echo $STAT_COLLECT_DATE

This worked fine for me. Wanted to share you the same so that it would be helpful to anybody who is looking to read table values into variables.


Thanks,
Shiva
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to read table columns into Shell variables?
Author: Bruno Vroman, Belgium
Date: May 18, 2021, 08:23, 37 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Shiva,

thanks for the feedback. A first remark about your statement: not easy to read, and missing "aliases" in front of column names.
I rewrite your statement just adding indentation and "80-column friendly":
SELECT APPLICATION_NAME||':'||DBNAME||':'||USERNAME||':'||

ACCOUNT_STATUS||':'||LOCK_DATE||':'||EXPIRY_DATE||':'||LAST_LOGIN||':'||
USER_EMAIL||':'||MANAGERNAME||':'||MANAGER_MAIL||':'||STAT_COLLECT_DATE
FROM ( SELECT replace(trim(d.APPLICATION),' ','_') APPLICATION_NAME
, trim(a.DBNAME) DBNAME
, trim(a.USERNAME) USERNAME
, trim(ACCOUNT_STATUS) ACCOUNT_STATUS
, trim(to_char(lock_date,'DD-MON-YYYY_HH24_MI_SS')) LOCK_DATE
, trim(to_char(expiry_date,'DD-MON-YYYY_HH24_MI_SS')) EXPIRY_DATE
, trim(to_char(last_login,'DD-MON-YYYY_HH24_MI_SS')) LAST_LOGIN
, trim(b.EMAIL) USER_EMAIL
, trim(MANAGERNAME) MANAGERNAME
, trim(MANAGER_MAIL) MANAGER_MAIL
, trim(STAT_COLLECT_DATE) STAT_COLLECT_DATE
from GAPMASTER.VS_STALE_USERS a
, VS_AD_USERS b
, (select USERNAME, email MANAGER_MAIL
from vs_ad_users) c
, VS_AD_CONTACTS d
where profile in ('END_USER_PRFL','READ_ONLY_PRFL')
and upper(a.username) = upper(b.username)
and upper(b.managername) = upper(c.username)
and a.dbname = d.dbname
and a.dbname = 'IWP1'
order by 2,1
);
This is already easier to read, and we realize that the ORDER BY is not at the right level. Then I make a small effort to use for example uppercase for reserved words or Oracle functions, lowercase for column names, spacing, aliases... (there are a few columns for which I cannot identify the right alias) and I have:
SELECT x.appl || ':' || x.dbn || ':' || x.usrn || ':' ||

x.stat || ':' || x.lockd || ':' || x.expid || ':' ||
x.logd || ':' || x.mail || ':' || x.mgrn || ':' ||
x.mgrm || ':' || c.cold
FROM ( SELECT REPLACE( TRIM( d.application ), ' ', '_' ) appl
, TRIM( a.dbname ) dbn
, TRIM( a.username ) usrn
, TRIM( a.account_status ) stat
, TO_CHAR( a.lock_date, 'DD-MON-YYYY_HH24_MI_SS' ) lockd
, TO_CHAR( a.expiry_date, 'DD-MON-YYYY HH24_MI_SS' ) expid
, TO_CHAR( a.last_login, 'DD-MON-YYYY HH24_MI_SS' ) logd
, ?.user_email mail
, b.manager_name mgrn
, c.manager_mail mgrm
, TRIM( ?.stat_collect_date ) cold
FROM gapmaster.vs_stale_users a
INNER JOIN vs_ad_users b
ON UPPER( a.username ) = UPPER( b.username )
INNER JOIN ( SELECT vau.username, vau.manager_mail
FROM vs_ad_users vau ) c
ON UPPER( b.manager_name ) = UPPER( v.manager_name )
INNER JOIN vs_ad_contacts d
ON a.dbname = d.dbname
WHERE ?.profile IN ( 'END_USER_PRFL', 'READ_ONLY_PRFL' )
AND a.dbname = 'IWP1'
)
ORDER BY 2, 1
;
(final remarks:
 - are the "TRIM" really necessary? Note that SELECT TRIM( a.dbname ) ... WHERE a.dbname = 'IWP1' raises a smile ;-)
 - are the "UPPER" really necessary? Note that joining with UPPER( t1.aaa ) = UPPER( t2.bbb ) might be inefficient; maybe UPPER is not necessary, maybe a constraint on the table can impose that value is in uppercase, maybe you have functional indexes on upper( ... )
)

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to read table columns into Shell variables?
Author: S SPB, India
Date: May 18, 2021, 12:55, 37 days ago
Message: Hi Bruno,

you are correct! We dont need those trims/upper functions there. Actually it was not edited in the actual query part while chasing for this variable redirection from sql to shell script. :-)

Thanks for inputs and your valuable time in looking into this.

Thanks,
Shiva
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here