Need to generate numbers between a given range for each record. Question and Answer. Thanks for the question, Deepti. Id column is a primary key on the table. Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database! Sep 06, 2011 PL/SQL Developer Version 9.0.0.1601 Windows 7 (64-bit) 6.1 Build 7601 (Service Pack 1) In the SQL Window, the HOME and END keys do not seem to work. I was earlier using PL/SQL Developer version 6 and it seemed to work fine there. Set them in Key Configuratiopn in Preferences. Set them against Editor: Start of Line and Editor: End of Line as.
- Pl/sql Developer Download
- Download Sql Developer
- Pl Sql Developer Key Generator Download
- Pl Sql Developer Key Generator Download
- Pl Sql Developer Tool Tutorial
In this chapter, you will create and use the types of database objects that were discussed in 'Querying and Manipulating Data'.
Note that the statements
CREATE TABLE
,ALTER TABLE
, DROP TABLE
, and so on, use an implicit commit, and cannot be rolled back.This chapter contains the following sections:
Using Data Types
Data types associate a set of properties with values so you can use these values in the database. Depending on the data type, Oracle Database can perform different kinds of operations on the information in the database. For example, it is possible to calculate a sum of numeric values but not characters.
Oracle Database supports many kinds of data types, including the most common
VARCHAR2(length)
, NUMBER(precision, scale)
, DATE
, and also CHAR(length)
, CLOB
, TIMESTAMP
, and others. As you create a table, you must specify data types for each of its columns and (optionally) indicate the longest value that can be placed in the column.Some of the data types and their properties you will use here include the following:
- The
VARCHAR2
stores variable-length character literals, and is the most efficient option for storing character data. When creating aVARCHAR2
column in a table, you must specify the maximum number of characters in a column, which is a length between1
and4,000
. In theemployees
table, thefirst_name
column has aVARCHAR(20)
data type and theLAST_NAME
column has aVARCHAR2(25)
data type. - An option to the
VARCHAR2
data type,NVARCHAR2
stores Unicode variable-length character literals. - The
CHAR
data type stores fixed-length character literals; it uses blanks to pad the value to the specified string length, which is between1
and2,000
.An option to theCHAR2
data type,NCHAR
stores Unicode fixed-length character literals. - The
CLOB
data type is a character large object data type that contains single-byte or multibyte characters. The maximum size of a CLOB is (4 gigabytes - 1) x (database block size). - The
NUMBER
data type stores zero, and integers and real numbers as positive and negative fixed numbers with absolute values between 1.0 x 10-130 and 1.0 x 10126 using a fixed-point or floating-point format, with decimal-point precision. Oracle guarantees thatNUMBER
data types are portable between different operating systems, and recommends it for most cases where you need to store numeric data.You can use the precision option to set the maximum number of digits in the number, and the scale option to define how many of the digits are to the right of the decimal separator. In theemployees
table, thesalary
column is defined asNUMBER(8,2)
, providing 6 digits for the primary unit of currency (dollars, pounds, marks, and so on) and 2 digits for the secondary unit of currency (cents, pennies, pfennigs, and so on). - For floating-point numbers, Oracle Database provides the numeric
BINARY_FLOAT
andBINARY_DOUBLE
data types as enhancements to the basicNUMBER
data type.BINARY_FLOAT
(32-bit IEEE 754 format) ranges in absolute value between 1.17549 x e-38F and 3.40282 x e38FandBINARY_DOUBLE
(64-bit IEEE 754 format) ranges in absolute value between 2.22507485850720 x e-308 and 1.79769313486231 x e308. Both use binary precision that enables faster arithmetic calculations and often reduces storage requirements. - The
DATE
data type stores point-in-time values, dates and times; this includes the century, year, month, day, hours, minutes, and seconds. The valid date range is from January 1, 4712 BC to December 31, 9999 AD. Oracle Database supports many different formats for displaying date and time values. In theemployees
table, thehire_date
column is defined as aDATE
. - The
TIMESTAMP
data type stores values that are precise to fractional seconds, and is therefore useful in applications that must track event order. - The
TIMESTAMP WITH TIME ZONE
data type stores time zone information, and can therefore record date information that must be coordinated across several geographic regions.
Creating and Using Tables
Tables are the basic unit of data storage in an Oracle database, and hold all user-accessible data. Tables are two-dimensional objects made up of vertical columns that represent the fields of the table and horizontal rows that represent the values for each record in the table.
In this section, you will create all the necessary tables and other schema objects to implement an employee performance evaluation process for the existing
hr
schema.Creating a Table
To implement the employee evaluation process, you will need to establish three tables,
performance_parts
, evaluations
, and scores
.- The
performance_parts
table lists the categories of performance measurements, and the relative weight for each item. - The
evaluations
table will contain the employee's information, evaluation date, and the job, manager and department at the time of evaluation. You must preserve this information in this table because at any point in the future, the employee may change job designation, manager, or department. - The
scores
table contains the scores assigned to each performance category for each evaluation.
To create a table using SQL Developer interface:
You will create the
performance_parts
table using the SQL Developer graphical interface.- In the Connections navigation hierarchy, click the plus sign (+) next to
hr_conn
to expand the list of schema objects. - Right-click Tables.
- Select New Table.
Description of the illustration table_create_1.gif - In the Create Table window, enter the following information:
- For Schema, select
HR
. - For Name, enter
PERFORMANCE_PARTS
.
Description of the illustration table_create_2.gif - Click the default column that was created with the table.
- Enter the information for the first column in the table as follows:
- For Column Name, enter
PERFORMANCE_ID
. - For Type, enter
VARCHAR2
. - For Size, enter
2
.
Leave the value of Not Null and Primary Key properties. You will come back to this later, in 'Ensuring Data Integrity'.
Description of the illustration table_create_3.gif - Enter information for the second column as follows:Click Add Column.
- For Column Name, enter
NAME
. - For Type, enter
VARCHAR2
. - For Size, enter
80
.
- Enter information for the third column as follows:Click Add Column.
- For Column Name, enter
WEIGHT
. - For Type, enter
NUMBER
.
- Click OK.SQL Developer generates the new table,
performance_parts
. - In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
performance_parts
is a new table in thehr
schema, listed betweenlocations
andregions
.
You just created a new table,
performance_parts
. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table.In Example 3-1, you will create the
evaluations
table by entering the information directly in the SQL Worksheet pane.Example 3-1 Creating a Table in SQL Script
The results of the script follow.
You created a new table,
evaluations
. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.In Example 3-2, you will create another table,
scores
, by entering the information in the SQL Worksheet pane.Example 3-2 Creating the SCORES Table
The results of the statement follow.
You created a new table,
scores
. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.See Also:
- Oracle Database SQL Language Reference for information on the
CREATE TABLE
statement
Ensuring Data Integrity
The data in the table must satisfy the business rules that are modeled in the application. Many of these rules can be implemented throughintegrityconstraints that use the SQL language to explicitly state what type of data values are valid for each column.
When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule, so when your application includes a SQL statement that inserts or modifies data in the table, Oracle Database automatically ensures that the constraint is satisfied. If you attempt to insert, update, or remove a row that violates a constraint, the system generates an error, and the statement is rolled back. If you attempt to apply a new constraint to a populated table, the system may generate an error if any existing row violates the new constraint.
Because Oracle Database checks that all the data in a table obeys an integrity constraint much faster than an application can, you can enforce the business rules defined by integrity constraints more reliably than by including this type of checking in your application logic.
See Also:
- Oracle Database SQL Language Reference for information about integrity constraints
Understanding Types of Data Integrity Constraints
There are five basic types of integrity constraints:
- A
NOT NULL
constraint ensures that the column contains data (it is not null). - Aunique constraint ensures that multiple rows do not have the same value in the same column. This type of constraint can also be used on combination of columns, as a composite unique constraint. This constraint ignores null values.
- Aprimary keyconstraint combines
NOT NULL
andUNIQUE
constraints in a single declaration; it prevents multiple rows from having the same value in the same column or combination of columns, and prevents null values. - A foreign key constraint requires that for each value in the column on which the constraint is defined, there must be a matching value in a specified other table and column.
- A checkconstraint ensures that a value satisfies a specified condition. Use check constraints when you need to enforce integrity rules based on logical expressions, such as comparisons. Oracle recommends that you never use check constraints when other types of constraints can provide the necessary checking.
Adding Integrity Constraints
You will now add different types of constraints to the tables you created in 'Creating a Table'.
To Add a NOT NULL Constraint Using the SQL Developer Interface:
You will add a
NOT NULL
constraint to the table using the SQL Developer graphical interface.- In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
- Right-click the
performance_parts
table. - Select Edit.
Description of the illustration constraint_create_5.gif - In the Edit Table window, follow these steps:
- In the Edit Table window, click Columns.
- In the Columns list, select
NAME
. - In the Column Properties section, check Cannot be NULL.Click OK.
Description of the illustration constraint_create_6.gif
- In the Confirmation window, click OK.You have now created a
NOT NULL
constraint for thename
column of theperformance_parts
table.
The definition of the
name
column in the performance_parts
table is changed to the following; note that the constraint is automatically enabled.Example 3-3 shows how you can add another
NOT NULL
constraint to the performance_parts
table by entering the required information directly in the SQL Statement window.Example 3-3 Adding a NOT NULL Constraint in SQL Script
The results of the script follow.
You just created a
NOT NULL
constraint for column weight
of the performance_parts
table. If you click the SQL tab, you will see that the definition of the weight
column changed. You may need to click the Refresh icon.To add a unique constraint using the SQL Developer interface:
You will add a unique constraint to the
scores
table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL
constraint, to accomplish this task.- In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
- Right-click the
scores
table. - Select Constraint, and then select Add Unique.
Description of the illustration constraint_create_3.gif - In the Add Unique window, enter the following information:
- Set the constraint name to
SCORES_EVAL_PERF_UNIQUE
. - Set Column 1 to
EVALUATION_ID
. - Set Column 2 to
PERFORMANCE _ID
.
Click Apply.
Description of the illustration constraint_create_4.gif - In the Confirmation window, click OK.You have now created a unique constraint for the
scores
table.The following SQL statement was added to your table definition:
To add a primary key constraint using the SQL Developer interface:
You will add a primary key constraint to the
performance_parts
table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL
constraint, to accomplish this task.- In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.
- Right-click the
performance_parts
table. - Select Constraint, and then select Add Primary Key.
Description of the illustration constraint_create_1.gif - In the Add Primary Key window, enter the following information:
- Set the primary key name to
PERF_PERF_ID_PK
. - Set Column 1 to
PERFORMANCE_ID
.
Click Apply.
Description of the illustration constraint_create_2.gif - In the Confirmation window, click OK.You have now created a primary key constraint for the
performance_parts
table.
The following SQL statement was added to your table definition:
In Example 3-4, you will create a primary key constraint on the
evaluations
table by entering the required information directly in the SQL Statement window.Example 3-4 Adding a Primary Key Constraint in SQL Script
The results of the script follow.
You just created a primary key
eval_eval_id_pk
on the evaluations
table. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.To add a foreign key constraint using the SQL Developer interface:
You will add two foreign key constraints to the
scores
table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL
constraint, to accomplish this task.- In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.
- Right-click the
scores
table. - Select Constraint, and then select Add Foreign Key.
Description of the illustration constraint_create_7.gif - In the Add Foreign Key window, enter the following information:
- Set the foreign key name to
SCORES_EVAL_FK
. - Set Column Name to
EVALUATION_ID
. - Set Reference Table Name to
EVALUATIONS
. - Set Referencing Column to
EVALUATION_ID
.
Click Apply.
Description of the illustration constraint_create_8.gif - In the Confirmation window, click OK.You have now created a foreign key constraint on the
evalution_id
column from theevaluations
table. - Add another foreign key constraint by repeating steps 2 through 5, with the following parameters:
- Set the foreign key name to
SCORES_PERF_FK
. - Set Column Name to
PERFORMANCE_ID
. - Set Reference Table Name to
PERFORMANCE_PARTS
. - Set Referencing Column to
PERFORMANCE_ID
.
Click Apply.
The following SQL statements were added to your table definition:
In Example 3-5, you will create a foreign key constraint on the
evaluations
table by entering the required information directly in the SQL Statement window.Example 3-5 Adding a Foreign Key Constraint in SQL Script
The results of the script follow.
You have now created a foreign key constraint on the
employee_id
column from the employees
table. If you click the SQL tab, you will see the following SQL statement was added to your table definition. You may need to click the Refresh icon.To add a check constraint using the SQL Developer interface:
You will add a check constraint to the
scores
table using the SQL Developer graphical interface. You could also use the Edit Table window, as in the NOT NULL
constraint, to accomplish this task.- In the Connections navigation hierarchy, the plus sign (+) next to Tables to expand the list of tables.
- Right-click the
scores
table. - Select Constraint, and then select Add Check.
Description of the illustration constraint_create_9.gif - In the Add Check window, enter the following information:
- Set the Constraint Name to
SCORE_VALID
. - Set Check Condition to
score >=0 and score <=9
. - Set Status to
ENABLE
.
Click Apply.
Description of the illustration constraint_create_10.gif - In the Confirmation window, click OK.You have now created a check constraint on the
score
column of thescores
table.
The following SQL statement was added to your table definition:
Adding Data to a Table, Modifying, and Deleting
You can use SQL Developer to enter data into tables, to edit, and to delete existing data. The following tasks will show these processes for the
performance_parts
table.To add data to a table using the SQL Developer interface:
Follow these steps to add rows of data to the
performance_parts
table- In the Connections navigation hierarchy, double-click the
performance_parts
table. - Click the Data tab in the
performance_parts
table display. - In the Data pane, click the New Record icon.
Description of the illustration data_add_1.gif - In the new row, add the following information; you can click directly into the column, or tab between columns:
- Set
PERFORMANCE_ID
to 'WM
' - Set
NAME
to 'Workload Management
' - Set
WEIGHT
to0.2
Press the Enter key.
Description of the illustration data_add_2.gif - Add a second row with the following information: set
PERFORMANCE_ID
to 'BR
,set NAME
to 'Building Relationships
, and setWEIGHT
to0.2
.Press the Enter key. - Add a third row with the following information: set
PERFORMANCE_ID
to 'CF
', setNAME
to 'Customer Focus
', and setWEIGHT
to0.2
.Press the Enter key. - Add a fourth row with the following information: set
PERFORMANCE_ID
to 'CM
', setNAME
to 'Communication
', and setWEIGHT
to0.2
.Press the Enter key. - Add a fifth row with the following information: set
PERFORMANCE_ID
to 'TW
', setNAME
to 'Teamwork
', and setWEIGHT
to0.2
.Press the Enter key. - Add a sixth row with the following information: set
PERFORMANCE_ID
to 'RD
', setNAME
to 'Results Orientation
', and setWEIGHT
to0.2
.Press the Enter key. - Click the Commit Changes icon.
- Review and close the Data Editor Log window.
Description of the illustration data_add_3.gif - Review the new data in the table
performance_parts
.
Description of the illustration data_add_4.gif
You have added 6 rows to the
performance_parts
table.To modify table data using the SQL Developer interface:
Follow these steps to change data to the
performance_parts
table.- In the Connections navigation hierarchy, double-click the
performance_parts
table. - Click the Data tab in the
performance_parts
table display. - In the Data pane, in the '
Workload Management
' row, click theweight
value, and enter a new value for '0.3
'.In the 'Building Relationships
' row, click theweight
value, and enter a new value for '0.15
'.In the 'Customer Focus
' row, click theweight
value, and enter a new value for '0.15
'.
Description of the illustration data_add_5.gif - Press the Enter key.
- Click the Commit Changes icon.
- Review and close the Data Editor Log window.
Description of the illustration data_add_6.gif
You have now changed values in three rows of the
performance_parts
table.To delete table data using the SQL Developer interface:
Imagine that in the company modeled by the
hr
schema, management decided that the categories Workload Management and Results Orientation had too much overlap. You will now remove the row 'Results Orientation
' from the performance_parts
table.- In the Connections navigation hierarchy, double-click the
performance_parts
table. - Click the Data tab in the
performance_parts
table display. - In the Data pane, click the '
Results Orientation
' row. - Click the Delete Selected Row(s) icon.
Description of the illustration data_add_7.gif - Click the Commit Changes icon.
- Review and close the Data Editor Log window.
Description of the illustration data_add_8.gif
You have now removed a row from the
performance_parts
table.See Also:
Indexing Tables
When you define a primary key on a table, Oracle Database implicitly creates an index on the column that contains the primary key. For example, you can confirm that an index was created for the
evaluations
table on its primary key, by looking at its Indexes pane.Description of the illustration index_1.gif
In this section, you will learn how to add different types of indexes to the tables you created earlier.
To create an index using the SQL Developer interface:
Follow these steps to create a new index for the
evaluations
table.- In the Connections navigation hierarchy, right-click the
evaluations
table. - Select Index and then select Create Index.Alternatively, in the Connections navigation hierarchy, you can right-click Indexes and select New Index.
Description of the illustration index_2.gif - In the Create Index window, enter the following parameters:
- Ensure that the Schema is set to
HR
. - Set the Name to
EVAL_JOB_IX
.
Click theAdd Column Expression
icon, which looks like a 'plus' sign.- Set the Column Name or Expression to
JOB_ID
. - Set the Order to
ASC
.
Click OK.
Description of the illustration index_3.gif
You have now created a new index
EVAL_JOB_IX
on the column JOB_ID
in the evaluations
table. You can see this index by finding it in the list of Indexes in the Connections navigation hierarchy, or by opening the evaluations
table and browsing to the Indexes tab. The following script is the equivalent SQL statement for creating this index.To modify an index using SQL Developer interface:
Follow these steps to reverse the sort order of the
EVAL_JOB_IX
index.- In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.
- Right-click
EVAL_JOB_IX
, and select Edit.
Description of the illustration index_4.gif - In the Edit Index window, change Order to
DESC
.Click OK.
You changed the index. The following script is the equivalent SQL statement for creating this index:
To delete an index using SQL Developer interface:
Following steps to delete the
EVAL_JOB_IX
index.- In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.
- Right-click
EVAL_JOB_IX
, and select Drop.
Description of the illustration index_5.gif - In the Drop window, click Apply.
- In the Confirmation window, click OK.
You deleted the index
EVAL_JOB_IX
. The following script is the equivalent SQL statement for dropping this index.See Also:
- Oracle Database SQL Language Reference for information on the
CREATE INDEX
statement - Oracle Database SQL Language Reference for information on the
ALTER INDEX
statement - Oracle Database SQL Language Reference for information on the
DROP INDEX
statement
Dropping Tables
Sometimes it becomes necessary to delete a table and all its contents from your schema. To accomplish this, you must use the SQL statement
DROP TABLE
. You will use the tables that you already created to learn other concepts, so create a simple table that you can subsequently delete by running the following script in the SQL Statement window:To delete a table using the SQL Developer interface:
Follow these steps to delete
TEMP_TABLE
from the hr
schema.- In the Connections navigation hierarchy, right-click
TEMP_TABLE
. - Select Table, and then select Drop.
Description of the illustration index_6.gif - In the Drop window, click Apply.
- In the Confirmation window, click OK.
You deleted the table
TEMP_TABLE
. The following script is the equivalent SQL statement for dropping this table.See Also:
- Oracle Database SQL Language Reference for information on the
DROP TABLE
statement
Using Views
Views are logical tables based on one or more tables or views. Views are particularly useful if your business needs include frequent access to information that is stored in several different tables.
Creating a View
The standard syntax for creating a view follows:
To create a view using the SQL Developer interface:
Follow these steps to delete create a new view from the
hr
schema.- In the Connections navigation hierarchy, right-click Views.
- Select New View.
Description of the illustration view_1.gif - In the Create View window, enter the following parameters:
- Ensure that Schema is set to
HR
. - Set Name to
SALESFORCE
. - Set the SQL Query to the following:
- In SQL Parse Results, click Test Syntax.
Description of the illustration view_2.gif - Click OK.
You created a new view. The equivalent SQL statement for creating this view follows:
In Example 3-6, you will create a view of all employees in the company and their work location, similar to the query you used in 'Using Character Functions'.
Example 3-6 Creating a View in SQL Script
The results of the script follow.
You have now created new view that relies on information in 4 separate tables, or a 4-wayJOIN. In the Connections navigation hierarchy, if you click the 'plus' sign next to Views, you will see
emp_locations
.Updating a View
To change the properties of a view in SQL Developer interface:
You will change the
salesforce
view by adding to it the employees in the Marketing department, and then rename the view to sales_marketing
.- In the Connections navigation hierarchy, right-click the
salesforce
view. - Select Edit.
Description of the illustration view_3.gif - In the Edit View window, change the SQL Query by adding the following to the last line: '
OR department_id = 20
'.Click Test Syntax.Click OK.
Description of the illustration view_4.gif - To rename the view, right-click
salesforce
and select Rename.
Description of the illustration view_5.gif - In the Rename window, set New View Name to
sales_marketing
.Click Apply.
Description of the illustration view_6.gif - In the Confirmation window, click OK.
You changed the view. The equivalent SQL statements for changing and renaming the view are:
Dropping a View
To drop a view using the SQL Developer interface:
You will use the
DROP VIEW
statement to delete thesales_marketing
view.- In the Connections navigation hierarchy, right-click the
sales_marketing
view. - Select Drop.
Description of the illustration view_7.gif - In the Drop window, click Apply.
- In the Confirmation window, click OK.
You deleted the view. The equivalent SQL statement for dropping the view is:
See Also:
- Oracle Database SQL Language Reference for information on the
CREATE VIEW
statement - Oracle Database SQL Language Reference for information on the
DROP VIEW
statement
Using Sequences
Sequences are database objects that generate unique sequential values, which are very useful when you need unique primary keys. The
hr
schema already has three such sequences: departments_seq
, employees_seq
, and locations_seq
.The sequences are used through these pseudocolumns:
- The
CURRVAL
pseudocolumn returns the current value of a sequence.CURRVAL
can only be used after an initial call toNEXTVAL
initializes the sequence. - The
NEXTVAL
pseudocolumn increments the sequence and returns the next value. The first time thatNEXTVAL
is used, it returns the initial value of the sequence. Subsequent references toNEXTVAL
increment the sequence value by the defined increment, and return the new value.
Note that a sequence is not connected to any other object, except for conventions of use. When you plan to use a sequence to populate the primary key of a table, Oracle recommends that you use a naming convention to link the sequence to that table. Throughout this discussion, the naming convention for such sequences is
table_name
_seq
.Creating a Sequence
You can create a sequence in the SQL Developer Interface, or using the SQL Statement window.
To create a sequence using the SQL Developer interface:
The following steps will create a sequence,
evaluations_seq,
that you can use for the primary key of the evaluations
table.Pl/sql Developer Download
- In the Connections navigation hierarchy, right-click Sequences.
- Select New Sequence.
Description of the illustration sequence_1.gif - In the New Sequence window, enter the following parameters:
- Ensure that Schema is set to
HR
. - Set Name to
EVALUATIONS_SEQ
.
In the Properties tab:- Set Increment to
1
. - Set Start with to
1
. - Check Order.
Click OK.
Description of the illustration sequence_2.gif
You have now created a sequence that can be used for the primary key of the
evaluations
table. If you click '+' to expand the Sequence tree, you can see new sequence. The equivalent SQL statement is:In Example 3-7, you will create another sequence by entering the required information directly in the SQL Statement window.
Example 3-7 Creating a Sequence Using SQL Script
The results of the script follow.
See Also:
- Oracle Database SQL Language Reference for information on the
CREATE SEQUENCE
statement
Dropping a Sequence
To delete a sequence, you must use the SQL statement
DROP SEQUENCE
. To see how a sequence can be deleted in SQL Developer, you can use the test_seq
sequence you created earlier. If the new sequence does not appear in the Connections hierarchy navigator, click the refresh icon.To drop a sequence:
Follow these steps to drop a sequence.
- In the Connections navigator, right-click the
test_seq
sequence.
Description of the illustration sequence_3.gif - In the Drop window, click Apply.
Description of the illustration sequence_4.gif - In the Confirmation window, click OK.
You have now deleted the sequence
test_seq
. The equivalent SQL statement follows:See Also:
Download Sql Developer
- Oracle Database SQL Language Reference for information on the
DROP SEQUENCE
statement
Using Synonyms
A synonym is an alias for any schema object and can be used to simplify SQL statements or even obscure the names of actual database objects for security purposes. Additionally, if a table is renamed in the database (
departments
to divisions
), you could create a departments
synonym and continue using your application code as before.To create a synonym using the SQL Developer interface:
The following steps will create a synonym,
positions,
that you can use in place of the jobs
schema object.- In the Connections navigation hierarchy, right-click Synonyms.
- Select New Synonym.
Description of the illustration synonym_1.gif - In the New Synonym window, set the following parameters:
- Ensure that Schema is set to
HR
. - Set Name to
POSITIONS
.
In the Properties tab:- Select Object Based. This means that the synonym refers to a specific schema object, such as a table, a view, a sequence, and so on.
- Set Object Based to
JOBS
.
Click OK.
Description of the illustration synonym_2.gif
Pl Sql Developer Key Generator Download
You created a synonym
positions
for the jobs
table. The equivalent SQL statement follows:In Example 3-8, you use the new
positions
synonym in place of the jobs
table name.Example 3-8 Using a Synonym
The results of the query appear.
To drop a synonym:
Follow these steps to drop the
positions
synonym.- In the Connections navigator, right-click the
positions
synonym. - Select Drop.
Description of the illustration synonym_4.gif - In the Drop window, click Apply.
Description of the illustration synonym_5.gif - In the Confirmation window, click OK.
Pl Sql Developer Key Generator Download
You deleted synonym
positions
. The equivalent SQL statement follows:Pl Sql Developer Tool Tutorial
See Also:
- Oracle Database SQL Language Reference for information on the
CREATE SYNONYM
statement - Oracle Database SQL Language Reference for information on the
DROP SYNONYM
statement