| Oracle® HTML DB User's Guide Release 1.5 Part Number B10992-01 |
|
|
View PDF |
| Oracle® HTML DB User's Guide Release 1.5 Part Number B10992-01 |
|
|
View PDF |
This section provides information on how to use SQL Workshop to view and manage database objects as well as browse the data dictionary.
This section contains the following sections:
You can use SQL Workshop to view and manage database objects from a Web browser. SQL Workshop includes the following navigation tabs:
SQL Workshop. Offers quick access to the SQL Command Processor, SQL Script Repository, and Database Browser. Includes links to the Query By Example, Generate DDL (data definition language), and Create Table wizards.
SQL. Access the SQL Command Processor to run SQL or PL/SQL statements.
Scripts. Access the SQL Script Repository and Control Files Repository.
Browse. Use the Database Browser to view existing database objects and the data dictionary.
To access SQL Workshop:
Click the SQL icon. (See Figure 5-1).
Under SQL Workshop, select the appropriate link, or click the SQL, Scripts, or Browse tabs at the top of the page.
Oracle HTML DB is a browser based development environment which communicates over HTTP. Because HTTP is a stateless protocol, any command you issue using SQL Workshop is automatically followed by a database COMMIT. There is no support for transactions that span multiple pages in the SQL Workshop. For example, it not possible issue an UPDATE statement on one page in the SQL Workshop and then revert it on a subsequent page using a ROLLBACK command.
Since the commands COMMIT, ROLLBACK and SAVEPOINT are executed as one transaction, you can include these commands in SQL Workshop by using scripts
SQL Workshop does not support SQL*Plus commands. If you attempt to enter a SQL*Plus command in SQL Workshop an error message displays. The following are examples of unsupported SQL*Plus commands:
SET ECHO OFF SET ECHO ON SET VERIFY ON SET LONG 600 COLUMN dummy NOPRINT COLUMN name FORMAT A20 DEFINE ACCEPT PROMPT REMARK SHOW
You can use SQL Workshop to view database objects. For example, you can view details about database objects by querying the Oracle dictionary. You can also run SQL commands and SQL scripts in the SQL Command Processor or view database objects in the Database Browser.
Topics in this section include:
You can use the SQL Command Processor to run SQL commands and SQL scripts on any Oracle database schema for which you have privileges.
To use the SQL Command Processor:
Click the SQL icon and select the SQL tab.
The SQL Command Processor appears.
Select a schema from the list and follow the on-screen instructions.
To run entered commands, click Run SQL.
To save entered commands, click Save.
|
See Also: "Accessing Saved Commands in the SQL Archive" for more information on viewing saved commands and queries |
You can terminate commands in the Command Processor using either a semicolon (;) or forward slash (/). Consider the following examples:
INSERT INTO emp
(50,'John Doe','Developer',10,SYSDATE,1000,10);
INSERT INTO emp
(50,'John Doe','Developer',10,SYSDATE,1000,10)
/
The first example demonstrates the use of a semicolon (;). The second example demonstrates the use of forward slash (/).
You can use the Data Browser to view database objects. To find a database object, select the schema you would like to view. The values available in the schema depend upon your resource privileges.
To browse database objects:
Click the SQL icon and select the Browse tab.
The Data Browser appears.
To view details about a specific object, click the view icon.
To search for database objects:
Click the SQL icon.
Select the Browse tab and then Search Objects.
Enter search criteria in the fields provided and click Go.
You can search for columns within tables or text within source code by entering a search string in the Search field and selecting a Search Option at the top of the page. Searches are case insensitive and no wildcards or quotes are necessary.
Once you have located a specific table you can query the Oracle data dictionary to discover more details.
To Query by Example:
Click the SQL icon and select the Browse tab.
The Data Browser appears.
To view details about a specific object, click the view icon.
The Object Detail appears.
Select Query by Example from the Tasks list.
Follow the on-screen instructions.
You can also use the Data Browser to view database objects by type.
To view database objects by object type:
Click the SQL icon.
Under the Data Browser, select an object type.
The Data Browser appears.
To view details about a specific object, click the view icon.
Follow the on-screen instructions.
You can use SQL Workshop to manage database objects. For example, you can create new database objects, manage script files and control files, or alter a table.
Topics in this section include:
You can use the Data Browser to view existing database objects.
To view or edit existing database objects:
Click the SQL icon.
s, select the type of database object you would like to view.
To search for an object, select a schema, an object type, type a search string in the Search field, and click Go.
To view object details, click the view icon adjacent to the appropriate name.
Optionally, select a task from the Tasks list on the right side of the page.
You can create new database objects using the Create Database Object Wizard.
To create new database objects in SQL Workshop:
Click the SQL icon.
From the Tasks list on the right side of the page, select Create a database object.
The Create Database Object Wizard appears.
Follow the on-screen instructions.
You can drop database objects using the Drop Database Object Wizard. When you drop a table using this wizard, you also remove all related triggers and indexes.
To drop a database object:
Click the SQL icon.
From the Tasks list on the right side of the page, select Drop database object.
The Drop Database Object Wizard appears.
Select a schema and then an object type.
Follow the on-screen instructions.
If you are running Oracle HTML DB with an Oracle 10g database, you can use the Recycle Bin to view and restore dropped database objects. When you drop a table, the space associated with the table is not immediately removed. Oracle renames the table and places it and any associated objects in the Recycle Bin where it can be recovered at a later time.
|
Note: The Recycle Bin feature is only available if you are running Oracle HTML DB with an Oracle 10g database. |
To use the Recycle Bin:
Click the SQL icon.
Under SQL Workshop, click Recycle Bin.
The Recycle Bin appears.
To search for an object, select a schema, an object type, type a search string in the Search field, and click Go.
To view object details, click the view icon adjacent to the appropriate name.
On the Object Summary page you can:
Click Restore Object to restore the current object
Click Purge to permanently delete the current object
To empty the Recycle Bin without viewing the objects:
Click the SQL icon.
Under SQL Workshop, click Recycle Bin.
The Recycle Bin appears.
From the Tasks list on the right side of the page, select Purge Recycle Bin.
You can use the SQL Script Repository to view, edit, and run uploaded script files. For example, you can upload new script file as well as create and edit your create table, create index, and create PL/SQL package scripts.
Topics in this section include:
To view scripts in the SQL Script Repository:
Click the SQL icon and select the Scripts tab.
SQL Script Repository appears. Scripts are stored based on HTML DB username.
To search for a script, select a username from the Show list, enter a search string in the Find field (optional), and click Go.
While in the Script Repository you can:
Reorder a list by clicking the column heading
View details about a specific file by clicking the view icon
Edit a script by clicking the edit icon
Parse a script to be run by clicking Parse
Run a script by clicking Run in the Actions column
Delete a script by selecting it and clicking Delete Checked
Upload a script by clicking Upload
Create a script by clicking Create
In the Script Repository, click the view icon.
The Script - Files Details page appears.
Under View Links, you can:
Click Native file format to download the file locally
Click View document as text to view the file in your Web Browser
Click Parse this script to parse the script to run
To run a script in the Script Repository:
In the Actions column, click Run.
The Script - Run page appears.
If you have parameters in your script you must define them. You can define up to ten different parameters in each script.
Enter a parameter name and value in the fields provided.
To view the script file, click View File.
To run the script file, click Run Script.
The Script - Run Results page appears displaying the number of success, failure and the elapsed time. RED indicates that errors occurred while executing the file.
To view the script file source, click View Source.
To run the file again, click Run Script in the left navigation pane.
Once you have run a script file, you can view a history of previous executions by clicking Previous Runs on the Script - Run page.
To delete a script file from the Script Repository:
In the Script Repository, select the script to be deleted.
Click Delete Checked.
To upload a script file into the Script Repository:
In the Script Repository, click Upload.
The Upload Script page appears.
Follow the on-screen instructions.
If the script file you upload has a valid file extension, SQL Workshop recognizes the file as a script and automatically parses it. Table 5-1 describes the file extensions SQL Workshop considers to be valid for script files.
Table 5-1 Valid Script File Extensions
| Extension | Description |
|---|---|
| pkh | Package headers |
| plb | Package bodies |
| sql | Scripts |
| con | Constraints |
| ind | Indexes |
| sqs | Sequences |
| tab | Tables |
| trg | Triggers |
| pkb | Package bodes |
| pks | Package specs |
To create a script file while in the Script Repository:
In the Script Repository, click Create.
The Create Script page appears.
Follow the on-screen instructions.
You can parameterize a script using a pound (#) or ampersand (&). The following two examples demonstrate valid parameter syntax.
CREATE TABLE #OWNER#.xyz (X INT) / CREATE TABLE #OWNER#.abc (Y NUMBER) / CREATE TABLE &OWNER.xyz (X INT) / CREATE TABLE &OWNER.abc (Y NUMBER) /
If you include SELECT statements in a script, the script will run without errors, but the result set will not display.
You can export SQL Script Repository scripts using the Export Import Wizard in Application Builder.
To export a script from SQL Workshop:
Click the Build icon.
When Application Builder appears, click Export/Import.
The Export/Import Wizard appears.
Select Export and click Next.
Click the Export Script tab and follow the -screen instructions.
If you export a UNIX format, the wizard generates a file with rows delimited by CHR (10) (that is, line feeds). If you export a DOS format then each row is terminated with CHR(13)||CHR(10) (that is, CR LF or carriage return line feed).
When you click Save in SQL Command Processor, SQL Workshop saves entered commands and scripts to the SQL Archive.
SQL Archive is different from SQL Script Repository. By saving frequently used SQL commands in the SQL Archive, you can run the commands again without retyping. When you save a SQL command to SQL Archive, the saved command does not appear in Script Repository.
To view the SQL Archive:
Click the SQL icon.
Select the SQL tab and select Archive.
The SQL Archive appears.
Follow the on-screen instructions.
SQL Command History displays the 200 most recent commands and scripts run in the SQL Command Processor.
To view the SQL Command History:
Click the SQL icon.
Select the SQL tab and select History.
SQL Command History appears.
To run a command again, click the appropriate link.
The SQL command or the script displays in the SQL Command Processor.
You can use DDL statements to create, alter, and drop schema objects when they are no longer needed. You can also use DDL statements to grant and revoke privileges and roles, to analyze table, index, or cluster information, to establish auditing options, or to add comments to the data dictionary.
To generate DDL statement in SQL Workshop:
Click the SQL icon.
Under SQL Workshop, click Generate DDL.
The Generate DDL Wizard appears.
Follow the on-screen instructions.
|
See Also:
|
Control files enable you to run a series of scripts in a predefined order. From the Control Files Repository, you can create, edit, delete or run control files.
To access the Control Files Repository:
Click the SQL icon.
Select the Scripts tab and then Control Files.
Control Files Repository appears.
To search for a script, select a username from the Show list, enter a search string in the Find field (optional), and click Go.
While in the Control Files Repository you can:
Reorder a list by clicking the column heading
Edit a file by clicking the edit icon
Run a file by clicking Run
Delete a script by selecting it and clicking Delete Checked
In the Control Files Repository, click Create.
The Control File Create page appears.
Enter a name for the control file, select the script files you would like to include, and click Create.
Specify the execution order and click Done.
In the Control Files Repository, click the edit icon.
The Edit File page appears.
On the Edit File page you can:
Change the order in which files are executed by clicking Edit Execution Order
Add additional script files by clicking Add More Files
Delete a script by selecting it and clicking Delete Checked
To run a control file in the Control Files Repository:
In the Action Column, click Run.
The Run File page appears.
Select an Oracle Schema from the Parse As list.
If your script files include parameters you must define them. You can define up to ten different parameters in each script.
Enter a parameter name and value in the fields provided.
Click Run File.
The Run Results page appears displaying the number of success, failures, and the elapsed time. RED indicates that errors occurred while executing the file.
If you wish to run the control file in the background, select Run in Background. Running a control file in the background means that Oracle HTML DB submits it as a job. The advantage of this approach is that you do not have to wait for it to finish to continue using SQL Workshop.
To run the file again, click Run File in the left navigation pane.
Once you have run a file, you can view a history of previous executions by clicking Previous Runs on the Run File page.
To view the Control File Run History:
Run the control file as described in the previous procedure.
Click Run Script in the left navigation pane.
On the Script - Run page, click Previous Runs.
You can also use SQL Workshop to create new tables or edit existing tables.
To create a new table:
Click the SQL icon.
Under SQL Workshop, click Create Object.
The Create Table Wizard appears.
Select an object and click Next.
Follow the on-screen instructions.
To edit an existing table, you must first navigate to it using the Data Browser.
Click the SQL icon.
Under Data Browser, select Tables.
To search for a table, select a schema, table type, type a search string in the Search field, and click Go.
To view table details, click the view icon adjacent to the appropriate table name.
Select task from the Tasks list on the right side of the page.
UI (user interface) defaults enable developers to assign default user interface properties to a table, column, or view within a specified schema. When a developer creates a form or report using a wizard, the wizard uses this information to create default values for region and item properties.
Note that UI defaults are associated with a table and can be used in an application created from a form and report wizards. This means that you cannot use shared list of values to define UI defaults, since shared list of values are associated with a single application.
Topics in this section include:
|
See Also: "Application Builder Concepts" and "Using Application Builder" for more information on regions and item properties |
To view tables using UI Defaults:
Click the SQL icon.
Select the Browse tab and then UI Defaults.
To edit the UI Defaults associated with a specific table, click the edit icon next to the table name.
The following table-level UI defaults display at the top of the page:
Schema is the schema that owns the table.
Table Name is the name of the selected table.
Title is a modified version of Table Name in which the first letter is capitalized and any underscores are replaced with spaces.
Column-level UI Defaults appear next. You can edit attributes for all displayed columns, by clicking Grid Edit.
To edit a specific column, click the edit icon adjacent to the column name.
The Edit Column-level UI Defaults page appears.
The top of the Column-level UI Defaults page displays the table and column name. Column Name is the name of the selected column. Use Default for Label to specify a label for reports and forms. By default, this field displays a modified version of Column Name in which the first letter is capitalized and any underscores are replaced with spaces. Default attributes for reports and forms appear next.
Available Display for Reports attributes include:
Display - Indicates whether the column displays in a report. The default is Yes.
Display Seq - Specifies the display sequence of items in a report. The default value is based on the column ID, which is based on the order of the columns in the table.
Mask - Indicates if a mask should be applied against the data. Not used for character based items.
Alignment - Specifies report alignment (Left, Center, or Right). If the column is a number, the default is Right. Otherwise, the default is Left.
Searchable - Indicates whether the column should be searchable in reports. If the column is VARCHAR2 or CHAR, the default is Yes. If not, the default is No.
Group By - Indicates whether this column should be used for Group By and then the sequence of the grouping. The default is Yes.
Available Defaults for Reports attributes include:
Display - Indicates whether this column displays in a form. The default is Yes.
Display Seq - Specifies the sequence of items in a form. The default is based on the column ID, which is based on the order of the columns in the table.
Display As - Indicates how items in a form display. The default selection is Text Field.
Mask - Indicates a mask to be applied against the data in a form. Not used for character based items.
LOV Query - Generates a LOV (list of values). Only valid for certain Display As types.
Default Value - Specifies the default value associated with this column.
Width - Specifies the display width.
maxWidth - Specifies the maximum string length a user is allowed to enter in this item.
Height - Specifies the display height of an item.
Required - Used to generate a validation in which the resulting item must not be null. If resulting item is not null, select Yes.
Help Text - Becomes Item help. By default, this text is pulled from the column hint (if applicable).
You can view a listing of tables without UI Defaults on the Default Table & Column-level UI Defaults page.
To view the Default Table & Column-level UI Defaults page:
Click the SQL icon.
Select the Browse tab and then UI Defaults.
From the Tasks list, select Apply UI Defaults.
Follow the on-screen instruction.
When you export UI Defaults, all UI Defaults for the selected schema are exported to a single SQL*Plus script. When prompted by your browser, save this file to your hard drive. The file contains an API call to create table hints by making calls to the application PL/SQL API. All work is performed in a single transaction.
To export UI Default HInts:
Click the SQL icon.
Select the Browse tab and then UI Defaults.
From the Tasks list, select Export UI Defaults.
Follow the on-screen instruction.
Each Oracle database has a data dictionary. An Oracle data dictionary is a set of tables and views that are used as a read-only reference about the database. For example, a data dictionary stores information about both the logical and physical structure of the database. A data dictionary also stores information about valid Oracle database users, integrity constraints for tables in the database, and the amount of space allocated for a schema object as well as how much of it is being used.
To browse the data dictionary:
Click the SQL icon
Select the Browse tab and then Data Dictionary Browser.
The Data Dictionary Browser appears.
Click the view icon to display the Query By Example (QBE) form. Use this form to query the Oracle data dictionary for details about database objects.
To view data dictionary reports:
Click the SQL icon
Select the Browse tab and then Dictionary Reports.
The Data Dictionary Reports page appears.
Make a selection from the list and follow the on-screen instructions.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|