2
Building and Browsing Database Objects
Estimated completion time: 20 minutes
This chapter shows you how to use Oracle WebDB to build and browse database objects. Database objects are structures used to store data in the database. You can create the following database objects in WebDB:
In this chapter, you'll learn how to build tables and procedures using Oracle WebDB's database object build wizards.
Note:
You can still build database objects using SQL CREATE commands if you prefer. WebDB simply provides an alternative tool for you to use.
|
This chapter includes the following sections:
2.1 Before You Begin
- To complete this chapter, you must be a WebDB Developer (that is, you must be a member of the WEBDB_DEVELOPER role) and have Build In privileges on your own schema. If you are not a WebDB Developer, you will not be able to access WebDB's database object building features. If you do not have Build In privileges on your own schema, you will not be able to build database objects in that schema. You must also be a member of the RESOURCE role. If you need to be added to the WEBDB_DEVELOPER or RESOURCE roles or granted Build In privileges on your own schema, ask your DBA.
- If you have not already started Oracle WebDB, follow the instructions in "Oracle WebDB Basic Concepts, Starting Oracle WebDB".
2.2 Building a Table
The Human Resources department has requested a table to store information about the company's employees.
- If you are not already on your WebDB home page, click
at the top right of the page.
- Click Build.
Note:
If you cannot see the Build link, you are probably not a WebDB Developer. Ask your DBA to add you to the WEBDB_DEVELOPER role.
|
- Click Database Objects to display the Build Database Objects page.
The page displayed contains the Create a Database Object panel.
Tip:
You can also click in the toolbar at the bottom of any page to display the Build Database Objects page.
|
- Click
to display the Create Table Wizard.
- In the Schema list, choose your own schema.
Note:
Your schema name is the same as your user name.
If your own schema is not listed, you have probably not been granted Build In privileges on it. Ask your DBA to grant these privileges to you.
|
- In the Table Name field, type
TUTORIAL_TABLE
.
- Click
to display the Columns page.
WARNING:
Click if you need to go back to a previous page of the wizard. Do not click your browser's Back button.
|
- Complete the column details using the information in the following table.
- Click
to display the Storage page.
- You can accept the default values on this page. Click
to display the Create Table page.
- Click OK to create the table and return to the Build Database Objects page.
2.3 Building a Procedure
The Human Resources department has requested a procedure to increase the salaries of the employees in a specified department by a specified percentage.
- Click
to display the Create Procedure Wizard.
- In the Schema list, choose your own schema.
- In the Procedure Name field, type
TUTORIAL_PROC
.
- Click
to display the Arguments page.
- Complete the argument details using the information in the following table.
- Click
to display the Create Procedure page.
- In the Procedure Body field, change the text so it reads:
BEGIN
UPDATE TUTORIAL_TABLE
SET SAL = ((P_PCT_RAISE/100)+1)*SAL
WHERE DEPTNO = P_DEPTNO;
END;
- Click
to display the Create Procedure page.
- Click OK to create the procedure and return to the Build Database Objects page.
2.4 Querying a Table
So far you have created a table (TUTORIAL_TABLE) and a procedure (TUTORIAL_PROC). Now you need to test these objects to make sure they meet the requirements of the Human Resources department. First, you need to find the table in the database.
- In the toolbar at the bottom of the page, click
to display the Browse Database page.
The page displayed contains the Find Database Objects panel.
- In the Schema field, type the name of your own schema (the schema where you created the table).
Tip:
Click to display a list of schemas if you don't know the name of the schema you want to browse.
|
- In the Type list, choose Tables.
- Click Browse to list all the tables in your own schema.
Note:
To browse database objects in a schema other than your own, you must have been granted Browse In privileges to do so by the DBA. If you have Build In privileges on a schema, you automatically have Browse In privileges on that schema.
|
- Click Tutorial_Table to display the Query and Update Table page.
The form on the Query and Update page is divided into three sections. The first section lists the columns in the table. You can type values in the fields to specify query criteria or to add new data. The second and third sections of the form enable you to specify the way the query results are displayed. For example, you can order the results by a specified column, or download the results and display them in Microsoft Excel.
TUTORIAL_TABLE does not currently contain any data. Let's insert a row so that you have some test data to work with.
- Use the information in the following table to complete the first section of the Query and Update Table form.
- Click Insert New Row to add the data to your table.
- Click Browse Table in the link history to return to the Query and Update Table page.
- Click Query to take a look at the data you just added.
- At the top right of the page, click Return to Table to return to the Query and Update Table page.
- Let's add some more data. Use the information in the following table to add more rows to TUTORIAL_TABLE. After completing the form for each row, click Insert New Row, and then Browse Table to return to the form to add the next row.
- After you have finished, in the Query and Update Table page, click Query again to review all the data.
You can specify values to restrict the results of the query. For example, you might only want to display employee details for those employees who earn more than 3000.
- Click Return to Table.
- In the field for the SAL column, type
>3000
.
- Click Query to display all those employees with a salary greater than 3000.
You can also restrict which columns to query and control how the query results are displayed. For example, you might only want to see the name and salary of employees in Department 10.
- Click Return to Table.
- Uncheck EMPNO, JOB, and DEPTNO.
ENAME and SAL should be checked.
- In the field for the DEPTNO column, type
10
.
- In the Order By list, choose SAL.
- In the Query Options list, choose Display Results in Table with Borders.
- Click Query to display all those employees in Department 10. Make a note of this information, it will be useful to refer to later in this chapter.
2.5 Executing a Procedure
Now that you have added some data to TUTORIAL_TABLE, let's test TUTORIAL_PROC.
- In the toolbar at the bottom of the page, click
to display the Browse Database page.
- In the Schema field, type the name of your own schema (the schema where you created the procedure).
- In the Type list, choose Procedures.
- Click Browse to list all the procedures in your own schema.
- Click Tutorial_Proc to display the Execute Procedure page. This is where you provide values for the arguments defined in the procedure.
- In the field for the P_DEPTNO argument, type
10
.
- In the field for the P_PCT_RAISE argument, type
5
.
- Click Execute.
- The
image indicates that the procedure executed successfully.
Now let's take another look at TUTORIAL_TABLE to check the results of the procedure.
- Click your browser's Back button to return to the Execute Procedure page.
- In the toolbar at the bottom of the page, click
to display the Browse Database page.
- In the Schema field, type the name of your own schema.
- In the Object name field, type
TUTORIAL%
.
- Click Browse to list all the objects in your own schema that start with "tutorial".
- Click Tutorial_Table to display the Query and Update Table page.
- Click Query to view all the data in the table.
- Compare the salaries of the employees in Department 10 (KING, CLARK, and MILLER) with those that you noted down at the end of the previous exercise. Executing TUTORIAL_PROC has increased them by 5%.
2.6 What's Next?
In this chapter, you learned how to:
- Create a table
- Create a procedure
- Query a table and add rows to a table
- Execute a procedure
In Chapter 3, "Building Components", you will build components based on the TUTORIAL_TABLE table you created in this chapter.