Sample Text

Decrypting User Password of a User in Oracle Apps R12?

To achieve this you need to create a small package and run a query which I wrote below
--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/
--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/


--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'RJ78724';
Ritesh Jain No comments:

The Difference Between Views and Materialized Views in R12


1. Moment Of Execution

A view’s SQL is executed at run-time. The results are fetched from the view’s base tables when the view is queried.
A materialized view (called snapshot in older Oracle versions) is a "pre-answered" query – the query is executed when the materialized view is refreshed. Its result is stored in the database and the query only browses the result.


2. Space

A view occupies no space (other than that for its definition in the data dictionary).
A materialized view occupies space. It exists in the same way as a table: it sits on a disk and could be indexed or partitioned.


3. Freshness of Output

A view’s output is built on the fly; it shows real-time data from the base tables being queried.
A materialized view does not reflect real-time data. The data is only as up to date as the last time the materialized view was refreshed.

4. Where To Use

A view is best used when:
 You want to hide the implementation details of a complex query
 You want to restrict access to a set of rows/columns in the base tables
A materialized view is best used when:
You have a really big table and people do frequent aggregates on it, and you want fast response
You don’t mind the result being a little out of date, or your application data has more queries than updates (as in a BI/data warehousing system)
Ritesh Jain No comments:

Views vs Materialized Views in R12

Like its predecessor the view, materialized views allow you to store the definition of a query in the database.
TableViewMaterialized View




select * from T ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
create view v
as
select *
from   t ;

select * from V ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
create materialized view mv
as
select *
from   t ;

select * from MV ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
Unlike views, however, materialized views also store the results of the query in the database. In the following queries note how the rowid's for the table and the view are identical, indicating the view returns the exact same data stored in the table. The rowids of the materialized view, on the other hand, differ from those of the table. This indicates the materialized view is returning a physically separate copy of the table data.
TableViewMaterialized View
select rowid
from T
order by rowid ;

ROWID
------------------
AAAgY9AAEAAAAVfAAA
AAAgY9AAEAAAAVfAAB
AAAgY9AAEAAAAVfAAC
AAAgY9AAEAAAAVfAAD
 
select rowid
from V
order by rowid ;

ROWID
------------------
AAAgY9AAEAAAAVfAAA
AAAgY9AAEAAAAVfAAB
AAAgY9AAEAAAAVfAAC
AAAgY9AAEAAAAVfAAD
 
select rowid
from MV
order by rowid ;

ROWID
------------------
AAAgZFAAEAAADyEAAA
AAAgZFAAEAAADyEAAB
AAAgZFAAEAAADyEAAC
AAAgZFAAEAAADyEAAD
 
The difference between views and materialized views becomes even more evident than this when table data is updated.
TableViewMaterialized View
update t set val = upper(val);
 
select * from T ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from V ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from MV ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
Note how, after the update, the view data matches the table data but the materialized view data does not. Data in materialized views must be refreshed to keep it synchronized with its base table. Refreshing can either be done manually, as below, or automatically by Oracle in some cases.
TableViewMaterialized View
execute dbms_mview.refresh( 'MV' );
 
select * from T ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from V ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from MV ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
Now that the materialized view has been refreshed its data matches that of its base table.
Ritesh Jain No comments:

Use extract function and xpath function to retrieve xml element data 

SQL> CREATE TABLE R22106(
       id  NUMBER PRIMARY KEY,
       emps XMLType NOT NULL
                         );

Table created.


SQL> INSERT INTO R22106 VALUES (1, xmltype('<?xml version="1.0" standalone="no"
 ?>
    <emps>
       <emp>
            <home_address>address 1</home_address>
        </emp>
    </emps>')
    );

1 row created.


SQL> select extract(emps, '/emps/emp/home_address/text()' ) "Xml Element Value"
    from R22106;

  



Xml Element Value

address 1

Ritesh Jain No comments:

Query to find Suppliers , do not have contact directory

To gather phone, fax, and email in Address Book we can use this query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Author : $ Ritesh Jain
-- -- Creation Date : 25 October 2017 -- -- Description : Sql query to get supplier, who does not have -- Contact directory Information -------------------------------------------------------------------------- SELECT ASS.VENDOR_NAME "SUPPLIER NAME", ASS.SEGMENT1 "VENDOR NUMBER", ASS.VENDOR_ID "VENDOR ID", 'NOT AVAILABLE' AS "CONTACT DIRECTORY" FROM AP_SUPPLIERS ASS, HZ_PARTIES HP WHERE 1=1 AND ASS.PARTY_ID=HP.PARTY_ID AND NOT EXISTS(SELECT * FROM HZ_RELATIONSHIPS HR WHERE HR.SUBJECT_ID =ASS.PARTY_ID AND HR.SUBJECT_TYPE ='ORGANIZATION' AND HR.RELATIONSHIP_CODE ='CONTACT' AND HR.RELATIONSHIP_TYPE ='CONTACT' AND HR.STATUS ='A' AND HR.object_table_name = 'HZ_PARTIES' ) ORDER BY ASS.CREATION_DATE DESC;

Posted By Ritesh Jain
Ritesh Jain No comments:
Subscribe to: Posts (Atom)

Contact Form

Name

Email *

Message *