메뉴 건너뛰기

Korea Oracle User Group

새소식

The Modern Command Line (SQL, PL/SQL)

명품관 2015.11.24 15:11 조회 수 : 619

TECHNOLOGY: Command Line Administration

 

The Modern Command Line

By Jeff Smith

 

SQLcl is a new Java-based command-line interface for Oracle Database.

The command-line batch query tool SQL*Plus has been the de facto interface for working with Oracle Database for 30 years. And although newer graphical user interfaces for Oracle Database have become popular and have evolved, the feature set for SQL*Plus has remained fairly static—until now.

The new take on SQL*Plus, SQLcl, is based on the script engine in Oracle SQL Developer and is attached to a Java-based command-line interface. In addition to delivering a more modern way of working on the command line, SQLcl also introduces new commands and features missing from SQL*Plus itself.

This article shows how to install SQLcl, connect to your database, and take advantage of the five most compelling features in SQLcl. It assumes that you are familiar with SQL*Plus and Oracle SQL Developer.

Getting Started

Download SQLcl from the Oracle SQL Developer product page on the Oracle Technology Network.

SQLcl supports connections via EZConnect, TNS, LDAP, TWO_TASK, and more—and all without an Oracle client installed or configured.

For example, I can connect to my local Oracle Database 12c pluggable database (PDB) by using the following EZConnect syntax:

'sql user@//server:port/service' 


If you have a TNSNAMES.ORA file available, you can also reference connections defined there. To make the file available to SQLcl, set your TNS_ADMIN environment variable.

For the examples in this article, I’ll be using the sample HR schema included with Oracle Database.

Top Five

There are many features in SQLcl that experienced SQL*Plus users will want to explore. This article looks at the five most compelling ones.

Feature 1: An inline editor. In SQL*Plus, when you want to edit the text of the current query or PL/SQL block, you need to launch an external editor with the EDIT command. In the modern world, a command-line interface can allow for basic keyboarding, such as using arrow keys and the Backspace key.

With that in mind, in SQLcl you can now use your arrow keys to move back, up, down, and all around your text directly at the cursor and can also hold down the Backspace key to delete your query beyond the current line of your buffer.

As you arrow up through the text, the current line is marked with an asterisk by the line number. When your edits are complete, you can use the Ctrl+R key sequence to execute the entire text.

Additionally, there are quick navigation keys, such as:

  • Ctrl+W. Go to the top of the buffer.
  • Ctrl+S. Go to the bottom of the buffer.
  • Ctrl+A. Go to the start of a line.
  • Ctrl+E. Go to the end of a line.

These commands are documented in the Help text for the EDIT command. At a SQLcl command prompt, execute HELP EDIT to see the full command instructions.

The default editor for SQLcl is this inline editor, but if you prefer another, you can set SQLcl to use it instead.

Feature 2: History. It’s likely that you will want to rerun a query you have already executed or run a variation of one of your queries. Many SQL*Plus users figured out that they could access previous queries by taking advantage of rlwrap. This wasn’t set up by default, however, and it was available only to Linux and UNIX users. In SQLcl your queries and scripts are recorded and ready for playback by default.

Just as in Oracle SQL Developer, SQLcl stores the previous 100 statements or scripts.

Additionally, your query history is maintained from one session to the next and older queries age out as the history limit of 100 entries is met.

You can access your query history in two ways:

  1. Cycle through the history by using your up and down arrow keys.
  2. Access the full history list with the HISTORY command.

In addition to the HISTORY command, the following commands provide more information and operations:

  • HISTORY USAGE. View the history usage.
  • HISTORY TIME. View the time spent executing each statement.
  • HISTORY CLEAR. Clear the history.

As you use the arrow keys to navigate the history list, SQLcl will paint the text of the query at the command prompt. After you have recalled a statement, you can also edit it by using the arrow keys.

Feature 3: Formatting. In SQL*Plus you write custom code to transform your query output into comma- or tab-delimited format. In SQLcl you can return your query output in the desired format by using the SET SQLFORMAT command.

The formats supported in SQLcl include

  • ANSICONSOLE
  • CSV
  • Delimited
  • Fixed-width
  • HTML
  • INSERT statements
  • JSON
  • SQL*Loader
  • Text
  • XML

Here’s an example that returns a query to JSON format:

SQL>set sqlformat json

SQL>select * from hr.employees fetch first 1 rows only;

{"items":[
{"employee_id":198,"first_name":"Donald","last_
name":"OConnell","email":"DOCONNEL","phone_
number":"650.507.9833","hire_date":"21-JUN-99","job_id":"SH_
CLERK","salary":2600,"manager_id":124,"department_id":50}]}


Note that the FETCH FIRST support in Oracle Database 12c is required for this query.

When you define a format, all query output will be formatted as requested. To disable the formatting, run the set sqlformat command without a format.

Next Steps 

 

 DOWNLOAD the SQLcl command-line tool.”
 

 LEARN more about SQLcl.

Feature 4: Tab completion. Given a contest between more typing or less typing, less typing should be the clear winner. In addition to the inline editor feature and the HISTORY command, SQLcl gives you another opportunity to do a little less typing. If you need to include a table or a column in a query, press the Tab key for assistance.

For example, in the following, after typing “where D,” I press the Tab key.

o55sql-dev-f1

SQLcl automatically completes the text to the lowest common match of available columns in the HR.DEPARTMENTS table.

I type “N” and press the Tab key again.

o55sql-dev-f2

SQLcl automatically completes the typing up to DEPARTMENT_NAME.

Feature 5: New commands. Several new SQLcl features and commands extend what’s available in SQL*Plus. New commands for SQL*Plus users include

  • ALIAS
  • APEX
  • BRIDGE
  • CD
  • CTAS
  • DDL
  • FORMAT
  • HISTORY
  • INFORMATION
  • LOAD
  • NET
  • REPEAT
  • SSHTUNNEL
  • TNSPING

Run HELP <command> to print the full text of each command description, along with examples of how to use it.

For example, for the CTAS command, enter

help CTAS

 

o55sql-dev-f3

and SQLcl returns information about CTAS.


Jeff Smith is a senior principal product manager in Oracle’s Database Development Tools group and responsible for Oracle SQL Developer and Oracle SQL Developer Data Modeler. He specializes in database tools and development environments.

 

출처 : http://www.oracle.com/technetwork/issue-archive/2015/15-sep/o55sql-dev-2692807.html

번호 제목 글쓴이 날짜 조회 수 추천 수
187 MariaDB 10.4.2 Beta Release 명품관 2019.02.01 352585 0
186 Announcing the general availability of Oracle Linux 7.2 명품관 2015.11.30 186730 0
185 MOS Note:136697.1 - New HCHECK.SQL for Oracle Database 12c 명품관 2016.06.01 119022 0
184 MOATS: The Mother of All Tuning Scripts! (by TANEL PODER) 명품관 2016.04.28 83335 0
183 오라클 18c Express Edition(XE) 윈도우 버전 사용 가능 명품관 2019.02.22 64241 0
182 Oracle Database 19c 다운로드 가능 명품관 2019.04.30 59529 0
181 Oracle Magazine Junary/February 2019 명품관 2019.03.12 39906 0
180 Watch featured OTN Virtual Technology Summit Replay Sessions - Nov 30, 2015 명품관 2015.12.01 28163 0
179 Time-out and Thanks by Tom Kyte (Tom Kyte 의 휴식) 명품관 2016.01.08 20396 0
178 MySQL Database Service (in Oracle Cloud Infrastructure) file 명품관 2021.02.04 16543 0
177 오라클 릴리즈 로드맵 [2] file 명품관 2019.05.15 7145 1
176 New! Announcing MySQL Enterprise Transparent Data Encryption 명품관 2016.04.23 6005 0
175 Oracle Cloud Infrastructure and Red Hat Enterprise Linux 명품관 2023.02.01 5603 0
174 오라클 19c에서 desupport 되는 기능들 명품관 2019.02.19 5114 0
173 Oracle Database 19c 매뉴얼 문서 오픈 명품관 2019.02.15 4389 0
172 오라클 클라우드 피닉스, 서울 리전이 마이크로소프트 Azure와 연결 리전으로 추가 명품관 2022.01.28 4342 0
171 오라클 데이터베이스 19c 버전이 리눅스 8.x Update 1+에서 인증 명품관 2020.05.14 4284 0
170 Oracle Database Migration Assistant for Unicode(DMU) 명품관 2016.05.31 4002 0
169 Oracle Database 19c requires OL7, RHEL7 or SLES12 or newer 명품관 2019.02.15 3740 0
168 Oracle Database certification on Microsoft Windows 10 명품관 2016.01.08 3071 0
위로