-2

I am trying to substitute a unix variable to a select statement, but I am getting the below error. What did I do wrong?

sqlplus  "/ as sysdba" << EOF
spool /home/oracle/l.log

archive log list;
exit;
EOF

Adest=`cat  /home/oracle/l.log |head -4|tail -1|awk '{print $3}`
sqlplus "/ as sysdba" << EOF
spool /home/oracle/register.lst
select 'alter database register logfile '||''''||$Adest||'/1_'||sequence#||'_915925946.dbf'||'''' from v\$archived_log where applied='NO';
@ /home/oracle/register.lst
exit;
EOF

Error Log:-
SQL> select 'alter database register logfile '||''''||||'/1_'||sequence#||'_915925946.dbf'||'''' from v$archived_log where applied='NO';
select 'alter database register logfile '||''''||||'/1_'||sequence#||'_915925946.dbf'||'''' from v$archived_log where applied='NO'
                                                 *
ERROR at line 1:
ORA-00936: missing expression 


SQL> @ /home/oracle/register.lst
SQL> exit;


cat  /home/oracle/l.log|head -4|tail -1|awk {'print $3'}
/u01/app/oracle/DG1_ARCH
  • 3
    1) cat spool /home/oracle/l.log|head -3|tail -1|awk '{print $3} can be replace by awk 'NR==3 { print $3} ' spool /home/oracle/l.log – Archemar Oct 12 '16 at 13:30
  • 2) have you check $Adest is set ? echo $Adest before calling sql ? – Archemar Oct 12 '16 at 13:31
  • yes i have added new script – rajadba Oct 12 '16 at 13:39
  • This is the value i want to store in this varaible Adest [oracle@hpdba ~]$ cat /home/oracle/l.log|head -4|tail -1|awk {'print $3'} /u01/app/oracle/DG1_ARCH – rajadba Oct 12 '16 at 14:05
  • replace sqlplus "/ as sysdba" by cat, does the value for $Adest is set ? if no, try Adest=$( ... ) instead of backquote. – Archemar Oct 12 '16 at 14:24
0

It is working fine after adding the below

select 'alter database register logfile '''||'$Adest'||'/1_'||sequence#||'_915925946.dbf'||''';' from v\$archived_log where applied='NO';

Thanks for your suggestion and help!!!

0

Thanks for your help below is working fine.

select 'alter database register logfile '''||'$Adest'||'/1_'||sequence#||'_915925946.dbf'||''';' from v\$archived_log where applied='NO';  

SQL> 
alter database register logfile '/u01/app/oracle/DG1_ARCH/1_452_915925946.dbf';
alter database register logfile '/u01/app/oracle/DG1_ARCH/1_453_915925946.dbf';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.