Avatar of Hec Ramsey
Hec RamseyFlag for United States of America

asked on 

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!!!
* PL/SQLOracle Database

Avatar of undefined
Last Comment
johnsone
SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Avatar of Hec Ramsey
Hec Ramsey
Flag of United States of America image

ASKER

bingo all, thansk!!!  used jester method.
Avatar of johnsone
johnsone
Flag of United States of America image

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.
Avatar of Hec Ramsey
Hec Ramsey
Flag of United States of America image

ASKER

Ah, used Johnsone method, need error handling.  But all good to know,  THANKS!!
Avatar of johnsone
johnsone
Flag of United States of America image

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

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo