asked on

Connecting from sqlplus / Unix

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?

SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeOM_DBA

From command prompt:

sqlplus /nolog
conn SCOTT/tiger
whenever sqlerror exit failure;
SET ECHO ON SERVEROUTPUT ON
@&&PROC
/
EXIT

Open in new window

slightwv (䄆 Netminder)

>>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

Geert G

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 !

MikeOM_DBA

I posted a one script solution above at #39720080

sakthikumar

ASKER

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial