Sybernet / Supplied Procedures Reference Release 3.00 December 30, 2007

HTTP.SP_HTML_ISQL

The Sybernet Interactive SQL and Editor allows you to execute SQL and edit objects in your database. The Navigation window (or frame) allows you to select schemas and objects. The Input window (consisting of the ISQL, Editor, and Grants window) is where you submit commands (SQL or DDL) to Oracle. The Output window (shared by all) is where the output of your results are displayed.

Batch commands can be separated by go or a forward slash. The former is a Sybase convention; the latter is an Oracle convention. Comments (either C-style comments or PL/SQL comments) are unconditionally stripped from the beginning of each command. Redundant batch separators (go or /) are allowed. GO immediately followed by an exclamation mark (go!) means continue to the next command even if the last command failed.

To save real estate the standard menu bar is hidden away in its own frame at the bottom of this screen. Just click on this frame--if you can find it--and drag upwards until the menubar is displayed. The Cancel button is especially useful when you need to terminate a query from hell or a stored procedure that is looping.

The Navigation Window

The left most frame is used to select schemas or owners and objects from your database. At the very top is a drop-down list of those schemas that actually own something, but always includes your schema whether or not you actually own anything. To change schemas, simply select that owner from this list of schemas.

Changing schemas in Oracle is not the same as changing databases in Sybase. If you submit an object from another schema and you do not prepend the object name with the appropriate schema name, that object will be created in your schema.

The rest of this window contains a list of object types. Each object type is preceded by a checkbox that when checked indicates you selected this type. Clicking an object type will list the objects of that type in this window. All of these work the same except for Packages (not to be confused with package bodies). When you click Packages, not only are the package specifications displayed, but also each subprogram in that package. The purpose here is to allow you to select just the prototype that you are interested in.

The checkbox next to each object name can mean several things. If it is a permission, it means you have this permission. If it is a role, it means you have this role. If it is a stored procedure, it means this procedure is registered in Sybernet.

The ISQL Window

This window is used for entering adhoc SQL statements. When an object is selected, it too is loaded into this window suitable for execution. That is, if you select a table, its SELECT statement is built; If you select a procedure, its run statement is built. Some objects (like database links or packages) can't be executed. In this case, the window is cleared.

Anytime you submit a query with this window that query is saved in the history list. There are buttons on this screen that allow you to scroll forwards or backwards through this history.

Format

Submit

|<

<

>

>|

Xref

Find

Editor

The Editor Window

This window is used to edit an object in your database. The object can be dropped. The object can be executed in its own window if it is a procedure or subprogram. You can also replace text in this window with the Replace button.

Typically, only stored procedures with default null parameters are executed in the editor. Use the ISQL window for any other combinations.

Undo

Submit

Replace

Copy

Drop

Exec

Xref

Find

Grants

The Grants Window

Whenever an object is loaded, its permissions are loaded in this window. Although informational in purpose, you can edit this screen and submit those changes to your database.

Undo

Submit

Xref

Find

Grants

Find Dialog

This dialog searches for objects, columns, and parameters under the current schema. In general, your search is case insensitive. If searching for literal text, you will want to include the appropriate wild-card characters. If your search contains a name and value pair (separated by a relational operator), you can also search for any table containing that column (the name) that contains (or doesn't contain) this value. If the column name is case-sensitive, the name part should be double-quoted in the usual way. The value part can also be double-quoted if the search target is case-sensitive. If the name part is the keyword TEXT, you are also telling it to search for any source text containing this text or pattern. The table below illustrates the various search modes.

PLAN_TYPE

%PLAN%

PLAN_TYPE = 10

PLAN_TYPE = %10

%PLAN% = 10

"Plan_Type" = "10"

TEXT = PLAN_TYPE=10

TEXT = %PLAN_TYPE%=%10%

The following relational operators are allowed:

• =
• <
• >
• <=
• >=
• <>
• !=

There is no LIKE operator. The search function assumes anything containing a percent sign (%) or underscore (_) is to be searched using a LIKE comparison. If you really want to search for a percent sign or underscore, escape these characters by preceding them with a back slash (\); for example, a search for PLAN_TYPE = \%10 means you are looking for a PLAN_TYPE containing %10.

Source objects can only be searched with equality. Since it doesn't make sense to search for TEXT < ABC, this expression is ignored.

While it seems natural to you to specify PLAN_TYPE = '10,' don't do this because PLAN_TYPE could be a varchar, date, or number. Just let the search function figure out what is to be compared. Unless you really want to search for single-quotes, you should really search for PLAN_TYPE = 10.

You can optimize your search by specifying a case-sensitive search. Searching for PLAN_TYPE = 10 (for example) when you know that PLAN_TYPE is uppercase and 10 is uppercase can be optimized by specifying "PLAN_TYPE" = "10" instead of PLAN_TYPE = 10. For large databases, this will make a real difference.

Replace Dialog

Clicking the Replace button in the Editor window allows you to replace some or all text in this window. Options exist for performing a literal or case-sensitive search. You simply enter what you want to search for and what you want to replace this text by. Some options do not work in MicroSoft Internet Explorer or any Netscape compatible less than 7.0.

Literal

Entire Word

Selection Expression

Case Sensitive

Search Backwards

Wrap-around Search

Replace All

Find

Cancel

Replace

Format Options

SQL commands executed from the ISQL window can be formatted in several flavors. Most of the time the Preformatted format is what you want to use. This is especially true when selecting columns from a table or view, or when writing output using http.write() and http.writeln(). When executing a stored procedure or subprogram that produces HTML, the Suppressed option is the best choice. But if you need a quick and dirty way to send your query to Excel or displayed as comma separated data, this drop-down list can be your friend

Comma Separated

Echo

Excel

HTML Table

Preformatted

Suppressed

See Also