Forum Stats

  • 3,724,396 Users
  • 2,244,746 Discussions
  • 7,850,991 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Error in shell script

792422
792422 Member Posts: 24
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..

Best Answer

  • DK2010
    DK2010 Member Posts: 1,542 Silver Trophy
    Accepted Answer
    Use this code:
    #!/bin/ksh
    VALUE1=`/apps/opt/oracle/product/10.2.0/bin/sqlplus -silent / as sysdba &lt;&lt;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 &quot; $VALUE1&quot;

Answers

  • Fran
    Fran Member Posts: 3,211
    just change "from V\$RMAN_BACKUP_JOB_DETAILS" for "from V$RMAN_BACKUP_JOB_DETAILS"
  • You are aware the END is missing before the last backtick `? So your 'Here-document' never ends.

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 792422
    792422 Member Posts: 24
    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
    Kh$n Member Posts: 231
    edited April 2013
    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
    Jaroslaw Bicz Member Posts: 55
    why <<END not <<EOF ?
  • 792422
    792422 Member Posts: 24
    But END is not part of the query..
    The error is not letting the sql run in the 1st phase itself..
  • 792422
    792422 Member Posts: 24
    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
    Fran Member Posts: 3,211
    Please check:
    http://tuxiom.com/forum/index.php?topic=20.0
  • Jaroslaw Bicz
    Jaroslaw Bicz Member Posts: 55
    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
    792422 Member Posts: 24
    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.
  • 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
    DK2010 Member Posts: 1,542 Silver Trophy
    Accepted Answer
    Use this code:
    #!/bin/ksh
    VALUE1=`/apps/opt/oracle/product/10.2.0/bin/sqlplus -silent / as sysdba &lt;&lt;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 &quot; $VALUE1&quot;
  • 792422
    792422 Member Posts: 24
    Thanks man.. it worked..
This discussion has been closed.