Please check http://download.35mb.com/kvreddy83/ for good oracle documents
Procedures/Functions
Pl/SQL procedure and functions are examples of PL/SQL named blocks.
Pl/SQL procedure and functions are examples of PL/SQL named blocks.
The Difference between a procedure and function is that function will return a single value to a calling program such as SQL select statement. A procedure, on the other hand, does not return value, only a status code. However, procedure may have one or many variables that can be set and returned as part of the argument list to the procedures
Advantages
Procedures are compiled and stored in the data dictionary once. When more than one user needs to call the procedure, it is already compiled and only one copy of the stored procedure exists in the shared pool.
In addition, network traffic is reduced, even if the procedural features are not used.
How Procedures and Functions Differ
You create a procedure to store a series of action for later execution. A procedure can contain ZERO or more parameter that can be transferred to and from the calling environment, but a procedure does not have a return a value.
You create a function when you want to compute a value, which be returned to the calling environment. A function can contain Zero or more parameters that transferred from the calling environment. Function should return only a single value, and the value is returned through a RETURN statement. Functions used in SQL statements cannot have OUT or IN OUT mode parameters.
Advantages
Procedures are compiled and stored in the data dictionary once. When more than one user needs to call the procedure, it is already compiled and only one copy of the stored procedure exists in the shared pool.
In addition, network traffic is reduced, even if the procedural features are not used.
How Procedures and Functions Differ
You create a procedure to store a series of action for later execution. A procedure can contain ZERO or more parameter that can be transferred to and from the calling environment, but a procedure does not have a return a value.
You create a function when you want to compute a value, which be returned to the calling environment. A function can contain Zero or more parameters that transferred from the calling environment. Function should return only a single value, and the value is returned through a RETURN statement. Functions used in SQL statements cannot have OUT or IN OUT mode parameters.
*****************************************************************************************************
Definer rights
A routine stored in the database by default, is executed with the definer rights (owner of the routine), depending on the user who calls it. This is a good way of having the required code perform process logic in one place. It gives better control, preventing direct access to objects that belong to another user, which might result in security issues.
For example, table APPPARMST belongs to schema A. User A creates a procedure UPDATE_PAR allowing for updates of a table. User B is granted execute privileges on the procedure. Now user B cannot access the table as no privileges have been granted, but can call the procedure to do the required process logic for updating the table.
Invoker Rights
Invoker rights are a new model for resolving references to database elements in a PL/SQL program unit. From Oracle 8i onwards, we can decide if a program unit should run with the authority of the definer or of the invoker. This means that multiple schemas, accessing only those elements belonging to the invoker, can share the same piece of code.
For example, let's take the above case. The table, APPPARMST, is created in schema B also. Each of the schemas will now own the same set of objects but different data, as they are being used for different purposes. Since the called procedure, UPDATE_PAR, is owned by User A, the ideal solution in Oracle 8 and earlier releases, was to compile it in schema B also, so that it will use the objects thereof.
A routine stored in the database by default, is executed with the definer rights (owner of the routine), depending on the user who calls it. This is a good way of having the required code perform process logic in one place. It gives better control, preventing direct access to objects that belong to another user, which might result in security issues.
For example, table APPPARMST belongs to schema A. User A creates a procedure UPDATE_PAR allowing for updates of a table. User B is granted execute privileges on the procedure. Now user B cannot access the table as no privileges have been granted, but can call the procedure to do the required process logic for updating the table.
Invoker Rights
Invoker rights are a new model for resolving references to database elements in a PL/SQL program unit. From Oracle 8i onwards, we can decide if a program unit should run with the authority of the definer or of the invoker. This means that multiple schemas, accessing only those elements belonging to the invoker, can share the same piece of code.
For example, let's take the above case. The table, APPPARMST, is created in schema B also. Each of the schemas will now own the same set of objects but different data, as they are being used for different purposes. Since the called procedure, UPDATE_PAR, is owned by User A, the ideal solution in Oracle 8 and earlier releases, was to compile it in schema B also, so that it will use the objects thereof.
With Oracle 8i, there is no need for this duplication of code. A single compiled program unit can be made to use schema A's objects when invoked by User A and schema B's objects when invoked by User B. This way, we have the option of creating a code repository in one place and sharing it with various production users. The owner of the routine must grant EXECUTE privilege to other users.
To enable code to run with Invoker rights, an AUTHID clause needs to be used before the IS or AS keyword in the routine header. The AUTHID clause tells Oracle whether the routine is to be run with the invoker rights (CURRENT_USER), or with the Owner rights (DEFINER). If you do not specify this clause, Oracle by default assumes it to be AUTHID DEFINER.
E.g.
create or replace procedure update_par(pi_parcod in varchar2,
pi_val in varchar2,
pio_status in out varchar2)
authid current_user is
begin
pio_status = 'OK';
update appparmst
set parval = pi_val
where parcod = pi_parcod
and rownum = 1;
if sql%notfound then
pio_status = 'Error in resetting the parameter';
end if;
end;
Restriction in using Invoker rights
1. When compiling a new routine, direct privileges are only considered to resolve any external references. Grants through roles are ignored. The same applies when executing a routine created with invoker rights.
2. AUTHID is specified in the header of a program unit. The same cannot be specified for individual programs or methods within a package or object type.
3. Definer rights will always be used to resolve any external references when compiling a new routine.
4. Maintain extra caution on privileges being assigned to a different user. If the wrong privileges are assigned, a routine with invoker rights may have a mind of its own! Such issues would be difficult to debug. So ensure that the grants are perfectly in place.
5. For an invoker rights routine referred in a view or a database trigger, the owner of these objects is always considered as the invoker, and not the user triggering it.
E.g.
SQL> DOC The above Procedure is created in user A and user B refers it.
SQL> conn a/a@oradata1
Connected.
SQL> grant execute on update_par to B;
Grant succeeded.
SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' user, l_status);
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from a.appparmst;
PARCOD PARVAL
-------------------- ----------------------------------------------------
updated by User A
SQL> conn b/b@oradata1
Connected.
SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' user, l_status);
5 commit;
6 end;
declare
*
ERROR at line 1
ORA-00942 table or view does not exist
ORA-06512 at "A.UPDATE_PAR", line 6
ORA-06512 at line 4
SQL> DOC the error occurred because table APPPARMST does not exist for user B.
DOC> I create it for user B and then call update_par again
SQL> CREATE TABLE APPPARMST
2 (PARCOD VARCHAR2(20) NOT NULL,
3 PARVAL VARCHAR2(200));
Table created.
SQL> insert into appparmst values('updated by', null);
1 row created.
SQL> commit;
Commit complete.
SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' user, l_status);
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from b.appparmst;
PARCOD PARVAL
-------------------- ---------------------------------------
updated by User B
SQL> DOC example over.
Invoker rights is a powerful option, to be used with caution. To reduce code maintenance, this option should be thought of in the design stage, based on the need to share code across schemas with a similar setup.
********************************************************************************************************
Creation of Global Temporary Tables
Applications often use some form of temporary data store for processes that are to complicate to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
• Creation of Temporary Global Tables
• Miscellaneous Features
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
Miscellaneous Features for Temporary Tables
• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index are that same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.
E.g.
create or replace procedure update_par(pi_parcod in varchar2,
pi_val in varchar2,
pio_status in out varchar2)
authid current_user is
begin
pio_status = 'OK';
update appparmst
set parval = pi_val
where parcod = pi_parcod
and rownum = 1;
if sql%notfound then
pio_status = 'Error in resetting the parameter';
end if;
end;
Restriction in using Invoker rights
1. When compiling a new routine, direct privileges are only considered to resolve any external references. Grants through roles are ignored. The same applies when executing a routine created with invoker rights.
2. AUTHID is specified in the header of a program unit. The same cannot be specified for individual programs or methods within a package or object type.
3. Definer rights will always be used to resolve any external references when compiling a new routine.
4. Maintain extra caution on privileges being assigned to a different user. If the wrong privileges are assigned, a routine with invoker rights may have a mind of its own! Such issues would be difficult to debug. So ensure that the grants are perfectly in place.
5. For an invoker rights routine referred in a view or a database trigger, the owner of these objects is always considered as the invoker, and not the user triggering it.
E.g.
SQL> DOC The above Procedure is created in user A and user B refers it.
SQL> conn a/a@oradata1
Connected.
SQL> grant execute on update_par to B;
Grant succeeded.
SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' user, l_status);
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from a.appparmst;
PARCOD PARVAL
-------------------- ----------------------------------------------------
updated by User A
SQL> conn b/b@oradata1
Connected.
SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' user, l_status);
5 commit;
6 end;
declare
*
ERROR at line 1
ORA-00942 table or view does not exist
ORA-06512 at "A.UPDATE_PAR", line 6
ORA-06512 at line 4
SQL> DOC the error occurred because table APPPARMST does not exist for user B.
DOC> I create it for user B and then call update_par again
SQL> CREATE TABLE APPPARMST
2 (PARCOD VARCHAR2(20) NOT NULL,
3 PARVAL VARCHAR2(200));
Table created.
SQL> insert into appparmst values('updated by', null);
1 row created.
SQL> commit;
Commit complete.
SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' user, l_status);
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from b.appparmst;
PARCOD PARVAL
-------------------- ---------------------------------------
updated by User B
SQL> DOC example over.
Invoker rights is a powerful option, to be used with caution. To reduce code maintenance, this option should be thought of in the design stage, based on the need to share code across schemas with a similar setup.
********************************************************************************************************
Creation of Global Temporary Tables
Applications often use some form of temporary data store for processes that are to complicate to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
• Creation of Temporary Global Tables
• Miscellaneous Features
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
Miscellaneous Features for Temporary Tables
• If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index are that same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.
Please check http://download.35mb.com/kvreddy83/ for good oracle documents
Please post comments for any queries and any oracle books.
Going forward lot of oracle material will be added to the post
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
No comments:
Post a Comment