We help IT Professionals succeed at work.

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

Hec Ramsey
Hec Ramsey asked
on
247 Views
Last Modified: 2017-04-07
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

This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
David VanZandtSr Software Engineer
CERTIFIED EXPERT

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

Author

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

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

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.