SQL::Shell::Manual(3)

📦

version 1.18 · 3 versions

1.18 · fedora 43 (latest)
1.17 · fedora 43
1.18 · fedora 44

🎀 SQL::Shell::Manual 💖

hiii bestie, welcome to the user contributed Perl documentation for SQL::Shell::Manual — aka the whole entire user guide for sqlsh. buckle up, it's giving ✨comprehensive✨. 🦋

🌸 NAME 🌸

SQL::Shell::Manual — user guide for sql shell. that's it, that's the vibe. 💅

⭐ SYNOPSIS ⭐

sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger

👑 DESCRIPTION 👑

so this whole thing is a guide to using sqlsh. sqlsh is an interactive shell you run from the command-line to work with databases. 🗄️

but wait, there's more — it can also run in "batch mode", where it takes a list of commands from stdin (that's the -i switch, iykyk), or you can just pass it a single command right there on the command-line. main character energy fr fr. 😌

🔌 Connecting

you've got options, bestie:

set a DSN in the environment as DBI_DSN
supply it with the -d option
or use the connect command

unixbox% sqlsh
unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger

you can also connect from inside sqlsh:

unixbox% sqlsh
> connect DBI:Oracle:IFLDEV scott tiger
DBI:Oracle:IFLDEV> show $dbh Name
+--------+
| Name   |
+--------+
| IFLDEV |
+--------+

and disconnect:

DBI:Oracle:IFLDEV> disconnect
> show $dbh Name
Not connected to database.

if you don't supply a password, sqlsh will prompt you for it:

unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott
Enter password for scott:

and you can specify a blank password by passing -p:

unixbox% sqlsh -d DBI:Oracle:IFLDEV -u guest -p

from within sqlsh you can get a list of DBI drivers:

unixbox% sqlsh
> show drivers

  CSV
  DBM
  ExampleP
  Excel
  File
  Multiplex
  Oracle
  Proxy
  SQLite
  Sponge
  mysql

and a list of possible data sources for a driver:

unixbox% sqlsh
> show datasources Oracle

  dbi:Oracle:GISCPS
  dbi:Oracle:IFL1
  dbi:Oracle:IFLDEV
  dbi:Oracle:IFLTEST

common DBI DSNs include:

DBI:Oracle:<SID>
DBI:mysql:<DB>
DBI:ADO:<DSN>
DBI:Excel:file=<xls>
DBI:CSV:f_dir=<dir>
DBI:SQLite:dbname=<filename>

🧚 Exploring the schema

🌈 `show tables`

this lists the tables in your database, along with other attributes that may be provided by your platform and driver:

DBI:SQLite:dbname=test.db> show tables
+-----------+-------------+--------------------+--------------+---------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME         | TABLE_TYPE   | REMARKS |
+-----------+-------------+--------------------+--------------+---------+
| NULL      | main        | sqlite_master      | SYSTEM TABLE | NULL    |
| NULL      | temp        | sqlite_temp_master | SYSTEM TABLE | NULL    |
| NULL      | main        | commands           | TABLE        | NULL    |
+-----------+-------------+--------------------+--------------+---------+

for some database drivers this may include some system tables. 🙈

🍓 `show tablecounts`

this lists the tables with a rowcount for each one:

DBI:SQLite:dbname=test.db> show tablecounts
+-----------------------------+------+
| table                       | rows |
+-----------------------------+------+
| "main"."sqlite_master"      | 2    |
| "temp"."sqlite_temp_master" | 0    |
| "main"."commands"           | 12   |
+-----------------------------+------+

for some database drivers this may include some system tables. this command does a SELECT COUNT(*) FROM on every single table in your database. 😭

so like… you may not want to do this on databases with large numbers of tables, and/or tables with large numbers of rows. that's not a vibe check you want to fail. 💀

💜 `show catalogs`

if your platform supports it, this shows a listing of available database catalogs:

DBI:ODBC:localdb> show catalogs
+----------------+
| TABLE_CAT      |
+----------------+
| AdventureWorks |
| master         |
| msdb           |
| tempdb         |
+----------------+

✨ `show schemas`

this command will list the schemas available in your database.

⚠️ note that this is different from show schema (singular), which shows table descriptions for every table in your schema (see below, bestie, we get there).

DBI:SQLite:dbname=test.db> show schemas
+-------------+
| TABLE_SCHEM |
+-------------+
| main        |
| temp        |
+-------------+

🦋 `show tabletypes`

list the available table-types in your database:

DBI:SQLite:dbname=test.db> show tabletypes
+-----------------+
| TABLE_TYPE      |
+-----------------+
| LOCAL TEMPORARY |
| SYSTEM TABLE    |
| TABLE           |
| VIEW            |
+-----------------+

🌸 `desc`

lists the columns in a table:

DBI:Oracle:IFLDEV> desc commands
+-------------+----------------+------+
| Field       | Type           | Null |
+-------------+----------------+------+
| COMMAND     | VARCHAR2(200)  | YES  |
| DESCRIPTION | VARCHAR2(1020) | YES  |
+-------------+----------------+------+

👑 `show schema`

lists the columns in a table, for each table in the schema:

DBI:Oracle:IFLDEV> show schema

schema dump
COMMANDS:
+-------------+----------------+------+
| Field       | Type           | Null |
+-------------+----------------+------+
| COMMAND     | VARCHAR2(200)  | YES  |
| DESCRIPTION | VARCHAR2(1020) | YES  |
+-------------+----------------+------+

💅 Current shell settings (`show settings`)

to list some sqlsh internal settings:

DBI:SQLite:dbname=test.db> show settings
+------------------+-------+
| PARAMETER        | VALUE |
+------------------+-------+
| auto-commit      | on    |
| delimiter        | \t    |
| enter-whitespace |       |
| escape           | off   |
| longreadlen      | 512   |
| longtruncok      | on    |
| multiline        | off   |
| verbose          | on    |
| width            | 80    |
+------------------+-------+

note that not all settings are yet included in this output. just so you know. 🙈

🔥 Querying the database

DBI:SQLite:dbname=test.db> select * from commands
+------------------+--------------------------------------------------------------+
| command          | desc                                                         |
+------------------+--------------------------------------------------------------+
| show drivers     | Displays a list of DBI drivers                               |
| show datasources | Displays a list of available data sources for a driver       |
| connect          | Connects to a data source                                    |
| disconnect       | Disconnects from a data source                               |
| show tables      | List the tables in the schema                                |
| show tablecounts | List the tables in the schema with a rowcount for each table |
| show schema      | Lists the columns in each table in the schema                |
| desc             | List the columns in a table                                  |
| set              | Set a parameter                                              |
| help             | Displays sqlsh help in your $PAGER                           |
| reload           | Reloads sqlsh                                                |
| exit             | Quits sqlsh                                                  |
+------------------+--------------------------------------------------------------+

🫶 BLOB values

you can control the amount of BLOB data fetched by setting the longreadlen parameter:

DBI:Oracle:IFLDEV> set longreadlen 4096
LongReadLen set to '4096'

DBI:Oracle:IFLDEV> show $dbh LongReadLen
+-------------+
| LongReadLen |
+-------------+
| 4096        |
+-------------+

note that the longtruncok parameter should also be set (it is by default):

DBI:Oracle:IFLDEV> show $dbh LongTruncOk
+-------------+
| LongTruncOk |
+-------------+
| 1           |
+-------------+

🌈 Values containing non-word characters

suppose we have values in our database which contain whitespace characters (e.g. tabs):

DBI:Oracle:IFLDEV> set enter-whitespace on
Whitespace may be entered as \n, \r and \t

DBI:Oracle:IFLDEV> insert into commands(command,description) values('test', 'one\ttwo')
INSERT commands: 1 rows affected

when we query the table we see these as literal values:

DBI:Oracle:IFLDEV> select * from commands
+---------+-------------+
| COMMAND | DESCRIPTION |
+---------+-------------+
| test    | one two     |
+---------+-------------+

we can instead chose to display them escaped:

DBI:Oracle:IFLDEV> set escape show-whitespace
DBI:Oracle:IFLDEV> select * from commands
+---------+-------------+
| COMMAND | DESCRIPTION |
+---------+-------------+
| test    | one\ttwo    |
+---------+-------------+

alternatively we can use uri-escaping:

DBI:Oracle:IFLDEV> set escape uri-escape on
DBI:Oracle:IFLDEV> select * from commands
+---------+-------------+
| COMMAND | DESCRIPTION |
+---------+-------------+
| test    | one%09two   |
+---------+-------------+

✨ Entering multi-line statements

to enable multiline mode:

DBI:Oracle:IFLDEV> set multiline on

you can then build up statements over multiple lines, ending with a semicolon, e.g.:

DBI:Oracle:IFLDEV> select
DBI:Oracle:IFLDEV> count(*)
DBI:Oracle:IFLDEV> from
DBI:Oracle:IFLDEV> commands
DBI:Oracle:IFLDEV> ;
+----------+
| COUNT(*) |
+----------+
| 12       |
+----------+

to disable multiline mode, remember you need to end the statement in a semicolon:

DBI:Oracle:IFLDEV> set multiline off;

💜 Altering the display mode

the default (box) display mode is similar to that used by the mysql client — it works well for tables of fairly short values. the record display mode is good for viewing single records:

DBI:SQLite:dbname=test.db> set display-mode record
DBI:SQLite:dbname=test.db> select * from commands where command='desc'
--------------------------------------------------------------------------------
command | desc
desc    | List the columns in a table
--------------------------------------------------------------------------------

ok bestie that's record mode doing its lil thing ✨ and honestly every display mode has a whole personality:

The spaced display mode — despite sounding like a description of sqlsh's author 💀 — gives you a minimum clutter view of the data.
The tabbed display mode generally looks horrendous but is useful for a quick cut+paste of delimited values.
The sql display mode generates insert statements using a $table placeholder for where the data is to be inserted.
The xml display mode generates element-only XML which can be parsed into a list of hashes with XML::Simple.

Altering the database 💅

By default transactions are not automatically committed, so you have to explicitly commit them yourself. no auto-save here, bestie:

DBI:Oracle:IFLDEV> insert into commands(command, description) values ('dump','Writes a table or query results to a delimited file')
INSERT commands: 1 rows affected

DBI:Oracle:IFLDEV> commit

and you can roll back mistakes 🫶:

DBI:Oracle:IFLDEV> delete from commands
DELETE commands: 11 rows affected

DBI:Oracle:IFLDEV> rollback
DBI:Oracle:IFLDEV> select count(*) from commands
+----------+
| COUNT(*) |
+----------+
| 12       |
+----------+

If you prefer, you can switch autocommit on:

set autocommit on
insert ...
update ...

This is the preferred mode of operation when connecting to some database platforms like SQL Server. Depending on the platform, not all commands work within a "transaction", and some platforms prefer that they be run with autocommit on. Your mileage may vary. fr fr, it depends on the vibes of your particular platform.

Clearing the database 🧹

The wipe tables command can be used to remove all the data each of the tables in the database:

DBI:Oracle:IFLDEV> wipe tables
Wipe all data from:

  COMMANDS

Are you sure you want to do this? (type 'yes' if you are) yes

Wiped all data in database

It prompts you to confirm before anihilating your database. read that twice. it wipes EVERYTHING. the confirmation prompt is your last chance, bestie. 😭

The `send` and `recv` commands 🦋

These commands were added in v1.16. Their purpose is to give the user more control over how commands are interpreted by the shell before they are sent to the DB, without the need to make the shell identify the commands to determine whether to expect output or not. They are intended to deal with platform-specific variations of SQL syntax that aren't covered by the generic command-matching process in sqlsh.

send is used when you don't expect output from the command.
recv is used for cases where the command provides output, and you'd like it rendered and displayed, as if it had come from a select.

Example 1 🍓

On IBM Netezza, you can query previously deleted records by giving the database the following command:

set show_deleted_records=TRUE

If you try to do that from sqlsh, it will tell you that the command is not recognized, because sqlsh has a built-in set command, and it is trying to match it to what you have typed at the prompt. not the shell being dumb, it's the shell being ✨opinionated✨

The solution is to use the send command to submit the set expression to the DB without having it intercepted by sqlsh:

send set show_deleted_records=TRUE

Example 2 🍓

On SQL Server there are a number of procedure calls that provide output. For instance:

exec xp_cmdshell 'dir *.exe'

or simply

xp_cmdshell 'dir *.exe'

While sqlsh supports the execute command, this is intended to run commands from a local SQL file. In this case, sqlsh would just reply that the command is not recognized. The solution is to use the recv command:

recv exec xp_cmdshell 'dir *.exe'

recv xp_cmdshell 'dir *.exe'

This will make sqlsh submit the exec expression to the DB as if it were a select command, so that any output is rendered and displayed. iconic behavior honestly ✨

Example 3 🍓

Several database platforms allow giving the use command to query a different database. You are in effect switching databases without disconnecting. The command looks as follows:

use MY_DB_NAME

If I give this command while in sqlsh, it will not be recognized, however, I could pass it on to the DB by using send:

send use MY_DB_NAME

In this case we type send instead or recv because we don't expect any output from the use command.

Note that if your DB platform supports the use command, you may also need a command to tell you which database you're currently using. This is platform-dependent, but I will provide an example from SQL Server:

select dbname() as current_database

Dumping delimited data 🎀

dump can either be used to dump an entire table:

dump mytable into export.txt

or the rowset resulting from a query:

dump select type, count(*) from mytable group by type into histogram.txt delimited by :

An example:

DBI:SQLite:dbname=test.db> dump commands into commands.csv delimited by ,
Dumping commands into commands.csv
Dumped 12 rows into commands.csv

DBI:SQLite:dbname=test.db> more commands.csv
command,desc
show drivers,Displays a list of DBI drivers
show datasources,Displays a list of available data sources for a driver
connect,Connects to a data source
disconnect,Disconnects from a data source
show tables,List the tables in the schema with a rowcount for each table
show schema,Lists the columns in each table in the schema
desc,List the columns in a table
set,Set a parameter
help,Displays sqlsh help in your $PAGER
reload,Reloads sqlsh
exit,Quits sqlsh

You can also dump all the tables in a database into a directory:

dump all tables into dumpdir/

Logging 🌸

You can chose to log commands:

log commands logfile.txt

or query results:

log queries dumpfile.txt

or both:

log all history.log

Exporting data as XML ⭐

DBI:Oracle:IFLDEV> set log-mode xml

DBI:Oracle:IFLDEV> log queries export.xml
Logging queries to export.xml

DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
+------------------+--------------------------------------------------------------+
| COMMAND          | DESCRIPTION                                                  |
+------------------+--------------------------------------------------------------+
| show drivers     | Displays a list of DBI drivers                               |
| show datasources | Displays a list of available data sources for a driver       |
| show tables      | List the tables in the schema                                |
| show tablecounts | List the tables in the schema with a rowcount for each table |
| show schema      | Lists the columns in each table in the schema                |
+------------------+--------------------------------------------------------------+

DBI:Oracle:IFLDEV>> more export.xml
<rowset>
        <record>
                <COMMAND>show drivers</COMMAND>
                <DESCRIPTION>Displays a list of DBI drivers</DESCRIPTION>
        </record>
        <record>
                <COMMAND>show datasources</COMMAND>
                <DESCRIPTION>Displays a list of available data sources for a driver</DESCRIPTION>
        </record>
        <record>
                <COMMAND>show tables</COMMAND>
                <DESCRIPTION>List the tables in the schema</DESCRIPTION>
        </record>
        <record>
                <COMMAND>show tablecounts</COMMAND>
                <DESCRIPTION>List the tables in the schema with a rowcount for each table</DESCRIPTION>
        </record>
        <record>
                <COMMAND>show schema</COMMAND>
                <DESCRIPTION>Lists the columns in each table in the schema</DESCRIPTION>
        </record>
</rowset>

DBI:Oracle:IFLDEV>> no log
Stopped logging queries

Exporting data as SQL 🔥

DBI:Oracle:IFLDEV> set log-mode sql

DBI:Oracle:IFLDEV> log queries export.sql
Logging queries to export.sql

DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
+------------------+--------------------------------------------------------------+
| COMMAND          | DESCRIPTION                                                  |
+------------------+--------------------------------------------------------------+
| show drivers     | Displays a list of DBI drivers                               |
| show datasources | Displays a list of available data sources for a driver       |
| show tables      | List the tables in the schema                                |
| show tablecounts | List the tables in the schema with a rowcount for each table |
| show schema      | Lists the columns in each table in the schema                |
+------------------+--------------------------------------------------------------+

DBI:Oracle:IFLDEV>> more export.sql
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show drivers','Displays a list of DBI drivers');
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show datasources','Displays a list of available data sources for a driver');
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show tables','List the tables in the schema');
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show tablecounts','List the tables in the schema with a rowcount for each table');
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show schema','Lists the columns in each table in the schema');

DBI:Oracle:IFLDEV>> no log
Stopped logging queries

You can then replace $table with the table name you want the INSERT stataments to be issued against:

unixbox% perl -p -i -e 's/\$table/show_commands/' export.sql

Loading data 🧚

Loading a tab-delimited text file is simple:

load export.txt into mytable

Here's an example:

DBI:SQLite:dbname=test.db> create table commands(command varchar(50), desc varchar(255))
CREATE table commands: 0 rows affected

bestie the way this just works?? type it and boom:

DBI:SQLite:dbname=test.db> load commands.tsv into commands
Loaded 12 rows into commands from commands.tsv

and yeah, just like with dump, you can swap out the delimiter character whenever you want:

load export.csv into mytable delimited by ,

you can also ask for character set translations, which is giving multilingual icon behaviour:

load export.txt into mytable from CP1252 to UTF-8

...but only if your database engine literally can't do the character set conversions itself, okay? don't make it do extra credit work. peep Locale::Recode for the full list of character set names ✨

🎀 Manipulating the command history 🎀

okay so your command history is a whole ✨vibe✨ and you can absolutely save it to a file:

save history to history.txt

and you can load a bunch of commands back into the history too, like a little nostalgia drop:

load history from handy_queries.sql

this is so useful when you pair it up with log commands 🫶 and whenever you want a clean slate, you can clear the whole history:

clear history

or just look at it, like a mirror:

show history

🌈 Running batches of commands 🌈

you can run a whole sequence of sqlsh commands straight from a file:

> execute commands.sqlsh

and that file? might've been generated by save history or log commands — the girlies are collaborating 💅

you can also pipe commands into sqlsh on STDIN if you call it with the -i switch:

unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger -i < commands.sqlsh

🔥 one line, whole script executed. main character energy fr fr.

👑 VERSION 👑

v1.17 — periodt.

💖 AUTHOR 💖

John Alden
Miguel Gualdron

and that's the whole page, bestie!! this iconic lil manual is perl v5.42.0, dated 2025-12-05, and lives in the SQL::Shell::Manual(3) section 💜 now go forth and query something gorgeous 🦋✨💅

📦 where this page comes from

fedora 43: perl-SQL-Shell 1.18-1.fc43 · GPL-2.0-or-later
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-14: a language model's rewrite of the original, under its terms