Results 1 to 11 of 11

Thread: Email script

  1. #1
    Join Date
    Nov 2005
    Posts
    11

    Unanswered: Email script

    Good Morning

    I have made a shell script that runs a sql script and emails me every morning. Thats the proposed idea. If I run this command sqlplus 'user/password as sysdba' @dbspace.sql works with no issue when I run it inside the shell script I get

    Code:
    ERROR at line 1:
    ORA-01034: ORACLE not available
    
    
    Enter value for tdate: SELECT	a.tablespace_name
    *
    ERROR at line 1:
    ORA-01034: ORACLE not available
    
    
    Elapsed: 00:00:00.01
    select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER
    *
    ERROR at line 1:
    ORA-01034: ORACLE not available
    
    
    Elapsed: 00:00:00.00
    SQL> Disconnected
    Anyone have any thoughts about where ive gone wrong ive done this in the past with no issues.

    Thanks for any help you can provide.
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    First make work, then make it fancy.

    If you can't get "SELECT COUNT(*) FROM USER_OBJECTS" to succeed, you are wasting your time.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2005
    Posts
    11
    @anacedent where do you see that command. Like I said when I run this command sqlplus 'user/password as sysdba' @dbspace.sql it works with no issue.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Like I said when I run this command sqlplus 'user/password as sysdba' @dbspace.sql it works with no issue.

    How do you reconcile the statement above with posted errors?
    I believe the errors better reflect reality.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2005
    Posts
    11
    @anacedent

    thats what that the question is. Why does it work one way (see attached picture) and not from the script.
    Attached Thumbnails Attached Thumbnails picture1.jpg  

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it works for me.

    Code:
    bcm@bcm-laptop:~$ cat sample.sh
    TABLE=v\$instance
    sqlplus -S dbadmin/admindb   << EOF
    set serveroutput on size 100000
    set heading off
    set pagesize 0
    set feedback off
    spool instance.txt
    select 'ORACLE_SID: ' || instance_name from $TABLE; 
    spool off
    exit
    EOF
    cat instance.txt
    bcm@bcm-laptop:~$ sh -x sample.sh
    + TABLE='v$instance'
    + sqlplus -S dbadmin/admindb
    ORACLE_SID: v112
    + cat instance.txt
    ORACLE_SID: v112                                                                
    bcm@bcm-laptop:~$
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2005
    Posts
    11
    you didnt call another file you just pasted the contents in the script? was that the only change?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    unwilling or incapable of running your own tests?
    Code:
    bcm@bcm-laptop:~$ cat sample.sh
    TABLE=v\$instance
    cat now.sql
    sqlplus -S dbadmin/admindb   << EOF
    set serveroutput on size 100000
    set heading off
    set pagesize 0
    set feedback off
    spool instance.txt
    @now.sql
    spool off
    exit
    EOF
    cat instance.txt
    bcm@bcm-laptop:~$ sh -x sample.sh
    + TABLE='v$instance'
    + cat now.sql
    select sysdate from dual ;
    + sqlplus -S dbadmin/admindb
    2012-04-30 10:45:21
    + cat instance.txt
    2012-04-30 10:45:21                                                             
    bcm@bcm-laptop:~$
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Nov 2005
    Posts
    11
    Really man? thanks for your help.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you do not require OS command line variable substitution,
    then you should avoid using "here script" ( <<EOF) all together.
    Just make static shell script invoking static SQL files.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you are running a cron job make sure that you define the varaibles ORACLE_HOME and ORACLE_SID in your script.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •