Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: oracle scripts fail need help

    I cannot get my new scripts to run correctly:

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 29 13:57:32 2004

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    Connected.
    Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Dec 29 13:57:39 2004

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    SP2-0734: unknown command beginning "user..." - rest of line ignored.


    #!/usr/bin/ksh
    . /data/oracle/.profile
    . /data/oracle/.kshrc
    ORACLE_SID=SID
    export ORACLE_SID
    REPORT_DIR=/data/oracle/output/${ORACLE_SID}/
    export REPORT_DIR
    cd /data/oracle/scripts/${ORACLE_SID}/

    sqlplus /nolog @create_variables.sql ${REPORT_DIR}

    chmod 700 set_os_date_variables_${ORACLE_SID}.sh

    chmod 600 define_sqlplus_variables_${ORACLE_SID}.sql

    . set_os_date_variables_${ORACLE_SID}.sh

    sqlplus /nolog @master${ORACLE_SID}.sql

    chgrp monit ${REPORT_DIR}r${TODAY}*

    chmod o-rwx ${REPORT_DIR}r${TODAY}*


    ================================================== ==
    connect.sql
    ===========================================

    connect user/password@SID

    ==============================
    create_variables.sql
    ==============================


    @@connect.sql
    set feedback off
    set linesize 200
    set pagesize 0
    set heading off
    set echo off
    set termout off
    set timing off
    set verify off
    set trimspool on
    spool set_os_date_variables_${ORACLE_SID}.sh
    --
    select '#!/usr/bin/ksh' from dual;
    select '# This file created dynamically by create_environmental_variables_scripts.sql' from dual;
    select 'TODAY=' || to_char(sysdate, 'YYYY_MM_DD') from dual;
    select 'export TODAY' from dual;
    select 'YESTERDAY=' || to_char(sysdate - 1, 'YYYY_MM_DD') from dual;
    select 'export YESTERDAY' from dual;
    spool off

    --!chmod 700 set_os_date_variables_${ORACLE_SID}.sh

    spool define_sqlplus_variables_${ORACLE_SID}.sql
    SELECT 'DEFINE TODAY = ' || '''' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '''' FROM DUAL;
    select 'DEFINE ORACLE_SID = ' || UPPER(INSTANCE_NAME) FROM SYS.V_$INSTANCE;
    select 'DEFINE REPORT_DIR = ' || '''' || '&1' || '''' FROM DUAL;
    spool off

    --!chmod 600 define_sqlplus_variables_${ORACLE_SID}.sql
    exit



    Can anyone help me out here? I was able to get these to run on the other instances. Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Your problem is in the @master${ORACLE_SID}.sql script you did not post.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2003
    Posts
    369

    sorry hear it is

    Sorry must have got cut off while pasting here is

    master{SID}.sql
    ========================

    $ cat masterSID.sql
    set serveroutput on
    set pagesize 72
    set linesize 120
    set feedback off
    set termout off
    set trimspool on
    --
    -- get the date to use as part of the report file names
    accept indate char default '010101' prompt 'enter rundate '
    --
    @dbscript1.sql /data/oracle/output/SID/

    =====================================


    I get this part to work after fixing the connect string error but it hangs at SQLPLUS prompt now.

    =============================
    dbscript1.sql
    ==============================

    spool off
    --accept indate char default '010101' prompt 'enter rundate'
    define spoolfile = '&1.r&indate.SID.txt'
    --set serveroutput on
    --set pagesize 72
    --set linesize 120
    --
    var adminoption refcursor;
    begin
    open :adminoption for
    select a.GRANTEE "Role/User", a.GRANTED_ROLE "Role/Privilege", a.ADMIN_OPTION "Admin Option",
    a.DEFAULT_ROLE "Default Role/Table name"
    from sys.DBA_ROLE_PRIVS a
    where a.GRANTEE not in ('DBA','SYS','SYSADM','SYSTEM') and a.ADMIN_OPTION <> 'NO'
    union
    select b.GRANTEE, b.PRIVILEGE, b.GRANTABLE, b.TABLE_NAME from DBA_TAB_PRIVS b
    where b.GRANTEE not in ('DBA','SYS','SYSADM','SYSTEM') and b.GRANTABLE <> 'NO' and b.PRIVILEGE <> 'SELECT';
    end;
    /
    --
    ttitle 'Users/Roles with privilege to grant roles/privileges to others'
    column grantee format a30 wrapped
    column granted_role format a30 wrapped
    column admin_option format a10 wrapped
    column default_role format a30 wrapped
    spool &spoolfile
    print adminoption
    spool off

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With free advice you get what you paid for it...
    1) My head hurts after looking at the "code"
    2) I would have done this in PERL, which would preclude jumping back and forth between the shell & SQL*Plus
    3) In the past rather than "fight" to get SQL*Plus to generate filenames based upon date(s), I simply spooled to a given filename & later renamed it at the OS level.
    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
    May 2003
    Posts
    369

    PERL versus SQLPLUS

    I appreciate your help. I agree if I had the luxury of time I would have also used a series of perl scripts to manage the database files instead of SQLPLUS. However due to time constraints, I inherited a lot of code in SQLPLUS and KSH scripts. I just needed to make a few enhancements. Anyways I think its working now the scripts take a long time to run.

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You may now need to do some performance tuning ... now that you are
    with 9i...
    Check these parameters especially ...

    ALTER SESSION SET optimizer_index_caching = 99 ;
    ALTER SESSION SET optimizer_index_cost_adj = 10 ;

    CURSOR_SHARING=SIMILAR
    HASH_JOIN_ENABLED=TRUE

    You might need to turn back the optimizer features, however; I've had
    no problems with 9.2.0.5

    OPTIMIZER_FEATURES_ENABLE = 8.1.7

    Ensure that you have gathered stats ... for sys & system users ...
    also ... you need to gather system stats now that 9i is more machine
    "aware"...

    HTH
    Gregg

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Using SQL*Plus to create shell scripts is perverse.
    Writing scripts "from scratch" daily is inelegant at best.
    Don't you know how to use arguments to both SQL*Plus & shell scripts to generate different results based upon the arguments?
    Much of what I understood to exist & get generated could be done with a collection of static routines (SQL & shell) by passing in date arguments.
    There is always time to do it over & never enough time to do it right. :-(
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    I agree with anacedent, everything you are trying to do can be done more efficiently and without creating scripts on the fly.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    May 2003
    Posts
    369

    re-write

    I agree and will update these scripts with PERL or KSH/PL-SQL. Actually my goal is eventually to migrate all these SQL/PLSQL scripts to a web front end reporting interface via PL/SQL server pages and XML. Thats my New Year's resolution. Understand its been a while since I did serious coding as not an everyday thing for this techie. Thanks

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    XML? first read this, this and this.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Looks like you've inherited a real horror.

    Although I'm a big fan of Korn shell, I don't agree with scripting that interacts with the database any more than it absolutely needs to. From this point of view, Perl can just make things worse, because it can do much more and be ten times as complicated. Whereas in Korn shell you might move a few files around, call SQL*Loader and then execute a stored procedure via SQL*Plus, a Perl programmer can and will declare a nested array of ref pointers and start building dynamic SQL statements.

    It looks as though the scripts you posted amount to a report. I couldn't actually get my head around how it all works, but if you want a report with a web interface, iSQL*Plus will probably do the job.

Posting Permissions

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