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

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!!!
Hec RamseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
johnsoneSenior Oracle DBACommented:
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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorCommented:
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
0
Determine the Perfect Price for Your IT Services

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

Hec RamseyAuthor Commented:
bingo all, thansk!!!  used jester method.
0
johnsoneSenior Oracle DBACommented:
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.
0
Hec RamseyAuthor Commented:
Ah, used Johnsone method, need error handling.  But all good to know,  THANKS!!
0
johnsoneSenior Oracle DBACommented:
There was no error handling in the original.  The question was how to run from command line and exit appropriately.  Not, fix my code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.