| Oracle® HTML DB User's Guide Release 1.6 Part Number B14303-02 |
|
|
View PDF |
| Oracle® HTML DB User's Guide Release 1.6 Part Number B14303-02 |
|
|
View PDF |
This section provides information on how to use SQL Workshop to view and manage database objects, create and manage user interface defaults, and browse the data dictionary.
This section contains the following topics:
You can use SQL Workshop to view and manage database objects from a Web browser.
To access the SQL Workshop home page, click the SQL Workshop icon on the Workspace home page. (See Figure 5-1.)
The SQL Workshop home page is divided into four primary sections:
SQL Workshop. Offers quick access to the SQL Command Processor, User Interface Defaults, and Create Database Object wizards.
Database Browser. Offers a view of existing database objects by type.
SQL Scripts. Manage uploaded script files and control files as well as generate DDL (data definition language) statements.
Task list. Available options include:
Manage Recycle Bin. View and restore dropped database objects.
View SQL History. View recent commands and scripts run in the SQL Command Processor.
Manage SQL Archive. Save frequently used SQL commands in the SQL Archive.
Drop Database Object. Drop database objects using the Drop Database Object Wizard.
Explain Plan. View the "plan" the Oracle Optimizer uses to run your SQL command.
Query Data Dictionary. Browse the Oracle data dictionary.
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, you cannot 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 use the SQL Command Processor to run SQL commands and SQL scripts on any Oracle database schema for which you have privileges.
To access the SQL Command Processor:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, click SQL Command Processor.
The SQL Command Processor appears.
Select a schema from the list and follow the on-screen instructions.
To run entered commands, click Run SQL.
For example, to describe a database object you would type DESC MY_OBJECT. For example:
DESC DEMO_ABOUT
Alternatively, if you are using Internet Explorer, you can also press CTRL+Enter to execute your SQL Statement.
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 (/).
Use Explain Plan to view the plan the Oracle Optimizer uses to run your SQL Command.
To view the Explain Plan:
Click SQL Workshop on the Workspace home page.
From the Tasks list on right side of the page, select Explain Plan.
Explain Plan appears.
Enter a command in the field provided and click Explain Plan.
You can use the Database 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 SQL Workshop on the Workspace home page.
Under Database Browser, 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. Searches are case insensitive and no wildcards or quotes are necessary.
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.
Once you have located a specific table you can declaratively create a report to display the data in the table.
To Query by Example:
Click SQL Workshop on the Workspace home page.
Under Database Browser, select Tables.
The Database Browser appears.
Click the View icon to view details about a specific object.
The Object Detail appears.
From the Tasks list, select Query by Example.
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 create new database objects using the Create Database Object Wizard.
To create new database objects in SQL Workshop:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, select Create Object.
The Create Database Object Wizard appears.
Select the type of database object you would like to create.
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 SQL Workshop on the Workspace home page.
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 Oracle Database 10g release 1 (10.1), 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. The Oracle database 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 SQL Workshop on the Workspace home page.
From the Tasks list on the right side of the page, select Manage 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 SQL Workshop on the Workspace home page.
From the Tasks list on the right side of the page, select Manage 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 files as well as create and edit your create table, create index, and create PL/SQL package scripts.
Topics in this section include:
Use the SQL Script Repository to view, edit, run, and delete uploaded script files.
To view scripts in the SQL Script Repository:
Click SQL Workshop on the Workspace home page.
Under SQL Scripts, click Scripts.
SQL Script Repository appears. Scripts are stored based on your workspace 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
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 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 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 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 again.
Once you have run a script file, you can view a history of previous executions by clicking Previous Runs.
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 terminate a statement in a script by adding a carriage return and a forward slash (/) at the end of each statement. Consider the following examples:
INSERT INTO emp values(10,'aaa')
/
INSERT INTO emp values(20,'bbb')
/
INSERT INTO emp values(30,'ccc')
/
You can parameterize a script using a pound sign (#) 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:
Navigate to the Workspace home page.
Click Application Builder.
When Application Builder appears, click Export/Import.
The Export Import Wizard appears.
Select Export and click Next.
Click the Script Files button and follow the on-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 Archives.
The SQL Archives is different from the SQL Script Repository. By saving frequently used SQL commands in the SQL Archives, you can run the commands again without retyping. When you save a SQL command to the SQL Archives, the saved command does not appear in the Script Repository.
To view the SQL Archives:
Click SQL Workshop on the Workspace home page.
From the Tasks list on the right side of the page, select Manage SQL Archive.
SQL Archives appears. Scripts are stored based on your workspace username.
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 SQL Workshop on the Workspace home page.
From the Tasks list on the right side of the page, select View SQL History.
SQL Command History appears.
To run a command, click the appropriate SQL link.
The selected SQL command or the script displays in the SQL Command Processor.
If you are running Oracle HTML DB with Oracle Database 10g release 1 (10.1), 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, analyze table, index, or cluster information, establish auditing options, or add comments to the data dictionary.
To generate DDL statement in SQL Workshop:
Click SQL Workshop on the Workspace home page.
Under SQL Scripts, 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 SQL Workshop on the Workspace home page.
Under SQL Scripts, click Script Control.
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 Control File page appears.
On the Edit Control 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.
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.
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.
To run the file again, select Run File again
You can also use SQL Workshop to create new tables or edit existing tables.
To create a new table:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, click Create Object.
The Create Database Object Wizard appears.
Select Table and click Next.
Follow the on-screen instructions.
Click SQL Workshop on the Workspace home page.
Under Database 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.
The Table Definition appears.
To edit the table or generate a report, make selection from the Tasks list. Table 5-2 describes the options available on the Tasks list.
Table 5-2 Table Tasks
To view user interface defaults, click the Manage User Interface Defaults button.
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.
Since user interface defaults are associated with a table, you can use them with applications created using the form and report wizards.
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 that utilize user interface defaults:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, select User Interface Defaults.
A list of tables for which user interface defaults are defined displays.
Select a specific table by clicking the Edit icon adjacent to the table name.
You define user interface defaults for a specific column by editing column attributes on the Column Definition page.
To edit column attributes:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, select User Interface Defaults.
A list of tables for which user interface defaults are defined displays.
Select a specific table by clicking the Edit icon adjacent to the table name.
The View Details page appears. The following information displays at the top of the page:
Schema is the schema that owns the table.
Table/View Name is the name of the selected table or view.
Report Region Title and Form Region Title becomes the default title for all report or form regions. Both of these names a modified versions of Table/View Name in which the first letter is capitalized and any underscores are replaced with spaces.
Column-level User Interface Defaults appear next. You can edit select attributes for all displayed columns, by clicking Grid Edit.
To select a specific column, click the Edit icon adjacent to the column name.
Edit Column-level Defaults is divided into two pages: Column Definition and List of Values. The topics that follow describes how to edit specific attributes on the Column Definition and List of Values pages.
The top of the page displays the selected schema, table or view name, and column name.
Use Label to specify default label text for items in a form and the heading for columns in reports.
Available Display for Reports attributes include:
Display - Indicates whether the column displays in a report. The default is Yes.
Display Sequence - 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.
Display As - Specifies how the column should be displayed in reports
Mask - Indicates if a mask should be applied against the data. This attribute is not applicable 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 the column should be used for Group By and then the sequence of the grouping. The default is Yes.
Aggregate By - Indicates whether the column should be used for aggregation in reports and charts.
Available Defaults for Forms attributes include:
Display - Indicates whether the column displays in a form. The default is Yes.
Display Sequence - 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.
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).
To access List of Values defaults, click the List of Values tab. Once you select the type, you are prompted to enter either Display Value, Return Value pairs, or a list of values query.
The top of the page displays the selected schema, table or view name, and column name. Use the List of Values Type list to specify whether the selected column will include a static or dynamic list of values.
You can view details about a specific column by accessing the Column Definition report. The Column Definition report displays the data type, data length, nullable, default, comment as well as any check constraints, primary and unique keys, and foreign keys that reference the column.
To view the Column Definition report:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, select User Interface Defaults.
Select a table by clicking the Edit icon adjacent to the table name.
Select a column.
From the Tasks list, select View Column Definition.
You can view a listing of tables without user interface defaults on the Tables without Defaults page.
To apply user interface defaults to a table or view:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, select User Interface Defaults.
From the Tasks list, select Tables without User Interface Defaults.
To apply user interface defaults to a table or view, select the Default link to the left of the table or view name.
Use the Compare Defaults report to monitor consistency in user interface design across all pages in a single application or multiple applications in the current workspace. Running the Compare Defaults report compares currently defined user interface defaults (or column attributes) against the column attributes set for forms, reports, and tabular forms.
To run the Compare Defaults report:
Click SQL Workshop on the Workspace home page.
Under SQL Workshop, select User Interface Defaults.
From the Tasks list, select Compare User Interface Defaults.
The Compare Defaults page appears.
Select a schema, table name, and column.
From Display, select the attributes you want to compare.
Optionally select an application.
Click Go.
You export user interface defaults in the same way you export any related application file. Exporting user interface defaults from one Oracle HTML DB development instance to another involves the following steps:
Export the user interface defaults using the Export User Interface Defaults utility
Import the exported file into the target Oracle HTML DB instance
Install the exported file from Export Repository
When you export user interface defaults, all user interface defaults for the selected schema are exported to a single script. The file contains an API call to create table hints by making calls to the application PL/SQL API. You can use this file to import user interface defaults to another database and Oracle HTML DB instance.
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 SQL Workshop on the Workspace home page.
From the Tasks list, select Query Data Dictionary.
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 SQL Workshop on the Workspace home page.
From the Tasks list, select Query Data Dictionary.
The Data Dictionary Browser appears.
From the Tasks list, select Data Dictionary Reports.
