Materialized Views in Oracle

Materialized Views in Oracle

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.
In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.
Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.


Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views

The following statement creates the rowid materialized view on table emp located on a remote database:
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:
SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)

Analytical Functions

Find more oracle material at http://download.35mb.com/kvreddy83

Enter your search terms

Submit search form

Oracle Analytic Functions




Analytic functions were introduced in Release 2 of 8i and simplify greatly the means by which pivot reports and OLAP queries can be computed in straight, non-procedural SQL. Prior to the introduction of analytic functions, complex reports could be produced in SQL by complex self-joins, sub-queries and inline-views but these were resource-intensive and very inefficient. Furthermore, if a question to be answered was too complex, it could be written in PL/SQL, which
by its very nature is usually less efficient than a single SQL statement.

There are three types of SQL extensions that fall under the banner of "analytic functions" though the first could be said to provide "analytic functionality" rather than actually be analytic
functions:

new grouping of resultsets through extensions to the GROUP BY clause (ROLLUP and CUBE)

new analytic functions themselves; and TOP-N analysis (largely enabled by the analytic functions).

Practical Examples

AVG
Returns a running average AVG(DISTINCT | ALL <_expression>) OVER
(analytic clause)
CREATE TABLE vote_count (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED
PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

Analytic functions were introduced in Release 2 of 8i and simplify greatly the means by which pivot reports and OLAP queries can be computed in straight, non-procedural SQL. Prior to the introduction of analytic functions, complex reports could be produced in SQL by complex self-joins, sub-queries and inline-views but these were resource-intensive and very inefficient. Furthermore, if a question to be answered was too complex, it could be written in PL/SQL, which by its very nature is usually less efficient than a single SQL statement.

There are three types of SQL extensions that fall under the banner of "analytic functions" though the first could be said to provide "analytic functionality" rather than actually be analytic functions:
new grouping of resultsets through extensions to the GROUP BY clause (ROLLUP and CUBE)
new analytic functions themselves; and
TOP-N analysis (largely enabled by the analytic functions).
Each of these will be dealt with in turn.
"Business-Savvy SQL" by Ganesh Variar details some of the improvements made in Oracle 9i to analytic functions, including the ability to use analytic functions in PL/SQL, the introduction of the new GROUPING SETS feature, and the new ability to supply hypothetical arguments to the RANKing function, to provide 'what if?' analysis.
Lastly, with Oracle Database 10g, the analytical capabilities of SQL have been further extended through the introduction of the SQL MODEL clause. According to Jonathan Gennick in the OTN article "Announcing The New Model":
You're writing a query that returns computed column values. As input to your calculations, you need values from multiple rows. What do you do? This is actually a fairly common problem. Solutions to it often involve inscrutable combinations of self-joins and subqueries.
Oracle Database 10g enables a new approach. Using the SELECT statement's new MODEL clause, you can treat relational data as a multidimensional array to which you can apply spreadsheet-like calculations. The result is a query that is easier to develop, easier to understand, and easier to modify.


AVG
AVG(DISTINCT | ALL ) OVER (analytic clause)
CREATE TABLE vote_count (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

CORR
CORR(, ) OVER ()
conn sh/sh

SELECT t.calendar_month_number,
CORR (SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.calendar_month_number) AS CUM_CORR
FROM sales s, times t
WHERE s.time_id = t.time_id AND calendar_year = 1998
GROUP BY t.calendar_month_number;

COUNT
COUNT(<*, [DISTINCT | ALL] >) OVER ()
SELECT submit_date, num_votes, TRUNC(COUNT(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AS DAY_COUNT
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, COUNT(*)
OVER(PARTITION BY submit_date ORDER BY submit_date
ROWS UNBOUNDED PRECEDING) NUM_RECS
FROM vote_count;

COVAR_POP
COVAR_POP(, ) OVER ()
conn oe/oe

SELECT job_id,
COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id;

COVAR_SAMP
COVAR_SAMP(, ) OVER ()
See COVAR_POP Demo above

CUME_DIST
CUME_DIST() OVER ( )
conn oe/oe

SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
WHERE job_id LIKE 'PU%';

DENSE_RANK
DENSE_RANK() OVER ( )
conn oe/oe

SELECT d.department_name, e.last_name, e.salary, DENSE_RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) AS DENSE_RANK
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN (30, 60);

FIRST
SELECT KEEP
(DENSE_RANK FIRST ORDER BY [ NULLS )
OVER (PARTITION BY )
FROM
GROUP BY ;
conn oe/oe

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id IN (30, 60)
ORDER BY department_id, salary;

FIRST_VALUE
FIRST_VALUE( [IGNORE NULLS])
OVER ()
conn oe/oe

SELECT last_name, salary, hire_date, FIRST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90
ORDER BY hire_date);

LAG
LAG(, , )
OVER ([] )
conn oe/oe

SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS PREV_SAL
FROM employees
WHERE job_id = 'PU_CLERK';

LAST
KEEP (DENSE_RANK LAST ORDER BY
( NULLS )
See FIRST Demo above

LAST_VALUE
LAST_VALUE ( IGNORE NULLS) OVER ()
conn oe/oe

SELECT last_name, salary, hire_date, FIRST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90
ORDER BY hire_date);

LEAD
LEAD()
[()]
OVER ()
SELECT submit_date, num_votes,
LEAD(num_votes, 1, 0) OVER (ORDER BY submit_date) AS NEXT_VAL
FROM vote_count;

MAX
MAX ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;

MIN
MIN ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;

NTILE
NTILE () OVER ([query_partition_clause] )
conn oe/oe

SELECT last_name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees
WHERE department_id = 100;

PERCENT_RANK
PERCENT_RANK() OVER ( )
conn oe/oe

SELECT department_id, last_name, salary, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
FROM employees
ORDER BY pr, salary;

PERCENTILE_CONT
PERCENTILE_CONT() WITHIN GROUP (ORDER BY [ASC | DESC]) OVER ()
conn oe/oe

SELECT last_name, salary, department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) PCT_CONT, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) PCT_RANK
FROM employees
WHERE department_id IN (30, 60);

PERCENTILE_DISC
PERCENTILE_DISC() WITHIN GROUP (ORDER BY )
conn oe/oe

col cume_dist format 9.999

SELECT last_name, salary, department_id,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) PCT_DISC,
CUME_DIST() OVER (PARTITION BY department_id
ORDER BY salary DESC) CUME_DIST
FROM employees
WHERE department_id IN (30, 60);

RANK
RANK() OVER ( ORDER BY )
conn oe/oe

SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) "Rank"
FROM employees
WHERE department_id = 80;

RATIO_TO_REPORT
RATIO_TO_REPORT() OVER ()
conn oe/oe

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS RR
FROM employees
WHERE job_id = 'PU_CLERK';

REGR_ (Linear Regression) Functions
Generic Syntax FUNCTION_NAME (,) OVER ()
REGR_AVGX See REGR_AVGY Demo

SELECT job_id, employee_id ID, salary,
REGR_SLOPE(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) slope,
REGR_INTERCEPT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) intcpt,
REGR_R2(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) rsqr,
REGR_COUNT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) count,
REGR_AVGX(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgx,
REGR_AVGY(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgy
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;
REGR_AVGY See REGR_AVGX Demo

SELECT job_id,
REGR_AVGY(SYSDATE-hire_date, salary) avgy,
REGR_AVGX(SYSDATE-hire_date, salary) avgx
FROM employees
WHERE department_id in (30, 50)
GROUP BY job_id;
REGR_COUNT See REGR_AVGX Demo

SELECT job_id,
REGR_COUNT(SYSDATE-hire_date, salary) count
FROM employees
WHERE department_id in (30, 50)
GROUP BY job_id;
REGR_INTERCEPT See REGR_AVGX Demo

SELECT job_id,
REGR_SLOPE(SYSDATE-hire_date, salary) slope,
REGR_INTERCEPT(SYSDATE-hire_date, salary) intercept
FROM employees
WHERE department_id in (50,80)
GROUP BY job_id
ORDER BY job_id;
REGR_R2 See REGR_AVGX Demo

SELECT job_id, REGR_R2(SYSDATE-hire_date, salary) Regr_R2
FROM employees
WHERE department_id IN (50, 80)
GROUP by job_id;
REGR_SLOPE See REGR_AVGX Demo
See REGR_INTERCEPT Demo
REGR_SXX SELECT job_id,
REGR_SXY(SYSDATE-hire_date, salary) regr_sxy,
REGR_SXX(SYSDATE-hire_date, salary) regr_sxx,
REGR_SYY(SYSDATE-hire_date, salary) regr_syy
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id
ORDER BY job_id;
REGR_SXY See REGR_SXX Demo
REGR_SYY See REGR_SXX Demo

ROW_NUMBER

ROW_NUMBER() OVER ( ORDER BY )
CREATE TABLE test (
id NUMBER(1),
degrees NUMBER(3));

INSERT INTO test VALUES (0,235);
INSERT INTO test VALUES (0,276);
INSERT INTO test VALUES (1,211);
INSERT INTO test VALUES (1,250);
INSERT INTO test VALUES (1,255);
INSERT INTO test VALUES (2,55);
INSERT INTO test VALUES (2,277);
INSERT INTO test VALUES (2,69);
INSERT INTO test VALUES (3,25);
INSERT INTO test VALUES (3,166);
INSERT INTO test VALUES (3,262);
INSERT INTO test VALUES (4,47);
INSERT INTO test VALUES (4,238);
INSERT INTO test VALUES (4,40);
COMMIT;

SELECT * FROM test;

-- choose the starting cell
SELECT id, degrees
FROM (
SELECT id, degrees, (360 - degrees) d360,
row_number() OVER(PARTITION BY id
ORDER BY CASE
WHEN (degrees < 360 - degrees) THEN degrees
ELSE 360 - degrees
END) rn
FROM test) t
WHERE rn = 1;

-- order the rest clockwise
SELECT *
FROM (
SELECT t.id, t.degrees,
row_number() over(partition by t.id
ORDER BY CASE
WHEN (t.degrees < starting_cell.degrees) THEN t.degrees + 360
ELSE t.degrees
END) rn
FROM test t
JOIN (
SELECT id, degrees, (360 - degrees) d360,
row_number() over(partition by id
ORDER BY CASE
WHEN (degrees < 360 - degrees) THEN degrees
ELSE 360 - degrees
END) rn
FROM test) starting_cell
ON t.id = starting_cell.id
WHERE starting_cell.rn=1)t
ORDER BY id, rn

STDDEV
STDDEV([DISTINCT | ALL] ) OVER ()
conn oe/oe

col stddev format 99999.999

SELECT last_name, salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;

STDDEV_POP
STDDEV_POP() OVER ()
conn oe/oe

SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees;

STDDEV_SAMP
STDDEV_SAMP() OVER ()
conn oe/oe

SELECT department_id, last_name, hire_date, salary,
STDDEV_SAMP(salary) OVER (PARTITION BY department_id
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees;

SUM

CREATE TABLE vote_count (
submit_date DATE NOT NULL,
vote_count NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, vote_count, SUM(vote_count)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING) TOT_VOTE
FROM vote_count
ORDER BY submit_date;

VAR_POP
VAR_POP() OVER ()
conn sh/sh

SELECT t.calendar_month_desc,
VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 2001
GROUP BY t.calendar_month_desc;

VAR_SAMP
VAR_SAMP() OVER ()
See VAR_POP Demo above

VARIANCE
VARIANCE([DISTINCT | ALL] ) OVER ()
SELECT last_name, salary,
VARIANCE(salary) OVER (ORDER BY hire_date) AS VARIANCE
FROM employees
WHERE department_id = 30;

Oracle Interview Questions

Please check http://download.35mb.com/kvreddy83/ for good oracle documents

Procedures/Functions

Enter your search terms

Submit search form

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.

*****************************************************************************************************

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.

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.
********************************************************************************************************

Enter your search terms

Submit search form

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




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

Enter your search terms

Submit search form