running myfile.SQL from command line SQLPLUS 12c does not exit.

Hec Ramsey
Hec Ramsey used Ask the Experts™
on
Hiya.  I am running a script saved as L:\MY_SCRIPT.SQL from command line.  

I want the script to execute, then close SQLPLUS.  
I am trying all the suggested code , adding EXIT. QUIT, . (period) to MY_SCRIPT.SQL  and get errors.
Without these statements the script runs successfully but SQLPLUS stays open.

Windows 7
Oracle 12C 1.0.2.0

Below is the command I cam using
C:\ sqlplus MY_SCHEMA/my_password@MY_DATABASE_LINK @L:\MY_SCRIPT.SQL

The script I am running is straight from Oracle documents:
DECLARE
    x   NUMBER := 100;
    BEGIN
        FOR i IN 1..10 LOOP
           IF MOD (i, 2) = 0 THEN    --i is even
             INSERT INTO temp VALUES (i, x, 'i is even');
           ELSE
             INSERT INTO temp VALUES (i, x, 'i is odd');
           END IF;
           x := x + 100;
       END LOOP;
   END;
   .
/



Below is the output including error:

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 1 15:54:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 01 2017 15:50:29 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
 .
   *
ERROR at line 14:
ORA-06550: line 14, column 4:
PLS-00103: Encountered the symbol "." The symbol "." was ignored.


THANKS!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't have an oracle instance to test this with but I believe it will work...

REM assuming that sqlplus exists at C:\ the run the script like this for it to "auto exit"...
REM
REM   echo quit | C:\sqlplus MY_SCHEMA/my_password@MY_DATABASE_LINK @L:\MY_SCRIPT.SQL


DECLARE
    x   NUMBER := 100;
    BEGIN
        FOR i IN 1..10 LOOP
           IF MOD (i, 2) = 0 THEN    --i is even
             INSERT INTO temp VALUES (i, x, 'i is even');
           ELSE
             INSERT INTO temp VALUES (i, x, 'i is odd');
           END IF;
           x := x + 100;
       END LOOP;
   END;
/

Open in new window

Senior Oracle DBA
Commented:
The exit statement that would get you out of SQL*Plus has to go outside the PL/SQL block.

This should be the contents of MY_SCRIPT.SQL
DECLARE
    x   NUMBER := 100;
    BEGIN
        FOR i IN 1..10 LOOP
           IF MOD (i, 2) = 0 THEN    --i is even
             INSERT INTO temp VALUES (i, x, 'i is even');
           ELSE
             INSERT INTO temp VALUES (i, x, 'i is odd');
           END IF;
           x := x + 100;
       END LOOP;
   END;
/
EXIT

Open in new window

That worked without error on my system.
David VanZandtOracle Database Administrator III

Commented:
Hopefully it's apparent that the period character is not valid syntax, as the two responders have omitted it. Just FWIW, an older method would work in shell or bat scripts:

#! /usr/bash
sqlplus user/pass ... <<EOF
@<path>script.sql
EOF
exit
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
bingo all, thansk!!!  used jester method.
johnsoneSenior Oracle DBA

Commented:
While Jester's method would work as it is syntactically correct, it does not gracefully exit from SQL*Plus.  If you were going to be trying to do any type of error checking, it would likely be returning some sort of error code.

Author

Commented:
Ah, used Johnsone method, need error handling.  But all good to know,  THANKS!!
johnsoneSenior Oracle DBA

Commented:
There was no error handling in the original.  The question was how to run from command line and exit appropriately.  Not, fix my code.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial