0

I'm currently trying to implement a bash script that runs at the end of the day that runs a simple oracle query. The command works just fine in Oracle but when inside a .sql file it does not run.

I've attempted to put all of the code on one line and adding semicolons.

Contents of batch file (with user/pass altered):

sqlplus username/password@database @set_changed.sql

Contents of set_changed.sql file:

UPDATE ris_web a
SET a.changed = 0
where exists
(
    select modified_date from invn_sbs b
    where b.item_sid = a.item_sid
        and b.modified_date <= sysdate-1
);
COMMIT;
END;
Michael Fleck
  • 11
  • 2
  • 1
    Do you get the same error message when you use EXIT; instead of END; ? – nebulopathy Aug 05 '19 at 23:33
  • What's the purpose of the pipe `|` in your batch file? You are passing the output of the `exit` command, so nothing, to the input of `sqlplus`; is that really what you want? – aschipfl Aug 06 '19 at 08:18
  • @nebulopathy After replacing `END;` with `EXIT;` I'm just seeing the cursor blink after it says "Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production" – Michael Fleck Aug 06 '19 at 17:27
  • @aschipfl It wasn't anything important. I took it out. It was just something I was experimenting with. – Michael Fleck Aug 06 '19 at 17:29

3 Answers3

0

In SQLPlus you should use a / on a separate line to terminate an SQL statement or PL/SQL block instead of using a semicolon, so change your file to

UPDATE ris_web a
SET a.changed = 0
where exists
(
    select modified_date from invn_sbs b
    where b.item_sid = a.item_sid
        and b.modified_date <= sysdate-1
)
/

SHOW ERRORS
/

COMMIT
/

SHOW ERRORS
/

END

You might also want to have a look at this question

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • Hi @bobjarvis. Thank you for your response. When I altered my set_changed.sql file to match your response it just hangs on the command line. I've attempted to experiment with your solution in an attempt to get it working but I'm still not able to find any success. – Michael Fleck Aug 06 '19 at 17:17
0

You have end; command at the end of the script but no BEGIN for it.

Simply replace the END; with /

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

The original syntax was correct. There were uncommitted changes in sqldeveloper that were causing the .sql file to never finish running. Thank you everyone for your help.

Michael Fleck
  • 11
  • 2