Solved

Connecting from sqlplus / Unix

Posted on 2013-12-14
7
415 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
ID: 39720080
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
ID: 39720083
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)
ID: 39720559
>>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 37

Expert Comment

by:Geert Gruwez
ID: 39724129
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
ID: 39724158
I posted a one script solution above at #39720080
0
 

Author Comment

by:sakthikumar
ID: 39800623
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
ID: 39800675
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle rollup query 3 51
Migrate Oracle Database from ASM to Non-ASM on a Windows server. 1 35
SQL query question 8 42
Can't Access My Database 57 35
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

19 Experts available now in Live!

Get 1:1 Help Now