PL/SQL - Data Encryption/Decryption

The below example gives us an idea how to encrypt and decrypt data using PL/SQL.

DECLARE

   l_data   VARCHAR2 (255);

   l_str    VARCHAR2 (100) := 'This is for testing';

BEGIN

   l_data := RPAD (l_str, (TRUNC (LENGTH (l_str) / 8) + 1) * 8, CHR (0));

   --Calling the decryption fuction

   DBMS_OBFUSCATION_TOOLKIT.DESEncrypt (input_string => l_data,

                                                                                      key_string => 'MagicKey',

                                                                                      encrypted_string => l_str);

   DBMS_OUTPUT.put_line ('Encrypted Data: ' || l_str); --Encrypted String

   --Calling the decryption fuction

   DBMS_OBFUSCATION_TOOLKIT.DESDecrypt (input_string => l_str,

                                                                                      key_string => 'MagicKey',

                                                                                      decrypted_string => l_data);

   l_str := RTRIM (l_data, CHR (0));

   DBMS_OUTPUT.put_line ('Decrypted Data: ' || l_str); --Decrypted String

END;

We can replace the Key String with different values.

If we are trying to encrypt/decrypt data on a specific column in a table, it is better to write different procedures/functions for both encryption and decryption.

Web ADI With Very Simple Example

Document on Oracle WebADI

(Application Desktop Integrator)

Advantage of WebADI

• Load data into database tables

• Familiar medium (Excel) 

• User Friendly 

• FTP tool is not required 

• No need to provide access of server to end user

Step 1

 Step 2

    Set the profile as per below screen shot

    BNE Allow No Security Rule - Yes 

     

 Step 3

Step 4 

Create Following database objects

CREATE TABLE XXDEPT

(

   DEPTNO   NUMBER,

   DNAME    VARCHAR2 (20)

);

CREATE TABLE XXEMP

(

   EMPNO   NUMBER,

   ENMAE   VARCHAR2 (20)

);

CREATE OR REPLACE PACKAGE XX_DEPT_EMP_PACK

IS

   PROCEDURE XX_DEPT_EMP (P_DEPT_NO      NUMBER,

                          P_DEPT_NAME    VARCHAR2,

                          P_EMPNO        NUMBER,

                          P_ENAME        VARCHAR2);

END XX_DEPT_EMP_PACK;

CREATE OR REPLACE PACKAGE BODY XX_DEPT_EMP_PACK

IS

   PROCEDURE XX_DEPT_EMP (P_DEPT_NO      NUMBER,

                          P_DEPT_NAME    VARCHAR2,

                          P_EMPNO        NUMBER,

                          P_ENAME        VARCHAR2)

   IS

   BEGIN

      INSERT INTO XXDEPT

           VALUES (P_DEPT_NO, P_DEPT_NAME);

      INSERT INTO XXEMP

           VALUES (P_EMPNO, P_ENAME);

      COMMIT;

   END;

END;

Step 5

Create customer integrator 

1. Add Desktop Integrator Responsibility

Navigate to  as per below screen shot 

  Select Respective version of MS Office and Click Next

Select content as None

Click Next and Create Document

Now the system will prompt you to open the Excel

Click on Open and wait until the Template Loads

Enter the Data following Respectively to Create the Document

Metadata Type                 => CREATE 

Application Short Name  => XXNMPT 

Integrator User Name      => User Defined Name (Template Name) 

API Procedure Name       => XXMP_DEPT_EMP 

API Package                     => XXMP_DEPT_EMP_PACK 

Interface Parameter          => Any Name 

API Type                          => Procedure

Step 6 

Define Layout

Navigate Desktop Integrator à Define Layout

Define the Layout Name “User Defined Name” and Click on Next

Select the Headers and Lines for the columns in which you want to Design the Template

Click on Next and Apply

Close the Application

Again Click on Create Document and select the Integrator which you Created

Select the MS Office Verison

Select the Content as None

Click on Create Document

Open the Excel and Enter the Data

Enter the Data and upload

Check whether the Data has been Uploaded or Not

SELECT * FROM XXDEPT

SELECT * FROM XXEMP

Happy Learning  :)               

        By                          

ERP Cirlces Team

SQL Lab's

SQL LAB SESSION -1

1. Create a table school with following attributes.

2. Display the structure of the tables School

3. Insert the following rows

4. Insert the following information to School table

5. Add the following 3 attributes to school

6.Modify the width of the attributes Sname to 20 characters

7.Create duplicate table for school with name School_Details

8.Drop the table School

9.Create five different tables for different classes from student_details

10. Update the name of the student with Bindu whose Regno is 109

11. Update the date of join with 16-JAN-99 whose Regno is 110

12. Give Regno as 111 for Jyosna

13. Save the data permanently

14. Delete the records from school_details

15. Get back the details what you have deleted

SQL LAB SESSION -2

1. Display the details of school

2. Display the information about student Rajesh

3. Display the information about students whose Regno between 106 and 109

4. Display the information about students any information is null

5. Display the information about students whose Regno is 108,107 and 110

6. Display the information about students whose name start with ‘K’ or ‘R’

7. Display the information about students who are joined after 12-JAN-99

8. Display the information from school details whose name ends with character ‘h’

9. Display the information from school details whose name contains only 5 characters

10. Display the information school_details who are belongs to VI Class

11. Display the details about Jyosna and Kalyani from School

12. Display the details from school whose total > 300 for which m1 , m2 > 70

13. Display the details from school for class VI OR VII

14. Display the details from school which Doj between 01-jan-98 and 15- mar-99

15. Display the details from school whose class has no values

16. Display the details of about the who’s name contains letter ‘A’

17. Display the student information who’s average marks > 70 %

18. Display failed student information

19. Display the information about student who failed in Physics

20. Display the information about the student who failed in Physics and Chemistry

Happy Learning :)

                          By

                                    ERP Circles Team

AOL - Concurrent Program/User/Responsibilities/Form/Function Queries

Concurrent Programs

1. Query to list out all the responsibilities attached to a 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 = :user_name

       AND furg.user_id = fu.user_id

       AND furg.responsibility_id = fr.responsibility_id

       AND fr.language(+) = USERENV ('lang');

2. List of responsibilities to which the concurrent program is assigned

SELECT fcp.concurrent_program_name,

       fcpt.user_concurrent_program_name,

       fcp.concurrent_program_id,

       frt.responsibility_name,

       REQUEST_GROUP_NAME

  FROM fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_request_groups frg,

       fnd_request_group_units frgu,

       fnd_responsibility fr,

       fnd_responsibility_tl frt

 WHERE     CONCURRENT_PROGRAM_NAME LIKE :p_conc_program_name

       AND fcp.concurrent_program_id = fcpt.concurrent_program_id

       AND fr.responsibility_id = frt.responsibility_id

       AND frgu.request_group_id = frg.request_group_id

       AND frgu.request_group_id = fr.request_group_id

       AND fcpt.concurrent_program_id = frgu.request_unit_id

       AND fcpt.language(+) = USERENV ('lang')

       AND frt.language(+) = USERENV ('lang');

3. To find out concurrent program details including parameters, their valuesets, default values etc.

SELECT fcpl.user_concurrent_program_name,

       fcp.concurrent_program_name,

       fav.application_short_name,

       fav.application_name,

       fav.application_id,

       fdfcuv.end_user_column_name,

       fdfcuv.form_left_prompt prompt,

       fdfcuv.enabled_flag,

       fdfcuv.required_flag,

       fdfcuv.display_flag,

       fdfcuv.flex_value_set_id,

       ffvs.flex_value_set_name,

       flv.meaning default_type,

       fdfcuv.DEFAULT_VALUE,descriptive_flexfield_name

  FROM fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpl,

       fnd_descr_flex_col_usage_vl fdfcuv,

       fnd_flex_value_sets ffvs,

       fnd_lookup_values flv,

       fnd_application_vl fav

 WHERE     fcp.concurrent_program_id = fcpl.concurrent_program_id

       AND fcpl.user_concurrent_program_name = :conc_prg_name

       AND fcpl.language = USERENV ('lang')

       AND fav.application_id = fcp.application_id

       AND fdfcuv.descriptive_flexfield_name =

              '$SRS$.' || fcp.concurrent_program_name

       AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

       AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'

       AND flv.lookup_code(+) = fdfcuv.default_type

       AND flv.language(+) = USERENV ('lang');

3A. How to Delete Errored Concurrent Requests of an User

DELETE FROM fnd_concurrent_requests

      WHERE     status_code = 'E'

            AND request_date LIKE SYSDATE

            AND requested_by = (SELECT user_id

                                  FROM fnd_user

                                 WHERE user_name = 'USERNAME')

Request Sets

4. To find out the request sets to which a concurrent program is assigned

SELECT DISTINCT user_request_set_name

  FROM fnd_request_sets_tl frst

 WHERE frst.request_set_id IN (SELECT request_set_id

                                 FROM fnd_request_set_programs frsp

                                WHERE frsp.concurrent_program_id =

                                         (SELECT concurrent_program_id

                                            FROM fnd_concurrent_programs_tl fcpt

                                           WHERE UPPER (fcpt.user_concurrent_program_name) = UPPER ('&conc_prog_name')));

5. To list out the concurrent programs assigned to a request set

SELECT user_concurrent_program_name

  FROM fnd_concurrent_programs_tl

 WHERE concurrent_program_id IN (SELECT concurrent_program_id

                                   FROM fnd_request_set_programs

                                  WHERE request_set_id =

                                           (SELECT request_set_id

                                              FROM fnd_request_sets_tl

                                             WHERE UPPER (user_request_set_name) = UPPER ('&request_set_name')));

6. To find locks in a particular instance

   

SELECT DECODE (request, 0, 'holder: ', 'waiter: ') || sid session,

                  inst_id,

                  id1,

                  id2,

                  lmode,

                  request,

                  TYPE

    FROM gv$lock

   WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE

                                                        FROM gv$lock

                                                      WHERE request > 0)

   ORDER BY id1, request;

7. To find the programs referring a certain package

SELECT fcr.request_id,

       fcpv.user_concurrent_program_name,

       fcpv.concurrent_program_name,

       fcpv.concurrent_program_id,

       fcr.status_code,

       fcr.phase_code

  FROM fnd_concurrent_programs_vl fcpv,

       fnd_executables fe,

       sys.dba_dependencies dd,

       fnd_concurrent_requests fcr

 WHERE     fcpv.executable_id = fe.executable_id

       AND fe.execution_method_code = 'I'

       AND SUBSTR (fe.execution_file_name,

                   1,

                     INSTR (fe.execution_file_name,

                            '.',

                            1,

                            1)

                   - 1) = UPPER (dd.name)

       AND dd.referenced_type IN ('VIEW',

                                  'TABLE',

                                  'TRIGGER',

                                  'PACKAGE')

       AND dd.referenced_name = UPPER ('&package_name')

       AND fcr.concurrent_program_id = fcpv.concurrent_program_id

       AND fcr.phase_code NOT IN ('C', 'P');

8. To find out SID, Serial# of a concurrent request

SELECT a.request_id,

       d.sid,

       d.serial#,

       c.SPID

  FROM apps.fnd_concurrent_requests a,

       apps.fnd_concurrent_processes b,

       v$process c,

       v$session d

 WHERE     a.controlling_manager = b.concurrent_process_id

       AND c.pid = b.oracle_process_id

       AND b.session_id = d.audsid

       AND a.request_id = &conc_req_id

       AND a.phase_code = 'R';

9. To find out trace file location of concurrent request

SELECT request_id,

       oracle_process_id traceid,

       req.enable_trace,

          dest.VALUE

       || '/'

       || LOWER (dbnm.VALUE)

       || '_ora_'

       || oracle_process_id

       || '.trc'

          trace_file,

       prog.user_concurrent_program_name,

          DECODE (phase_code, 'R', 'Running')

       || '-'

       || DECODE (status_code, 'R', 'Normal')

          Status,

       ses.sid,

       ses.serial#,

       ses.module

  FROM fnd_concurrent_requests req,

       v$session ses,

       v$process proc,

       v$parameter dest,

       v$parameter dbnm,

       fnd_concurrent_programs_vl prog,

       fnd_executables execname

 WHERE     req.request_id = &conc_req_id

       AND req.oracle_process_id = proc.spid(+)

       AND proc.addr = ses.paddr(+)

       AND dest.name = 'user_dump_dest'

       AND dbnm.name = 'db_name'

       AND req.concurrent_program_id = prog.concurrent_program_id

       AND req.program_application_id = prog.application_id

       AND prog.executable_application_id = execname.application_id

          AND prog.executable_id = execname.executable_id;

10. How to find assigned user for a given responsibility

  SELECT DISTINCT fu.user_name,

                fu.email_address,

                papf.full_name,

                papf.employee_number,

                fr.responsibility_key

  FROM fnd_logins fl,

       fnd_login_responsibilities flr,

       fnd_responsibility fr,

       fnd_responsibility_tl frt,

       fnd_user fu,

       per_all_people_f papf

 WHERE     flr.responsibility_id = fr.responsibility_id

       AND flr.login_id = fl.login_id

       AND fu.user_id = fl.user_id

       AND fr.responsibility_id = frt.responsibility_id

       AND papf.person_id = fu.employee_id

       AND frt.responsibility_name LIKE 'XX%AP%SUPER%' 

11. How to find the responsibilities for a given request set

SELECT DISTINCT frg.request_group_id,

                frg.request_group_name,

                frgu.REQUEST_UNIT_ID,

                fcp.REQUEST_SET_NAME,

                fcp.REQUEST_SET_ID,

                frvl.RESPONSIBILITY_NAME

  FROM FND_REQUEST_GROUPS frg,

       FND_REQUEST_GROUP_UNITS frgu,

       FND_REQUEST_SETS fcp,

       FND_RESPONSIBILITY_VL frvl

 WHERE     frg.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID

       AND frg.REQUEST_GROUP_ID = frvl.REQUEST_GROUP_ID

       AND frgu.request_unit_type = 'S'             --P for Program, S for Set

       AND fcp.REQUEST_SET_ID = frgu.REQUEST_unit_ID

       AND fcp.REQUEST_SET_NAME LIKE 'XX%'

12. Query to fetch the forms and functions along with the prompt name

SELECT DISTINCT Prompt,

                function_name,

                user_function_name,

                form_name,

                user_form_name

  FROM FND_FORM_FUNCTIONS_VL ffv, fnd_form_vl fv, fnd_menu_entries_vl fm

 WHERE     fv.form_id = ffv.form_id

       AND fm.function_id = ffv.function_id

       AND prompt IS NOT NULL

       AND ffv.application_id = 20003

13. Query to Search the form Names and Menu Names for a Particular Form/Function/Menu

SELECT DISTINCT Prompt,

                Menu_name,

                function_name,

                user_function_name,

                form_name,

                user_form_name

  FROM FND_FORM_FUNCTIONS_VL ffv,

       fnd_form_vl fv,

       fnd_menu_entries_vl fm,

       fnd_menus fmn,

       fnd_application fa

 WHERE     fv.form_id = ffv.form_id

       AND fm.function_id = ffv.function_id

       AND fmn.menu_id = fm.menu_id

       AND prompt IS NOT NULL

       AND form_name = NVL ( :form_name, form_name)

       AND function_name = NVL ( :function_name, form_name)

       AND user_form_name = NVL ( :user_form_name, user_form_name)

       AND user_function_name = NVL ( :user_function_name, user_form_name)

       AND prompt = NVL ( :prompt, prompt)

       AND menu_name = NVL ( :menu_name, menu_name)

       AND ffv.application_id = fa.application_id                      --20003

       AND fa.application_short_name = 'XXARS' 

14. How to find the Responsibility and user to which function is assigned

  SELECT DISTINCT u.user_name,

                  rtl.responsibility_name,

                  ff.function_name,

                  ffl.user_function_name

    FROM fnd_compiled_menu_functions cmf,

         fnd_form_functions ff,

         fnd_form_functions_tl ffl,

         fnd_responsibility r,

         fnd_responsibility_tl rtl,

         fnd_user_resp_groups urg,

         fnd_user u

   WHERE     cmf.function_id = ff.function_id

         AND r.menu_id = cmf.menu_id

         AND urg.responsibility_id = r.responsibility_id

         AND rtl.responsibility_id = r.responsibility_id

         AND cmf.grant_flag = 'Y'

         AND r.application_id = urg.responsibility_application_id

         AND u.user_id = urg.user_id

         AND UPPER (ffl.user_function_name) =

                UPPER ('Batch Progression Sales Order')   --XXARS_BATCH_PRO_SO

         AND ff.function_id = ffl.function_id

ORDER BY u.user_name

15. How to find the Responsibilities for given function name       

SELECT d.responsibility_name

  FROM fnd_menu_entries a,

       fnd_menu_entries_vl b,

       fnd_form_functions c,

       fnd_responsibility_vl d

 WHERE     a.menu_id = b.menu_id

       AND a.function_id = b.function_id

       AND b.function_id = c.function_id

       AND a.menu_id = d.menu_id

       AND c.function_name = :function_name


16. To Get the Performance Timings of Concurrent Programs   

   SELECT fu.user_name,

         fcp.user_concurrent_program_name,

         req.request_id,

         req.request_date,

         req.phase_code,

         req.status_code,

         req.argument_text,

         DECODE (req.phase_code,

                 'R', ROUND ( (SYSDATE - req.actual_start_date) * 1440, 2))

            current_time,

         ROUND (

              (  NVL (req.actual_completion_date, req.LAST_UPDATE_DATE)

               - req.actual_start_date)

            * 1440,

            2)

            total_duration_min

    FROM apps.fnd_concurrent_requests req,

         apps.fnd_concurrent_programs_vl fcp,

         apps.fnd_user fu

   WHERE     fcp.concurrent_program_Id = req.concurrent_program_id

         AND req.request_date > SYSDATE - 365

         AND fu.user_id = req.requested_by

         AND req.concurrent_program_id = 91341

ORDER BY req.request_date DESC