Error in shell script

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"


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..

DK2010

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"

Fran

just change "from V\$RMAN_BACKUP_JOB_DETAILS" for "from V$RMAN_BACKUP_JOB_DETAILS"

Unknown

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

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

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"

792422

Thanks man.. it worked..