Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error in shell script

792422Apr 12 2013 — edited Apr 12 2013
isitpasrd03{icfgtst2}/apps/opt/oracle> cat text.sh
#!/bin/ksh
VALUE1=`/apps/opt/oracle/product/10.2.0/bin/sqlplus -silent / as sysdba <<END
set pagesize 0 feedback off verify off heading off echo off
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY for a10
col OUTPUT_BYTES_DISPLAY for a10
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V\$RMAN_BACKUP_JOB_DETAILS where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY');
`

echo " $VALUE1"


isitpasrd03{icfgtst2}/apps/opt/oracle> sh text.sh
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY')
*
ERROR at line 1:
ORA-00942: table or view does not exist


I do not understand where the error is.. can anyone help me on this pls..
This post has been answered by DK2010 on Apr 12 2013
Jump to Answer

Comments

Fran
just change "from V\$RMAN_BACKUP_JOB_DETAILS" for "from V$RMAN_BACKUP_JOB_DETAILS"
unknown-698157
You are aware the END is missing before the last backtick `? So your 'Here-document' never ends.

----------
Sybrand Bakker
Senior Oracle DBA
792422
isitpasrd03{icfgtst2}/apps/opt/oracle> cat text.sh
#!/bin/ksh
VALUE1=`/apps/opt/oracle/product/10.2.0/bin/sqlplus -silent / as sysdba <<END
set pagesize 0 feedback off verify off heading off echo off
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY for a10
col OUTPUT_BYTES_DISPLAY for a10
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V$RMAN_BACKUP_JOB_DETAILS where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY');
`

echo " $VALUE1"

SAME IS THE ERROR

isitpasrd03{icfgtst2}/apps/opt/oracle> sh text.sh
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY')
*
ERROR at line 1:
ORA-00942: table or view does not exist
Kh$n
shell considering $ is reserved word so try escape character v$RMAN_BACKUP_JOB*

have a look into below link

http://one-size-doesnt-fit-all.blogspot.com/2007/12/ora-04044-procedure-function-package-or.html

Edited by: Kh$n on Apr 12, 2013 4:37 AM
Jaroslaw Bicz
why <<END not <<EOF ?
792422
But END is not part of the query..
The error is not letting the sql run in the 1st phase itself..
792422
isitpasrd03{icfgtst2}/apps/opt/oracle> cat text.sh
#!/bin/ksh
VALUE1=`/apps/opt/oracle/product/10.2.0/bin/sqlplus -silent / as sysdba <<END
set pagesize 0 feedback off verify off heading off echo off
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY for a10
col OUTPUT_BYTES_DISPLAY for a10
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V\$RMAN_BACKUP_JOB_DETAILS where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY');
`
END
echo " $VALUE1"

ITS THE SAME ERROR

isitpasrd03{icfgtst2}/apps/opt/oracle> sh text.sh
text.sh: END: not found
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY')
*
ERROR at line 1:
ORA-00942: table or view does not exist
Fran
Please check:
http://tuxiom.com/forum/index.php?topic=20.0
Jaroslaw Bicz
sqlplus -s / as sysdba <<EOF
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V\$RMAN_BACKUP_JOB_DETAILS where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY');
EOF works for me
792422
Thanks for your response, but I want the output redirected to a variable that is the reason why I am using "`".

So I could not use EOF.. I want the query to be run within `QUERY` so that I can redirect output to a variable.
unknown-698157
Sorry to nitpick.

You start, correctly, a Here document by using <<END.
So you need to close the here document, by using END on a separate line on position 1.
Has nothing to do with sqlplus but with proper shell scripting.

---------
Sybrand Bakker
Senior Oracle DBA
DK2010
Answer
Use this code:
#!/bin/ksh
VALUE1=`/apps/opt/oracle/product/10.2.0/bin/sqlplus -silent / as sysdba <<END
set pagesize 0 feedback off verify off heading off echo off
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY for a10
col OUTPUT_BYTES_DISPLAY for a10
select start_time,end_time,output_device_type,input_type,status,time_taken_display,input_bytes_display,output_bytes_display from V\\$RMAN_BACKUP_JOB_DETAILS where to_date(Start_time, 'DD-MON-YY')=to_date(sysdate,'DD-MON-YY');
END`

echo " $VALUE1"
Marked as Answer by 792422 · Sep 27 2020
792422
Thanks man.. it worked..
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 10 2013
Added on Apr 12 2013
#general-database-discussions
13 comments
1,192 views