Informatica Reference

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

For example:

CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,…}. It will cache up to 20 values for performance.

If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

Now that you’ve created a sequence object to simulate an autonumber field, we’ll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to usenextval.

For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, ‘Kraft Foods’);

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

What is a WHEN OTHERS clause?

The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.

The syntax for the WHEN OTHERS clause in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

    WHEN exception_name2 THEN
[statements]

    WHEN exception_name_n THEN
[statements]

    WHEN OTHERS THEN
[statements]

END [procedure_name];

 

The syntax for the WHEN OTHERS clause in a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

    WHEN exception_name2 THEN
[statements]

    WHEN exception_name_n THEN
[statements]

    WHEN OTHERS THEN
[statements]

END [function_name];

 

Here is an example of a procedure that uses a WHEN OTHERS clause:

CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;

BEGIN
IF sales_in = 0 THEN
RAISE no_sales;

ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,’You have tried to insert a duplicate order_id.’);

    WHEN no_sales THEN
raise_application_error (-20001,’You must have sales in order to submit the order.’);

    WHEN OTHERS THEN
raise_application_error (-20002,’An error has occurred inserting an order.’);

END;

In this example, if an exception is encountered that is not a DUP_VAL_ON_INDEX or a no_sales, it will be trapped by the WHEN OTHERS clause.

What is a named system exception?

Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.

Oracle has a standard set of exceptions already named as follows:

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON	ORA-01012	You tried to execute a call to Oracle before logging in.
LOGIN_DENIED	ORA-01017	You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND	ORA-01403	You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS	ORA-01422	You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	You tried to divide a number by zero.
INVALID_NUMBER	ORA-01722	You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR	ORA-06500	You ran out of memory or memory was corrupted.
PROGRAM_ERROR	ORA-06501	This is a generic “Contact Oracle support” message because an internal problem was encountered.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN	ORA-06511	You tried to open a cursor that is already open.

 

The syntax for the Named System Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

    WHEN exception_name2 THEN
[statements]

    WHEN exception_name_n THEN
[statements]

    WHEN OTHERS THEN
[statements]

END [procedure_name];

 

The syntax for the Named System Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section

EXCEPTION
WHEN exception_name1 THEN
[statements]

    WHEN exception_name2 THEN
[statements]

    WHEN exception_name_n THEN
[statements]

    WHEN OTHERS THEN
[statements]

END [function_name];

 

Here is an example of a procedure that uses a Named System Exception:

CREATE OR REPLACE PROCEDURE add_new_supplier
(supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
IS

BEGIN
INSERT INTO suppliers (supplier_id, supplier_name )
VALUES ( supplier_id_in, supplier_name_in );

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,’You have tried to insert a duplicate supplier_id.’);

    WHEN OTHERS THEN
raise_application_error (-20002,’An error has occurred inserting a supplier.’);

END;

In this example, we are trapping the Named System Exception called DUP_VAL_ON_INDEX. We are also using the WHEN OTHERS clause to trap all remaining exceptions.

What is a named programmer-defined exception?

Sometimes, it is necessary for programmers to name and trap their own exceptions – ones that aren’t defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.

The syntax for the Named Programmer-Defined Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]

    exception_name EXCEPTION;

BEGIN
executable_section

    RAISE exception_name ;

EXCEPTION
WHEN exception_name THEN
[statements]

    WHEN OTHERS THEN
[statements]

END [procedure_name];

 

The syntax for the Named Programmer-Defined Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]

    exception_name EXCEPTION;

BEGIN
executable_section

    RAISE exception_name ;

EXCEPTION
WHEN exception_name THEN
[statements]

    WHEN OTHERS THEN
[statements]

END [function_name];

 

Here is an example of a procedure that uses a Named Programmer-Defined Exception:

CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;

BEGIN
IF sales_in = 0 THEN
RAISE no_sales;

ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;

EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,’You must have sales in order to submit the order.’);

    WHEN OTHERS THEN
raise_application_error (-20002,’An error has occurred inserting an order.’);

END;

In this example, we have declared a Named Programmer-Defined Exception called no_sales in our declaration statement with the following code:

no_sales EXCEPTION;

 

We’ve then raised the exception in the executable section of the code:

 IF sales_in = 0 THEN
RAISE no_sales;

Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception called no_sales.

Finally, we tell our procedure what to do when the no_sales exception is encountered by including code in the WHEN clause:

WHEN no_sales THEN
raise_application_error (-20001,’You must have sales in order to submit the order.’);

We are also using the WHEN OTHERS clause to trap all remaining exceptions:

 WHEN OTHERS THEN
raise_application_error (-20002,’An error has occurred inserting an order.’);

A role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.

Creating a Role

To create a role, you must have CREATE ROLE system privileges.

The syntax for creating a role is:

CREATE ROLE role_name
[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

Note: If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role – a role that is enabled only by applications using an authorized package.

The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.

For Example:

CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_role
IDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

Grant Privileges (on Tables) to Roles

You can grant roles various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Privilege	Description
Select	Ability to query the table with a select statement.
Insert	Ability to add new rows to the table with the insert statement.
Update	Ability to update rows in the table with the update statement.
Delete	Ability to delete rows from the table with the delete statement.
References	Ability to create a constraint that refers to the table.
Alter	Ability to change the table definition with the alter table statement.
Index	Ability to create an index on the table with the create index statement.

 

The syntax for granting privileges on a table is:

grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a role named test_role, you would execute the following statement:

grant select, insert, update, delete on suppliers to test_role;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to test_role;

Revoke Privileges (on Tables) to Roles

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from role_name;

For example, if you wanted to revoke delete privileges on a table called suppliers from a role named test_role, you would execute the following statement:

revoke delete on suppliers from test_role;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from test_role;

Grant Privileges (on Functions/Procedures) to Roles

When dealing with functions and procedures, you can grant roles the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege	Description
Execute	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

 

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to role_name;

For example, if you had a function called Find_Value and you wanted to grant execute access to the role named test_role, you would execute the following statement:

grant execute on Find_Value to test_role;

Revoke Privileges (on Functions/Procedures) to Roles

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a role. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from role_name;

If you wanted to revoke execute privileges on a function called Find_Value from a role named test_role, you would execute the following statement:

revoke execute on Find_Value from test_role;

Granting the Role to a User

Now, that you’ve created the role and assigned the privileges to the role, you’ll need to grant the role to specific users.

The syntax to grant a role to a user is:

GRANT role_name TO user_name;

For Example:

GRANT test_role to smithj;

This example would grant the role called test_role to the user named smithj.

The SET ROLE statement

The SET ROLE statement allows you to enable or disable a role for a current session.

When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The syntax for the SET ROLE statement is:

SET ROLE
( role_name [ IDENTIFIED BY password ]
| ALL [EXCEPT role1, role2, … ]
| NONE );

The role_name phrase is the name of the role that you wish to enable.

The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.

The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.

The NONE phrase disables all roles for the current session. (including all default roles)

For Example:

SET ROLE test_role IDENTIFIED BY test123;

This example would enable the role called test_role with a password of test123.

Setting a role as DEFAULT Role

A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.

The syntax for setting a role as a DEFAULT role is:

ALTER USER user_name
DEFAULT ROLE
( role_name
| ALL [EXCEPT role1, role2, … ]
| NONE );

The user_name phrase is the name of the user whose role you are setting as DEFAULT.

The role_name phrase is the name of the role that you wish to set as DEFAULT.

The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.

The NONE phrase disables all roles as DEFAULT.

For Example:

ALTER USER smithj
DEFAULT ROLE
test_role;

This example would set the role called test_role as a DEFAULT role for the user named smithj.

ALTER USER smithj
DEFAULT ROLE
ALL;

This example would set all roles assigned to smithj as DEFAULT.

ALTER USER smithj
DEFAULT ROLE
ALL EXCEPT test_role;

This example would set all roles assigned to smithj as DEFAULT, except for the role called test_role.

Dropping a Role

It is also possible to drop a role. The syntax for dropping a role is:

DROP ROLE role_name;

For Example:

DROP ROLE test_role;

This drop statement would drop the role called test_role that we defined earlier.

 

Grant Privileges on Tables

You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Privilege	Description
Select	Ability to query the table with a select statement.
Insert	Ability to add new rows to the table with the insert statement.
Update	Ability to update rows in the table with the update statement.
Delete	Ability to delete rows from the table with the delete statement.
References	Ability to create a constraint that refers to the table.
Alter	Ability to change the table definition with the alter table statement.
Index	Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:

grant privileges on object to user;

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:

grant select, insert, update, delete on suppliers to smithj;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to smithj;

If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:

grant select on suppliers to public;

Revoke Privileges on Tables

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from user;

For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:

revoke delete on suppliers from anderson;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke all on suppliers from public;

Grant Privileges on Functions/Procedures

When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege	Description
Execute	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to user;

For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:

grant execute on Find_Value to smithj;

If you wanted to grant all users the ability to execute this function, you would execute the following:

grant execute on Find_Value to public;

Revoke Privileges on Functions/Procedures

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from user;

If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:

revoke execute on Find_Value from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke execute on Find_Value from public;

In Oracle, you can create your own functions.

The syntax for a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN – The parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or function.
2. OUT – The parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT – The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

The following is a simple example of a function:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

    cursor c1 is
select course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
END;

This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.

You could then reference your new function in an SQL statement as follows:

select course_name, FindCourse(course_name) as course_id
from courses
where subject = ‘Mathematics’;

A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code. We’ll take a look at three different syntaxes for cursors.

Cursor without parameters (simplest)

The basic syntax for a cursor without parameters is:

CURSOR cursor_name
IS
SELECT_statement;

For example, you could define a cursor called c1 as below.

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

The result set of this cursor is all course_numbers whose course_name matches the variable called name_in.

Below is a function that uses this cursor.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

    CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Cursor with parameters

The basic syntax for a cursor with parameters is:

CURSOR cursor_name (parameter_list)
IS
SELECT_statement;

For example, you could define a cursor called c2 as below.

CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
from courses_tbl
where subject_id = subject_id_in;

The result set of this cursor is all course_numbers whose subject_id matches the subject_id passed to the cursor via the parameter.

Cursor with return clause

The basic syntax for a cursor with a return clause is:

CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;

For example, you could define a cursor called c3 as below.

CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
SELECT *
from courses_tbl
where subject = ‘Mathematics’;

The result set of this cursor is all columns from the course_tbl where the subject is Mathematics.

In Oracle, you can create your own procedures.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN – The parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or function.
2. OUT – The parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT – The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

 

The following is a simple example of a procedure:

CREATE OR REPLACE Procedure UpdateCourse
( name_in IN varchar2 )
IS
cnumber number;

    cursor c1 is
select course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

insert into student_courses
( course_name,
course_number)
values ( name_in,
cnumber );

commit;

close c1;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
END;

This procedure is called UpdateCourse. It has one parameter called name_in. The procedure will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.

 

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Create an Index

The syntax for creating a index is:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a “plan of execution” when SQL statements are executed.

For Example:

CREATE INDEX supplier_idx
ON supplier (supplier_name);

In this example, we’ve created an index on the supplier table called supplier_idx. It consists of only one field – the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx
ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx
ON supplier (supplier_name, city)
COMPUTE STATISTICS;

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

The syntax for creating a function-based index is:

CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];

For Example:

CREATE INDEX supplier_idx
ON supplier (UPPER(supplier_name));

In this example, we’ve created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

Rename an Index

The syntax for renaming an index is:

ALTER INDEX index_name
RENAME TO new_index_name;

For Example:

ALTER INDEX supplier_idx
RENAME TO supplier_index_name;

In this example, we’re renaming the index called supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

The syntax for collecting statistics on an index is:

ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;

For Example:

ALTER INDEX supplier_idx
REBUILD COMPUTE STATISTICS;

In this example, we’re collecting statistics for the index called supplier_idx.

Drop an Index

The syntax for dropping an index is:

DROP INDEX index_name;

For Example:

DROP INDEX supplier_idx;

In this example, we’re dropping an index called supplier_idx.