Solved

Connecting from sqlplus / Unix

Posted on 2013-12-14
7
410 Views
Last Modified: 2014-01-28
Below is a statement from Unix shell scripting.

DUMMY=`sqlplus <<ENDSQL
  ${MMS_ORA_USER}/${MMS_ORA_PASS}
  whenever sqlerror exit failure;
  whenever oserror exit failure;
  SET ECHO ON SERVEROUTPUT ON
  BEGIN ${PROC};
  END;
  /
  EXIT
  ENDSQL`

How can I achieve the same from CMD prompt/sql plus?
0
Comment
Question by:sakthikumar
7 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 250 total points
Comment Utility
Try this:
# 1) You set the variables:
MMS_ORA_USER=SCOTT
MMS_ORA_PASS=tiger

cat - <<!  >PROC.sql
DECLARE cnt Number:=0;
BEGIN
  SELECT COUNT(*) INTO cnt
  FROM USER_OBJECTS;
  DBMS_OUTPUT.PUT_LINE(''Object count is:''||cnt);
END;'
/
!

#2) You execute sql plus:

dummy=`sqlplus  /nolog <<EOSQL
conn ${MMS_ORA_USER}/${MMS_ORA_PASS}
whenever sqlerror exit failure;
SET ECHO ON SERVEROUTPUT ON
@&&PROC
/
EXIT
EOSQL`

Open in new window

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
From command prompt:
sqlplus /nolog
conn SCOTT/tiger
whenever sqlerror exit failure;
SET ECHO ON SERVEROUTPUT ON
@&&PROC
/
EXIT

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>How can I achieve the same from CMD prompt/sql plus?

Windows does not have 'here' scripts.

You cannot really do everything in the same script like you can to in Unix.

It's best to create the .sql file that has all the Oracle SQL commands then a CMD script that calls it.

For example:
q.sql contains:  select 'Hello' from dual;

q.cmd contains: sqlplus user/password @q.sql
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
why wouldn't you be able to do everything in 1 cmd script ?

create a cmd script which creates the sql on the fly and calls it

set oracle_sid=sid
set sqlfile=c:\scripts\test.sql
(
echo.spool test.txt
echo.select 'hello' from dual;
echo.spool off
echo.exit
) > %sqlfile%

sqlplus -L -S "/ as sysdba" @%sqlfile%

Open in new window


long live the penguin !
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
I posted a one script solution above at #39720080
0
 

Author Comment

by:sakthikumar
Comment Utility
Hi all,

I am not able to understand, please tell me clearly.

I will have the command prompt like this

computer name\desktop>

what file type and what content I should create.

Please explain.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
If you go with the two file version I posted it in http:#a39720559

one file q.sql has:
select 'Hello' from dual;

The file called q.bat (or a CMD file) has:
sqlplus user/password @c:\pathTosql\q.sql

Then from your prompt:
c:\myscripts\q.bat
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now