[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to retrive  value from sqlplus into unix variable

Posted on 2007-10-10
13
Medium Priority
?
3,872 Views
Last Modified: 2013-12-18
sqlplus $CON_STR <<EOF >> $FeedLog
set heading off;
        select to_char(date_value,'YYYYMMDD'),to_char(date_value,'YYYY'),to_char(date_value,'YY'),to_char(date_value,'Mon'),to_char(date_value,'DD'),to_char(date_value,'MM')

        into    '$BUS_DATE','$BUS_YYYY','$BUS_YY','$BUS_MON' ,'$BUS_DD','$BUS_MM'
         from xxx  ;
exit;
EOF

echo $BUS_MM
echo $BUS_MON
echo $BUS_YYYY
echo $BUS_YY
echo $BUS_DATE
echo $BUS_DD
0
Comment
Question by:sumanth_ora
  • 6
  • 6
13 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 20049687
see the sample below...

Ora_Msg=`sqlplus -s  << _EOF_
${LOGON}@${ORA_SID}
whenever SQLError exit failure;
whenever OSError exit failure;
SET FEED OFF
SET FEEDBACK OFF
SET HEAD OFF
SET TIMING OFF
VARIABLE STATS_ID NUMBER

BEGIN
SELECT STATISTICS_ID INTO :STATS_ID FROM STANDARDDBO.BATCH_RUN_STATISTICS WHERE APPLICATION_CD='IM_REPORTING_LOAD' AND STATUS_CD='WIP';
END;
/

EXEC UPD_FEED_PARAMETERS.AIP_UPDATE_MINTMSTP;
EXEC STANDARDDBO.BATCH_LOG_PKG.AIP_UPDATE_STATS(:STATS_ID,NULL,NULL,SYSTIMESTAMP,SYSTIMESTAMP,'SUCCESS',NULL,USER);

_EOF_
`

Print "Procedure execution successful."
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20049886
In your case it will be something like this...

sqlplus $CON_STR <<EOF >> $FeedLog
set heading off;
VARIABLE BUS_DATE CHAR
VARIABLE BUS_YYYY CHAR
VARIABLE BUS_YY CHAR
VARIABLE BUS_MON CHAR
VARIABLE BUS_DD CHAR
VARIABLE BUS_MM CHAR

BEGIN
 select to_char(date_value,'YYYYMMDD'),to_char(date_value,'YYYY'),to_char(date_value,'YY'),to_char(date_value,'Mon'),to_char(date_value,'DD'),to_char(date_value,'MM')
INTO :BUS_DATE,:BUS_YYYY,:BUS_YY,:BUS_MON,:BUS_DD,:BUS_MM
         from xxx  ;
END;
/


_EOF_
0
 

Author Comment

by:sumanth_ora
ID: 20050274
sqlplus $CON_STR <<EOF >> $FeedLog
set heading off;
dbms_output.enable(20000);
Variable BUS_DATE varchar2(8);
Variable BUS_YYYY varchar2(4);
Variable BUS_YY varchar2(2);
Variable BUS_MON varchar2(3);
Variable BUS_DD varchar2(3);
Variable BUS_MM varchar2(2);

        select to_char(date_value,'YYYYMMDD'),to_char(date_value,'YYYY'),to_char(date_value,'YY'),to_char(date_value,'Mon'),to_char(date_value,'DD'),to_char(date_value,'MM')

        into    :BUS_DATE,:BUS_YYYY,:BUS_YY,:BUS_MON ,:BUS_DD,:BUS_MM
         from EXAMIN_BUSINESS_DATE  where date_type='CurrentDate' AND PROCESS_NAME='Examin';
         DBMS_OUTPUT.PUT_LINE(:BUS_DATE);
         DBMS_OUTPUT.PUT_LINE(:BUS_YYYY);
         DBMS_OUTPUT.PUT_LINE(:BUS_YY);
         DBMS_OUTPUT.PUT_LINE(:BUS_MON);
         DBMS_OUTPUT.PUT_LINE(:BUS_DD);
         DBMS_OUTPUT.PUT_LINE(:BUS_MM);
exit;
EOF

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 10 12:28:29 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL> SP2-0734: unknown command beginning "dbms_outpu..." - rest of line ignored.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3
20070731 2007 07 Jul          31 07

SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
unable to get the current business date for this job. Please Check
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 18

Expert Comment

by:sventhan
ID: 20050302
user print command instead dbms_output...
0
 

Author Comment

by:sumanth_ora
ID: 20050360
I need to  print each one in seperate line
0
 

Author Comment

by:sumanth_ora
ID: 20050387
PRINT :BUS_DATE;
         PRINT :BUS_YYYY;
         PRINT :BUS_YY;
         PRINT :BUS_MON;
         PRINT :BUS_DD;
         PRINT :BUS_MM;





SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 10 13:16:06 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL> SP2-0734: unknown command beginning "dbms_outpu..." - rest of line ignored.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3
20070731 2007 07 Jul          31 07

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
unable to get the current business date for this job. Please Check ...


0
 
LVL 18

Expert Comment

by:sventhan
ID: 20050434
0
 

Author Comment

by:sumanth_ora
ID: 20050537
I can't pass pass unix variable as parameter

Here is thescript



sqlplus $CON_STR <<EOF >> $FeedLog
set heading off;
dbms_output.enable(20000);
Variable BUS_DATE varchar2(8);
Variable BUS_YYYY varchar2(4);
Variable BUS_YY varchar2(2);
Variable BUS_MON varchar2(3);
Variable BUS_DD varchar2(3);
Variable BUS_MM varchar2(2);

        select to_char(date_value,'YYYYMMDD'),to_char(date_value,'YYYY'),to_char(date_value,'YY'),to_char(date_value,'Mon'),to_char(date_value,'DD'),to_char(date_value,'MM')

        into    :BUS_DATE,:BUS_YYYY,:BUS_YY,:BUS_MON ,:BUS_DD,:BUS_MM
         from EXAMIN_BUSINESS_DATE  where date_type='CurrentDate' AND PROCESS_NAME='Examin';
         PRINT BUS_DATE;
         PRINT BUS_YYYY;
         PRINT BUS_YY;
         PRINT BUS_MON;
         PRINT BUS_DD;
         PRINT BUS_MM;
exit;
EOF


I lke to print the values each line to the log file  $FeedLog

So I can read the values from log file into shell variable's

0
 

Author Comment

by:sumanth_ora
ID: 20050548
PRINT :BUS_DATE;
         PRINT: BUS_YYYY;
         PRINT :BUS_YY;
         PRINT :BUS_MON;
         PRINT : BUS_DD;
         PRINT :BUS_MM;
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20050573
I take back my comment using the print comment. Try my last post and it that does not help please let me know.

Thanks,
0
 

Author Comment

by:sumanth_ora
ID: 20050666
I got error while using DBMS_****

sqlplus $CON_STR <<EOF >> $FeedLog
set heading off;
dbms_output.enable(20000);
Variable BUS_DATE varchar2(8);
Variable BUS_YYYY varchar2(4);
Variable BUS_YY varchar2(2);
Variable BUS_MON varchar2(3);
Variable BUS_DD varchar2(3);
Variable BUS_MM varchar2(2);

        select to_char(date_value,'YYYYMMDD'),to_char(date_value,'YYYY'),to_char(date_value,'YY'),to_char(date_value,'Mon'),to_char(date_value,'DD'),to_char(date_value,'MM')

        into    :BUS_DATE,:BUS_YYYY,:BUS_YY,:BUS_MON ,:BUS_DD,:BUS_MM
         from EXAMIN_BUSINESS_DATE  where date_type='CurrentDate' AND PROCESS_NAME='Examin';
         DBMS_OUTPUT.PUT_LINE(:BUS_DATE);
         DBMS_OUTPUT.PUT_LINE(:BUS_YYYY);
         DBMS_OUTPUT.PUT_LINE(:BUS_YY);
         DBMS_OUTPUT.PUT_LINE(:BUS_MON);
         DBMS_OUTPUT.PUT_LINE(:BUS_DD);
         DBMS_OUTPUT.PUT_LINE(:BUS_MM);
exit;
EOF

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 10 12:28:29 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL> SP2-0734: unknown command beginning "dbms_outpu..." - rest of line ignored.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3
20070731 2007 07 Jul          31 07

SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
unable to get the current business date for this job. Please Check
back to top
0
 
LVL 18

Accepted Solution

by:
rbrooker earned 2000 total points
ID: 20052081
FN=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<! | grep KEEP | sed 's/KEEP//'
connect / as sysdba
select 'KEEP' ||
    to_char(date_value,'YYYYMMDD') || '|' ||
    to_char(date_value,'YYYY') || '|' ||
    to_char(date_value,'YY') || '|' ||
    to_char(date_value,'Mon') || '|' ||
    to_char(date_value,'DD') || '|' ||
    to_char(date_value,'MM')
from xxx;
exit;
!`
BUS_MM=`echo ${FN} | awk -F"|" '{print $1}'`
BUS_MON=`echo ${FN} | awk -F"|" '{print $2}'`
BUS_YYYY=`echo ${FN} | awk -F"|" '{print $3}'`
BUS_YY=`echo ${FN} | awk -F"|" '{print $4}'`
BUS_DATE=`echo ${FN} | awk -F"|" '{print $5}'`
BUS_DD=`echo ${FN} | awk -F"|" '{print $6}'`

what this does is select all the dates into a string seperated with the pipe character
01/01/2006|01/01/2007|01/01/2008|etc...
this is returned into the OS variable FN
each of the six variables that you require are then extracted from this string using awk to find values between the pipe characters.

good luck :)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

873 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