| oracle_home path set up help [message #613091] |
Tue, 29 April 2014 13:05  |
 |
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 #613094 is a reply to message #613092] |
Tue, 29 April 2014 14:14  |
|
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 #613096 is a reply to message #613095] |
Tue, 29 April 2014 14:35 |
|
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 #613098 is a reply to message #613097] |
Tue, 29 April 2014 15:19 |
|
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 #613128 is a reply to message #613107] |
Wed, 30 April 2014 07:25 |
|
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 #613130 is a reply to message #613129] |
Wed, 30 April 2014 07:35 |
|
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 #613134 is a reply to message #613132] |
Wed, 30 April 2014 07:58 |
|
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 #613149 is a reply to message #613148] |
Wed, 30 April 2014 09:00 |
|
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
