Run a SQL Script File From SQL*Plus

Ok, this is another simple post. It is about SQL*Plus this time , particularly about how to execute a SQL script file from your ORACLE database server command prompt.

We have this simple Hello World SQL code. 

SET serveroutput ON;

DECLARE

   message  varchar2(20):= 'Hello, World!';

BEGIN

   dbms_output.put_line(message);

END;

/

Please pay attention to the first line:

SET serveroutput ON;

and ,also , don't forget the slash '/' at the end of the file. You will not see the desired output on SQL*Plus if you miss any of those ,although the script will work perfectly on a GUI tool like SQL Developer or Toad without them.
The first line is basically to turn output messages on. The slash "/" is mandatory to tell SQL*Plus that this is the end of the file. 

In this example, I used a Solaris 11 test machine with ORACLE 11g database server. I uploaded the SQL script file to my server and gave it the name hello.sql

Here you can see the file in path:
/ora-dev/devdb/home/oradev

Then I navigated to that path before launching SQL*plus.

And here I logged in to SQL*Plus as SYSDBA

Finally, I executed the SQL file by simply typing the file name preceded by '@' symbol



That's all