How to run SQL statement/commands from a file

book

Article ID: 160405

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

I've got an SQL statement or a series of statements that I want to run more than once.  Is there a way to put them in a file and then run them from inside sqlplus?

Resolution

You can put the SQL statements in text file and then run them on demand. 

Put the statement, statements or script in a text file and use the extension ".sql". 

From the OS command prompt, navigate to the directory where the filename.sql file is stored.

Log into SQLPlus from the command line as the oracle user you wish to execute the statement.

At the SQL> prompt, type the "@" symbol followed by the filename

example:

SQL> @oracle_create_user.sql

The statements in the file will be run as if they had been typed in at the prompt directly.

If you already logged into SQLPlus and wish to run an existing .sql file in a different directory, simply supply the directory path ending with the file:

example:

SQL> @\home\ralph\diseased\scripts\show_lampshade_count.sql