Sqlplus command from linux

Записная книжка ежа

Шпаргалка по SQL*Plus

SQL*Plus — программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.

Основное преимущество SLQ*Plus — доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.

К написанию этой статьи меня подтолкнула книга «Oracle SQL*Plus: The Definitive Guide, 2nd Edition», написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.

При этом сразу считаю нужным оговориться, что я использую SQLplus для написания и выполнения скриптов на удаленных машинах, в этой статье описываю именно используемые для этого команды.

Область возможного использования SQLplus при этом гораздо шире, например — построение отчетов, в том числе в формате HTML.

Параметры, подключение к базе, запуск скриптов

Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:

Выполнение SQL запросов

Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.

Запрос может быть выполнен тремя способами:

• точка с запятой в конце запроса

• строка с слешем «/» после запроса

• пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)

Выполнение PL/SQL блоков

Пример PL/SQL блока:

Правила выполнения PL/SQL блоков:

• Первое слово в PL/SQL блоке должно быть из списка: BEGIN, DECLARE, CREATE PROCEDURE,
  CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE TYPE, CREATE TYPE BODY. Регистр не важен.
• Блок может состоять из нескольких строк
• Можно вставлять /* комментарии */, они также могут быть на несколько строк
• Пустые строки не разрешены внутри блока

Сигнал к выполнению блока может быть подан двумя путями:

• Строка, содержащая только слеш «/» после блока — выполнить сразу

• Строка, содержащая точку «.» после блока — поместить в буфер. Содержимое буфера может быть выведено командой LIST и выполнено, путем указания одиночного слеша «/» или команды RUN.

Одиночное выражение PL/SQL может быть выполнено, используя:

В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:

Работа с переменными

Переменные могут быть заданы двумя способами:

Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.

Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus — будет использовано старое значение переменной. Чтобы этого избежать — можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:

ACCEPT можно использовать для валидации:

Для ввода дат в определенном формате:

SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.

Несмотря на это, можно использовать NEW_VALUE, чтобы задать числовую переменную, полученную как результат запроса.

Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:

Присвоить bind-переменной значение &-переменной:

Вывести значение bind-переменной:

Присвоить &-переменной значение bind-переменной:

Получаем OUT-параметр процедуры в bind-переменную:

Условное выполнение в SQLplus:

Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:

Настройки выполнения скриптов

Действуют на протяжении сессии в SQLplus.

Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:

Другие команды

Взаимодействие с unix shell

Обработать результат выполнения SQLplus-скрипта в Unix:

Вернуть код ответа в Unix:

Вывод из скрипты в переменную командной строки unix:

Источник

A Using SQL Command Line

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.

This section contains the following topics:

For information about running SQL language statements, see Chapter 3, «Using SQL».

SQL*Plus User’s Guide and Reference for complete information about SQL*Plus

Oracle Database SQL Reference for information about using SQL statements

Oracle Database Express Edition 2 Day DBA for information about connecting to Oracle Database XE with SQL Command Line

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.

Using SQL Command Line

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

This contains the following topics:

Before starting SQL Command Line, make sure that the necessary environmental variables have been set up properly. See Oracle Database Express Edition 2 Day DBA for information about setting environmental variables for SQL Command Line.

Starting and Exiting SQL Command Line

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

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 @ host_computer_name

After you have started SQL Command Line, the SQL> prompt displays as follows:

At the SQL> prompt, you can enter SQL statements.

When you want to exit SQL Command Line, enter EXIT at the SQL prompt, as follows:

Displaying Help With SQL Command Line

To display a list of Help topics for SQL Command Line, enter HELP INDEX at the SQL prompt as follows:

From the list of SQL Command Line Help topics, you can display Help about an individual topic by entering HELP with a topic name. For example, the following displays Help about the SQL Command Line COLUMN command, which enables you to format column output:

SQL> HELP COLUMN

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:

SQL> SELECT * FROM employees;

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:

SQL> SELECT employee_id, first_name, last_name
2 FROM employees
3 WHERE employee_id >= 105 AND employee_id

The output from the previous SELECT statement is similar to:

EMPLOYEE_ID FIRST_NAME LAST_NAME
———— ——————— ————————
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
6 rows selected.

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

SQL Command Line DESCRIBE Command

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

SQL> DESCRIBE employees
Name Null? Type
—————————————- ——— ————
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL Command Line SET Commands

The SQL Command Line SET commands can be used to specify various SQL Command Line settings, such as the format of the output from SQL SELECT statements. For example, the following SET commands specify the number of lines for each page and the number of characters for each line in the output:

SQL> SET PAGESIZE 200
SQL> SET LINESIZE 140

To enable output from PL/SQL blocks with DBMS_OUTPUT.PUT_LIN E, use the following:

SQL> SET SERVEROUTPUT ON

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

For information about the SQL Command Line SERVEROUTPUT setting to display output from a PL/SQL program, see «Inputting and Outputting Data with PL/SQL».

SQL*Plus User’s Guide and Reference for information about setting up the SQL Command Line environment with a login file

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 .sql extension for the script file name.

A SQL script file is executed with a START or @ command. For example, in a Windows environment, you can execute a SQL script as follows:

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

SQL> START /home/cjones/my_scripts/my_sql_script.sql

You can use SET ECHO ON to cause a script to echo each statement that is executed. You can use SET TERMOUT OFF 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 SQLPATH environment variable.

Oracle Database Express Edition 2 Day DBA for information about setting environment variables for Oracle Database Express Edition

SQL*Plus User’s Guide and Reference for information about setting the SQL Command Line SQLPATH environment variable to specify the default location of SQL scripts

Spooling From SQL Command Line

The SPOOL 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 SPOOL command followed by a file name. For example:

SQL> SPOOL my_log_file.log

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

SQL> SPOOL my_log_file.log APPEND

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

Using Variables With SQL Command Line

You can create queries that use variables to make SELECT statements more flexible. You can define the variable before running a SQL statement, or you specify that the statement prompts for a variable value at the time that the SQL statement is run.

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 «Using Bind Variables With PL/SQL».

Prompting for a Variable Value in a Query

You can use & to identify a variable that you want to define dynamically. In Example A-1, including the &employee_id variable causes the SQL statement to prompt for a value when the statement is executed. You can then enter a value for the employee_id 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_variable .

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

When you run the previous SELECT statement, the output is similar to:

Enter value for employee_id: 125
.
EMPLOYEE_ID LAST_NAME JOB_ID
———— ————————- ———-
125 Nayer ST_CLERK

Reusing a Variable Value in a Query

You can use && to identify a variable that you want to define dynamically multiple times, but only want to prompt the user once. In Example A-2, including the &&column_name 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 &&column_name in the SQL statement.

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

Defining a Variable Value for a Query

In Example A-3, the &job_id variable is defined before running the SQL statement with the DEFINE command, and the defined value is substituted for the variable when the statement is executed. Because the variable has already been defined, you are not prompted to enter a value.

Example A-3 Defining a Variable for a Query in SQL Command Line

Источник

Accessing the Oracle Database from Linux

This Note describes how to run Oracle client applications on the School’s Linux workstations and servers.

For information about the Oracle database management system in the School, and to learn about your Oracle account and schema on it, see

Oracle SQL Applications

The Oracle client applications covered in the Note are —

Using SQL Developer in the Lab

Oracle’s SQL Developer is installed on the School’s Ubuntu Linux Mate Workstations in Teaching Laboratory #3.

Starting SQL Developer

You can start SQL Developer from either the Mate Graphical User Interface menu, or from the Linux terminal.

In the Mate GUI, SQL Developer can be found in the Programming sub-menu in the Applications menu.

Click the left mouse button down over the Mate Menu button at the left of the bottom edge panel to open the Menu panel.

If the panel shows Favourites , click on All applications to see the Applications sub-menus instead.

Move down over Programming and click over SQL Developer .

Alternatively, SQL Developer can be invoked from the command line in a Linux terminal.

The terminal must be associated with a graphical display (ie the X11 DISPLAY variable must be set), like it would be if you are logged in at a workstation, or SQL Developer will give an error message and fail to run.

As SQL Developer runs, a splash screen shows its progress. It should take only a few seconds to start up.

It’s possible to import settings from an earlier version of SQL Developer that you may have run.

During start-up, SQL Developer shows this import panel (even if, as is the case here, there are no previous settings to import).

Click on No to continue.

Oracle can make use of product usage information by tracking the features that you use in SQL Developer .

Untick the option box to turn this off and click on OK .

SQL Developer first shows its start window.

There are four parts to the window –

• A menu bar across the top
• A Connections pane to the top left
• A Reports pane to the bottom left
• The Start Page in the pane to the right

The Start Page has links to videos, tutorials and demonstrations that you can use to learn more about SQL Developer .

Setting up a Database Connection

Click on the + in the Connections pane to create a new connection to the School’s Oracle database server.

A New Database Connection panel opens.

This panel allows you to specify your account user name and password and other details about the Oracle database so that you can connect to it.

In the panel, think of a Connection Name and type it in the field. Any name will do – we’ve put comsc in this example.

Enter your School Oracle user name and password into the Username and Password fields. Leave the Save Password box unticked.

Use the pull-down menu to change the Connection Type to TNS .

Now, we can use the network alias to specify our database server. Click on the pull-down menu and choose CSORA12EDU .

Click on the Test button to see if the settings work. (If they don’t, you have probably made a mistake in your user name or password). Once it’s ok, click on Save to save the settings.

Connecting to the Database

The connection you have configured appears in the Connections pane at the left of the SQL Developer window. Click on the + alongside the name to connect to the database.

You will need to enter your Oracle account password to complete the connection. A panel appears showing your Oracle connection user name. Enter your password in the Password field.

If you have typed your password correctly, an SQL Worksheet tab opens in the main pane to the right.

Note that, once you have connected to the database, you can start a new SQL Worksheet tab at any time by clicking on the SQL icon in the top menu bar (or type Alt-F10 ).

Using the SQL Worksheet in SQL Developer

The simplest use of SQL Developer is to run SQL statements in the SQL Worksheet . Look at the tutorials, demonstrations and help within SQL Developer for other usage.

Type SQL statements in the SQL Worksheet tab. The tab is an editable window where you can type and amend text.

You can type as many statements as you want but remember to erase unwanted earlier text so that it doesn’t get executed again.

To execute the SQL statement, click the Run statement icon above the tab. Alternatively, just type Ctrl-ENTER .

The results of your SQL statement are output in a new Query Result pane as shown here.

There’s also an SQL History pane that logs the commands you have executed.

Exiting SQL Developer

To exit SQL Developer , either close the window using the X at the top right of the window, or select Exit from the File pull-down menu.

If you have statements in the SQL Worksheet tab, you will be given the opportunity to save them to an SQL file that SQL Worksheet can read and run later.

Using SQL*Plus

SQL*Plus is a terminal-based application that can connect to an Oracle database to run SQL statements and view results. It’s a handy appplication to use if a Graphical User Interface is not available. SQL*Plus is installed on the School’s Linux workstations and servers as sqlplus .

Running SQL*Plus

The Linux application program sqlplus is a command that can be invoked in a Linux terminal. The terminal can be a remote SSH login to a Linux system in the School, for example lapis.cs.cf.ac.uk, or the Mate GUI terminal application.

In Teaching Laboratory #3 on the Ubuntu Mate workstations, start a Mate Terminal from the All applications menu System Tools category, or from the Menu System shortcuts Terminal entry, or from the desktop background menu that is obtained by clicking the right mouse button on the background.

In the terminal, invoke SQL*Plus with the sqlplus command. Put your Oracle account user name, followed by an @ and then the Network Alias csora12edu as an argument the command. E.g.

Logging in to Oracle with SQL*Plus

You will be prompted for your School Oracle password. Type your password (which is not echoed) and press the RETURN key.

SQL*Plus connects to the Oracle server and prompts you for SQL commands.

Writing, saving, getting and editing commands

Type your SQL statements after the SQL prompt. Each SQL statement is terminated by a semi-colon.

You can edit the statements in the command buffer by typing the edit command:

This will invoke the simple text editor. If you prefer to use a different editor, you should set the EDITOR environment variable before running sqlplus . E.g.: or or

Remember to write or save the edited changes before closing the editor.

The amended SQL statements will appear in the SQL*Plus terminal window.

Type the command run to execute the new statements.

It is also possible to invoke the text editor on a named file:

You can save the SQL*Plus command buffer to a file and execute the contents of a file. Use save followed by a file name to save the buffer to a file.

Use start or @ followed by the file name to execute the SQL statements from the file.

Terminating sqlplus

When you have finished with SQL*Plus , type the quit command.

Источник