12-1. Creating Standard and Privileged Database Connections
Problem
You want to create a persistent connection to your database from within Oracle SQL Develop using both privileged and standard accounts so that you can work with your database.
Solution
Open Oracle SQL Developer, and select New from the File menu. This will open the Create a New window. Select the Database Connection option, and click OK. A New/Select Database Connection window opens, which has a list of existing database connections on the left side and an input form for creating a new connection on the right side, as shown in Figure 12-1.
Figure 12-1. Creating a database connection
If you are creating a standard connection, choose the Basic connection type. If you are creating a privileged connection as SYS, then choose the SYSDBA connection type. Once you have created a connection, then you will be able to connect to the database via the user for which you have made a connection and browse the objects belonging to that user’s schema.
How It Works
Before you can begin working with PL/SQL code in Oracle SQL Developer, you must create a database connection. Once created, this connection will remain in the database list that is located on the left side of the Oracle SQL Developer environment. During the process of creating the connection, you can either select the box to allow the password to be cached or keep it deselected so that you will be prompted to authenticate each time you want to use the connection. From a security standpoint, it is advised that you leave the box unchecked so that you are prompted to authenticate for each use.
Once the connection has been successfully established and you are authenticated, the world of Oracle SQL Developer is opened up, and you have a plethora of options available. At this point, you have the ability to browse through all the database tables, views, stored programs, and other objects that are available to the user account that you used to initiate the connection to the database by simply using the tree menu located within the left pane of the environment. Figure 12-2 shows a sample of what you will see when your database connection has been established.
Figure 12-2. Database connection in the navigator
Note If you plan to develop PL/SQL code for system events such as an AFTER LOGON trigger, you should create a separate connection for the privileged user using SYSDBA. This will allow you to traverse the privileged database objects.
As mentioned in the introduction to this chapter, you will learn how to use those features provided by Oracle SQL Developer that are useful for PL/SQL application development. This does not mean the other features offered by the environment are not useful, but it would take an entire book to cover each feature that Oracle SQL Developer has to offer. Indeed, there are entire books on the topic. This book strives to provide you with the education and concepts that you will need to know to develop complete and robust PL/SQL applications using Oracle SQL Developer.