Need help with Oracle and UNIX shell scripts

[Ben_181]

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

[Steven E. Protter]

I'm a little concerned with the version mixing here. Why do I see a connection to a 64 bit 9.2.x database and a 8.1.7 database at the top?

Do the same versions of sqlplus work for both databases that are as different as these two?

Just a thought, but the authentication may have changed from version to version.

We mirgrated from 8.1.7 to 9.2.0.4 but use different ORACLE_HOME directories and therefore different binaries to actually access the data and run sql.

SEP

[Hein van den Heuvel]

So you are connecting an 8.1.7 SQlplus client to a 9i server. You may want to consider to upgrade, but it should not influence your problem.

It seems like a round-about way, going in and out through SQPlus to set up stuff that can all be done from the shell. Please consider that for you own sanity?

Anyway, did this setup ever work? What changed?
If you run the .sql scripts manually, one at a time do they work?
Is that username still valid?

hope this helps some,
Hein.

[Ben_181]

Yes I agree. Running this in SQLPLUS is messy but I inherited these scripts and needed to modify them to run with a new instance of Oracle. They currently work for the other production database instances. I am still debugging. Thanks again.

[Bill Hassell]

This doesn't look correct:

> ORACLE_SID=SID
> export ORACLE_SID

The reason is that ORACLE_SID will contain the 3 characters "SID" and not the contents of a variable called SID. It should probably read:

ORACLE_SID=$SID
export ORACLE_SID

or in ksh or POSIX sh, you can simplify this to:

export ORACLE_SID=$SID