Run SQL script from command line

------------- Check database if already exists then drop -------------------

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'db_Product' )

------------- Create Database-------------

CREATE DATABASE db_Product

-------------Switch database -------------

------------ Check table if already exists then drop -------------------

IF EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'tbl_Product' )

------------ Create tbl_Product table ----------------------------------

----------------Insert data into tbl_Product table-------------------------

INSERT INTO tbl_Product VALUES (1, 'Mobile', 5200, 15);

INSERT INTO tbl_Product VALUES (2, 'Laptop', 25452, 25);

INSERT INTO tbl_Product VALUES (3, 'Desktop', 18451, 8);

----------------Count total inseted data --------------------------------

SELECT COUNT(*) AS 'TotalRecords' FROM tbl_Product

This section provides an introduction to SQL Command Line (SQL*Plus), an interactive and batch command-line query tool that is installed with Oracle Database Express Edition.

Overview of SQL Command Line

SQL Command Line (SQL*Plus) is a command-line tool for accessing Oracle Database XE. It enables you to enter and run SQL, PL/SQL, and SQL*Plus commands and statements to:

• Query, insert, and update data

• Execute PL/SQL procedures

• Examine table and object definitions

• Develop and run batch scripts

• Perform database administration

You can use SQL Command Line to generate reports interactively, to generate reports as batch processes, and to write the results to a text file, to a screen, or to an HTML file for browsing on the Internet.

This section describes SQL Command Line (SQL*Plus), a command-line utility to run SQL and PL/SQL.

This contains the following topics:

Note:

Starting and Exiting SQL Command Line

To start SQL Command Line from the operating-system command prompt, enter the following:

sqlplus

When prompted, enter the username and password of the user account (schema) that you want to access in the local database. For example, enter HR for the username and my_hr_password for the password when prompted.

You can also include the username and password when you start SQL Command Line. For example:

sqlplus hr/my_hr_password

If you want to connect to a database running on a remote system, you need to include a connect string when starting SQL Command Line. For example:

sqlplus hr/my_hr_password

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

After you have started SQL Command Line, the

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

At the

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

When you want to exit SQL Command Line, enter

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

Displaying Help With SQL Command Line

To display a list of Help topics for SQL Command Line, enter

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

From the list of SQL Command Line Help topics, you can display Help about an individual topic by entering

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

Entering and Executing SQL Statements and Commands

To enter and execute SQL statements or commands, enter the statement or command at the SQL prompt. At the end of a SQL statement, put a semi-colon (;) and then press the Enter key to execute the statement. For example:

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

If the statement does not fit on one line, enter the first line and press the Enter key. Continue entering lines, and terminate the last line with a semi-colon (;). For example:

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

The output from the previous

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

Note that a terminating semi-colon (;) is optional with SQL Command Line commands, such as sqlplus7 o r sqlplus8, but required with SQL statements.

SQL Command Line DESCRIBE Command

SQL Command Line provides the sqlplus7 command to display a description of a database object. For example, the following displays the structure of the HR0 table. This description is useful when constructing SQL statements that manipulate the HR0 table.

SQL Command Line SET Commands

The SQL Command Line sqlplus8 commands can be used to specify various SQL Command Line settings, such as the format of the output from SQL

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

To enable output from PL/SQL blocks with sqlplus hr/1E, use the following:

sqlplus hr/2

To view all the settings, enter the following at the SQL prompt:

sqlplus hr/3

For information about the SQL Command Line sqlplus hr/4 setting to display output from a PL/SQL program, see .

See Also:

Running Scripts From SQL Command Line

You can use a text editor to create SQL Command Line script files that contain SQL*Plus, SQL, and PL/SQL statements. For consistency, use the sqlplus hr/5 extension for the script file name.

A SQL script file is executed with a sqlplus hr/6 or

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

sqlplus hr/8

A SQL script file can be executed in a Linux environment as follows:

sqlplus hr/9

You can use sqlplus8 my_hr_password1 my_hr_password2 to cause a script to echo each statement that is executed. You can use sqlplus8 my_hr_password4 my_hr_password5 to prevent the script output from displaying on the screen.

When running a script, you need to include the full path name unless the script is located in the directory from which SQL Command Line was started, or the script is located in the default script location specified by the my_hr_password6 environment variable.

See Also:

• for information about setting environment variables for Oracle Database Express Edition

• for information about setting the SQL Command Line my_hr_password6 environment variable to specify the default location of SQL scripts

Spooling From SQL Command Line

The my_hr_password8 command can be used to direct the output from SQL Command Line to a disk file, which enables you to save the output for future review.

To start spooling the output to an operating system file, you enter the my_hr_password8 command followed by a file name. For example:

sqlplus hr/0 sqlplus hr/1

If you want to append the output to an existing file:

sqlplus hr/0 sqlplus hr/1 sqlplus hr/4

To stop spooling and close a file, enter the following:

sqlplus hr/5

Using Variables With SQL Command Line

You can create queries that use variables to make

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

When using a variable in a SQL statement, the variable name must be begin with an ampersand (&).

This section contains the following topics:

For information about using bind variables in PL/SQL code, see .

Prompting for a Variable Value in a Query

You can use sqlplus hr/7 to identify a variable that you want to define dynamically. In , including the sqlplus hr/8 variable causes the SQL statement to prompt for a value when the statement is executed. You can then enter a value for the sqlplus hr/9 that corresponds to the employee information that you want to display, such as employee ID 125. Note that you can use any name for the variable, such as my_hr_password0.

Example A-1 Prompting for a Variable Value in SQL Command Line

-- prompt for employee_id in a query, you need to enter a valid ID such as 125
SELECT employee_id, last_name, job_id FROM employees 
  WHERE employee_id = &employee_id;

When you run the previous

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id, last_name FROM employees WHERE job_id = '&job_id';

Reusing a Variable Value in a Query

You can use my_hr_password7 to identify a variable that you want to define dynamically multiple times, but only want to prompt the user once. In , including the my_hr_password8 variable causes the SQL statement to prompt for a value when the statement is executed. The value that is entered is substituted for all remaining occurrences of my_hr_password8 in the SQL statement.

Example A-2 Reusing a Variable Value in SQL Command Line

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

Defining a Variable Value for a Query

In , the

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

-- prompt for a column name, such as job_id, which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

How to run SQL script from cmd?

How to run MySQL script from command line?

How do I run a .SQL script?

How to run SQL script from command line Linux?