메뉴 건너뛰기

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

번호 제목 글쓴이 날짜 조회 수 추천 수
127 MariaDB Python Connector 1.1.6 now available 명품관 2023.03.09 174 0
126 What's new in Oracle Help Center Learn: February 2023 명품관 2023.03.09 272 0
125 What’s in it for you? Attend Free MySQL Summit on March 23rd 명품관 2023.03.09 135 0
124 Exadata System Software Updates - March 2023 명품관 2023.03.09 192 0
123 Exadata System Software Release 23.1 명품관 2023.03.08 209 0
122 MariaDB Community Server 11.0 now RC 명품관 2023.02.27 140 0
121 MariaDB 10.11.2 GA now available 명품관 2023.02.27 235 0
120 RHEL runs on OCI supported by Oracle and Red Hat 명품관 2023.02.02 119 0
119 Oracle Cloud Infrastructure and Red Hat Enterprise Linux 명품관 2023.02.01 5603 0
118 오라클 클라우드 피닉스, 서울 리전이 마이크로소프트 Azure와 연결 리전으로 추가 명품관 2022.01.28 4340 0
117 Microsoft Azure와 Oracle 클라우드 인프라를 통합하는 Oracle 애플리케이션 솔루션 명품관 2022.01.28 333 0
116 MariaDB Java Connector 2.7.5 now available 명품관 2022.01.20 395 0
115 Oracle Critical Patch Update for January 2022 명품관 2022.01.20 350 0
114 오라클 OCI 자격 시험 무료 기한을 2022년 2월 28일까지 연장 [2] 명품관 2021.12.28 584 0
113 MariaDB 10.8.0 preview releases now available 명품관 2021.12.27 304 0
112 AWS Builders Online Series - AWS를 빠르게 시작하는 방법(2021년 1월 20일) 명품관 2021.12.23 386 0
111 Oracle Developer Day(2022년 1월 19일(수)) 명품관 2021.12.23 191 0
110 SQLcl 21.4 Downloads available! 명품관 2021.12.23 360 0
109 Oracle REST Data Services 21.4 available ! 명품관 2021.12.23 311 0
108 Log4Shell and MariaDB (CVE-2021-44228) 명품관 2021.12.22 281 0
위로