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:
Источник
SQL*Plus Commands
SQL*Plus is a command-line tool that provides access to the Oracle RDBMS. SQL*Plus enables you to:
Enter SQL*Plus commands to configure the SQL*Plus environment
Startup and shutdown an Oracle database
Connect to an Oracle database
Enter and execute SQL commands and PL/SQL blocks
Format and print query results
SQL*Plus is available on several platforms.
The commands shown in Table A-1 are SQL*Plus commands available in the command-line interface. Not all commands or command parameters are shown.
Table A-1 Basic SQL*Plus Commands
Database Operation
SQL*Plus Command
Log in to SQL*Plus
List help topics available in SQL*Plus
Execute host commands
Show SQL*Plus system variables or environment settings
Alter SQL*Plus system variables or environment settings
Start up a database
Connect to a database
Note : Brackets in boldface are part of the syntax and do not imply optionality.
List column definitions for a table, view, or synonym, or specifications for a function or procedure
Edit contents of the SQL buffer or a file
Get a file and load its contents into the SQL buffer
Save contents of the SQL buffer to a file
List contents of the SQL buffer
Delete contents of the SQL buffer
Add new lines following current line in the SQL buffer
Append text to end of current line in the SQL buffer
Find and replace first occurrence of a text string in current line of the SQL buffer
sepchar can be any nonalphanumeric ASCII character such as «/» or «!»
Capture query results in a file and, optionally, send contents of file to default printer
Run SQL*Plus statements stored in a file
ext can be omitted if the filename extension is .sql
Execute commands stored in the SQL buffer
List and execute commands stored in the SQL buffer
Execute a single PL/SQL statement or run a stored procedure
Источник
SQL*Plus Quick Start
This chapter contains single pages of instructions to get you up and running after you have installed SQL*Plus.
Some Resources
Here are some resources you might find useful when working with iSQL*Plus:
iSQL*Plus Quick Start
Do the following steps to start iSQL*Plus:
Connect to the Internet or your intranet, and start your web browser.
Enter your Oracle9 i HTTP Server URL, followed by isqlplus , in your web browser’s Location or Address field. The iSQL*Plus URL looks like this:
If you do not know the Oracle9 i HTTP Server URL, ask your System Administrator.
Press Enter to go to the URL. The iSQL*Plus Login screen is displayed in your web browser.
Enter your Oracle9 i username and password in the Username and Password fields. If you do not know your Oracle9 i username and password, ask your Database Administrator.
Leave the Connection Identifier field blank to connect to the default database. Enter the Oracle Net database alias to connect to a database other than the default. If restricted database access has been configured, the Connection Identifier field will be a dropdown list of available databases you can select.
Click Login to connect to the database. The iSQL*Plus Work screen is displayed in your web browser.
Now you can start entering and executing SQL, PL/SQL and SQL*Plus statements and commands in the Work screen Input area.
SQL*Plus Command-line Quick Start for Windows
Do the following steps to start SQL*Plus and connect to the default database:
Open a Windows command prompt.
At the command-line prompt, enter the SQL*Plus command in the form:
When prompted, enter your Oracle9 i username and password. If you do not know your Oracle9 i username and password, ask your Database Administrator.
SQL*Plus starts and connects to the default database.
Now you can start entering and executing SQL, PL/SQL and SQL*Plus statements and commands at the SQL> prompt.
Do the following to start SQL*Plus and connect to a specified database other than the default:
Start SQL*Plus and connect to the default database.
Use the CONNECT command to specify the Oracle Net database alias (@ connect_identifier) of the database you want to connect to. To do this enter the CONNECT command in the form:
To hide your password, enter the CONNECT command in the form:
You will be prompted to enter your password.
Alternatively, to start SQL*Plus connected to a database other than the default, enter the SQL*Plus command in the form:
To hide your password, enter the SQL*Plus command in the form:
You will be prompted to enter your password.
SQL*Plus Command-line Quick Start for UNIX
Do the following steps to start SQL*Plus and connect to the default database:
Open a UNIX terminal.
At the command-line prompt, enter the SQL*Plus command in the form:
When prompted, enter your Oracle9 i username and password. If you do not know your Oracle9 i username and password, ask your Database Administrator.
SQL*Plus starts and connects to the default database.
Now you can start entering and executing SQL, PL/SQL and SQL*Plus statements and commands at the SQL> prompt.
Do the following to start SQL*Plus and connect to a specified database other than the default:
Start SQL*Plus and connect to the default database.
Use the CONNECT command to specify the Oracle Net database alias (@ connect_identifier) of the database you want to connect to. To do this enter the CONNECT command in the form:
To hide your password, enter the CONNECT command in the form:
You will be prompted to enter your password.
Alternatively, to start SQL*Plus connected to a database other than the default, enter the SQL*Plus command in the form:
To hide your password, enter the SQL*Plus command in the form:
You will be prompted to enter your password.
Источник
ORACLE DBA
Welcome to Oracle Tech Hub
Dear All, As per our user’s requested, now you can follow us by email. You will get email in «Promotions email», please click to confirm.
Pages
Sunday, 27 November 2016
SQLPLUS: Command not found in linux Solution
Hi DBA-Mates, SQLPLUS command not found . We hit this issue when we trying to connect as shown below example sqlplus ‘/as sysdba’ or sqlplus apps/password .
This is very generic and common error , hope everyone we have faced this issue. This very simple.
1. We need to check the sqlplus directory under oracle home.
2. If you don’t know the oracle database ORACLE_HOME, there is a simple way to find out it as:
$ps -ef|grep lsnr It will show us the path of ORACLE_HOME, from where you can find out.
3. Check your ORACLE_HOME is set or not from below command.
$echo $ORACLE_HOME (If it is not set then kindly export it as shown below).
4. Check your ORACLE_SID is set or not, from below command.
5. Check your $PATH , and set it as shown below in work around give.
Work-around, if the sqlplus not connecting.
[oracle@ebs dbhome_1]$ sqlplus ‘/as sysdba’ -bash: sqlplus: command not found [oracle@ebs dbhome_1]$ So, [oracle@ebs dbhome_1]$ pwd /u01/StageR122/oracle/product/11.2.0/dbhome_1
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:
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
