Package Getting Hung While Compiling

get the locked objects:

SELECT s.sid,

      s.serial#,

       l.lock_type,

       l.mode_held,

       l.mode_requested,

       l.lock_id1

    FROM   dba_lock_internal l,

       v$session s

    WHERE s.sid = l.session_id

    AND UPPER(l.lock_id1) LIKE '%PKG_NAME%'

    AND l.lock_type = 'Body Definition Lock';

Kill the sessions:

ALTER SYSTEM KILL SESSION 'sid,serial#' ;

Oracle Objects Being Accessed by Programs/Users

Objects Being Accessed:

select client_identifier,program,state,object,module, action,s.* 

from gv$session s , gv$access a 

where status='ACTIVE'

and a.sid = s.sid and a.inst_id = s.inst_id
and object in ('Object_name');

Objects are Locked by DDL:

select * from
DBA_DDL_LOCKS
where name = :name_object;

DB Lock in the Oracle Objects

 SELECT object_name, machine, s.sid, s.serial#, s.*

FROM gv$locked_object l, dba_objects o, gv$session s  

WHERE l.object_id = o.object_id

AND l.session_id = s.sid;

Release SESSION SQL:  
--alter system kill session 'sid, serial#';  
ALTER system kill session '23, 1647';

Flexfields server-side validation package report error. virtual_value_set() exception : ORA-06502: PLSQL numeric or value error: number precision too large column#x

First of all we need to understand that this issue is coming from a seeded package. i.e. fnd_flex_server1

In this package you can search with the procedure name you are seeing in the error message to confirm where the error is coming from.

The issue might be due to either KFF or DFF as the same package is being used for both the flex fields.

In my case the below query was returning the record and it was failing while iterating the data from the cursor.

You need to pass table_id, application id, flex field name, context to get the records using the cursor.

Query:

SELECT g.end_user_column_name, g.application_column_name,

              c.column_type, c.width, g.required_flag,

              g.security_enabled_flag, g.concatenation_description_len,

              g.default_type, g.default_value, g.flex_value_set_id,

              g.runtime_property_function,

              NULL

       FROM fnd_descr_flex_column_usages g, fnd_columns c

      WHERE g.application_id = descstruct.application_id

        AND g.descriptive_flexfield_name = descstruct.desc_flex_name

        AND g.descriptive_flex_context_code = descstruct.desc_flex_context

        AND g.enabled_flag = 'Y'

        AND c.application_id = t_apid

        AND c.table_id = t_id

        AND c.column_name = g.application_column_name

      ORDER BY g.column_seq_num;

 

Check Rollback Blocks Post Concurrent Request is Cancelled

  SELECT inst_id,

         usn,

         state,

         undoblockstotal                     "Total",

         undoblocksdone                      "Done",

         undoblockstotal - undoblocksdone    "ToDo",

         inst_id,

         xid,

         rcvservers,

         DECODE (

             cputime,

             0, 'unknown',

               SYSDATE

             + (  (  (undoblockstotal - undoblocksdone)

                   / (undoblocksdone / DECODE (cputime, 0, 1)))

                / 86400))                    "Estimated time to complete"

    FROM gv$fast_start_transactions

   WHERE state != 'RECOVERED'

ORDER BY 1, 2;

End Date a Responsibility For an FND User(Backend Script)

 DECLARE

    v_user_name             VARCHAR2 (100) := 'ROHAN_RAJPUT';

    v_responsibility_name   VARCHAR2 (100) := 'AR XX Superuser';  -- Resp name

    v_application_name      VARCHAR2 (100) := NULL;

    v_responsibility_key    VARCHAR2 (100) := NULL;

    v_security_group        VARCHAR2 (100) := NULL;

BEGIN

    SELECT fa.application_short_name,

           fr.responsibility_key,

           frg.security_group_key

      INTO v_application_name, v_responsibility_key, v_security_group

      FROM fnd_responsibility     fr,

           fnd_application        fa,

           fnd_security_groups    frg,

           fnd_responsibility_tl  frt

     WHERE     fr.application_id = fa.application_id

           AND fr.data_group_id = frg.security_group_id

           AND fr.responsibility_id = frt.responsibility_id

           AND frt.LANGUAGE = USERENV ('LANG')

           AND frt.responsibility_name = v_responsibility_name;

    fnd_user_pkg.delresp (username         => v_user_name,

                          resp_app         => v_application_name,

                          resp_key         => v_responsibility_key,

                          security_group   => v_security_group);

    COMMIT;

    DBMS_OUTPUT.put_line (

           'Responsiblity '

        || v_responsibility_name

        || ' is removed from the user '

        || v_user_name

        || ' Successfully');

EXCEPTION

    WHEN OTHERS

    THEN

        DBMS_OUTPUT.put_line (

               'Error encountered while deleting responsibilty from the user and the error is '

            || SQLERRM);

END;

/

ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "package name" has been invalidated

Ideally this error comes when the package in question is Invalid but sometime we see this error even if our package is Valid.

Instead of getting into too much details and debugging just request your DBAs to recompile the package or do it yourself if you are a confident developer with apps access ;) 

To compile both of sepc and body :-

                                Alter package pkg_name compile;

To compile only Body :-

                                Alter package pkg_name compile body;

If its recompiled without any error then just retest.

That's it.

Check Scheduled Concurrent Programs in Oracle Apps

SELECT fcr.request_id,
       DECODE (fcpt.user_concurrent_program_name,
               'Report Set', 'Report Set:' || fcr.description,
               fcpt.user_concurrent_program_name)
          CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2 (fcr.resubmit_interval,
             'PERIODICALLY',
             NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE'))
          PROG_SCHEDULE_TYPE,
       DECODE (
          NVL2 (fcr.resubmit_interval,
                'PERIODICALLY',
                NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
          'PERIODICALLY',    'EVERY '
                          || fcr.resubmit_interval
                          || ' '
                          || fcr.resubmit_interval_unit_code
                          || ' FROM '
                          || fcr.resubmit_interval_type_code
                          || ' OF PREV RUN',
          'ONCE',    'AT :'
                  || TO_CHAR (fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
          'EVERY: ' || fcrc.class_info)
          PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests fcr,
       apps.fnd_user fu,
       apps.fnd_conc_release_classes fcrc
 WHERE     fcpt.application_id = fcr.program_application_id
       AND fcpt.concurrent_program_id = fcr.concurrent_program_id
       AND fcr.requested_by = fu.user_id
       AND fcr.phase_code = 'P'
       AND fcr.requested_start_date > SYSDATE
       AND fcpt.LANGUAGE = 'US'
       AND fcrc.release_class_id(+) = fcr.release_class_id
       AND fcrc.application_id(+) = fcr.release_class_app_id;

Retrieve DFF data in Oracle Apps

  SELECT ffv.descriptive_flexfield_name        "DFF Name",
         ffv.application_table_name            "Table Name",
         ffv.title                             "Title",
         ap.application_name                   "Application",
         ffc.descriptive_flex_context_code     "Context Code",
         ffc.descriptive_flex_context_name     "Context Name",
         ffc.description                       "Context Desc",
         ffc.enabled_flag                      "Context Enable Flag",
         att.column_seq_num                    "Segment Number",
         att.form_left_prompt                  "Segment Name",
         att.application_column_name           "Column",
         fvs.flex_value_set_name               "Value Set",
         att.display_flag                      "Displayed",
         att.enabled_flag                      "Enabled",
         att.required_flag                     "Required"
    FROM apps.fnd_descriptive_flexs_vl   ffv,
         apps.fnd_descr_flex_contexts_vl ffc,
         apps.fnd_descr_flex_col_usage_vl att,
         apps.fnd_flex_value_sets        fvs,
         apps.fnd_application_vl         ap
   WHERE     ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
         AND ap.application_id = ffv.application_id
         AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
         AND ffv.application_id = ffc.application_id
         AND ffc.descriptive_flex_context_code =
             att.descriptive_flex_context_code
         --and descriptive_flexfield_name like '%Site Uses%'
         AND fvs.flex_value_set_id(+) = att.flex_value_set_id
         --AND (ffv.title) like 'Site Use Information'
         --and ffv.application_table_name='RA_INTERFACE_LINES_ALL'
         AND ffv.descriptive_flexfield_name = 'RA_INTERFACE_LINES'
         AND ffc.descriptive_flex_context_code LIKE 'ORDER ENTRY'
ORDER BY att.column_seq_num

Change Oracle Apps password from Backend

SET SERVEROUTPUT ON;

DECLARE
      flag BOOLEAN;
BEGIN
      flag := fnd_user_pkg.changepassword(username=> 'MY_USER'
                                          ,newpassword => 'welcome1');
    IF flag
     THEN
           DBMS_OUTPUT.PUT_LINE('Your Password has been successfully reset');
     ELSE
           DBMS_OUTPUT.PUT_LINE('Password reset has failed');
     END IF;
END;
/
COMMIT;

Query to check Responsibilities Assigned to an Apps User

SELECT fu.user_name,
       fr.responsibility_name,
       furg.START_DATE,
       furg.END_DATE
  FROM fnd_user_resp_groups_direct furg,
       fnd_user fu,
       fnd_responsibility_tl fr
 WHERE     fu.user_name = UPPER ('&user_name')
       AND furg.user_id = fu.user_id
       AND furg.responsibility_id = fr.responsibility_id
       AND fr.language = USERENV ('LANG');

Execution History of an SQL ID

-- This script allows you to view the execution history of a SQL ID. Use it to investigate things like:

-- 1. Changing plan hash values.

-- 2. High elapsed time per execution.

-- 3. High % of cluster waits.

-- 4. Whether a SQL profile is being used.

SELECT   snap_id "Snap"
       , sql_id "SQL ID"
       , plan_hash_value "Plan"
       , end_interval_time "End Time"
       , executions "Execs"
       , ROUND(CASE WHEN executions > 0 THEN elapsed_time / executions ELSE elapsed_time END, 4) "ElapPexec"
       , ROUND(CASE WHEN executions > 0 THEN rows_processed / executions ELSE rows_processed END, 2) "RowPexec"
       , ROUND(CASE WHEN executions > 0 THEN buffer_gets / executions ELSE buffer_gets END, 0) "GetPexec"
       , ROUND(CASE WHEN executions > 0 THEN disk_reads / executions ELSE disk_reads END, 2) "ReadPexec"
       , ROUND(CASE WHEN executions > 0 THEN gc_time / executions ELSE gc_time END, 4)  "GCpExec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (gc_time / elapsed_time) ELSE 0 END, 2)  "GC %"
       , ROUND(CASE WHEN executions > 0 THEN cpu_time / executions ELSE cpu_time END, 4)  "CPUpExec"
       , ROUND(CASE WHEN executions > 0 THEN ccwait / executions ELSE ccwait END, 4)  "ConcPexec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (cpu_time / elapsed_time) ELSE 0 END, 2)  "CPU %"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (ccwait / elapsed_time) ELSE 0 END, 2)  "Conc %"
       , ROUND(elapsed_time, 1) "Elapsed"
       , parsing_schema_name "Schema"
       , sql_profile "SQL profile"
       , min_inst_id "LowInst"
       , max_inst_id "HghInst"
       , ROUND(cpu_time, 1) "CPU"
       , buffer_gets "Gets"
       , disk_reads  "Reads"
       , ROUND(gc_time, 1) "Cluster"
       , rows_processed "Rows"
       , ROUND(ccwait, 1) "Concurrency"
       , ROUND(CASE WHEN executions > 0 THEN plsql_time / executions ELSE plsql_time END, 4) "PLSpExec"
       , ROUND(CASE
                  WHEN executions > 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / executions / rows_processed / 1000000
                  WHEN executions = 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / rows_processed / 1000000
                  ELSE
                     0
               END
             , 4)
            "ElapProw"
       , sql_text "SQL Text"
FROM     (SELECT   s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI') end_interval_time
                 , SUM(sq.executions_delta) executions
                 , SUM(sq.disk_reads_delta) disk_reads
                 , SUM(sq.buffer_gets_delta) buffer_gets
                 , SUM(sq.rows_processed_delta) rows_processed
                 , SUM(sq.cpu_time_delta) / 1000000 cpu_time
                 , SUM(sq.plsexec_time_delta) / 1000000 plsql_time
                 , SUM(sq.clwait_delta) / 1000000 gc_time
                 , SUM(sq.ccwait_delta) / 1000000 ccwait
                 , SUM(elapsed_time_delta) / 1000000 elapsed_time
                 , sq.force_matching_signature
                 , MIN(sq.instance_number) min_inst_id
                 , MAX(sq.instance_number) max_inst_id
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1) sql_text
          FROM     dba_hist_sqlstat sq, dba_hist_snapshot s, dba_hist_sqltext sl
          WHERE    1 = 1
          AND      sq.sql_id = :sqlid
          --and   sq.plan_hash_value=3983895616
          AND      s.snap_id = sq.snap_id
          --and   s.snap_id between 37279 and 37305
          AND      TRUNC(s.end_interval_time, 'MI') > SYSDATE - 90
          --and   sq.parsing_schema_name in ('APPS_FR')
          AND      s.dbid = sq.dbid
          AND      sq.dbid = sl.dbid
          AND      s.instance_number = sq.instance_number
          AND      sq.sql_id = sl.sql_id
          AND      sq.elapsed_time_delta > 0
          GROUP BY s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.force_matching_signature
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI')
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1))
ORDER BY 1 DESC;

-- Once you have the SQL, use the following SQL to extract all the execution plans:

SELECT * FROM TABLE(DBMS_XPLAN.display_awr(sql_id => :sqlid , format => 'ADVANCED -ALIAS -OUTLINE'))

Oracle Apps: Find Fields in DFFs

1. Query to Find All the fields in a particular DFF:-

SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.fnd_application_vl ap
           WHERE     ffv.descriptive_flexfield_name =
                        att.descriptive_flexfield_name
                 AND ap.application_id = ffv.application_id
                 AND ffv.descriptive_flexfield_name =
                        ffc.descriptive_flexfield_name
                 AND ffv.application_id = ffc.application_id
                 AND ffc.descriptive_flex_context_code =
                        att.descriptive_flex_context_code
                 AND fvs.flex_value_set_id = att.flex_value_set_id
                 AND ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title;


2. Query to Find Only Required fields in a particular DFF :-

SELECT *
  FROM (  SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.fnd_application_vl ap
           WHERE     ffv.descriptive_flexfield_name =
                        att.descriptive_flexfield_name
                 AND ap.application_id = ffv.application_id
                 AND ffv.descriptive_flexfield_name =
                        ffc.descriptive_flexfield_name
                 AND ffv.application_id = ffc.application_id
                 AND ffc.descriptive_flex_context_code =
                        att.descriptive_flex_context_code
                 AND fvs.flex_value_set_id = att.flex_value_set_id
                 AND ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title)
 WHERE required_flag = 'Y';

MVC Architecture

•  MODEL
•  VIEW
•  CONTROLLER

How the start with CONNECT BY clause in Oracle works

This page explains the hierarchical query using the (start with) connect by clause in an Oracle SQL Query.

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause (START WITH condition1) CONNECT BY condition2 

The START WITH clause is optional and specifies the rows athat are the root(s) of the hierarchical query. If you omit this clause, then Oracle uses all rows in the table as root rows. The START WITH condition can contain a subquery, but it cannot contain a scalar subquery expression. 

The CONNECT BY clause specifies the relationship between parent rows and child rows of the hierarchy. The connect_by_condition can be any condition, however, it must use the PRIOR operator to refer to the parent row. Restriction on the CONNECT BY clause: The connect_by_condition cannot contain a regular subquery or a scalar subquery expression. 

The PRIOR operator to refer to the parent row can be used as following: 

Sample query on the employees table:

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

shows employee_id, last_name, manager_id and level in the tree for the employee hierarchy. 

In Oracle 9i a new feature for hierarchical queries is added: sys_connect_by_path It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition. Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column. 
Examples
The following example returns the path of employee names from employee Kochhar to all employees of Kochhar (and their employees): 

SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

In Oracle 10 3 new features for hierarchical queries are added:

connect_by_root

CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. Restriction on CONNECT_BY_ROOT: You cannot specify this operator in the START WITH condition or the CONNECT BY condition. Example query:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

connect_by_isleaf

The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy. Example

SELECT employee_id, last_name, manager_id, connect_by_isleaf "IsLeaf"
   FROM employees
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id;

connect_by_iscycle

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0. 
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

Oracle String Functions

Function	Description
ASCII	Returns the number code that represents the specified character
ASCIISTR	Converts a string in any character set to an ASCII string using the database character set
CHR	Returns the character based on the number code
COMPOSE	Returns a Unicode string
CONCAT	Allows you to concatenate two strings together
Concat with ||	Allows you to concatenate two or more strings together
CONVERT	Converts a string from one character set to another
DECOMPOSE	Accepts a string and returns a Unicode string
DUMP	Returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression
INITCAP	Sets the first character in each word to uppercase and the rest to lowercase
INSTR	Returns the location of a substring in a string
INSTR2	Returns the location of a substring in a string, using UCS2 code points
INSTR4	Returns the location of a substring in a string, using UCS4 code points
INSTRB	Returns the location of a substring in a string, using bytes instead of characters
INSTRC	Returns the location of a substring in a string, using Unicode complete characters
LENGTH	Returns the length of the specified string
LENGTH2	Returns the length of the specified string, using UCS2 code points
LENGTH4	Returns the length of the specified string, using UCS4 code points
LENGTHB	Returns the length of the specified string, using bytes instead of characters
LENGTHC	Returns the length of the specified string, using Unicode complete of characters
LOWER	Converts all letters in the specified string to lowercase
LPAD	Pads the left-side of a string with a specific set of characters
LTRIM	Removes all specified characters from the left-hand side of a string
NCHR	Returns the character based on the number code in the national character set
REGEXP_INSTR	Returns the location of a regular expression pattern in a string
REGEXP_REPLACE	Allows you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching
REGEXP_SUBSTR	Allows you to extract a substring from a string using regular expression pattern matching
REPLACE	Replaces a sequence of characters in a string with another set of characters
RPAD	Pads the right-side of a string with a specific set of characters
RTRIM	Removes all specified characters from the right-hand side of a string
SOUNDEX	Returns a phonetic representation (the way it sounds) of a string
SUBSTR	Allows you to extract a substring from a string
TRANSLATE	Replaces a sequence of characters in a string with another set of characters
TRIM	Removes all specified characters either from the beginning or the end of a string
UPPER	Converts all letters in the specified string to uppercase
VSIZE	Returns the number of bytes in the internal representation of an expression

Sequence of triggers firing in oracle forms

Trigger Firing sequence:

Order as follows

First Logon Triggers are fired1.PRE-LOGON
2.ON-LOGON
3.POST-LOGON

After that Pre Triggers4. PRE-FORM
5. PRE-BLOCK
6. PRE-TEXT

After that WHEN-NEW Triggers
7. WHEN-NEW-FORM-INSTANCE
8. WHEN-NEW-BLOCK-INSTANCE
9. WHEN-NEW-ITEM-INSTANCE

After that ITEM Level TriggersAfter this focus is on the first item of the Block. If you type some data and press the tab key following trigger will fire in sequence

10.KEY-NEXT-ITEM (This trigger is present on the item level).

11.POST-CHANGE (This trigger is present on the item level).

12.WHEN-VALIDATE-ITEM (This trigger is present on the item level).

13.POST-TEXT-ITEM (This trigger is present on the item level).

14.WHEN-NEW-ITEM-INSTANCE (Block Level Trigger).

Now focus will go the next item present on the form.
If there are no further items present on the screen them if we enter data and press tab key then only KEY-NEXT-ITEM is fired. Now suppose we close the form then the item level triggers are fired.

POST TRIGGERS

15.POST-BLOCK
16.POST-FORM

What is a Sub Query? Describe its Types?

A sub query is a SELECT statement that is embedded in a clause of another SELECT statement. Sub query can be placed in WHERE, HAVING and FROM clause.

Guidelines for using sub queries:

1. Enclose sub queries within parenthesis
2. Place sub queries on the right side of the comparison condition.
3. Use Single-row operators with single-row sub queries and Multiple-row operators with multiple-row sub queries.

Types of sub queries:

1. Single-Row Sub query: Queries that return only one row from the Inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
2. 
   
3. Multiple-Row Sub query: Queries that return more than one row from the inner Select statement. There are also multiple-column sub queries that return more than one column from the inner select statement. Operators includes: IN, ANY, ALL.

PL SQL Vs SQL

	PL Sql vs Sql
Comparison	SQL	PL/SQL
Execution	Single command at a time	Block of code
Application	Source of data to be displayed	Application created by data aquired by SQL
Structures include	DDL and DML based queries and commands	Includes procedures, functions, etc
Recommended while	Performing CRUD operations on data	Creating applications to display data obtained using sql
Compatibility with each other	SQL can be embedded into PL/SQL	PL/SQL cant be embedded in SQL

Materialized Views in Oracle

A materialized view in Oracle 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 in Oracle 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)

REFRESH CLAUSE
[refresh [fast|complete|force]
 [on demand | commit]
 [start with date] [next date]
 [with {primary key|rowid}]]


The refresh option specifies:

The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed


Refresh Method - FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.





Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method - COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method - FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause




WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations


Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL>  CREATE MATERIALIZED VIEW mv_emp_pk
    REFRESH FAST 
 START WITH SYSDATE 
 NEXT  SYSDATE + 2
 WITH PRIMARY KEY 
 AS SELECT * FROM emp@remote_db;

Materialized view created.


In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Summary

Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes.