Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Connecting from sqlplus / Unix

Posted on 2013-12-14
7
Medium Priority
?
465 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 1000 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 78

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 38

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

963 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