Introduction to SQLcl

Posted on August 09, 2023 · 6 mins read

Introduction to SQLcl

Installation and setup of SQLcl on various operating systems.

  1. download from Oracle site
  2. unzip
  3. add to path

Connecting to an Oracle Database on-premises

sql username/password@//hostname:port/service_name
sql username/password@tns_alias

Here are some examples:

sql HR/HR@//localhost:1521/ORCL
sql HR/HR@orcl_tns

Like sqlplus but better UX

try the following:

set echo on
set define off
set timing on 
set time on

Most of sqlplus features exists. But you heve more options to change the prompt:

set sqlprompt "_USER @_CONNECT_IDENTIFIER >"
set time on
09:48:37 sagivba @my_db >

get help

examples:

  • help
  • help connect …

some hotkeys (Hey just like in bash terminal…)

  • Tab: Auto-completes commands, keywords, table names, and more.
  • Ctrl + C: Interrupts the currently running command or script.
  • Ctrl + L: Clears the screen, providing a clean command-line interface.
  • Ctrl + D: Exits SQLcl and closes the current session.
  • Up Arrow|Down Arrow: Scrolls through previously next entered commands.
  • Ctrl + R: Initiates a reverse search in command history. Press repeatedly to cycle through matching commands.
  • Ctrl + E: Moves the cursor to the end of the current line.
  • Ctrl + A: Moves the cursor to the beginning of the current line.
  • Ctrl + U: Clears the current line from the cursor position to the beginning.
  • Ctrl + K: Clears the current line from the cursor position to the end.
  • Ctrl + W: Deletes the word to the left of the cursor.
  • Ctrl + Y: Pastes the last deleted or cut text.
  • Ctrl + H: Shows the command history.
  • Ctrl + P: Moves to the previous line in the command history.
  • Ctrl + N: Moves to the next line in the command history.
  • Ctrl + T: Swaps the current character with the previous one.
  • Ctrl + F: Moves the cursor forward one character.
  • Ctrl + B: Moves the cursor backward one character.
  • Ctrl + Z: Suspends the current SQLcl session. Use the fg command to bring it back to the foreground.
  • Ctrl + S: Stops the output from scrolling, useful for reviewing large outputs.

Formatting and Output:

Format options:

DEFAULT|CSV|HTML|XML|JSON|ANSICONSOLE|INSERT|LOADER|FIXED|DELIMITED

  • DEFAULT - The DEFAULT option clears all previous SQLFORMAT settings and returns to the default output.
  • ANSICONSOLE - The ANSICONSOLE format resizes the columns to the width of the data to save space.
  • INSERT - The INSERT format produces the INSERT statements that could be used to recreate the rows in a table.
  • LOADER - The LOADER format produces pipe delimited output with string values enclosed by double-quotes. The column names are not included in the output. this format is useful for generating SQLLoader control files.
  • DELIMITED <delimiter> <left enclosure> <right enclosure> - The DELIMITED format is a special case, which allows you to manually define the delimiter string, as well as the characters that will be used to enclose string values. The basic format is as follows.

as hint

SELECT /*csv*/ * FROM emp;
SELECT /*html*/ * FROM emp;

as set command

SET SQLFORMAT JSON-FORMATTED
SELECT * FROM emp WHERE deptno = 20;

Spooling example (just like in SQLPLUS)

SET SQLFORMAT csv
SPOOL sales_export.csv
SELECT order_id, customer_name, order_amount, order_date
FROM sales_data;
SPOOL OFF
EXIT;

Scripting and Automation (just like in SQLPLUS)

export data to csv file

Lets save the example above as file:generate_report.sql Here is an example of data export into csv file

sql user/password@mytns_name @generate_report.sql

import data from csv file

SET SQLFORMAT csv

-- Load data from the CSV file into the employee_data table
-- Note: You might need to adjust the column names and delimiter based on your 
--       CSV format
LOAD DATA
INFILE 'employee_import.csv'
APPEND
INTO TABLE employee_data
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name, job_id)

APEX and sqlcl

apex list

This command list all installed APEX applications in a workspace. We can also list applications in a workspace changed on or after a given date

apex export -list -workspaceid 1908816359534887 -changesSince 2022-01-15

apex export

export an application to a single file

apex export -applicationid 1234

Exporting an Application Split into Multiple Files

apex export -applicationid 1234 -split
← Previous Post Next Post →