Search This Blog
Wednesday, 11 November 2015
Tuesday, 3 November 2015
MO_GLOBAL-Dive into R12 Multi Org Design
How is CLIENT_INFO being replaced in R12? Lets take an example. In pre Release 12, you would have had following methodology for PO_HEADERS_ALL a. A table is created in PO Schema, named PO_HEADERS_ALL b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL c. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info" But now in R12, following will happen a. A table is created in PO Schema, named PO_HEADERS_ALL b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL c. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL d. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY. This can be double-checked by running SQL select * from all_policies where object_name='PO_HEADERS' e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to below SELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id) Also see **** below, latter Does this mean, if I create a new custom table, I will have to apply RLS [ Row Level Security ] against Custom table too? Yes indeed, if it contains data partitioned by ORG_ID. All you need to do in such case is to assign package function MO_GLOBAL.ORG_SECURITY to that table/synonym/view. Will the Multi Org Row Level security be applied against the table or the synonym or the view? In theory, RLS can be applied against any of the above objects. However in practice, you will apply RLS against Objects in APPS Schema. This means, you will most probably apply RLS on Synonyms. Basically, the Multi Org Views are now replaced by RLS Secured Synonyms. Hence no code change is required where the pre-R12 Multi-Org secured view was being accessed. The responsibility of securing data as per ORG_ID now lies with RLS [also known as VPD - Virtual Private Database]. I have made changes to my Multi Org Security Profile, by attaching a new Org Hierarchy. Do i need to run any process? Just like we do in HRMS, it is advised that any changes to Security Profiles must be followed by running "Security List Maintenance" What is MO_GLOBAL.INITPurpose of mo_global.init :- It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used. If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp When & from where is mo_global.init called ? This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE Is mo_glob_org_access_tmp table a global temporary table? Yes, it is. Hence after Multi Org is initialised for your session, your session will have X number of records in table mo_glob_org_access_tmp. X is the number of organizations assigned to MO Security profile [view org hierarchy or org list in security profile] What is the purpose of MO_GLOBAL.ORG_SECURITY?The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending a where clause to the secured object. 1. MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE 2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ? This procedure has two parameters p_access_mode Pass a value "S" in case you want your current session to work against Single ORG_ID Pass a value of "M" in case you want your current session to work against multiple ORG_ID's p_org_id Only applicable if p_access_mode is passed value of "S" In SQL*Plus, I wish to set my session to work against a specific Org [one single org]. How do I do that in R12 SQL>> exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101); In the above case, ORG_ID 101 will be assigned as current org for your session. Internally, following code in blue will be executed by Oracle when you set your context to single Org, dbms_session.set_context('multi_org2', 'current_org_id', 101); **** If the current database session is initialised for Single Org[as in above step], then Where clause appended to object by Row-Level-Security will be WHERE org_id = sys_context('multi_org2','current_org_id') Why will I as a Apps Techie ever use MO_GLOBAL.SET_POLICY_CONTEXT ? Lets say you wish to call an API to create invoices in ORG_ID 101. In case the API does not have a parameter for Org_id, you can do the below a. exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101) b. Call the Invoice API, which will internally read the ORG_ID from MO Current Context. From SQL*Plus, I wish to simulate login to a specific responsibility. How do I do this? a. Call FND_GLOBAL.INITIALIZE This will set your responsibility id, user_id etc b. call MO_GLOBAL.INIT This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access. Copied Source Link |
Oracle Row Level Security
Since version 8i, Oracle Database has a functionality to restrict data from a table based on some configuration. Beauty of this functionality is that it is
seamlessly applied i.e., user has not to worry in reading the security definition and applying it in all of the queries. It is applied automatically by database engine.
/*
Test Case :
Environment : R12T (Vision Instance)
Scenario checked by using the purchasing responsibility
Parameters
user : OPERATIONS
Resp. : PURCHASING_OPERATIONS
*/
-- Total Records
SELECT COUNT(0) FROM PO_HEADERS_ALL
--23992 row found
-- Before script exection available records to apps user
SELECT COUNT(0) FROM PO_HEADERS
-- 0 rows found
-- To get the user and responsability info
SELECT US.USER_ID, US.USER_NAME, RM.RESPONSIBILITY_ID, RM.RESPONSIBILITY_KEY, RM.RESPONSIBILITY_NAME, RESPONSIBILITY_APPLICATION_ID, APS.APPLICATION_SHORT_NAME
FROM FND_USER_RESP_GROUPS_DIRECT RS ,FND_USER US ,fnd_responsibility_vl RM, FND_APPLICATION APS
WHERE RS.USER_ID = US.USER_ID
AND RS.RESPONSIBILITY_ID = RM.RESPONSIBILITY_ID
AND RS.RESPONSIBILITY_APPLICATION_ID = APS.APPLICATION_ID
AND US.USER_NAME = 'OPERATIONS'
AND RM.RESPONSIBILITY_KEY = 'PURCHASING_OPERATIONS'
--AND RM.RESPONSIBILITY_NAME = 'Purchasing'
--AND APS.APPLICATION_SHORT_NAME = 'PO'
AND RM.START_DATE <= TRUNC(SYSDATE)
AND (RM.END_DATE IS NULL OR RM.END_DATE >= TRUNC(SYSDATE))
AND US.START_DATE <= TRUNC(SYSDATE) AND (US.END_DATE IS NULL OR US.END_DATE >= TRUNC(SYSDATE))
;
-- Initialize global values
BEGIN FND_GLOBAL.APPS_INITIALIZE(1318, 50578, 201); END;
-- Initialize and set allowed organization into global temp table mo_glob_org_access_tmp
BEGIN MO_GLOBAL.INIT('PO'); END;
-- After script exection available records for the apps user
SELECT COUNT(0) FROM PO_HEADERS
-- show count of allowed data access set depending upon security profile
--** EXCEPTION
-- Wrong Res. id , User id, Apps id
BEGIN FND_GLOBAL.APPS_INITIALIZE(3333331013595, 33333350578, 333333201); END ;
----------------------------
-- In case of any exception call to reset org_id
BEGIN MO_GLOBAL.INIT('PO'); END;
BEGIN MO_GLOBAL.INIT('S',NULL); END;
-------------------------------------------
SELECT COUNT(0) FROM PO_HEADERS
--EXEC MO_GLOBAL.SET_POLICY_CONTEXT('M',202);
--EXEC FND_GLOBAL.INITIALIZE
-- select count(0) from mo_glob_org_access_tmp
Monday, 26 October 2015
Query to find Oracle EBS Application Short Name of a module
/* Find out Oracle EBS application short name with id */
SELECT FA.APPLICATION_ID "Application ID",
FAT.APPLICATION_NAME "Application Name",
FA.APPLICATION_SHORT_NAME "Application Short Name",
FA.BASEPATH "Basepath"
FROM FND_APPLICATION FA,
FND_APPLICATION_TL FAT
WHERE FA.APPLICATION_ID = FAT.APPLICATION_ID
AND FAT.LANGUAGE = USERENV('LANG')
AND UPPER(FAT.APPLICATION_NAME) LIKE '%PURCH%' -- <change it>
ORDER BY FAT.APPLICATION_NAME;
Thursday, 10 September 2015
SQL Queries for Checking Profile Option Values
SELECT fpo.profile_option_name SHORT_NAME,
fpot.user_profile_option_name PROFILE_NAME,
DECODE (fpov.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (fpov.level_id),
'10001', '',
'10002', fap.application_short_name,
'10003', frsp.RESPONSIBILITY_NAME,
'10005', fnod.node_name,
'10006', hou.name,
'10004', fu.user_name,
'UnDef')
"CONTEXT",
fpov.profile_option_value VALUE
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_user fu,
fnd_application fap,
FND_RESPONSIBILITY_TL frsp,
fnd_nodes fnod,
hr_operating_units hou
WHERE fpo.profile_option_id = fpov.profile_option_id(+)
AND fpo.profile_option_name = fpot.profile_option_name
AND fu.user_id(+) = fpov.level_value
AND frsp.application_id(+) = fpov.level_value_application_id
AND frsp.responsibility_id(+) = fpov.level_value
AND fap.application_id(+) = fpov.level_value
AND fnod.node_id(+) = fpov.level_value
AND hou.organization_id(+) = fpov.level_value
--AND fpot.user_profile_option_name IN ('User_Profile_Option_Name')
AND frsp.RESPONSIBILITY_NAME LIKE '%Global%HRMS%'
ORDER BY fpot.user_profile_option_name
fpot.user_profile_option_name PROFILE_NAME,
DECODE (fpov.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (fpov.level_id),
'10001', '',
'10002', fap.application_short_name,
'10003', frsp.RESPONSIBILITY_NAME,
'10005', fnod.node_name,
'10006', hou.name,
'10004', fu.user_name,
'UnDef')
"CONTEXT",
fpov.profile_option_value VALUE
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_user fu,
fnd_application fap,
FND_RESPONSIBILITY_TL frsp,
fnd_nodes fnod,
hr_operating_units hou
WHERE fpo.profile_option_id = fpov.profile_option_id(+)
AND fpo.profile_option_name = fpot.profile_option_name
AND fu.user_id(+) = fpov.level_value
AND frsp.application_id(+) = fpov.level_value_application_id
AND frsp.responsibility_id(+) = fpov.level_value
AND fap.application_id(+) = fpov.level_value
AND fnod.node_id(+) = fpov.level_value
AND hou.organization_id(+) = fpov.level_value
--AND fpot.user_profile_option_name IN ('User_Profile_Option_Name')
AND frsp.RESPONSIBILITY_NAME LIKE '%Global%HRMS%'
ORDER BY fpot.user_profile_option_name
Thursday, 20 August 2015
Favourite sites and blogs
- Oracle-Base
Tim Hall’s site of concise articles and new feature guides is one of the best resources for getting a feel for a new Oracle release, especially for database developers.
oracle forums
- OTN SQL-PL/SQL Forum
Some very high quality Oracle developers share their knowledge on this forum. - OraFAQ
Has some good developers answering a lot of questions on a wide range of categories.
Favorite Websites (References)
Wednesday, 19 August 2015
TOAD - Privileges required for Debugging Oracle Procedure
The following privileges are required for debugger:
1. GRANT ALTER SESSION TO user_name;
2. GRANT CREATE SESSION TO user_name;
3. GRANT EXECUTE ON DBMS_DEBUG to user_name;
Minimum requirements to debug other than your own procedures, functions, and packages:
1. GRANT ALTER ANY PROCEDURE TO user_name; (compile)
2. GRANT CREATE ANY PROCEDURE TO user_name; (edit / save)
The following additional privileges are required for debugger inversion 10g and any version released after that:
1. GRANT DEBUG ANY PROCEDURE TO user_name;
2. GRANT DEBUG CONNECT SESSION TO user_name;
Also, verify that the DBMS_DEBUG package in the sys schema is valid.
After the above, EXIT and RESTART Toad.
References:http://rupamverma.blogspot.com/2010/09/toad-privileges-required-for-debugging.html
Famous Quotes
1. Give a man a fish; feed him for a day. Teach him how to fish; feed him for a lifetime.
2. We make a living by what we get; we make a life by what we give. "Winston Churchill"
2. We make a living by what we get; we make a life by what we give. "Winston Churchill"
Oracle Application's URL from back end
option 1
SELECT home_url
FROM icx_parameters
option 2
Select PROFILE_OPTION_VALUE
From FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID =
(SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
AND LEVEL_VALUE=0;
SELECT home_url
FROM icx_parameters
option 2
Select PROFILE_OPTION_VALUE
From FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID =
(SELECT PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
AND LEVEL_VALUE=0;
Find concurrent program & Request set attached to a responsibility
I have a concurrent program which I want to submit .. but here is the problem. I dont know which responsibility this program is attached to. No wonder .. below is the query that can be used to find the list of responsibilities to which the programs is attached to.
Query to find concurrent program
Query to find concurrent program
select frt.responsibility_name, frg.request_group_name, frgu.request_unit_type,frgu.request_unit_id, fcpt.user_concurrent_program_name From fnd_Responsibility fr, fnd_responsibility_tl frt, fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt where frt.responsibility_id = fr.responsibility_id and frg.request_group_id = fr.request_group_id and frgu.request_group_id = frg.request_group_id and fcpt.concurrent_program_id = frgu.request_unit_id and frt.language = USERENV('LANG') and fcpt.language = USERENV('LANG') and fcpt.user_concurrent_program_name = :conc_prg_name order by 1,2,3,4
Query to find Request Set
select frt.responsibility_name, frg.request_group_name, frgu.request_unit_type,frgu.request_unit_id, fcpt.user_request_set_name From apps.fnd_Responsibility fr, apps.fnd_responsibility_tl frt, apps.fnd_request_groups frg, apps.fnd_request_group_units frgu, apps.fnd_request_Sets_tl fcpt where frt.responsibility_id = fr.responsibility_id and frg.request_group_id = fr.request_group_id and frgu.request_group_id = frg.request_group_id and fcpt.request_set_id = frgu.request_unit_id and frt.language = USERENV('LANG') and fcpt.language = USERENV('LANG') and fcpt.user_request_set_name = :request_set_name order by 1,2,3,4
Delete Cache in Oracle Apps R12
Many people are still are not aware on how to delete cache without taking DBA's help.
Here is the Navigation for the same
1)Go to Functional Administrator
2)Click on Core Services Tab
3)Click on Caching Framework Tab
4)Within Caching framework click on Global configuration
5)Under Cache policy click on "Clear ALL Cache" Button
5) Click on Apply
Here is the Navigation for the same
1)Go to Functional Administrator
2)Click on Core Services Tab
3)Click on Caching Framework Tab
4)Within Caching framework click on Global configuration
5)Under Cache policy click on "Clear ALL Cache" Button
5) Click on Apply
Oracle Analytic Functions
Analytic Functions
Introduced in Oracle 8i, analytic functions, also known as windowing functions, allow developers to perform tasks in SQL that were previously confined to procedural languages.
Related articles.
- RANK, DENSE_RANK, FIRST and LAST Analytic Functions
- FIRST_VALUE and LAST_VALUE Analytic Functions
- LAG and LEAD Analytic Functions
- LISTAGG Analystic Function in 11g Release 2
- Top-N Queries
- Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1)
Introduction
Probably the easiest way to understand analytic functions is to start by looking at aggregate functions. An aggregate function, as the name suggests, aggregates data from several rows into a single result row. For example, we might use the
AVG
aggregate function to give us an average of all the employee salaries in the EMP table.SELECT AVG(sal) FROM emp; AVG(SAL) ---------- 2073.21429 SQL>
The
GROUP BY
clause allows us to apply aggregate functions to subsets of rows. For example, we might want to display the average salary for each department.SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ORDER BY deptno; DEPTNO AVG(SAL) ---------- ---------- 10 2916.66667 20 2175 30 1566.66667 SQL>
In both cases, the aggregate function reduces the number of rows returned by the query.
Analytic functions also operate on subsets of rows, similar to aggregate functions in
GROUP BY
queries, but they do not reduce the number of rows returned by the query. For example, the following query reports the salary for each employee, along with the average salary of the employees within the department.SET PAGESIZE 50 BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp; EMPNO DEPTNO SAL AVG_DEPT_SAL ---------- ---------- ---------- ------------ 7782 10 2450 2916.66667 7839 10 5000 2916.66667 7934 10 1300 2916.66667 7566 20 2975 2175 7902 20 3000 2175 7876 20 1100 2175 7369 20 800 2175 7788 20 3000 2175 7521 30 1250 1566.66667 7844 30 1500 1566.66667 7499 30 1600 1566.66667 7900 30 950 1566.66667 7698 30 2850 1566.66667 7654 30 1250 1566.66667 14 rows selected. SQL>
This time
AVG
is an analytic function, operating on the group of rows defined by the contents of the OVER
clause. This group of rows is known as a window, which is why analytic functions are sometimes referred to as window[ing] functions. Notice how the AVG
function is still reporting the departmental average, like it did in the GROUP BY
query, but the result is present in each row, rather than reducing the total number of rows returned. This is because analytic functions are performed on a result set after all join, WHERE
, GROUP BY
and HAVING
clauses are complete, but before the final ORDER BY
operation is performed.Analytic Function Syntax
There are some variations in the syntax of the individual analytic functions, but the basic syntax for an analytic function is as follows.
analytic_function([ arguments ]) OVER (analytic_clause)
The
analytic_clause
breaks down into the following optional elements.[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
The sub-elements of the
analytic_clause
each have their own syntax diagrams, shown here. Rather than repeat the syntax diagrams, the following sections describe what each section of the analytic_clause
is used for.query_partition_clause
The
query_partition_clause
divides the result set into partitions, or groups, of data. The operation of the analytic function is restricted to the boundary imposed by these partitions, similar to the way a GROUP BY
clause affects the action of an aggregate function. If the query_partition_clause
is omitted, the whole result set is treated as a single partition. The following query uses an empty OVER
clause, so the average presented is based on all the rows of the result set.CLEAR BREAKS SELECT empno, deptno, sal, AVG(sal) OVER () AS avg_sal FROM emp; EMPNO DEPTNO SAL AVG_SAL ---------- ---------- ---------- ---------- 7369 20 800 2073.21429 7499 30 1600 2073.21429 7521 30 1250 2073.21429 7566 20 2975 2073.21429 7654 30 1250 2073.21429 7698 30 2850 2073.21429 7782 10 2450 2073.21429 7788 20 3000 2073.21429 7839 10 5000 2073.21429 7844 30 1500 2073.21429 7876 20 1100 2073.21429 7900 30 950 2073.21429 7902 20 3000 2073.21429 7934 10 1300 2073.21429 SQL>
If we change the
OVER
clause to include a query_partition_clause
based on the department, the averages presented are specifically for the department the employee belongs too.BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp; EMPNO DEPTNO SAL AVG_DEPT_SAL ---------- ---------- ---------- ------------ 7782 10 2450 2916.66667 7839 10 5000 2916.66667 7934 10 1300 2916.66667 7566 20 2975 2175 7902 20 3000 2175 7876 20 1100 2175 7369 20 800 2175 7788 20 3000 2175 7521 30 1250 1566.66667 7844 30 1500 1566.66667 7499 30 1600 1566.66667 7900 30 950 1566.66667 7698 30 2850 1566.66667 7654 30 1250 1566.66667 SQL>
order_by_clause
The
order_by_clause
is used to order rows, or siblings, within a partition. So if an analytic function is sensitive to the order of the siblings in a partition you should include an order_by_clause
. The following query uses the FIRST_VALUE
function to return the first salary reported in each department. Notice we have partitioned the result set by the department, but there is no order_by_clause
.BREAK ON deptno SKIP 1 DUPLICATES SELECT empno, deptno, sal, FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno) AS first_sal_in_dept FROM emp; EMPNO DEPTNO SAL FIRST_SAL_IN_DEPT ---------- ---------- ---------- ----------------- 7782 10 2450 2450 7839 10 5000 2450 7934 10 1300 2450 7566 20 2975 2975 7902 20 3000 2975 7876 20 1100 2975 7369 20 800 2975 7788 20 3000 2975 7521 30 1250 1250 7844 30 1500 1250 7499 30 1600 1250 7900 30 950 1250 7698 30 2850 1250 7654 30 1250 1250 SQL>
Now compare the values of the
FIRST_SAL_IN_DEPT
column when we include an order_by_clause
to order the siblings by ascending salary.SELECT empno, deptno, sal, FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno ORDER BY sal ASC NULLS LAST) AS first_val_in_dept FROM emp; EMPNO DEPTNO SAL FIRST_VAL_IN_DEPT ---------- ---------- ---------- ----------------- 7934 10 1300 1300 7782 10 2450 1300 7839 10 5000 1300 7369 20 800 800 7876 20 1100 800 7566 20 2975 800 7788 20 3000 800 7902 20 3000 800 7900 30 950 950 7654 30 1250 950 7521 30 1250 950 7844 30 1500 950 7499 30 1600 950 7698 30 2850 950 SQL>
In this case the "
ASC NULLS LAST
" keywords are unnecessary as ASC
is the default for an order_by_clause
and NULLS LAST
is the default for ASC
orders. When ordering by DESC
, the default is NULLS FIRST
.
It is important to understand how the
order_by_clause
affects display order. The order_by_clause
is guaranteed to affect the order of the rows as they are processed by the analytic function, but it may not always affect the display order. As a result, you must always use a conventional ORDER BY
clause in the query if display order is important. Do not rely on any implicit ordering done by the analytic function. Remember, the conventional ORDER BY
clause is performed after the analytic processing, so it will always take precedence.windowing_clause
We have seen previously the
query_partition_clause
controls the window, or group of rows, the analytic operates on. The windowing_clause
gives some analytic functions a further degree of control over this window within the current partition. The windowing_clause
is an extension of the order_by_clause
and as such, it can only be used if an order_by_clause
is present. The windowing_clause
has two basic forms.RANGE BETWEEN start_point AND end_point ROWS BETWEEN start_point AND end_point
Possible values for "start_point" and "end_point" are:
UNBOUNDED PRECEDING
: The window starts at the first row of the partition. Only available for start points.UNBOUNDED FOLLOWING
: The window ends at the last row of the partition. Only available for end points.CURRENT ROW
: The window starts or ends at the current row. Can be used as start or end point.value_expr PRECEDING
: A physical or logical offset before the current row using a constant or expression that evaluates to a positive numerical value. When used withRANGE
, it can also be an interval literal if theorder_by_clause
uses aDATE
column.value_expr FOLLOWING
: As above, but an offset after the current row.
The documentation states the start point must always be before the end point, but this is not true, as demonstrated by this rather silly, but valid, query.
SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING) AS avg_of_current_sal FROM emp; EMPNO DEPTNO SAL AVG_OF_CURRENT_SAL ---------- ---------- ---------- ------------------ 7934 10 1300 1300 7782 10 2450 2450 7839 10 5000 5000 7369 20 800 800 7876 20 1100 1100 7566 20 2975 2975 7788 20 3000 3000 7902 20 3000 3000 7900 30 950 950 7654 30 1250 1250 7521 30 1250 1250 7844 30 1500 1500 7499 30 1600 1600 7698 30 2850 2850 SQL>
In fact, the start point must be before or equal to the end point. In addition, the current row does not have to be part of the window. The window can be defined to start and end before or after the current row.
For analytic functions that support the
windowing_clause
, the default action is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". The following query is similar to one used previously to report the employee salary and average department salary, but now we have included an order_by_clause
so we also get the default windowing_clause
.SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno ORDER BY sal) AS avg_dept_sal_sofar FROM emp; EMPNO DEPTNO SAL AVG_DEPT_SAL_SOFAR ---------- ---------- ---------- ------------------ 7934 10 1300 1300 7782 10 2450 1875 7839 10 5000 2916.66667 7369 20 800 800 7876 20 1100 950 7566 20 2975 1625 7788 20 3000 2175 7902 20 3000 2175 7900 30 950 950 7654 30 1250 1150 7521 30 1250 1150 7844 30 1500 1237.5 7499 30 1600 1310 7698 30 2850 1566.66667 SQL>There are two things to notice here.
- The addition of the
order_by_clause
without awindowing_clause
means the query is now returning a running average. - The default
windowing_clause
is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", not "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". The fact it is RANGE, not ROWS, means it stops at the first occurrence of the value in the current row, even if that is several rows earlier. As a result, duplicate rows are only included in the average when the salary value changes. You can see this in the last two records of department 20 and in the second and third records of department 30.
In my opinion, the default
windowing_clause
should have been "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING". This would make the accidental inclusion of the windowing_clause
much less confusing.
The following query shows one method for accessing data from previous and following rows within the current row using the
windowing_clause
. This can also be accomplished with LAG and LEAD.CLEAR BREAKS SELECT empno, deptno, sal, FIRST_VALUE(sal) OVER (ORDER BY sal ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS previous_sal, LAST_VALUE(sal) OVER (ORDER BY sal ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS next_sal FROM emp; EMPNO DEPTNO SAL PREVIOUS_SAL NEXT_SAL ---------- ---------- ---------- ------------ ---------- 7369 20 800 800 950 7900 30 950 800 1100 7876 20 1100 950 1250 7521 30 1250 1100 1250 7654 30 1250 1250 1300 7934 10 1300 1250 1500 7844 30 1500 1300 1600 7499 30 1600 1500 2450 7782 10 2450 1600 2850 7698 30 2850 2450 2975 7566 20 2975 2850 3000 7788 20 3000 2975 3000 7902 20 3000 3000 5000 7839 10 5000 3000 5000 SQL>
Using Analytic Functions
The best way to understand what analytic functions are capable of is to play around with them. This article contains links to other articles I've written about specific analytic functions and the following documentation links list all analytic functions available in Oracle 12c Release 1. The "*" indicates that these functions allow for the full analytic syntax, including the
windowing_clause
.- AVG *
- CLUSTER_DETAILS
- CLUSTER_DISTANCE
- CLUSTER_ID
- CLUSTER_PROBABILITY
- CLUSTER_SET
- CORR *
- COUNT *
- COVAR_POP *
- COVAR_SAMP *
- CUME_DIST
- DENSE_RANK
- FEATURE_DETAILS
- FEATURE_ID
- FEATURE_SET
- FEATURE_VALUE
- FIRST
- FIRST_VALUE *
- LAG
- LAST
- LAST_VALUE *
- LEAD
- LISTAGG
- MAX *
- MIN *
- NTH_VALUE *
- NTILE
- PERCENT_RANK
- PERCENTILE_CONT
- PERCENTILE_DISC
- PREDICTION
- PREDICTION_COST
- PREDICTION_DETAILS
- PREDICTION_PROBABILITY
- PREDICTION_SET
- RANK
- RATIO_TO_REPORT
- REGR_ (Linear Regression) Functions *
- ROW_NUMBER
- STDDEV *
- STDDEV_POP *
- STDDEV_SAMP *
- SUM *
- VAR_POP *
- VAR_SAMP *
- VARIANCE *
For more information see:
TOAD SQL Editor
Predefined Shortcuts
Keyboard shortcuts are one of the features that make TOAD so powerful and easy to use. TOAD comes with a host of predefined shortcuts. These shortcuts save keystrokes and mouse actions and perform a variety of tasks such as issuing a describe on the current highlighted object, or find (find next, find previous, and so on). The shortcuts differ slightly between the SQL Editor and the PL/SQL Editor (covered in Chapter 4). This section focuses on the shortcuts for the SQL Editor only.
Table 3.1 shows all the shortcuts in keystroke order and Table 3.2 shows all the shortcuts in description order. The third column refers you to the figure in this chapter where the shortcut is illustrated.
Table 3.1 SQL Editor Shortcuts by Keystroke
Shortcut
|
Description
|
Illustration
|
F1
|
Windows Help File
| |
F2
|
Toggle Output Window
| |
Shift+F2
|
Toggle Data Grid Window
| |
F3
|
Find Next Occurrence
| |
Shift+F3
|
Find Previous Occurrence
| |
F4
|
Describe Table, View, Procedure, Function
| |
F5
|
Execute SQL as a script
| |
F6
|
Toggle between SQL Editor and Results Panel
| |
F7
|
Clear All Text
| |
F8
|
Recall Previous SQL Statement
| |
F9
|
Execute Statement
| |
Ctrl+F9
|
Set Code Execution Parameters
| |
Shift+F9
|
Execute Current SQL statement at Cursor
| |
F10 or right-click
|
Pop-up Menu
| |
Ctrl+F12
|
External Editor, Pass Contents
| |
Ctrl+A
|
Select All Text
| |
Ctrl+C
|
Copy
| |
Ctrl+E
|
Execute Explain Plan on the Current Statement
| |
Ctrl+F
|
Find Text
| |
Ctrl+G
|
Goto Line
| |
Ctrl+L
|
Convert Text to Lowercase
| |
Ctrl+M
|
Make Code Statement
| |
Ctrl+N
|
Recall Named SQL Statement
| |
Ctrl+O
|
Open a Text File
| |
Ctrl+P
|
Strip Code Statement
| |
Ctrl+R
|
Find and Replace
| |
Ctrl+S
|
Save File
| |
Shift+Ctrl+S
|
Save File As
| |
Ctrl+T
|
Columns Drop-down
| |
Shift+Ctrl+R
|
Alias Replacement
| |
Shift+Ctrl+T
|
Columns Drop-Down no alias
| |
Ctrl+Spacebar
|
Code Templates
| |
Ctrl+U
|
Converts Text to Uppercase
| |
Ctrl+V
|
Paste
| |
Ctrl+X
|
Cut
| |
Ctrl+Z
|
Undo Last Change
| |
Ctrl+.
|
Display Pop-up List of Matching Table Names
| |
Shift+Ctrl+Z
|
Redo Last Undo
| |
Alt+Up Arrow
|
Display Previous Statement
| |
Alt+Down Arrow
|
Display Next Statement (After Alt+Up Arrow)
| |
Ctrl+Home
|
In the data grid: goes to the top of the record set
| |
Ctrl+End
|
In the data grid: goes to the end of the record set
| |
Ctrl+Tab
|
Cycles through the Collection of MDI Child Windows
|
TIP
The competent TOAD user makes use of Shift+F9 to execute SQL statements one at a time out of a script, and F8 to recall the previous SQL statement. The TOAD user also makes use of cut and paste to move code between TOAD windows.
Shift+F9 executes single SQL statements.
F8 recalls the previous SQL statement.
Table 3.2 SQL Editor Shortcuts by Description
Description
|
Shortcut
|
Illustration
|
Alias Replacement
|
Shift+Ctrl+R
| |
Clear All Text
|
F7
| |
Code Templates
|
Ctrl+Spacebar
| |
Columns Drop-down
|
Ctrl+T
| |
Columns Drop-down no alias
|
Shift+Ctrl+T
| |
Convert Text to Lowercase
|
Ctrl+L
| |
Convert Text to Uppercase
|
Ctrl+U
| |
Copy
|
Ctrl+C
| |
Cut
|
Ctrl+X
| |
Cycles through the Collection of MDI Child Windows
|
Ctrl+Tab
| |
Describe Table, View, Procedure, Function, or Package
|
F4
| |
Display Next Statement (After Alt+Up Arrow)
|
Alt+Down Arrow
| |
Display Previous Statement
|
Alt+Up Arrow
| |
Display Pop-up List of Matching Table Names
|
Ctrl+.
| |
Execute Current SQL Statement at Cursor
|
Shift+F9
| |
Execute SQL as a Script
|
F5
| |
Execute Explain Plan on the Current SQL Statement
|
Ctrl+E
| |
Execute SQL Statement
|
F9
| |
External Editor, Pass Contents
|
Ctrl+F12
| |
Find and Replace
|
Ctrl+R
| |
Find Next Occurrence
|
F3
| |
Find Previous Occurrence
|
Shift+F3
| |
Find Text
|
Ctrl+F
| |
Goto Line
|
Ctrl+G
| |
In the data grid: goes to the end of the record set
|
Ctrl+End
| |
In the data grid: goes to the top of the record set
|
Ctrl+Home
| |
Make Code Statement
|
Ctrl+M
| |
Open a Text File
|
Ctrl+O
| |
Paste
|
Ctrl+V
| |
Pop-up Menu
|
F10 or RT-Mouse
| |
Recall Named SQL
|
Ctrl+N
| |
Recall Previous SQL Statement
|
F8
| |
Redo Last Undo
|
Shift+Ctrl+Z
| |
Save File
|
Ctrl+S
| |
Save File As
|
Shift+Ctrl+S
| |
Select All Text
|
Ctrl+A
| |
Strip Code Statement
|
Ctrl+P
| |
Toggle between SQL Editor and Results Panel
|
F6
| |
Toggle Full Screen Editor
|
F2
| |
Undo Last Change
|
Ctrl+Z
| |
Verify Statement Without Execution (Parse)
|
Ctrl+F9
| |
Windows Help File
|
F1
|
Reference : http://www.informit.com/articles/article.aspx?p=31544&seqNum=2
Subscribe to:
Posts (Atom)