Home » Infrastructure » Unix » oracle_home path set up help (SunOS 5.10)  () 1 Vote
oracle_home path set up help [message #613091] Tue, 29 April 2014 13:05 Go to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Hi,

am completely new to unix scripting.
The following code from unix will call the oracle proc present in particular schema.
#!/bin/ksh
echo "connecting to the target database"
ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4
$ORACLE_HOME/bin sqlplus -s core/sri@xyz<<END;
echo $?
echo "DB connected scuccessfully"
EXEC core.WCC_UPDATE_REC_OMEGA1('NULL','NZLTSTA','NZL', 'N','N','N','N','N','N','N','N','N','N','N','Y');
echo "procedure ran scuccessfully"
commit;
exit;
END



i just brwose through the internet and wrote the above script.
To be honest am no sure about the oracle_home path set up and the above script will just connect to the database and run the proc.
please advice and help to set up the correct oracle_home path.
i have used the below query to find out the oracle_home path

SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries 
WHERE library_name='DBMS_SUMADV_LIB';


/oracle/app/oracle/product/10.2.0.4

the error i got when i run

Quote:

$ $ ksh edit1.ksh
connecting to the target database
edit1.ksh[3]: ^M: not found
edit1.ksh[4]: /oracle/app/oracle/product/10.2.0.4/bin: cannot execute
edit1.ksh[4]: ^M: not foundksh: $: not found
Re: oracle_home path set up help [message #613092 is a reply to message #613091] Tue, 29 April 2014 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ " ^M: not found" I bet you wrote your script in Windows and copy it on Unix in binary mode, copy it in ASCII mode.
2/ "echo $?" this is NOT a SQL*PLus command. Between "<<END" and "END" you are in SQL*Plus abd shell commands does not apply. Use "prompt" instead of "echo".

Re: oracle_home path set up help [message #613094 is a reply to message #613092] Tue, 29 April 2014 14:14 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Hi Michael,

Thanks,

I have modifed it in ASCII mode
#!/bin/ksh
echo "connecting to the target database"
ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4
$ORACLE_HOME/bin sqlplus -s CORE/nike1102@TOPSWCCT<<END;
prompt $?
prompt "DB connected scuccessfully"
EXEC core.WCC_UPDATE_REC_OMEGA1('NULL','NZLTSTA','NZL', 'N','N','N','N','N','N','N','N','N','N','N','Y');
prompt "procedure ran scuccessfully"
commit;
exit;
END



and the same error meassage i got.
$ ksh edit1.ksh
connecting to the target database
edit1.ksh[4]: /oracle/app/oracle/product/10.2.0.4/bin: cannot execute
Re: oracle_home path set up help [message #613095 is a reply to message #613094] Tue, 29 April 2014 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORACLE_HOME/bin/sqlplus
Re: oracle_home path set up help [message #613096 is a reply to message #613095] Tue, 29 April 2014 14:35 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Thanks..i included your inputs but still it shows some different errors this time.
$ ksh edit1.ksh
connecting to the target database
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
$


[Updated on: Tue, 29 April 2014 14:38]

Report message to a moderator

Re: oracle_home path set up help [message #613097 is a reply to message #613096] Tue, 29 April 2014 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4

[Updated on: Tue, 29 April 2014 14:44]

Report message to a moderator

Re: oracle_home path set up help [message #613098 is a reply to message #613097] Tue, 29 April 2014 15:19 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Thanks Michael.

$ ksh edit1.ksh
connecting to the target database
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus


i just had a check on user name ,password and database name by which i used to connect to oracle database via Toad and everything looks fine.
But when i use the same string in unix script showing me the error.

Could you help and advice me.

[Updated on: Tue, 29 April 2014 15:56]

Report message to a moderator

Re: oracle_home path set up help [message #613101 is a reply to message #613098] Tue, 29 April 2014 18:04 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
>i used to connect to oracle database via Toad and everything looks fine.
you need to test using sqlplus since sqlplus is what is used by your script!
Re: oracle_home path set up help [message #613106 is a reply to message #613101] Wed, 30 April 2014 00:01 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Thanks,

I checked with the same user name,password and connect identifier in SQL plus environment and it worked fine.

As mentioned above this is not connecting from the above unix script.

Please guide,Thanks in advance

Re: oracle_home path set up help [message #613107 is a reply to message #613106] Wed, 30 April 2014 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do you have more than one Oracle product installed on your computer? If so, as you set ORACLE_HOME to /oracle/app/oracle/product/10.2.0.4, does this version's TNSNAMES.ORA contain entry for a database you are trying to connect to through your script? If not, add it. Alternatively, consider using TNS_ADMIN environment variable (search the board for more info).
Re: oracle_home path set up help [message #613128 is a reply to message #613107] Wed, 30 April 2014 07:25 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Thanks you..i have configured TNSNAMES.ORA..now its connecting to the database correctly

but getting the error on call procedure
$ ksh edit1.ksh
connecting to the target database

SQL*Plus: Release 10.2.0.4.0 - Production

DB connected scuccessfully
edit1.ksh[6]: syntax error at line 6 : `(' unexpected


exect errror is on the below line

EXEC core.WCC_UPDATE_REC_OMEGA1('NULL','NZLTSTA','NZL', 'N','N','N','N','N','N','N','N','N','N','N','Y');

i used both EXEC/EXECUTE/CALL ..but no luck..

please guide me i may be missing on important points
Re: oracle_home path set up help [message #613129 is a reply to message #613128] Wed, 30 April 2014 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

cat edit1.ksh


(And don't forget code tags.)

Re: oracle_home path set up help [message #613130 is a reply to message #613129] Wed, 30 April 2014 07:35 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
$ cat edit1.ksh
#!/bin/ksh
echo "connecting to the target database"
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4
${ORACLE_HOME}/bin/sqlplus -V core@xyz sri;
echo "DB connected scuccessfully"
call  core.WCC_UPDATE_REC_OMEGA1('NULL','NZLTSTA','NZL', 'N','N','N','N','N','N
,'N','N','N','N','N','Y');
echo "procedure ran scuccessfully"
commit;
exit;
END

[Updated on: Wed, 30 April 2014 07:37]

Report message to a moderator

Re: oracle_home path set up help [message #613131 is a reply to message #613130] Wed, 30 April 2014 07:42 Go to previous messageGo to next message
John Watson
Messages: 4370
Registered: January 2010
Location: Global Village
Senior Member
echo is shell command, not a SQL*Plus command. You have been told this already.
And you have removed the << that will direct inpout to SQL*Plus.
Re: oracle_home path set up help [message #613132 is a reply to message #613130] Wed, 30 April 2014 07:43 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is wrong. You have ECHO again (weren't you told to use PROMPT instead?). What do you need -V for when starting SQL*Plus? Why CALL and not EXEC (CALL won't work anyway)?
Re: oracle_home path set up help [message #613134 is a reply to message #613132] Wed, 30 April 2014 07:58 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
I tried with << and prompt with sqlplus -s it doesn't worked one of my colleague adviced me try with echo and removing<< with sqlplus -v
$ cat edit1.ksh
#!/bin/ksh
echo "connecting to the target database"
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4
${ORACLE_HOME}/bin/sqlplus -S core@topswcct nike1102<<END;
prompt "DB connected scuccessfully"
EXEC core.WCC_UPDATE_REC_OMEGA1('NULL','NZLTSTA','NZL', 'N','N','N','N','N','N
'N','N','N','N','N','Y');
prompt "procedure ran scuccessfully"
commit;
exit;
END


and after the execution
$ ksh edit1.ksh
connecting to the target database

SQL*Plus: Release 10.2.0.4.0 - Production

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Usage 1: sqlplus -H | -V

    -H             Displays the SQL*Plus version and the
                   usage help.
    -V             Displays the SQL*Plus version.

Usage 2: sqlplus [ [<option>] [<logon>] [<start>] ]

  <option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]

    -C <version>   Sets the compatibility of affected commands to the
                   version specified by <version>.  The version has
                   the form "x.y[.z]".  For example, -C 10.2.0
    -L             Attempts to log on just once, instead of
                   reprompting on error.
    -M "<options>" Sets automatic HTML markup of output.  The options
                   have the form:
                   HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
                   [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
    -R <level>     Sets restricted mode to disable SQL*Plus commands
                   that interact with the file system.  The level can
                   be 1, 2 or 3.  The most restrictive is -R 3 which
                   disables all user commands interacting with the
                   file system.
    -S             Sets silent mode which suppresses the display of
                   the SQL*Plus banner, prompts, and echoing of
                   commands.

  <logon> is: (<username>[/<password>][@<connect_identifier>] | /)
              [AS SYSDBA | AS SYSOPER] | /NOLOG

    Specifies the database account username, password and connect
    identifier for the database connection.  Without a connect
    identifier, SQL*Plus connects to the default database.

    The AS SYSDBA and AS SYSOPER options are database administration
    privileges.

    The /NOLOG option starts SQL*Plus without connecting to a
    database.

  <start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]

    Runs the specified SQL*Plus script from a web server (URL) or the
    local file system (filename.ext) with specified parameters that
    will be assigned to substitution variables in the script.

When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run.  The files may
contain SQL*Plus commands.

Refer to the SQL*Plus User's Guide and Reference for more information.
$


the PROMPT"XXXXXX" not getting displayed in result
Re: oracle_home path set up help [message #613137 is a reply to message #613134] Wed, 30 April 2014 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is "nike1102"?

Quote:
the PROMPT"XXXXXX" not getting displayed in result


You must first be able to enter into SQL*Plus; you failed till now.

Re: oracle_home path set up help [message #613139 is a reply to message #613137] Wed, 30 April 2014 08:12 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
sorry,
user name:core;pwd:nike1102;connection string:topswcct
Re: oracle_home path set up help [message #613141 is a reply to message #613139] Wed, 30 April 2014 08:20 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
SQL*Net (@@topswcct) is NOT required to connect to local database

is script invoked on the DB Server itself?
Re: oracle_home path set up help [message #613142 is a reply to message #613139] Wed, 30 April 2014 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So:
<logon> is: (<username>[/<password>][@<connect_identifier>] 

Not what you used.

Re: oracle_home path set up help [message #613145 is a reply to message #613142] Wed, 30 April 2014 08:40 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Hi Black,

the proc is present on the database which is been hosted on unix server from where am running this unix script
yes,its invoked on the same DB server itself

if i wont use @topswcct then
$ ksh edit1.ksh
connecting to the target database
ERROR:
ORA-12162: TNS:net service name is incorrectly specified


SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
$


Hi Michael,
here i used the correct sqlplus syntax
$ ksh edit1.ksh
connecting to the target database
ERROR:
ORA-12162: TNS:net service name is incorrectly specified


SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
$
Re: oracle_home path set up help [message #613147 is a reply to message #613145] Wed, 30 April 2014 08:42 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
adding on it..i have tried another script for TOPSWCCp where the TNSNAMES.ORA has already been set up properly.the results are

$ cat new.ksh
#!/bin/ksh
echo "connecting to the target database"
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4
${ORACLE_HOME}/bin/sqlplus -V aus/nike1102@topswccp<<END;
prompt "DB connected scuccessfully"
exit;
END
$ ksh new.ksh
connecting to the target database

SQL*Plus: Release 10.2.0.4.0 - Production

$

Re: oracle_home path set up help [message #613148 is a reply to message #613147] Wed, 30 April 2014 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
what does
sqlplus -V do (& don't do)?

[oracle@localhost trace]$ sqlplus -V

SQL*Plus: Release 11.2.0.2.0 Production

[oracle@localhost trace]$ 


Re: oracle_home path set up help [message #613149 is a reply to message #613148] Wed, 30 April 2014 09:00 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
As per my understanding..it is an usage option

-V Displays the SQL*Plus version.
and

-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
commands.

Does this mean it got connected to the database with the given credentials..Kindly throw some light on my understanding.

[Updated on: Wed, 30 April 2014 09:02]

Report message to a moderator

Re: oracle_home path set up help [message #613150 is a reply to message #613149] Wed, 30 April 2014 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
so sqlplus works as documented

sqlplus -V #ONLY displays the version banner & exits as demonstrated above
Re: oracle_home path set up help [message #613152 is a reply to message #613150] Wed, 30 April 2014 09:15 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Please tell me why the very next lines are not getting diaplayed,after sqlplus

Still am confusd that its connected or not?..

Please tell me sir..
Re: oracle_home path set up help [message #613153 is a reply to message #613152] Wed, 30 April 2014 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
[oracle@localhost trace]$ cat noise.sql 
PROMPT "MAKE SOME NOISE!"
EXIT
[oracle@localhost trace]$ sqlplus -V scott/tiger @noise

SQL*Plus: Release 11.2.0.2.0 Production

[oracle@localhost trace]$ sqlplus  scott/tiger @noise

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 30 07:11:12 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

"MAKE SOME NOISE!"
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost trace]$ 

Re: oracle_home path set up help [message #613159 is a reply to message #613153] Wed, 30 April 2014 09:45 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
The below message iam getting when i connect with the credentilas in Oracle SQL *plus

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Apr 30 20:02:16 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

but not through this unix script..do i need to change anything on the script?

Please let me know Sir..


Re: oracle_home path set up help [message #613161 is a reply to message #613159] Wed, 30 April 2014 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
>Please let me know Sir..

Please look behind you.
Did you see me standing there?
Since I am NOT standing behind you & only know what you post here, I have no idea what you did, what is in your script, or what you see.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>do i need to change anything on the script?
if the script does not do what you desire to occur, then you need to change it so it does what you want it to do; but I am unclear what you want.

so once again what results from "sqlplus -V" which I have showed your twice already?
Re: oracle_home path set up help [message #613168 is a reply to message #613161] Wed, 30 April 2014 10:07 Go to previous messageGo to next message
rvsri
Messages: 26
Registered: February 2014
Location: Chennai India
Junior Member
Ok..i will follow the guidelines.

Here i am trying to connect to DB from unix script by the given credentials(to be honest the passwords are correct)

Thank you very much for guiding me till this point and expecting your help .

$ cat new.ksh
#!/bin/ksh
echo "connecting to the target database"
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4
${ORACLE_HOME}/bin/sqlplus -V aus/nike1102@topswccp<<END;
prompt "DB connected scuccessfully"
exit;
END
$ ksh new.ksh
connecting to the target database

SQL*Plus: Release 10.2.0.4.0 - Production

$


Re: oracle_home path set up help [message #613169 is a reply to message #613168] Wed, 30 April 2014 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
working as implemented!

so once again what results from "sqlplus -V" which I have showed your twice already?
Re: oracle_home path set up help [message #613170 is a reply to message #613168] Wed, 30 April 2014 10:11 Go to previous message
Michel Cadot
Messages: 58497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As BlackSwan said and showed:

Quote:
sqlplus -V #ONLY displays the version banner & exits as demonstrated above


Previous Topic: how to call stored procedure in shell script
Next Topic: how to check overall utilization in unix?
Goto Forum:
  


Current Time: Wed Jul 23 07:33:56 CDT 2014

Total time taken to generate the page: 0.07205 seconds