Informatica Reference

Archive for April 2012

An inline view is term given to sub query in FROM clause of query which can be used as table. Inline view effectively is a named sub query

Ex : Select Tab1.col1,Tab1.col.2,Inview.col1,Inview.Col2
From Tab1, (Select statement) Inview
Where Tab1.col1=Inview.col1

SELECT DNAME, ENAME, SAL FROM EMP ,
(SELECT DNAME, DEPTNO FROM DEPT) D
WHERE A.DEPTNO = B.DEPTNO

In the above query (SELECT DNAME, DEPTNO FROM DEPT) D is the inline view.

Inline views are determined at runtime, and in contrast to normal view they are not stored in the data dictionary,

There are lot of instances when we want data from sub query to available in main query, before oracle 7.3 only option was to create view and use that in sub query

Disadvantage of using this is

1.Separate view need to be created which is an overhead
2.Extra time taken in parsing of view

This problem is solved by inline view by using select statement in sub query and using that as table.

Advantage of using inline views:

1. Better query performance
2. Better visibility of code

Practical use of Inline views:

1. Joining Grouped data with Non grouped data
2. Getting data to use in another query

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:

  1. You executed a SELECT INTO statement and no rows were returned.
  2. You referenced an uninitialized row in a table.
  3. 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.’);

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;