Docs Home

tbSQL

This chapter describes the tbSQL utility and its usage.

Overview

tbSQL is an interactive Tibero utility that processes SQL statements. It allows to process SQL queries, Data Definition Language (DDL), and transactional SQL statements and to create and execute PSM programs. It also allows DBAs to execute commands to manage Tibero systems.

In addition, tbSQL provides many other functions, such as auto commit setting, OS command execution, output saving, and script creation. Especially, you can create a script including multiple SQL statements, PSM programs, and tbSQL utility commands.

tbSQL is one of the most frequently used Tibero utilities. It provides the following functions.

  • Inputs, edits, saves, and executes general SQL statements and PSM programs

  • Sets and ends transactions

  • Executes batch processing through a script

  • Allows DBAs to manage databases

  • Starts and ends databases

  • Executes external utilities and programs

  • Configures the tbSQL utility.

Quick start

tbSQL is automatically installed and uninstalled along with Tibero.

Execution

The following executes tbSQL.

Executing tbSQL

tbsql

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

When tbSQL is executed successfully, the SQL prompt is displayed. You can enter and execute SQL statements at the prompt.

The following is the command syntax for executing tbSQL.

Usage 

tbsql [[options]|[connect_string]|[start_script]]

  • [options]

Option

Description

-h, --help

Displays help information.

-v, --version

Displays the version.

-s, --silent

Does not display the start message and prompt.

-i, --ignore

Does not execute the login script (tbsql.login).

  • [connect_string]

Information about a user account that attempts to access Tibero. It is specified as follows:

username[/password[@connect_identifier]]

The following describes each item for connect_string.

Item

Description

username

User name. It is not case-sensitive except when it is enclosed in double

quotation marks (" ").

password

User password. It is not case-sensitive except when it is enclosed in single quotation marks (' ').

connect_identifier

Either Data Source Name (DSN) that contains database connection

information or a connection description that complies with predefined rules.

  • [start_script]

Script file to execute when tbSQL starts. It is specified as follows:

@filename[.ext] [parameter ...]

The following describes each item for start_script.

Item

Description

filename

File name.

ext

File extension.

If not set, the extension set in the SUFFIX system variable is used by default.

parameter

Substitution variables used in the file.

Database Connection

The SQL prompt displayed after executing tbSQL means that it is ready to connect to a database.

If there are any tasks to process before starting the database session, create the tbsql.login file. This file is searched in the current directory. If not found, it is searched in the directory set in the TB_SQLPATH environment variable.

The following connects to a database by using tbSQL.

Connecting to Database 

$ tbsql SYS/syspassword

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved. 

Connected.

The previous example executes tbSQL and connects to a database with specified username and password in the UNIX shell prompt.

There are the following rules for username and password.

Item

Description

Username

Not case-sensitive like schema object names.

However, usernames enclosed in double quotation marks (" ") are case-sensitive.

Password

Not case-sensitive.

However, passwords enclosed in single quotation marks (' ') are case-sensitive.

If connect_identifier is omitted as in the previous example, the connection is made to the default database. To connect to a specific database, specify connect_identifier in one of the following ways.

  • Data Source Name (DSN) Specify the name defined in the tbdsn.tbr file or ODBC data source in Windows.

The following is an example.

tibero7=(
    (INSTANCE=(HOST=192.168.36.42) 
              (PORT=8629)
              (DB_NAME=tibero7)
          )
)

If tbdsn.tbr includes the previous information, connect to the database as follows:

$ tbsql tibero/tmax@tibero7

  • Connection description Specify connection information in one of the following two methods.

Method 1

(INSTANCE=(HOST=host)(PORT=port)(DB_NAME=dbname))

Example

$ tbsql 'tibero/tmax@(INSTANCE=(HOST=192.168.36.42)(PORT=8629)(DB_NAME=tibero7))'

Method 2

host:port/dbname

Example

$ tbsql 'tibero/[email protected]:8629/tibero7'

Interface

The following shows the interface between users and tbSQL.

$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

SQL> CONNECT dbuser
Enter password : dbuserpassword 
Connected to Tibero.

In the previous example, after tbSQL is executed, a connection is made to a database by using the CONNECT command with the username, dbuser.

Like this, tbSQL is a text-based user interface utility.

In this document, all SQL statements, PSM programs, and tbSQL commands, with some exceptions, are written in uppercase letters.

(Command parameters written in lowercase letters are specified by the user.)

The tbSQL interface works as follows:

  • When tbSQL is executed successfully, the SQL prompt is displayed. At the SQL prompt, you can enter SQL statements, PSM programs, and tbSQL commands.

  • Input can span multiple lines. SQL statements and PSM programs can be not executed when entered, but tbSQL commands are executed when entered.

  • Not case-sensitive. Input text is not case-sensitive, with some exceptions such as a string value in an SQL statement.

For example, the following two statements have the same meaning.

SQL> SET AUTOCOMMIT ON
SQL> set autocommit on

Environment Configuration

To configure tbSQL environment, use the SET command. You can set the output format of SQL query results, transaction commit option, and other environment options.

The following is the syntax of the SET command. 

SET [system_variable] [system_variable_value]

For detailed information, refer to “System Variables”.

End

To end tbSQL, use the EXIT or QUIT command at the SQL prompt.

SQL> EXIT

For detailed information about tbSQL commands, refer to “tbSQL Commands”.

System Variables

This section describes system variables for tbSQL. To set the system variables, use the SET command, and to display the results, use the SHOW command.

The following describes each system variable.

System Variable

Default Value

Description

AUTOCOMMIT

OFF

Executes a commit after processing SQL statements.

AUTOTRACE

OFF

Displays the plan and statistics of a running query.

BLOCKTERMINATOR

"." (0x2E)

Sets a character that indicates the end of a PSM block.

COLSEP

" " (0x20)

Sets a delimiter for columns displayed after processing a

SELECT statement.

CONCAT

"." (0x2E)

Sets a character that indicates the end of a substitution

variable name.

DDLSTATS

OFF

Displays the plan and statistics of a running DDL statement.

DEFINE

"&" (0x26)

Sets a character used to define a substitution variable.

DESCRIBE

10

Sets the level of object specification to display.

ECHO

OFF

Displays running script queries when a script file is executed

with the START or @ command.

EDITFILE

.tbedit.sql

Sets the default file name used for the EDIT command.

ESCAPE

OFF

Sets an escape character used to ignore the substitution

variable character defined in DEFINE.

EXITCOMMIT

ON

Executes a commit when a utility is ended.

FEEDBACK

0

Displays the SQL statement results.

HEADING

ON

Displays column headers for query results.

HEADSEP

"|" (0x7C)

Sets a new line character for column headers.

HISTORY

50

Sets the size of command history.

INTERVAL

1

Sets the wait time between executions of the LOOP command.

LINESIZE

80

Sets the length of a line on the screen.

LONG

80

Sets the output length of large object data.

MARKUP

OFF

Sets the output of a utility in html.

NEWPAGE

1

Sets the number of empty lines added to the beginning of each page.

NUMFORMAT

""

Sets the default format of numeric columns.

NUMWIDTH

10

Sets the output length of numeric data.

PAGESIZE

24

Sets the number of lines per page.

PAUSE

OFF

Waits for user input before displaying the next page.

RECSEP

WRAPPED

Displays a row separator.

RECSEPCHAR

" " (0x20)

Sets a character used as a row separator.

ROWS

ON

Displays query statement results.

SERVEROUTPUT

OFF

Displays the result of the DBMS_OUTPUT package.

SQLCODE

Displays the last SQLCODE.

SQLPROMPT

"SQL> "

Sets the prompt string.

SQLTERMINATOR

";" (0x3B)

Sets a character that indicates the end of an SQL statement.

SUFFIX

sql

Sets the default file extension.

TERMOUT

ON

Displays the result of commands in a script.

TIME

OFF

Displays the current time in the prompt.

TIMEOUT

3

Sets the timeout for a server response to the ping command.

TIMING

OFF

Displays the processing time along with SQL and PSM statement results.

TRIMOUT

ON

Trims whitespaces at the end of each line when displaying SQL and PSM results.

TRIMSPOOL

OFF

Trims whitespaces at the end of each line when spooling SQL and PSM results.

UNDERLINE

"-" (0x2D)

Sets a character used to underline headers.

VERIFY

ON

Displays commands including substitution variables after replacing the variables with values.

WRAP

ON

Wraps lines that are longer than LINESIZE.

The following sets system variables.

SET AUTOCOMMIT ON 
SET PAGESIZE 32 
SET TRIMSPOOL ON

AUTOCOMMIT

Executes a commit after processing SQL statements such as INSERT, UPDATE, DELETE, MERGE, and PSM blocks.

Syntax

SET AUTO[COMMIT] {ON|OFF|n}

Item

Description

ON

Executes an auto commit.

OFF

Does not execute a commit. (Default value)

If set to OFF, a commit must be executed manually.

n

Executes a commit after processing n SQL statements.

Setting to 0 is the same as setting to OFF, and setting to 1 is the same as setting to ON.

AUTOTRACE

Displays the plan and statistics of a running query. To use this variable, DBA or PLUSTRACE privilege is required. The PLUSTRACE privilege includes permissions required to use AUTOTRACE. A user who has DBA privilege can create the PLUSTRACE privilege and grant it to another user.

To create the privilege, the $TB_HOME/scripts/plustrace.sql script can be used.

Syntax

SET AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] [PLANS[TAT]]

  • Input

Item

Description

ON

Displays the plan and statistics depending on the query result and options.

OFF

Does not display the query plan and statistics. (Default value)

TRACE[ONLY]

Does not display the query result, but displays the plan and statistics depending on options.

  • Options

The following describes each option.

Item

Description

None

Both the plan and statistics are displayed.

EXP[LAIN]

Displays the plan.

STAT[ISTICS]

Displays the statistics.

PLANS[TAT]

Displays query execution information such as processing time, the number of processed rows, and execution count for each node.

BLOCKTERMINATOR

Sets a character that indicates the end of a PSM block.

Syntax 

SET BLO[CKTERMINATOR] {c|ON|OFF}

Item

Description

c

Character that indicates the end of a PSM block. (Default value: ".")

ON

Enables this variable. (Default value)

OFF

Disables this variable.

COLSEP

Sets a delimiter for columns displayed after processing a SELECT statement.

Syntax 

SET COLSEP {text}

Item

Description

text

Column delimiter. (Default value: " ")

CONCAT

Sets a character that indicates the end of a substitution variable name.

Syntax

SET CON[CAT] {c|ON|OFF}

Item

Description

c

Character that indicates the end of a substitution variable name. (Default value: ".")

ON

Enables this variable. (Default value)

OFF

Disables this variable.

DDLSTATS

Displays the plan and statistics of a running DDL statement. To use this variable, AUTOTRACE must be enabled.

Syntax

SET DDLSTAT[S] {ON|OFF}

Item

Description

ON

Enables this variable.

OFF

Disables this variable. (Default value)

DEFINE

Sets a character used to define a substitution variable.

Syntax

SET DEF[INE] {c|ON|OFF}

Item

Description

c

Character that indicates a substitution variable. (Default value: "&")

ON

Enables this variable. (Default value)

OFF

Disables this variable.

DESCRIBE

Sets the level of object specification to display.

Syntax

SET DESCRIBE DEPTH {n}

Item

Description

n

Level of object specification to display recursively. (Default value: 10)

ECHO

Displays running script queries when a script file is executed with the START or @ command.

Syntax

SET ECHO {ON|OFF}

Item

Description

ON

Enables this variable.

OFF

Disables this variable. (Default value)

EDITFILE

Sets the default file name used for the EDIT command. If the extension is omitted, the value set in SUFFIX is used.

Syntax 

SET EDITF[ILE] filename[.ext]

Item

Description

filename[.ext]

File name used for the EDIT command. (Default value: .tbedit.sql)

ESCAPE

Sets an escape character used to ignore the substitution variable character defined in DEFINE. If the escape character is followed by a substitution variable, the variable is not recognized as a substitution variable.

Syntax

SET ESC[APE] {c|ON|OFF}

Item

Description

c

Escape character. (Default value: "\")

ON

Enables this variable.

OFF

Disables this variable. (Default value)

EXITCOMMIT

Executes a commit when tbSQL ends.

Syntax

SET EXITC[OMMIT] {ON|OFF}

Item

Description

ON

Enables this variable. (Default value)

OFF

Disables this variable.

FEEDBACK

Displays the SQL statement results.

Syntax

SET FEED[BACK] {n|ON|OFF}

Item

Description

n

Minimum number of rows to display the results. (Default value: 0)

ON

Enables this variable. (Default value)

OFF

Disables this variable.

HEADING

Displays column headers for query results.

Syntax


SET HEA[DING] {ON|OFF}

Item

Description

ON

Enables this variable. (Default value)

OFF

Disables this variable.

HEADSEP

Sets a new line character for column headers.

Syntax 

SET HEADS[EP] {c|ON|OFF}

Item

Description

c

New line character. (Default value: "|")

ON

Enables this variable. (Default value)

OFF

Disables this variable.

HISTORY

Sets the size of command history.

Syntax

SET HIS[TORY] {n}

Item

Description

n

Size of command history. (Default value: 50)

INTERVAL

Sets the wait time between executions of the LOOP command.

Syntax 

SET INTER[VAL] {n}

Item

Description

n

Wait time in seconds. (Default value: 1)

LINESIZE

Sets the length of a line on the screen.

Syntax 

SET LIN[ESIZE] {n}

Item

Description

n

Length of a line on the screen. (Default value: 80)

LONG

Sets the output length of large object data, such as CLOB, BLOB, NCLOB, LONG, and XML data.

Syntax

SET LONG {n}

Item

Description

n

Output length of large object data. (Default value: 80, maximum value: 2,000,000,000)

MARKUP

Displays the output of a utility in HTML.

Syntax

SET MARKUP HTML {ON|OFF} [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] 
[SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]

  • Input parameters

Item

Description

ON

Enables HTML MARKUP.

OFF

Disables HTML MARKUP. (Default value)

  • Option The following are options for detail settings of HTML markup.

    Item

    Description

    HEAD text

    Specifies text to be included in the <head> tag. (Default value: default setting of the utility)

    BODY text

    Specifies attributes for the <body> tag. (Default value: none)

    TABLE text

    Specifies attributes for the <table> tag. (Default value: default setting of the utility)

    ENTMAP ON

    Enables conversion of characters <, >, ", and & into HTML entities. (Default value)

    ENTMAP OFF

    Disables conversion of characters <, >, ", and & into HTML entities.

    SPOOL ON

    Enables formatting with the <html> and <body> tags to the beginning and end of a SPOOL file.

    SPOOL OFF

    Disables formatting with the <html> and <body> tags to the beginning and end of a SPOOL file. (Default value)

    PREFORMAT

    ON

    Formats query results with the <pre> tag.

    PREFORMAT

    OFF

    Formats query results in HTML tables. (Default value)

The MARKUP capability is supported starting with Tibero 7 FS02 release.

NEWPAGE

Sets the number of empty lines added to the beginning of each page.

Syntax

SET NEWP[AGE] {1|n|NONE}

Item

Description

n

Number of empty lines. (Default value: 1)

NUMFORMAT

Sets the default format of NUMBER columns. The format applies only to numeric columns whose format is not set with the COLUMN command.

Syntax

SET NUMF[ORMAT] {fmt_str}

Item

Description

fmt_str

Default format of numeric columns. (Default value: "")

For more information about numeric type formats, refer to “Column Formats”.

NUMWIDTH

Sets the output length of NUMBER data. The length cannot exceed the value set in LINESIZE.

Syntax

SET NUM[WIDTH] {n}

Item

Description

n

Output length of numeric data. (Default value: 10)

PAGESIZE

Sets the number of lines per page.

Syntax 

SET PAGES[IZE] {n}

Item

Description

n

Number of lines per page. (Default value: 24)

PAUSE

Waits for user input before displaying the next page.

Syntax 

SET PAU[SE] {ON|OFF}

Item

Description

ON

Enables this variable.

OFF

Disables this variable. (Default value)

RECSEP

Displays a row separator.

Syntax

SET RECSEP {WR[APPED]|EA[CH]|OFF}

Item

Description

WRAPPED

Displays a row separator after wrapped rows. (Default value)

EACH

Displays a row separator after each row.

OFF

Disables this variable.

RECSEPCHAR

Sets a character used as a row separator. This character is repeatedly displayed as many times as LINESIZE.

Syntax

SET RECSEPCHAR {c}

Item

Description

c

Row separator. (Default value: " ")

ROWS

Displays query statement results.

Syntax 

SET ROWS {ON|OFF}

Item

Description

ON

Enables this variable. (Default value)

OFF

Disables this variable.

SERVEROUTPUT

Displays the result of the DBMS_OUTPUT package.

Syntax

SET SERVEROUT[PUT] {ON|OFF} [SIZE n]

Item

Description

ON

Enables this variable.

OFF

Disables this variable. (Default value)

n

SERVEROUTPUT buffer size. (Default value: 1000000)

SQLCODE

Displays the last SQLCODE. This value cannot be set with the SET command.

Syntax 

SHOW SQLCODE

SQLPROMPT

Sets the prompt string.

Syntax

SET SQLP[ROMPT] {prompt_string}

Item

Description

prompt_string

Prompt string. (Default value: "SQL> ")

Recognizes environment variables and the _user identifier in this string. For example, if '$ISQL_PROMPT' is specified, the value of the environment variable

$ISQL_PROMPT is used as the prompt string. The name of the environment variable is case-sensitive. If '_user' is specified, the value of _user is dynamically replaced with the currently connected user's name and used as the prompt string.

Both an environment variable and the _user identifier can be included in a string at the same time. The maximum length of the string is 128 characters.

SQLTERMINATOR

Sets a character that indicates the end of an SQL statement.

Syntax

SET SQLT[MINATOR} {c|ON|OFF}

Item

Description

c

Character that indicates the end of an SQL statement. (Default value: ";")

ON

Enables this variable.

OFF

Disables this variable.

SUFFIX

Sets the default file extension.

Syntax

SET SUF[FIX] {extension}

Item

Description

extension

Default file extension. (Default value: sql)

TERMOUT

Displays the result of commands in a script.

Syntax

SET TERM[OUT] {ON|OFF}

Item

Description

ON

Enables this variable. (Default value)

OFF

Disables this variable.

TIME

Displays the current time in the prompt.

Syntax

SET TI[ME] {ON|OFF}

Item

Description

ON

Enables this variable.

OFF

Disables this variable. (Default value)

TIMEOUT

Sets the timeout for a server response to the ping command.

Syntax

SET TIMEOUT {n}

Item

Description

n

Timeout for a server response in seconds. (Default value: 3)

TIMING

Displays the processing time along with SQL and PSM statement results.

Syntax

SET TIMI[NG] {ON|OFF}

Item

Description

ON

Enables this variable.

OFF

Disables this variable. (Default value)

TRIMOUT

Trims whitespaces at the end of each line when displaying SQL and PSM statement results.

Syntax 

SET TRIM[OUT] {ON|OFF}

Item

Description

ON

Enables this variable. (Default value)

OFF

Disables this variable.

TRIMSPOOL

Trims whitespaces at the end of each line when spooling SQL and PSM statement results.

Syntax

SET TRIMS[POOL] {ON|OFF}

Item

Description

ON

Enables this variable.

OFF

Disables this variable. (Default value)

UNDERLINE

Sets a character used to underline headers.

Syntax 

SET UND[ERLINE] {c|ON|OFF}

Item

Description

c

Underline character. (Default value: "-")

ON

Enables this variable. (Default value)

OFF

Disables this variable.

VERIFY

Displays commands including substitution variables after replacing the variables with values.

Syntax

SET VER[IFY] {ON|OFF}

Item

Description

ON

Enables this variable. (Default value)

OFF

Disables this variable.

WRAP

Wraps lines that are longer than LINESIZE.

Syntax

SET WRA[P] {ON|OFF}

Item

Description

ON

Enables this variable. (Default value)

OFF

Truncates lines that are longer than LINESIZE to match the LINESIZE.

Basic Functions

tbSQL is mainly used to execute user-entered queries such as SQL statements and PSM programs. This section describes how to enter and execute the queries and explains additional functions.

Entering Queries

At the tbSQL prompt, you can enter SQL statements, PSM programs, and tbSQL commands. The following describes how to enter them.

SQL Statements

Enter SQL statements as follows:

  • Entering SQL statements Enter general SQL statements at the prompt. An SQL statement can span multiple lines. To cancel entering a statement, enter an empty line.

  • Line break Break a line when entering an SQL statement in multiple lines. You can break a line anywhere except in the middle of a string. It is recommended to break a line by clause for readability.

  • Inserting comments Insert comments into SQL statements. Comments start with two dashes (--) and end with the newline character. They can start at the beginning of a line or after another string.

  • Using stored statements Entered SQL statements are stored in tbSQL's SQL buffer and can be reused. If they are modified, the modified statements are also stored in the buffer.

    The SQL buffer stores a single SQL statement or PSM program. Depending on the OS, press the up arrow key (­) or down arrow key (¯) to select a line of a stored statement. Since one line of a stored statement is displayed whenever you press the key, you can reuse a part or full of SQL statement.

The following enters an SQL statement in tbSQL.

SQL> SELECT ENAME, SALARY, ADDR 
    FROM EMP
    -- this is a comment. 
    WHERE DEPTNO = 5;
SQL>

PSM Programs

A PSM program consists of multiple SQL and PSM statements. Each SQL statement ends with a semicolon (;). When you start entering a PSM program, tbSQL will be automatically switched to the PSM program input mode. In the mode, SQL statements are not executed until an entire program is entered.

Entering the following statements switch tbSQL to the PSM program input mode: DECLARE and BEGIN for anonymous blocks, CREATE (OR REPLACE) PROCEDURE that creates a procedure, CREATE (OR REPLACE) FUNCTION that creates a function, and CREATE (OR REPLACE) TRIGGER that creates a trigger.

Enter PSM programs as follows:

  • Entering PSM Programs A PSM program can span multiple lines. To cancel entering a PSM program, enter the block terminator character (set in BLOCKTERMINATOR). The default character is a period (.). Enter the character in a separate line without any other characters.

  • Inserting comments Same as in SQL statements.

  • Using stored Statements Entered programs are stored in SQL buffer and can be reused.

The following enters an anonymous block in tbSQL.

SQL> DECLARE
    deptno NUMBER(2);
    BEGIN
        deptno := 5;
        UPDATE EMP SET SALARY = SALARY * 1.05
        WHERE DEPTNO = deptno;
        -- this is a comment.
    END;
    . 
SQL>

In the previous example, a line is entered as a comment, and the PSM program is ended by entering the block terminator character (.) below the END statement. You can check that the character is entered in a separate line without any other characters.

For more information about tbPSM, refer to "Tibero tbPSM Guide".

tbSQL Commands

Enter tbSQL commands at the prompt to execute SQL statements and manage Tibero database.

tFor information about the commands, refer to “tbSQL Commands”.

Executing Queries

There are the following three ways to execute queries entered at the tbSQL prompt.

  • Executing SQL statements and PSM programs stored in the SQL buffer The SQL buffer stores an SQL statement or a PSM program entered lastly. To execute the SQL statement or PSM program, enter the RUN or / command.

  • Executing SQL statements Entering a full SQL statement followed by a semicolon (;) executes the statement.

  • Executing an SQL statement and storing it in the SQL buffer To execute an SQL statement or a PSM program and store it in the butter, enter the / command. Enter the command in a separate line without any other characters like the block terminator character.

There is no separate command for executing tbSQL utility commands. The commands are not stored in the SQL buffer. They are executed when entered.

The following shows how to execute an SQL statement stored in the SQL buffer.

SQL> SELECT ENAME, SALARY, ADDR 
    FROM EMP
    -- this is a comment. 
    WHERE DEPTNO = 5;
......Execution result  ①......
SQL> /
......Execution result  ②......
SQL>

In the previous example, at the first prompt, an SQL statement is executed by entering the statement and a semicolon (;). At the second prompt, the / command is entered, which executes the SQL statement stored in the SQL buffer. Since the SQL buffer stores an SQL statement entered lastly, the SQL statement entered at the first prompt is executed. Therefore, the execution results (1) and (2) are the same.

The following shows how to execute an SQL statement and store it in the SQL buffer. A semicolon (;) is not entered at the end of the SQL statement.

SQL> SELECT ENAME, SALARY, ADDR 
    FROM EMP
    -- this is a comment. 
    WHERE DEPTNO = 5
    /
......Execution result ......
SQL>

Additional Functions

The following describes additional basic functions.

Inserting Comments

There are the following two ways to insert comments.

  • Using /* and */ Same as in the C and C++ programing languages. Enclose multi-line comments with /* and */. A comment cannot nest another comment.

  • Using two dashes (--) Single-line comments start with two dashes (--) and end with the newline character. They can start anywhere except for in a line with a period (.) that indicates the end of a PSM program.

The following causes an error.

(PSM program)
.-- incorrect comment
RUN

Autocommit

Data modified with an SQL statement is not applied to a database until the transaction including the statement is committed. A transaction usually includes multiple SQL statements.

In tbSQL, you can enable or disable autocommit by using the AUTOCOMMIT variable (default value: OFF). To set the variable, use the SET AUTOCOMMIT command. To check the current setting, use the SHOW AUTOCOMMIT command.

Executing OS Commands

To execute OS commands in tbSQL, use the ! or HOST command.

The following lists all script files with the .sql extension.

SQL> HOST dir *.sql
..... OS command execution result .....
SQL>

After executing an OS command, the tbSQL prompt is displayed.

If the ! or HOST command is used without an OS command, the OS command prompt is displayed. To return to the tbSQL prompt, enter EXIT as follows:

SQL> !
$ dir *.sql
..... OS command execution result .....
$ EXIT 
SQL>

Saving Input and Output

To save all data entered and displayed in tbSQL to a text file, use the SPOOL command. This command saves all user-entered SQL statements and PSM programs, tbSQL commands, query and program execution results, and the tbSQL prompt to a text file. If you execute the command, spooling starts from the next line. To stop this function, use the SPOOL OFF command.

The following uses the SPOOL command with a file name (save.txt). Note that if the file name already exists, the existing file is overwritten.

SQL> SPOOL save.txt 
Spooling is started. 
SQL> SELECT
    *
    FROM DUAL; 
    
DUMMY
------
X

1 row selected.

SQL> SPOOL OFF
Spooling is stopped: save.txt

The following are the contents of the save.txt file in the previous example.

SQL> SELECT
    *
    FROM DUAL; 
    
DUMMY
------
X

1 row selected.

SQL> SPOOL OFF

The file contains user-entered SQL statements, the query result, and the SPOOL OFF command.

Advanced Functions

This section describes the advanced tbSQL functions including batch processing using a script and DBA functions for managing Tibero.

Script Functions

A script is a series of SQL statements, PSM programs, and tbSQL commands. When executing a script in tbSQL, all the queries in the script are executed sequentially.

Creating a Script

You can create and edit a script file externally, or call an external editor in tbSQL to create and edit a script file.

The following uses vi as an external editor in tbSQL.

$ export TB_EDITOR=vi

To edit a script file by using an external editor, use the EDIT command with specifying the file name. The file extension can be omitted if the extension is the same as the value set in the SUFFIX system variable.

The following calls an external editor to edit the run.sql script file.

SQL> EDIT run

Add SQL statements, PSM programs, and tbSQL commands to a script file as follows:

  • Adding queries Same as entering queries at the tbSQL prompt. A query can span multiple lines.

  • Ending SQL statements and PSM programs An SQL statement must be ended with a semicolon (;). A PSM program must be ended with a period (.) in the last line.

  • Inserting comments You can insert comments into a script file.

When a script is executed, SQL statements in the script are executed immediately. PSM programs in the script are executed with the RUN or / command.

The following is a sample script file that executes some operations on the EMP table. Blank lines are allowed between lines.

-- SQL statement
SELECT ENAME, SALARY, ADDR FROM EMP 
    WHERE DEPTNO = 5;
UPDATE EMP SET SALARY = SALARY * 1.05 
    WHERE DEPTNO = 5;
    
-- PSM program
DECLARE
    deptno NUMBER(2);
BEGIN
    deptno := 20;
    UPDATE EMP SET SALARY = SALARY * 1.05
    WHERE DEPTNO = deptno; 
END;

RUN -- Executing the PSM program
/* Applying the changes  */ 
COMMIT;

Executing a Script

To execute a script file, use the START or @ command with specifying the file name. The file extension can be omitted if the extension is the same as the value set in the SUFFIX system variable (default value: sql). The script file is searched in the current directory. If not found, it is searched in the directory set in the TB_SQLPATH environment variable.

The following each line executes the run.sql script file and has the same result.

SQL> START run
SQL> @run

A script file can include one or more script files by using the START or @ command. Make sure not to get into an infinite loop when using a script file recursively.

You can execute a script file when starting tbSQL by using the @ command. This is useful when executing a batch program in the OS.

The following executes the run.sql script file when starting tbSQL.

$ tbsql dbuser/dbuserpassword @run

The following also executes the run.sql script file when starting tbSQL by using the shell redirection command.

$ tbsql dbuser/dbuserpassword < run.sql

DBA Functions

A user with the DBA privilege can execute DBA functions in tbSQL.

The following logs in as the SYS user with the DBA privilege.

$ tbsql sys/syspassword

After starting tbSQL, you can connect to a database as a user with the DBA privilege by using the CONNECT command.

The following connects to a database as the SYS user with the DBA privilege.

SQL> CONNECT sys/syspassword

A DBA can perform the following task in tbSQL.

● Ending Tibero by using the TBDOWN command

User Access Control

tbSQL allows to restrict users from executing certain commands. For this, the CLIENT_ACCESS_POLICY table is referenced. The SYS user creates this table, and a DBA defines the access policy in the table to grant command execution permission to specific users.

tbSQL loads the table data when a user connects to a database and it checks the permission whenever the user executes a command. The loaded data is released when the user disconnects from the database.

Privilege is not checked for SYS users.

Creating Access Control Table

The SYS user can create the access control table by executing the $TB_HOME/scripts/client_policy.sql file.

Privilege is not checked if the table does not exist or has an issue.

The contents of the CLIENT_ACCESS_POLICY table are as follows:

CLIENT VARCHAR2(32) NOT NULL 
USERID VARCHAR2(128)
ACTION VARCHAR2(256) 
POLICY VARCHAR2(64)

Item

Description

CLIENT

Case-sensitive client program name. Set to tbSQL.

USERID

User ID(s).

Specify as follows. A wildcard (%) is allowed.

  • TIBERO

  • T% (All users that start with T)

  • % (All users)

ACTION

Command to control.

POLICY

Access policy.

Set to DISABLED.

Configuring Command Access Control

To restrict executing tbSQL, SQL, or PSM commands, add rows to the CLIENT_ACCESS_POLICY table like the following. To allow executing the commands, delete the rows

CLIENT		USERID		ACTION		POLICY
----------      -----------     -----------     --------------
tbSQL           TIBERO          HOST            DISABLED 
tbSQL           %               INSERT          DISABLED 
tbSQL           PUBLIC          UPDATE          DISABLED

The following are the lists of commands that can be controlled.

  • tbSQL Commands 

ACCEPT	        APPEND	        ARCHIVE	        CHANGE
CLEAR	        COLUMN	        CONNECT	        DEFINE
DEL	        DESCRIBE	DISCONNECT	EDIT
EXECUTE	        EXIT	        EXPORT	        HELP (?)
HISTORY	        HOST (!)	INPUT	        LIST
LOADFILE	LOOP	        LS	        PASSWORD
PAUSE		PING	        PRINT	        PROMPT
QUIT		REMARK	        RESTORE	        RUN
SAVE		SET	        SHOW	        SPOOL
START	(@, @@)	TBDOWN	        UNDEFINE	VARIABLE
WHENEVER

  • SQL Commands 

ALTER	        ANALYZE	        AUDIT	        CALL
COMMENT	        COMMIT	        CREATE	        DELETE
DROP	        EXPLAIN	        FLASHBACK	GRANT
INSERT	        LOCK	        MERGE	        NOAUDIT
PURGE	        RENAME	        REVOKE	        ROLLBACK
SAVEPOINT	SELECT	        SET CONSTRAINTS	SET ROLE
SET TRANSACTION	TRUNCATE	UPDATE

  • PSM Commands 

BEGIN         DECLARE

The following is an example of using the user access control function.

..... Connect as SYS user .....
SQL> CONNECT SYS
..... Configure Access Control  .....
SQL> INSERT INTO CLIENT_ACCESS_POLICY VALUES ('tbSQL', 'TIBERO', 'SELECT', 'DISABLED');

..... Connect as TIBERO use .....
SQL> CONNECT TIBERO
SQL> SELECT * FROM DUAL;
TBS-70082: The 'SELECT' command has been disabled.

Encrypting Connection Information

Database connection information (connect_string) can be saved in an encryption file (wallet.dat) and reused in tbSQL. The file is located in the path set in the ISQL_WALLET_PATH environment variable and can be used from the next connection.

Creating an Encryption File

Connect to a database in tbSQL and then create an encryption file by using the SAVE CREDENTIAL command.

The following sets the ISQL_WALLET_PATH environment variable to wallet.dat in the current directory and then encrypts database connection information.

$ export ISQL_WALLET_PATH=./wallet.dat
$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Can't login with the wallet file.
Login the database and SAVE CREDENTIAL again.

Enter Username: dbuser
Enter Password: dbuserpassword 
Connected to Tibero.

SQL> SAVE CREDENTIAL
Complete to generate the wallet file.

In the previous example, since ISQL_WALLET_PATH is set before starting tbSQL, it is tried to decrypt the set file, but an error occurs because the set file does not exist. To resolve this issue, reconnect to the database and create the file by using the SAVE CREDENTIAL command.

The following creates wallet.dat in the current directory by encrypting database connection information without setting ISQL_WALLET_PATH.

$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

SQL> CONN dbuser/dbuserpassword 
Connected to Tibero.

SQL> SAVE CREDENTIAL "./wallet.dat" 
Complete to generate the wallet file.

You can specify the path of wallet.dat when executing SAVE CREDENTIAL.

Using an Encryption File

Set the encryption file (wallet.dat) created in the previous example in ISQL_WALLET_PATH before executing tbSQL to reuse database connection information used before creating the file.

The following connects to a database by using the file set in ISQL_WALLET_PATH.

$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Connected to Tibero.

To use the encryption file, it must be set in ISQL_WALLET_PATH. The encryption file is only available in tbSQL instance where the file is created. To use the same file in another tbSQL instance, the encryption file must be reconfigured using the aforementioned steps.

This function is not available in Windows.

tbSQL Commands

This section describes commands available in tbSQL.

The following is sample syntax. 

COM[MAND] param {choice1|choice2} [option] [arg]*

The following describes each item in the syntax.

Item

Description

Brackets ([ ])

Optional.

In the sample syntax, the [MAND], [option], and [arg] can be omitted.

Curly braces ({ })

Mutually exclusive parameters. One of them must be entered.

In the sample syntax, choice1 and choice2 are enclosed with curly braces ({, }) and separated by a vertical bar (|). One of them must be entered.

Vertical bar (|)

OR operator used for mutually exclusive parameters.

Asterisk (*)

None or multiple arguments can be entered.

In the sample syntax, [arg] followed by an asterisk (*) can be excluded or entered multiple times.

Italic letters

Must be replaced by another string depending on the command.

Case sensitivity

Commands are not case-sensitive.

The following are examples of valid commands.

COMMAND param choice1 
COM param choice1 option
COM param choice2 arg1 arg2 arg3

With tbSQL commands, you can execute SQL statements and manage databases. The following describes the commands in alphabetical order.

The following are the tbSQL commands.

Command

Description

!

Executes an OS command. Same as the HOST command.

%

Executes a command stored in the tbSQL history buffer.

@, @@

Executes a script file. Same as the START command.

/

Executes PSM programs or SQL statements that are saved in the SQL buffer. Same as the RUN command.

ACCEPT

Receives user input and saves it in a specified substitution variable.

APPEND

Appends user-entered text or statements to the SQL buffer.

ARCHIVE LOG

Displays redo log file information.

CHANGE

Finds an old pattern in the current line of the SQL buffer and changes it to a new

pattern.

CLEAR

Initializes or deletes the specified option.

COLUMN

Specifies the display properties of a specified column.

CONNECT

Connects to the database as a specified user.

DEFINE

Defines or displays substitution variables.

DEL

Deletes the line that is stored in the SQL buffer.

DESCRIBE

Displays the column information of the specified object.

DISCONNECT

Ends the connection to the current database.

EDIT

Edits the contents of an SQL buffer or a certain file by using an external editor.

EXECUTE

Processes PSM statements that are CALL statements or anonymous blocks.

EXIT

Ends tbSQL. Same as the QUIT command.

EXPORT

Exports SELECT statement results or table data to a file.

HELP

Displays help information.

HISTORY

Displays commands stored in the history buffer.

HOST

Executes an OS command. Same as the ! command.

INPUT

Adds a user-input SQL statement after the last line in the SQL buffer.

LIST

Displays specified lines from the SQL buffer.

LOADFILE

Saves a Tibero table in the format that can be recognized by Oracle SQL*Loader.

LOOP

Repeatedly executes a statement.

LS

Displays information about a user-created database object with a specific type

or name.

PASSWORD

Changes the user password.

PAUSE

Pauses the execution until the user presses the <Enter> key.

PING

Displays whether a specified database is accessible.

PRINT

Displays the value and name of user-defined bind variables.

PROMPT

Displays a specified message or an empty line.

QUIT

Ends tbSQL. Same as the EXIT command.

RESTORE

Restores user-selected data from a file.

RUN

Executes PSM programs or SQL statements that are stored in the SQL buffer. Same as the / command.

SAVE

Saves user-selected information to a file.

SET

Sets tbSQL system variables.

SHOW

Shows tbSQL system variables.

SPOOL

Saves all screen outputs to a file in the current directory.

START

Executes a script file. Same as the @ command.

TBDOWN

Ends Tibero database.

UNDEFINE

Deletes substitution variables.

VARIABLE

Declares user bind variables.

WHENEVER

Defines tbSQL action to take when an error occurs.

!

Executes an OS command. Same as the HOST command.

Syntax

! [command]

Item

Description

None

Goes to the Windows prompt where multiple OS commands can be entered. To

return to the tbSQL prompt, enter the EXIT command.

command

OS command.

Example

SQL> ! dir *.sql 
SQL> !

%

Executes a command stored in the tbSQL history buffer.

Syntax

% number

Item

Description

number

Command number in the history buffer.

Example

SQL> history
    1: set serveroutput on 
    2: set pagesize 40
    3: select 1 from dual;
SQL> %3

        1
---------
        1
        
1 row selected.

@, @@

Executes a script file. If the script file has the same extension as the one set in the SUFFIX system variable, the extension can be omitted. tbSQL finds the specified script file in the current directory.

System variables set with the SET command before executing the script remain in effect while executing the script. Use the EXIT or QUIT command in a script file to end tbSQL.

Same as the START command.

Syntax 

@ {filename}

Item

Description

filename

Script file name.

Example

SQL> @ run 
SQL> @ run.sql

/

Executes PSM programs or SQL statements that are stored in the SQL buffer.

Syntax

/

Example

SQL> SELECT * FROM DUAL;
..... SQL statement execution result  .....
SQL> /
..... The same result as above  .....

ACCEPT

Receives user input and saves it in a specified substitution variable. The value automatically replaces a value that matches &variable in an SQL statement or a PSM program.

Syntax

ACC[EPT] variable [FOR[MAT] format] [DEF[AULT] default] 
[PROMPT statement|NOPR[OMPT]] [HIDE]

Item

Description

variable

Substitution variable name. A new variable will be created if it does not exist.

FOR[MAT] format

Format of the substitution variable. If the value does not match the format, an error occurs.

DEF[AULT] default

Default value of the substitution variable.

PROMPT statement

Displays a prompt before receiving a user input

NOPR[OMPT]

Waits for a user input without displaying a prompt.

HIDE

Prevents to display a user input value.

Example

SQL> ACCEPT name PROMPT 'Enter name : ' 
Enter name : 'John'
SQL> SELECT &name FROM DUAL;
At line 1, column 8
old value : SELECT &name FROM DUAL 
new value : SELECT 'John' FROM DUAL

'JOHN'
------
John

1 row selected.

SQL>

APPEND

Appends user-entered text or statements to the SQL buffer.

Syntax 

A[PPEND] statement

Item

Description

statement

Text or statement to append to the SQL buffer.

Example

SQL> LIST 2
..... Select the second line .....
SQL> APPEND text
..... Add text to the second line .....

ARCHIVE LOG

Displays Archive log file information.

Syntax

ARCHIVE LOG LIST

Example

SQL> ARCHIVE LOG LIST

NAME                             VALUE
--------------------------       -----------------------------------------
Database log mode                Archive Mode
Archive destination              /home/tibero/database/tibero/archive
Oldest online log sequence	 300
Next log sequence to archive	 302
Current log sequence	         302

SQL>

CHANGE

Finds an old pattern in the current line of the SQL buffer and changes it to a new pattern. Typically, the current line of the last executed SQL statement is the last line. For information about how to change the current line, refer to the following example.

Syntax 

C[HANGE] delim old [delim [new [delim [option]]]]

  • Input

Item

Description

delim

Delimiter.

Digits and characters used for old or new patterns are not allowed.

old

Pattern to change. Not case-sensitive.

General words (for example, dual, ksc911) and '...' which means an arbitrary pattern can be used.

  • option

Item

Description

delim

Delimiter.

Digits and characters used for old or new patterns are not allowed.

new

New pattern.

option

  • g: Changes all old patterns in the current line.

  • c: Changes user-selected old patterns in the current line.

  • a: Changes all old patterns in the entire statement.

Example

Since the current line is always the last line by default, the DUAL in the second line is changed to T.

SQL> SELECT *
    FROM DUAL;
..... SQL execution results  .....
SQL> C/DUAL/T
    FROM T 
SQL>

To change the current line, enter the line number.

SQL> 5
    5 WHERE ROWNUM < 5 AND

An arbitrary pattern can be specified with '...'. It can be placed in the front, back, or middle of a word.

SQL> CHANGE /RE...AND/RE ROWNUM >= 5 AND/
    5 WHERE ROWNUM >= 5 AND
SQL> CHANGE /...AND/WHERE ROWNUM < 3/
    5 WEHRE ROWNUM < 3
SQL> CHANGE /WHE.../WHERE ROWNUM < 5 AND/
    5 WHERE ROWNUM < 5 AND

To change all old patterns in the entire statement, use the "a" option. The following changes * to the specified new pattern.

SQL> SELECT *
    FROM DUAL;
..... SQL execution results  .....
SQL> C/*/'replaced'/a 
    SELECT 'replaced' 
    FROM DUAL;
SQL>

CLEAR

Initializes or deletes specified data.

Syntax 

CL[EAR] [option]

  • option

Item

Description

BUFF[ER]

Clears the SQL buffer.

SCR[EEN]

Clears the screen.

COL[UMNS]

Initializes the display properties of all registered columns.

Example 

SQL> CLEAR BUFFER
SQL buffer is cleared 
SQL> CLEAR SCREEN
SQL> CLEAR COLUMNS

COLUMN

Specifies the display properties of a specified column. When the column name is specified, only the display properties of the column are displayed. Otherwise, those of all columns are displayed.

Syntax

COL[UMN] [name [option]]

  • input

Item

Description

name

Column name.

  • option

Item

Description

CLE[AR]

Initializes the column's display properties.

FOR[MAT] text

Sets the column format. For more information, refer to “Column Formats”.

HEA[DING] text

Sets the column heading.

NEW_V[ALUE]

variable

Sets the variable to save the column value.

WRA[PPED]

Wraps the text if the column data exceeds the limit.

TRU[NCATED]

Truncates the data if the column data exceeds the limit.

ON

Enables the display properties of the column.

OFF

Disables the display properties of the column.

Example

SQL> COLUMN
SQL> COLUMN empno
SQL> COLUMN empno CLEAR
SQL> COLUMN empno FORMAT 999,999 
SQL> COLUMN ename FORMAT A10
SQL> COLUMN sal HEADING the salary of this month 
SQL> COLUMN sal OFF
SQL> COLUMN job WRAPPED
SQL> COLUMN job TRUNCATED

CONNECT

Connects to the database as a specified user. If no user name or password is specified, tbSQL displays its prompt and requests the name or password.

Executing the CONNECT command commits the previously executed transaction, disconnects the existing connection, and then attempts to establish a new connection. Although it fails to establish the new connection, the previous connection is not recovered.

The following is the syntax.

Syntax

CONN[ECT] {username[/password[@connect_identifier]]}

Item

Description

username

User name.

password

Password for the user.

connect_identifier

Database connection information.

The information includes an IP address, a port number, and DB_NAME. It is set in the tbdsn.tbr file under the $TB_HOME/client/config directory. In Windows, it can be set in ODBC data source, and the source is searched first.

Example

SQL> CONNECT dbuser/dbuserpassword@db_id

DEFINE

Defines or displays substitution variables.

Syntax

DEF[INE] [variable]|[variable = value]

Item

Description

None

Displays all substitution variables.

variable

Substitution variable name.

variable = value

Name and default value of the substitution variable.

Example 

SQL> DEFINE NAME
..... Displays the substitution variable named NAME. .....
SQL> DEFINE NAME = 'SMITH'
..... Defines SMITH as the default value of the substitution variable. .....
SQL> DEFINE
..... Displays all substitution variables. .....

DEL

Deletes the line that is stored in the SQL buffer. If no line number is specified, all lines are deleted.

Syntax 

DEL [number|number number|number LAST|LAST]

Item

Description

number

Deletes the line with the specified number.

number number

Deletes all lines in the specified range.

number LAST

Deletes all lines from the line with the specified number to the last line.

LAST

Deletes the last line.

Example 

SQL> DEL 1
..... Deletes the first line. .....
SQL> DEL 1 3
..... Deletes all lines from the first line to the third line. .....
SQL> DEL 1 LAST
..... Deletes all lines from the first line to the last line. .....
SQL> DEL LAST
..... Deletes the last line. .....

DESCRIBE

Displays the column information of the specified object. An object can be a table, view, synonym, function, procedure, or package.

  • For tables and views, the column name, data type, constraint, index information, maximum length, precision, scale, and other items are displayed.

  • For functions and procedures, parameter information (name, data type, IN/OUT) are displayed. For packages, all details about the functions and procedures of the package are displayed.

  • The column information about an object owned by a specified user can also be displayed. If no username is specified, the current username is used by default.

  • Only the information belonging to the owner of a specified object is displayed. For tables, only the indexes of the table owner are displayed.

Syntax 

DESC[RIBE] [schema.]object_name[@dblink]

Item

Description

schema

Schema (or owner) that contains the target object.

object_name

Object to display column information for.

dblink

Database link that contains the target object.

Example 

SQL> DESCRIBE emp
SQL> DESC scott.emp
SQL> DESC scott.emp@gateway

DISCONNECT

Ends the connection to the current database. Executing this command commits currently running transaction but does not end tbSQL.

If a script file includes CONNECT but does not include DISCONNECT, a connection to a database remains open. Therefore, it is recommended to add DISCONNECT to a script file that includes CONNECT.

Syntax

DISCONN[ECT]

EDIT

Edits the contents of an SQL buffer or a certain file by using an external editor. An external editor can be specified in the $TB_EDITOR environment variable.

If $TB_EDITOR is not set, this command references $EDITOR. If neither variable is set, the vi editor is used. If the SQL buffer is empty, an error will be returned.

A file name can be specified without an extension if its extension is the same as the value set in the SUFFIX system variable. The default value of SUFFIX is .sql, and it can be changed with the SET command. tbSQL searches for the specified file in the current directory.

Syntax 

ED[IT] [filename]

Item

Description

None

Opens the contents stored in the current SQL buffer.

The default file (.tbedit.sql) is used.

This file will be deleted automatically when tbSQL ends.

filename

Name of the file to edit (usually a script file).

Example

SQL> EDIT run.sql 
SQL> EDIT run 
SQL> ED

EXECUTE

Processes PSM statements that are CALL statements or anonymous blocks. There must be a semicolon (;) at the end of a statement.

Syntax

EXEC[UTE] {statement}

Item

Description

statement

PSM statement.

Example

SQL> EXECUTE begin dbms_output.put_line('success'); end; 
success

PSM completed

SQL> EXECUTE call proc1();

This command is also useful when defining a value to a user-defined bind variable.

SQL> VAR x NUMBER;
SQL> EXEC :x := 5;

PSM completed

SQL>

EXIT

Ends tbSQL. (Same as the QUIT command.) Commits all running transactions and ends all database connections.

Syntax

EXIT [SUCCESS|FAILURE|WARNING|n|variable|:variable] [COMMIT|ROLLBACK]

Item

Description

SUCCESS

Returns 0 to indicate success.

FAILURE

Returns 1 to indicate failure.

WARNING

Returns 2 to indicate that tbSQL ends with a warning.

n

Integer exit code. The available code range depends on OS.

variable

Returns the value of a user variable defined with the DEFINE command or a system variable like SQL.SQLCODE. The variable must be a numeric type.

:variable

Specifies an exit code by using a bind variable defined with the VARIABLE command. The variable must be a numeric type.

COMMIT

Executes COMMIT before ending tbSQL.

ROLLBACK

Executes ROLLBACK before ending tbSQL.

EXPORT

Exports SELECT statement results or table data to a file with a format that tbLoader can recognize. The data can be exported in fixed format or variable format that uses column and row separators.

Syntax

EXP[ORT] {QUERY filename|TABLE [schema.]table} [variable_fmt|FIXED]

variable_fmt:
    [FIELDS {TERM[INATED] BY {,|text}|ENCL[OSED] BY {"|text}}] 
    [LINES TERM[INATED] BY {\n|text}]

Item

Description

filename

Name of the file to export. This name is used as a target table name in the control

file.

[schema.]table

Name of the table to export.

variable_fmt

  • FIELDS ...: Column separator for variable format.

  • LINES ...: Row separator for variable format.

  • FIXED: Fixed format indicator.

Example

The following creates t.csv (data file) and t.ctl (control file).

SQL> EXPORT QUERY 't.csv'
Ready to export to 't.csv' 

SQL> SELECT * FROM t;

10 rows exported.

The following exports data in variable format.

SQL> EXPORT QUERY 't.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
SQL> EXPORT QUERY 't.csv' FIELDS ENCLOSED BY '*'

HELP

Displays help information about commands including a specified word.

Syntax

H[ELP] [topic]

Item

Description

None

Displays help information about all tbSQL commands.

topic

Search word.

Example

SQL> HELP SET

HISTORY

Displays commands stored in the history buffer.

Syntax

HIS[TORY]

Example 

SQL> HISTORY
..... All commands stored in the history buffer .....

HOST

Executes an OS command. Same as the ! command.

Syntax

HO[ST] [command]

Item

Description

None

Entering the ! command without an OS command goes to the Windows prompt where multiple OS commands can be entered. To return to the tbSQL prompt, enter the EXIT command.

command

OS command.

INPUT

Adds a user-input SQL statement after the last line in the SQL buffer.

Syntax 

I[NPUT] [statement]

Item

Description

None

A multi-line SQL statement can be added.

statement

SQL statement to add.

Example 

SQL> select * from dual;
..... Outputs result .....
SQL> LIST
select * from dual SQL> INPUT where rownum < 2
select * from dual where rownum < 2
SQL>

The following specifies no option. Unlike the previous example, the SQL statement will be executed as soon as it is entered.

SQL> select * from dual;
..... Outputs result .....
SQL> INPUT
select * from dual
... Enter here. ...

LIST

Displays specified lines stored in the SQL buffer.

Syntax

L[IST] [number|number number|number LAST|LAST]

Item

Description

None

Displays all lines.

number

Displays the line with the specified number.

number number

Displays all lines in the specified range.

number LAST

Displays all lines from the line with the specified number to the last line.

LAST

Displays the last line.

Example 

SQL> LIST 1
..... Displays the first line. .....
SQL> LIST 2 3
..... Displays all lines from the second line to the third line. .....
SQL> LIST 2 LAST
.....Displays all lines from the second line to the last line. ......
SQL> LIST LAST
.....Displays the last line. .....

LOADFILE

Saves a Tibero table in the format that can be recognized by Oracle SQL*Loader.

Syntax

LOAD[FILE] {filename}

Item

Description

filename

File name without a file extension.

Example

The following saves the emp table in the format that can be recognized by Oracle SQL*Loader. This creates two files, emp.ctl and emp.dat.

SQL> LOADFILE emp
SQL> select * from emp;

LOOP

Repeatedly executes a statement. Enter <Ctrl>+C to end the loop.

Syntax

LOOP stmt

Item

Description

stmt

Statement to repeatedly execute.

Example 

SQL> LOOP select count(*) from v$session
..... The previous statement is executed every 1 second. .....
SQL> SET INTERVAL 10 SQL> LOOP ls
..... ls is executed every 10 seconds. .....

LS

Displays information about a user-created database object with a specific type or name.

Syntax

LS [object_type|object_name]

Item

Description

None

Displays all objects owned by the user.

object_type

One of:

  • FUNCTION

  • INDEX

  • PACKAGE

  • PROCEDURE

  • SEQUENCE

  • SYNONYM

  • TABLE

  • TABLESPACE

  • TRIGGER

  • USER

  • VIEW

object_name

Name of the object to display. The percent (%) sign can be used to indicate a temporary pattern.

Example

SQL> LS 
NAME		   SUBNAME		OBJECT_TYPE
---------------    -------------        ---------------
SYS_CON100	                        INDEX			 
SYS_CON400				INDEX
SYS_CON700				INDEX
_DD_CCOL_IDX1                           INDEX
......Omitted......				
UTL_RAW                                 PACKAGE 
DBMS_STATS                              PACKGE BODY
TB_HIDDEN2                              PACKGE BODY

SQL>
..... Displays all objects. .....

SQL> LS TABLESPACE 
TABLESPACE_NAME
-----------------------------------
SYSTEM 
UNDO 
TEMP 
USR
.....  Displays all objects with the TABLESPACE type. .....

SQL> LS USER 
USERNAME
------------------------------------
SYS
..... Retrieves users with a connection to the system. .....

PASSWORD

Changes the user password.

Syntax

PASSW[ORD] [username]

Item

Description

username

Username to change the password for. If not specified, the currently connected user is specified.

Example 

SQL> PASSWORD
Changing password for 'TIBERO'

Enter old password: ...Enter the current password...
Enter new password: ...Enter a new password...
Confirm new password: ...Re-enter the new password...

Password changed successfully. 
SQL>

PAUSE

Pauses the execution until the user presses the <Enter> key.

Syntax 

PAU[SE] [message]

Item

Description

message

Message to display when the user presses the <Enter> key.

Example

SQL> PAUSE please enter... 
please enter...
..... Press the <Enter> key. .....
SQL>

PING

Displays whether a specified database is accessible.

Syntax 

PING connect_identifier

Item

Description

connect_identifier

Name of the database to access.

Example

SQL> PING tibero7 
Server is alive.

SQL>

PRINT

Displays the name and value of user-defined bind variables.

Syntax

PRI[NT] [variable...]

Item

Description

Displays all bind variables.

variable

Bind variable names.

Example 

SQL> VARIABLE x NUMBER 
SQL> EXECUTE :x := 5;
SQL> PRINT x

        x
---------
        5
SQL>

PROMPT

Displays a specified message or an empty line.

Syntax

PRO[MPT] [message]

Item

Description

None

Displays an empty line.

message

Message to display.

Example

The following is an example of the externally written SQL file named PromptUsage.sql.

PROMPT >>> Test is started. 
CREATE TABLE T (c1 NUMBER); 
INSERT INTO T VALUES (1);
PROMPT Value 1 is inserted. 
COMMIT;
PROMPT <<< Test is ended.

The following shows the execution result of PromptUsage.sql.

SQL> @PromptUsage
>>> Test is started.
Table 'T' created.
1 row inserted.
Value 1 is inserted.
Commit succeeded.
<<< Test is ended.
File finished.

SQL>

QUIT

Ends tbSQL. Commits all running transactions and ends all database connections. (Same as the EXIT command.)

Syntax

Q[UIT] [SUCCESS|FAILURE|WARNING|n|variable|:variable] [COMMIT|ROLLBACK]

RESTORE

Restores user-selected data from a specified file.

Syntax

REST[ORE] HIST[ORY] filename[.ext]

Item

Description

filename[.ext]

Name of the file to read. If the file extension is omitted, the SUFFIX value is used.

Example

SQL> RESTORE HISTORY history.sql

RUN

Executes PSM programs or SQL statements that are stored in the SQL buffer. Same as the / command, but displays statements stored in the buffer.

Syntax

R[UN]

Example

SQL> select 1 from dual;

        1
---------
        1

1 row selected.

SQL> RUN
     1 select 1 from dual 1
     
        1
---------
        1 

row selected.

SAVE

Saves user-selected information to a specified file.

For more information about SAVE CREDENTIAL, refer to “Encrypting Connection Information”.

Syntax

SAVE CRED[ENTIAL] [filename]
SAVE HIST[ORY] filename[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Item

Description

CREDENTIAL

Encrypts and saves the database connection information. It no filename is specified, it is saved to the file set in ISQL_WALLET_PATH.

HISTORY

Saves command history. If the file extension is omitted, the value set in SUFFIX is used.

  • CREATE: Creates a file if the specified file does not exist, or causes an error if the file exists. (Default value)

  • REPLACE: Creates a file if the specified file does not exist, or overwrites the file if it exists.

  • APPEND: Creates a file if the specified file does not exist, or appends new data to the file if it exists.

filename

File to save to.

Example 

SQL> SAVE CREDENTIAL
SQL> SAVE CREDENTIAL "./wallet.dat" 
SQL> SAVE HISTORY history.sql
SQL> SAVE HISTORY history.sql APPEND

SET

Sets tbSQL system variables. The variable values can be checked with the SHOW command. However, the changed system variable is valid only in the current session.

For detailed information about system variables, refer to “System Variables”.

Syntax

SET {parameter} {value}

Item

Description

parameter

Variable name

value

Variable value

Example

SQL> SET AUTOCOMMIT ON

SHOW

Shows tbSQL system variables.

Syntax

SHO[W] {option}

  • option

    Item

    Description

    system_parameter

    Displays the tbSQL system variable with the specified name.

    ALL

    Displays all tbSQL system variables.

    ERROR

    Displays PSM program errors that occurred previously.

    PARAM[ETERS] [name]

    Displays the database system variable with the specified name. If name is omitted, all the system variables are displayed.

    RELEASE

    Displays the release information of tbSQL.

    SQLCODE

    Displays the SQLCODE for the most recently executed SQL.

Example

SQL> SHOW autocommit 
SQL> SHOW all
SQL> SHOW error
SQL> SHOW param db_name 
SQL> SHOW release
SQL> SHOW SQLCODE

SPOOL

Saves all screen outputs to a file in the current directory. The results of the HOST and ! commands are excluded.

Syntax

SP[OOL] [filename [APP[END]]|OFF]

Item

Description

None

Displays the current execution state of this command.

filename

Name of the file to save the output.

APP[END]

Option to add the output to the end of the output file.

OFF

Stops spooling.

Example

SQL> SPOOL report.txt 
SQL> SPOOL OFF

START

Executes a script file. (Same as the @ command.)

Syntax

STA[RT] {filename}

Item

Description

filename

Script file name.

TBDOWN

Ends Tibero database. Select one of four options depending on the urgency. Some options require recovery process when rebooting the database.

To execute this command, connect to the database as SYSDBA or SYSOPER.

Syntax

TBDOWN [NORMAL|POST_TX|IMMEDIATE|ABORT]

Item

Description

NORMAL

Waits until all users end their connections. (Default value)

POST_TX

Waits until currently running transactions are ended.

IMMEDIATE

Rolls back currently running transactions and then forcibly ends the database.

ABORT

Ends the database immediately without rolling back currently running transactions.

Example 

SQL> TBDOWN
SQL> TBDOWN ABORT

UNDEFINE

Deletes substitution variables defined with the ACCEPT or another command.

Syntax

UNDEF[INE] [variable...]

Item

Description

None

Deletes all substitution variables.

variable...

Substitution variable names.

Example

SQL> UNDEFINE x
SQL> UNDEFINE x y z

VARIABLE

Declares user bind variables that are available in PSM programs or SQL statements.

Syntax

VAR[IABLE] [variable [datatype]]

Item

Description

None

Displays all bind variables.

variable

Bind variable name

datatype

Data type. Options are:

  • NUMBER

  • CHAR(n)

  • VARCHAR(n)

  • VARCHAR2(n)

  • NCHAR(n)

  • NVARCHAR2(n)

  • RAW(n)

  • BLOB

  • CLOB

  • NCLOB

  • DATE

  • TIMESTAMP

  • REFCURSOR

Example

SQL> VARIABLE x NUMBER
SQL> EXEC :x := 1;

PSM completed.

SQL> SELECT :x FROM DUAL;
        :x
----------
         1
1 row selected.
SQL>

WHENEVER

Defines tbSQL action to take when an error occurs.

Syntax

WHENEVER {OSERROR|SQLERROR}
    {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:variable] | 
    CONTINUE [NONE|COMMIT|ROLLBACK]}

  • clause1

Item

Description

OSERROR

Performs the specified action for OS errors from the system on which tbSQL is running.

SQLERROR

Performs the specified action for errors that occur while executing SQL statements. tbSQL errors are ignored.

  • clause2 (Default value: CONTINUE)

Item

Description

EXIT

Ends the program when an error occurs.

For information about return codes, refer to the EXIT command.

CONTINUE

Continues on to the next command when an error occurs.

  • NONE: Does not process the transaction. (Default value)

  • COMMIT: Commits the transaction.

  • ROLLBACK: Rolls back the transaction.

Example

SQL> whenever sqlerror exit failure rollback 
SQL> select 1 from no_such_table;
TBR-8033: Specified schema object was not found. 
at line 1, column 16:
select 1 from no_such_table
$ echo exit code: $? 
exit code: 1
$

Culumn Formats

This section describes data types for tbSQL columns.

The column format can be specified and displayed using the COLUMN command.

Character Type

The length of CHAR, NCHAR, VARCHAR, and NVARCHAR types is the same as that of a database column by default. If data length is greater than column length, the data is written in the next line or truncated. This issue can be easily handled with a character type format.

Syntax

COL[UMN] {name} FOR[MAT] A{n}

Item

Description

name

Column name.

A{n}

A can be replaced with a. n indicates the length of character data.

Example

SQL> SELECT 'Tibero is the best choice' test FROM DUAL; 

TEST
--------------------------
Tibero is the best choice

1 row selected.

SQL> COL test FORMAT a10
SQL> SELECT 'Tibero is the best choice' test FROM DUAL; 

TEST
----------
Tibero is
the best c 
hoice

1 row selected.

SQL>

Numeric Type

The following describes how to set a numeric column format.

Syntax

col_name} FOR[MAT] {fmt_str}

  • input

Item

Description

col_name

Column name.

fmt_str

Column formats described in the following table.

  • fmt_str format The following shows formats that can be specified in fmt_str.

Format

Example

Description

Comma (,)

9,999

Displays a comma (,) at the specified location.

Period (.)

9.999

Displays a period (.) to separate integer and decimal parts.

$

$9999

Displays $ at the beginning.

0

0999, 9990

Displays 0 at the end.

9

9999

Displays as many digits as the specified number of digits.

B

B9999

If the integer part is 0, it is replaced with a blank space.

C

C9999

Displays ISO currency symbol at the specified location.

D

9D999

Displays a decimal character to separate the integer and decimal parts of a real number.

EEEE

9.99EEEE

Displays scientific notation.

G

9G999

Displays a group separator at the specified location of the integer part.

L

L9999

Displays the local currency symbol at the specified location.

MI

9999MI

Displays a minus sign after a negative number or a blank space after a positive number.

PR

9999PR

Displays a negative number enclosed in '<' and '>' (angle brackets) or a positive number enclosed in blank spaces.

RN

RN

Displays as uppercase roman numerals.

rn

rn

Displays as lowercase roman numerals.

S

S9999, 9999S

Displays the positive or negative sign at the beginning or end.

TM

TM

Displays the smallest number.

U

U9999

Displays the dual currency symbol at the specified location.

V

99V999

Displays the value multiplied by 10n.

n is the number of 9 that comes after V.

X

XXXX, xxxx

Displays in hexadecimal format.

Example

SQL> COLUMN x FORMAT 999,999 
SQL> SELECT 123456 x FROM DUAL;

        X
----------
  123,456

1 row selected.

PreviousUtility GuideNexttbExport

Last updated