13 Replies Latest reply on Apr 12, 2013 2:38 PM by 792422

    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..
        • 1. Re: Error in shell script
          Fran
          just change "from V\$RMAN_BACKUP_JOB_DETAILS" for "from V$RMAN_BACKUP_JOB_DETAILS"
          • 2. Re: Error in shell script
            You are aware the END is missing before the last backtick `? So your 'Here-document' never ends.

            ----------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: 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"

              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
              • 4. Re: Error in shell script
                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
                • 5. Re: Error in shell script
                  Jaroslaw Bicz
                  why <<END not <<EOF ?
                  • 6. Re: Error in shell script
                    792422
                    But END is not part of the query..
                    The error is not letting the sql run in the 1st phase itself..
                    • 7. Re: 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');
                      `
                      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
                      • 8. Re: Error in shell script
                        Fran
                        Please check:
                        http://tuxiom.com/forum/index.php?topic=20.0
                        • 9. Re: Error in shell script
                          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
                          • 10. Re: Error in shell script
                            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.
                            • 11. Re: Error in shell script
                              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
                              • 12. Re: Error in shell script
                                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"
                                • 13. Re: Error in shell script
                                  792422
                                  Thanks man.. it worked..