The SQL Sheet provides three types of commands. As appropriate, the commands can be started through the menu bar, the context menus, or you can type the commands directly in the expression input box.
Standard commands are: COMMIT, ROLLBACK, SELECT, INSERT, UPDATE, DELETE, CREATE, DROP.
SQL Sheet uses transactions, and the COMMIT is executed either automatically when you quit the SQL Sheet, or when you start it explicitly through the Execute menu Commit.
DEFINE <variable name>—Creates a user defined variable. Note that in SQL Sheet, the command must be finished with a ";".
DESC <table name>—Displays the table definition in the Executed script output box.
SPOOL commands—See SQL Assistant Menu Bar.
SPOOL <file name>—Creates a spool file with the given name.
SET HEADING ON/OFF— Switches off the headings of the output result. For example, use SET HEADING OFF and Set ECHO OFF if you want to create a script from the output, and you only need the data without any comments.
ABORT—Ignores all select statement that follow this command. For example, if you have several select statements, use this command to execute only the first part.
CONNECT—Connects to the database. Optionally, use parameters, for example CONNECT <user name/password>, or CONNECT <user name/password@service>.
EXPORTDUMP <user name> <filename>— Creates a (*.dmp) file. To specify the export options, click Tools menu Options, and set the CommandLineDumpExport option.
EXCEL <select * from <table name>>–Opens MS Excel and displays the result of the select statement in an (*.xls) file. Use the command EXCELFILE <file name> <select * from <table name>> to store the result of the select statement in a file without opening the Excel file.
ASCIIFILE <file name> <select statement>–Stores the result of the select statement in a TAB separated ASCII (*.txt) file.
GRID select * from <table name>–Displays the result in a grid dialog box. You can edit the values.
REM–Marks a comment. Or enter “--”
FIND <value> [,Filter]–Searches all columns of all tables for the given value. Optionally, set a filter.
CLS or CLEAR–Clears the output window.
EXECUTE <procedure name>– Executes a stored procedure.
SELECTDOUBLES <select statement> CONDITION <condition> KEY <key>–Finds duplicate values. For example: SELECTDOUBLES * from MY_FEATURE condition TB_POINT_NUMBER KEY FID <condition> contains the column names to be analyzed. <key> contains the primary key.
DELETEDOUBLES <select statement> CONDITION <condition> KEY <key>–Deletes duplicate entries.
SETDELETEDDOUBLES <...> CONDITION <condition> KEY <key>–Use this command for previous versions. Sets the attribute DELETED to 1 for duplicated values. Example: SETDELETEDDOUBLES * from MY_FEATURE CONDITION attribute1,attribute2.
SHELL Filename [,wait]–Executes a system command. Example: SHELL notepad.exe. Example: SHELL notepad.exe,true.
EXPLAINPLAN <select * from <table name>>–Find the execution plan of a select statement, so that you can analyze which indexes are used, and find out how to optimize the select statement.
REPEAT <...> FOR <...>— Repeats a select statement, and inserts parameters. The first select statement is repeated, and the values from the second select statement are inserted into the first select statement. The results are stored in a temporary table TB_SQL_RESULT.
GATHERTABLESTATS—Collects statistical information for all tables of a user. The command executes the following: BEGIN DBMS_STATS.GATHER_TABLE_STATS('username', 'tablename', cascade=>TRUE); END;
PACKDATA <select expression>—Writes all column values of a child table to a column PACKDATA1 of a parent table. The column PACKDATA1 is of the data type VARCHAR2. Example: packdata select p.fid, c.name from parent p, children c where p.fid=c.FID_parent order by p.fid;
PACKDATA2—Writes all column values of a child table to a column PACKDATA2 of a parent table. The column PACKDATA2 is of the data type CLOB. Use this command for large data sizes.
COMPDBWITH <user name>—Compares the data structure of the current Oracle user with another user, and displays the differences.
DEF <table name>—Displays the table definition in the Executed script output box. The command displays more details that the DECSC command.