PLSQL Debugging

CREATE TABLE xx_logging_table(seq_no NUMBER(15), MESSAGE VARCHAR2(500));

CREATE SEQUENCE apps.xx_logging_table_s
      INCREMENT BY 1
      START WITH 1
      MAXVALUE   1000000
      NOCACHE
      CYCLE;


CREATE OR REPLACE PROCEDURE apps.xx_logging_proc (p_message VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO xx_logging_table
        VALUES (apps.xx_logging_table_s.NEXTVAL, p_message);

   COMMIT;
END xx_logging_proc;

commit;


DECLARE
BEGIN
   FOR i IN 1 .. 10
   LOOP
      apps.xx_logging_proc('VALUE' || '-' || i);
   END LOOP;
END;


select * from xx_logging_table;

truncate table xx_logging_table;


Logging in Personalization:

         fnd_log.string (fnd_log.LEVEL_STATEMENT,
                         'XX_ENFORCE_EMAILADDR',  -- module
                         v_message  --message to be inserted in fnd_log_messages
                                    );

Support Analyzers

Get Proactive with Oracle E-Business Suite - Product Support Analyzer Index (Doc ID 1545562.1)

Consolidated note for analyzer scripts

Debug:

BI Publisher - Troubleshooting Oracle Business Intelligence (XML) Publisher For The Oracle E-Business Suite (Doc ID 364547.1)

Table Registration API [AD_DD ]

Table Registration API [Refer Oracle Apps Developer Guide for details]
---------------------------
API: AD_DD package.

Used for custom [Only below]:
###################################
1. Flexfields
2. Oracle Alert
3. Oracle Web Applications Desktop Integrator
###################################

1. register those tables (and all of their columns) that will be used with above.
2. run the AD_DD procedures against the APPS schema.
3. You need not register views.
4. Check yourself that tables exists in custom schema [api does not verify]
5. To modiy, first delete registration, and then re register.

AD_DD.register_table
AD_DD.register_column
AD_DD.delete_table
AD_DD.delete_column

Here is an example of using the AD_DD package to register a flexfield table and its columns:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);

EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID',1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE',2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','SUMMARY_FLAG',8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG',9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST','END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15,'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16,'VARCHAR2', 60, 'Y', 'N');



=============

procedure register_table (p_appl_short_name in varchar2,
 p_tab_name in varchar2,
 p_tab_type in varchar2,
 p_next_extent in number default 512,
 p_pct_free in number default 10,
 p_pct_used in number default 70);

 procedure register_column (p_appl_short_name in varchar2,
 p_tab_name in varchar2,
 p_col_name in varchar2,
 p_col_seq in number,
 p_col_type in varchar2,
 p_col_width in number,
 p_nullable in varchar2,
 p_translate in varchar2,
 p_precision in number default null,
 p_scale in number default null);

 procedure delete_table (p_appl_short_name in varchar2, p_tab_name in varchar2);

 procedure delete_column (p_appl_short_name in varchar2,  p_tab_name in varchar2, p_col_name in varchar2);

=> p_appl_short_ name The application short name of the application that owns the table (usually your custom application).
=> p_tab_name The name of the table (in uppercase letters).
=> p_tab_type Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table (used only by Oracle EBusiness Suite products).
=> p_pct_free The percentage of space in each of the table's blocks reserved for future updates to the table (1-99). The sum of
=> p_pct_free and p_pct_used must be less than 100.
=> p_pct_used Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.
=> p_col_name The name of the column (in uppercase letters).
=> p_col_seq The sequence number of the column in the table (the order in which the column appears in the table definition).
=> p_col_type The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
=> p_col_width The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
=> p_nullable Use 'N' if the column is mandatory or 'Y' if the column allows null values.
=> p_translate Use 'Y' if the column values will be translated for an Oracle E-Business Suite product release (used only by Oracle EBusiness Suite products) or 'N' if the values are not
=> translated (most application columns).
=> p_next_extent The next extent size, in kilobytes. Do not include the 'K'.
=> p_precision The total number of digits in a number.
=> p_scale The number of digits to the right of the decimal point in a number.

PLSQL : Nesting blocks

Notice that I am using same variable name, however outer variable name can be accessed by using official.ln_email

begin <<official>> 
    declare
        ln_email varchar2(30):= 'abhishek.agrawal@official.com';
    begin
        --PERSONAL
        declare
            ln_email varchar2(30):= 'appsoracle.abhi@gmail.com';
        begin
            DBMS_OUTPUT.PUT_LINE('PERSONAL MAIL: '||LN_EMAIL);
            DBMS_OUTPUT.PUT_LINE('OFFICIAL MAIL: '||OFFICIAL.LN_EMAIL);
        end;
   
    end;
end official;


PERSONAL MAIL: appsoracle.abhi@gmail.com
OFFICIAL MAIL: abhishek.agrawal@official.com

Oracle MERGE statement

https://www.oracletutorial.com/oracle-basics/oracle-merge/

Summary: in this tutorial, you will learn how to use the Oracle MERGE statement to perform an update or insert data based on a specified condition.

Introduction to the Oracle MERGE statement

The Oracle MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data from or insert data into the target table.

The following illustrates the syntax of the Oracle MERGE statement:

1 2 3 4 5 6 7 8 9 10 11

MERGE INTO target_table USING source_table ON search_condition     WHEN MATCHED THEN         UPDATE SET col1 = value1, col2 = value2,...         WHERE <update_condition>         [DELETE WHERE <delete_condition>]     WHEN NOT MATCHED THEN         INSERT (col1,col2,...)         values(value1,value2,...)         WHERE <insert_condition>;

Let’s examine the MERGE statement in detail:

First, specify the target table (target_table) which you want to update or insert into in the INTO clause.

Second, specify the source of data (source_table) to be updated or inserted in the USING clause.

Third, specify the search condition upon which the merge operation either updates or inserts in the ON clause.

For each row in the target table, Oracle evaluates the search condition:

• If the result is true, then Oracle updates the row with the corresponding data from the source table.
• In case the result is false for any rows, then Oracle inserts the corresponding row from the source table into the target table.

The MERGE statement becomes convenient when you want to combine multiple INSERT, UPDATE, and DELETE statements in a single operation.

Because the MERGE is a deterministic statement, you cannot update the same row of the target table multiple times in the same MERGE statement.

You can add an optional DELETE WHERE clause to the MATCHED clause to clean up after a merge operation. The DELETE clause deletes only the rows in the target table that match both ON and DELETE WHERE clauses.

Oracle MERGE prerequisites

To execute the MERGE statement, you must have the INSERT and UPDATE object privileges on the source tables. If you use the DELETE clause, you must also have the DELETE object privilege on the target table.

Oracle MERGE example

Suppose, we have two tables: members and member_staging.

We insert a new row to the members table whenever we have a new member. Then, the data from the members table is merged with data of the member_staging table.

The following statements create the members and member_staging tables:

1 2 3 4 5 6 7 8 9

CREATE TABLE members (     member_id NUMBER PRIMARY KEY,     first_name VARCHAR2(50) NOT NULL,     last_name VARCHAR2(50) NOT NULL,     rank VARCHAR2(20) );   CREATE TABLE member_staging AS SELECT * FROM members;

The following INSERT statements insert sample data into the members and member_staging tables:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

-- insert into members table     INSERT INTO members(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Gold'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Suarez','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Garza','Silver'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(7,'Ossie','Summers','Gold'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(8,'Paige','Mcfarland','Platinum'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(9,'Ronna','Britt','Platinum'); INSERT INTO members(member_id, first_name, last_name, rank) VALUES(10,'Tressie','Short','Bronze');   -- insert into member_staging table INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Silver'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Bronze'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Gate','Gold'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver'); INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');

When updating data from the members table to member_staging table, we should perform the following actions:

• We update the rows with member id 1, 3, 4, and 6 because the rank or the last name of these members in these tables are different.
• We insert the rows with member id 7 to 10 are because these rows exist in the members table but not in the member_staging table.

In total 8 rows should be merged.

The following is the MERGE statement that performs all of these actions in one shot.

1 2 3 4 5 6 7 8 9 10 11 12 13

MERGE INTO member_staging x USING (SELECT member_id, first_name, last_name, rank FROM members) y ON (x.member_id  = y.member_id) WHEN MATCHED THEN     UPDATE SET x.first_name = y.first_name,                         x.last_name = y.last_name,                         x.rank = y.rank     WHERE x.first_name <> y.first_name OR            x.last_name <> y.last_name OR            x.rank <> y.rank WHEN NOT MATCHED THEN     INSERT(x.member_id, x.first_name, x.last_name, x.rank)       VALUES(y.member_id, y.first_name, y.last_name, y.rank);

The merge statement compares each row in the members table with each row in the member_staging table based on the values in the member_id columns (see the ON clause above).

If the values in member_id columns of both tables are equal, the MERGE statement updates the first name, last name, and rank from the members table to the member_stagingtable only if the values of first name, last name, or rank columns of both tables are different.

Otherwise, it inserts the row from the members table into the member_staging table.

Oracle returned 8 rows merged as expected.

In this tutorial, you have learned how to use the Oracle MERGE statement to update or insert data based on a specified condition.