Open Oracle SQL Developer.

In the Connections navigator, right-click Connections and select New Connection.

Create the system_orcl connection with the following details and click Test to verify the status.

The test was successful. Click Connect to connect and save the connection.

Expand the new system connection. Right-click Other Users and select Create User.

Enter the following and click the System Privileges tab.

User name: test             Password test             Default Tablespace Users             Temporary Tablespace Temp

Select the following privileges and click Apply.

CREATE PROCEDURE             CREATE SEQUENCE             CREATE SESSION             CREATE TABLE             CREATE TRIGGER             CREATE TYPE             CREATE VIEW             UNLIMITED TABLESPACE

Review the SQL that was executed. Click Close.

Create another connection for the test user. Right-click Connections and select New Connection.

Enter the following details and click Connect.

Connection Name: test_orcl             Username: test             Password: test             Check Save Password             Hostname: localhost             Port: 1521             SID: orcl

Create one more connection for the hr user. Right-click Connections and select New Connection.

Enter the following details and click Connect.

Connection Name: hr_orcl             Username: hr             Password: hr             Check Save Password             Hostname: localhost             Port: 1521             SID: orcl

Note: If you worked on another tutorial and you already have a hr_orcl connection you can skip this step.

Select Tools > Database Export

Enter c:\< directory where you unzipped the files from the Prerequisites>\export.sql in the File field and select the hr_orcl connection.

Under the DDL options the Terminator and Pretty Print DDL options are selected by default. Review the rest of the options available, leaving them unchecked. Click Next.

If the export file exists, you receive a prompt to confirm overwriting. Click Yes.

The Types to Export window includes the Object Types you want to be exported from the hr_orcl connection. By default all the object types are selected. Click Toggle All check box to deselect all the object types.

Select only the object type Tables and then click Next.

Note: You must select at least one object type.

Click Go to search all objects that meet the selection criteria you specified in the previous step.

All tables owned by hr are listed. Click the tables EMPLOYEES and DEPARTMENTS and then use the arrow key to move them to the list box on the right hand side of the window.

Click Next.

You can review the information that will be used to create the output file, which will contain statements to export database objects and data according to your specifications. To create the output file, click Finish.

The file is also displayed in a SQL Worksheet window, where you can edit it and run it as a script. Select the test_orcl connection in the drop down list.

Click Run Script.

The script ran successfully. In the Connections navigator, expand the test_orcl connection.

Expand Tables. The DEPARTMENT and EMPLOYEES tables appear in the list.

Select Tools > Database Copy

You will copy the schema objects from the hr_orcl connection to the test_orcl connection. Select hr_orcl for Source Connection and test_orcl for Destination Connection and click Next.

Notice in the Copy Summary that all the tables in HR will be created in Test, even the EMPLOYEES and DEPARTMENTS. Click Finish.

The result of the comparison is displayed in a SchemaCopy log file in the SQL Developer window. Review the full results log. Do you expect the errors you find? Click Refresh to see the newly copied objects.

The newly copied objects are displayed.

Scroll down in the SchemaCopy log to see that you received an error when the CREATE TABLE "DEPARTMENTS" statement was run indicating that it already existed.

Select File > Open.

Select the proc_emp_cursor.sql file from the directory where you unzipped the files from the Prerequisites and click Open.

Select hr_orclfrom the list of connections.

Click the Run Script icon.

The procedure compiled successfully.

Select View > Find DB Object.

In the Find Database Object window, select hr_orcl for Connection, enter c_emp_cursor for Name and click Lookup.

Note that no occurrences were found. Click More.

Now when you perform the lookup, it will search in all types of database objects including PL/SQL procedures. Click Lookup.

Now there are occurrences of c_emp_cursor displayed.

Click one occurrence of c_emp_cursor to view and edit it in PL/SQL Editor.

You can also use wild cards while searching database objects. Change the Name to %emp_cursor and click Lookup.

All occurrences of the cursor c_emp_cursor and also the procedure proc_emp_cursor are displayed.

You can apply a filter on the search results. Select DECLARATION for Usage and click Lookup.

Only DECLARATION statements appear in the list.

You can also filter on the basis of Type. Change Usage back to All, enter %dept% for Name and select View for Type and click Lookup.

All the views with DEPT in its name and displayed.

Select Tools > Preferences.

Expand Database and select Drag And Drop.

Change the Drag and Drop effects to Insert. Click OK.

In Connections navigator, right-click hr_orcl and select Open SQL Worksheet.

Expand hr_orcl >Tables. Select and drag the JOBS table to the SQL Worksheet area.

A dialog appears asking you what type of statement you want to create. Notice that the default is set to Insert which you just changed in your preferences. This dialog will appear because the check box Prompt every time is selected. Deselect the Prompt every time check box and click Apply.

An Insert statement for the JOBS table was created.

Select Tools > Preferences.

Select Database > SQL Editor Code Templates. You want to create a new template, click Add Template.

Click in the Id field and enter excep. Then click in the Template field for that row.

Click the Edit icon to add the code for this template.

Enter the following code that will be used when the code template is referenced. Then click Close.

EXCEPTION             WHEN NO_DATA_FOUND THEN             DBMS_OUTPUT.PUT_LINE (‘The query did not return a result set‘);

Click OK.

Expand Procedures and select PROC_EMP_CURSOR.

Type exc in a new line below END LOOP; Notice that the code template popup appears. Double-click on the code template.

Notice that the code template is inserted into the procedure. Select Compile from the drop down list box.

The procedure compiled successfully.

Create a new database connection for the user HR_APEX. (If you still have a number of other tabs open from earlier sections, you can close those)

In the Connections navigator, right-click Connections and select New Connection.

Enter the following details and click Connect.

Connection Name: hr_apex_orcl             Username: hr_apex             Password: hr_apex             Hostname: localhost             Port: 1521             SID: orcl

In the Connections navigator, expand the Application Express node to display a list of all the applications owned by hr_apex schema.

Select Sample Application to display the application-level details, which includes Lists of Values, Lists, Templates, Tabs, etc.

Expand the Sample Application to see a list of the pages that make up the application.

Click the Master Detail page to display a detail tab with all the page-level details. This includes Regions, Items, Buttons, Processes, etc.

You can compare details of different pages or different applications. Click the Freeze View icon for the Master Detail page.

Select the Add Modify Customers page to open a new tab and display the details for that page.

Select the tab for the Add Modify Customers page and drag it below the Master Detail page and release. (As you drag you‘ll notice a blue box which shows the new positioning)

Click the Items tab for Add Modify Customers.

Then click the Items tab for the Master Detail page.

Now you can easily compare the values. When done reviewing, you can close both tabs.

In the Connection Navigator select the hr_apex connection, right-click the Application Express node and select Import Application.

In the dialog, click Browse to locate the file to import.

Select the timesheets_installer_1.0.sqlfile from the directory where you unzipped the files from the Prerequisites and click Select.

Click Next.

In the Choose Import Options dialog, check the Run Install Script option and accept the rest of the defaults. Then click Next.

Review the Results. Click Finish.

The import process will take a short while. Click OK to complete the install process.

Select the Reports tab.

Expand All Reports > Data Dictionary Reports > Application Express and review the available reports.

Click Applications. A Select Connection window appears, select hr_apex and click OK.

A report with details for each application is displayed. Click the Sample Application.

In the details report, select the LOV tab. Here you review the various Lists Of Values that exist with in the application.