How to handle the Exception in FORALL statement 11G Feature

The below mentioned way of handling the exceptions for FORALL statement is available in Oracle 11g DB version.

-- Temporary table for this example:

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE

   TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;

   emp_sr                                            empid_tab;

-- Exception handler for ORA-24381:

   ERRORS                                            NUMBER;

   dml_errors                                        EXCEPTION;

   PRAGMA EXCEPTION_INIT (dml_errors, -24381);

BEGIN

   SELECT employee_id

   BULK COLLECT INTO emp_sr

     FROM emp_temp

    WHERE hire_date < '30-DEC-94';

-- Add '_SR' to job_id of most senior employees:

   FORALL i IN emp_sr.FIRST .. emp_sr.LAST SAVE EXCEPTIONS

      UPDATE emp_temp

      SET job_id = job_id || '_SR'

      WHERE  emp_sr (i) = emp_temp.employee_id;

-- If errors occurred during FORALL SAVE EXCEPTIONS,

-- a single exception is raised when the statement completes.

EXCEPTION

-- Figure out what failed and why

   WHEN dml_errors

   THEN

      ERRORS                                                   := SQL%BULK_EXCEPTIONS.COUNT;

      DBMS_OUTPUT.put_line ('Number of statements that failed: ' || ERRORS);

      FOR i IN 1 .. ERRORS

      LOOP

         DBMS_OUTPUT.put_line ('Error #' || i || ' occurred during ' || 'iteration #'

                               || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX

                              );

         DBMS_OUTPUT.put_line ('Error message is ' || SQLERRM (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));

      END LOOP;

END;

/

How to Load Images Into BLOB Columns

1. Create a table as mentioned below.

CREATE  TABLE xx_load_images_into_blobs
 ( ID    NUMBER,
   blob_col BLOB
 );

2. Create a logical directory in the database to the physical file system:

 create or replace directory GET_FILES as '/usr/tmp';

3. Create a procedure to load the blobs from the file system using the logical directory.  The gif "xyz.gif" must exist in /usr/tmp directory. 

declare
   f_lob                                             BFILE;
   b_lob                                             BLOB;
BEGIN
   INSERT INTO xx_load_images_into_blobs        
VALUES (1
              , EMPTY_BLOB ()
               )
        RETURN blob_col
          INTO b_lob;

   f_lob                                                    := BFILENAME ('GET_FILES', 'xyz.gif');
   DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);
   DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));
   DBMS_LOB.fileclose (f_lob);
   COMMIT;
END;
/

4. After executing the above block run the Query mentioned below and check the result.

SELECT * FROM xx_load_images_into_blobs;

Script to Create the Instance in Install Base and Creation of relationship With Other Instance In Install base

DECLARE
   x_instance_rec                                    csi_datastructures_pub.instance_rec;
   x_ext_attrib_values                               csi_datastructures_pub.extend_attrib_values_tbl;
   x_party_tbl                                       csi_datastructures_pub.party_tbl;
   x_account_tbl                                     csi_datastructures_pub.party_account_tbl;
   x_pricing_attrib_tbl                              csi_datastructures_pub.pricing_attribs_tbl;
   x_org_assignments_tbl                             csi_datastructures_pub.organization_units_tbl;
   x_asset_assignment_tbl                            csi_datastructures_pub.instance_asset_tbl;
   x_txn_rec                                         csi_datastructures_pub.transaction_rec;
   x2_txn_rec                                        csi_datastructures_pub.transaction_rec;
   x_return_status                                   VARCHAR2 (100);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   x_created_manually_flag                           VARCHAR2 (100);
   l_org_id                                          NUMBER := 41;
   n                                                 NUMBER := 1;
   v_instance_id                                     NUMBER;
   p_commit                                          VARCHAR2 (5);
   p2_commit                                         VARCHAR2 (5);
   p_validation_level                                NUMBER;
   p_init_msg_lst                                    VARCHAR2 (500);
   v_instance_party_id                               NUMBER;
   v_ip_account_id                                   NUMBER;
   x_relationship_tbl                                csi_datastructures_pub.ii_relationship_tbl;
   v_relationship_id                                 NUMBER;
   v_success                                         VARCHAR2 (1) := 'T';
   x2_return_status                                  VARCHAR2 (100);
   x2_msg_count                                      NUMBER;
   x2_msg_data                                       VARCHAR2 (2000);
   p2_validation_level                               NUMBER;
   p2_init_msg_lst                                   VARCHAR2 (500);
BEGIN
   SELECT csi_item_instances_s.NEXTVAL
     INTO v_instance_id
     FROM SYS.DUAL;

   x_instance_rec.instance_id                               := v_instance_id;
   x_instance_rec.instance_number                           := v_instance_id;
   x_instance_rec.inventory_item_id                         := '<inventory_item_id>';
   x_instance_rec.inv_master_organization_id                := '<inv_master_organization_id>';
   x_instance_rec.serial_number                             := '<serial_number>';
   x_instance_rec.mfg_serial_number_flag                    := 'N';
   x_instance_rec.quantity                                  := 1;
   x_instance_rec.unit_of_measure                           := 'EA';
   x_instance_rec.accounting_class_code                     := '<accounting_class_code>';
   x_instance_rec.instance_status_id                        := 3;
   x_instance_rec.customer_view_flag                        := NULL;   --N
   x_instance_rec.merchant_view_flag                        := NULL;   --Y
   x_instance_rec.sellable_flag                             := NULL;   --N
   x_instance_rec.active_start_date                         := TRUNC (SYSDATE);
   x_instance_rec.location_type_code                        := 'HZ_LOCATIONS';
   x_instance_rec.location_id                               := '<location_id>';
   x_instance_rec.install_date                              := TRUNC (SYSDATE);
   x_instance_rec.creation_complete_flag                    := 'Y';
   x_instance_rec.version_label                             := '<version_label>';
   x_instance_rec.last_oe_po_number                         := '<so_po_number >';
   x_instance_rec.object_version_number                     := 1;

   -- ************* FOR PARTIES **********************************************************
   SELECT csi_i_parties_s.NEXTVAL
     INTO v_instance_party_id
     FROM SYS.DUAL;

   x_party_tbl (1).instance_party_id                        := v_instance_party_id;
   x_party_tbl (1).instance_id                              := v_instance_id;
   x_party_tbl (1).party_source_table                       := 'HZ_PARTIES';
   x_party_tbl (1).party_id                                 := '<party_id>';
   x_party_tbl (1).relationship_type_code                   := 'OWNER';
   x_party_tbl (1).contact_flag                             := 'N';
   x_party_tbl (1).active_start_date                        := SYSDATE;
   x_party_tbl (1).object_version_number                    := 1;

   -- *********** FOR PARTY ACCOUNT *****************************************************
   SELECT csi_ip_accounts_s.NEXTVAL
     INTO v_ip_account_id
     FROM SYS.DUAL;

   x_account_tbl (1).ip_account_id                          := v_ip_account_id;
   x_account_tbl (1).instance_party_id                      := v_instance_party_id;
   x_account_tbl (1).party_account_id                       := '<party_account_id>';
   x_account_tbl (1).relationship_type_code                 := 'OWNER';
   x_account_tbl (1).bill_to_address                        := '<bill_to_address>';
   x_account_tbl (1).ship_to_address                        := '<ship_to_address>';
   x_account_tbl (1).active_start_date                      := SYSDATE;
   x_account_tbl (1).object_version_number                  := 1;
   x_account_tbl (1).parent_tbl_index                       := 1;
   x_account_tbl (1).call_contracts                         := 'Y';
   -- ************************** TRANSACTION REC *****************************************
   x_txn_rec.transaction_date                               := TRUNC (SYSDATE);
   x_txn_rec.source_transaction_date                        := TRUNC (SYSDATE);
   x_txn_rec.transaction_type_id                            := 1;
   x_txn_rec.object_version_number                          := 1;
   csi_item_instance_pub.create_item_instance (p_api_version                 => 1.0
                                             , p_commit                      => p_commit
                                             , p_init_msg_list               => p_init_msg_lst
                                             , p_validation_level            => p_validation_level
                                             , p_instance_rec                => x_instance_rec
                                             , p_ext_attrib_values_tbl       => x_ext_attrib_values
                                             , p_party_tbl                   => x_party_tbl
                                             , p_account_tbl                 => x_account_tbl
                                             , p_pricing_attrib_tbl          => x_pricing_attrib_tbl
                                             , p_org_assignments_tbl         => x_org_assignments_tbl
                                             , p_asset_assignment_tbl        => x_asset_assignment_tbl
                                             , p_txn_rec                     => x_txn_rec
                                             , x_return_status               => x_return_status
                                             , x_msg_count                   => x_msg_count
                                             , x_msg_data                    => x_msg_data
                                              );

   IF x_return_status != apps.fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('failed. printing error msg...');
      DBMS_OUTPUT.put_line (apps.fnd_msg_pub.get (p_msg_index                   => apps.fnd_msg_pub.g_last
                                                , p_encoded                     => apps.fnd_api.g_false));
      v_success                                                := 'F';
      ROLLBACK;
      RETURN;
   ELSE
      DBMS_OUTPUT.put_line ('Inserted Install base data');
      DBMS_OUTPUT.put_line (' The instance Id is#: ' || v_instance_id);
      COMMIT;

      -- ****************** create relation ship *****************************************
      SELECT csi_ii_relationships_s.NEXTVAL
        INTO v_relationship_id
        FROM SYS.DUAL;

      x_relationship_tbl (1).relationship_id                   := v_relationship_id;
      x_relationship_tbl (1).relationship_type_code            := 'COMPONENT_OF';
      x_relationship_tbl (1).object_id                         := '<v_child_instance_id>';
      x_relationship_tbl (1).subject_id                        := v_instance_id;
      x_relationship_tbl (1).subject_has_child                 := 'N';
      x_relationship_tbl (1).position_reference                := NULL;
      x_relationship_tbl (1).active_start_date                 := SYSDATE;
      x_relationship_tbl (1).active_end_date                   := SYSDATE;
      x_relationship_tbl (1).display_order                     := NULL;
      x_relationship_tbl (1).mandatory_flag                    := 'N';
      x_relationship_tbl (1).CONTEXT                           := NULL;
      x_relationship_tbl (1).attribute1                        := NULL;
      x_relationship_tbl (1).attribute2                        := NULL;
      x_relationship_tbl (1).attribute3                        := NULL;
      x_relationship_tbl (1).attribute4                        := NULL;
      x_relationship_tbl (1).attribute5                        := NULL;
      x_relationship_tbl (1).attribute6                        := NULL;
      x_relationship_tbl (1).attribute7                        := NULL;
      x_relationship_tbl (1).attribute8                        := NULL;
      x_relationship_tbl (1).attribute9                        := NULL;
      x_relationship_tbl (1).attribute10                       := NULL;
      x_relationship_tbl (1).attribute11                       := NULL;
      x_relationship_tbl (1).attribute12                       := NULL;
      x_relationship_tbl (1).attribute13                       := NULL;
      x_relationship_tbl (1).attribute14                       := NULL;
      x_relationship_tbl (1).attribute15                       := NULL;
      x_relationship_tbl (1).object_version_number             := 1;
      x2_txn_rec.transaction_date                              := TRUNC (SYSDATE);
      x2_txn_rec.source_transaction_date                       := TRUNC (SYSDATE);
      x2_txn_rec.transaction_type_id                           := 1;
      x2_txn_rec.object_version_number                         := 1;
      csi_ii_relationships_pub.create_relationship (p_api_version                 => 1.0
                                                  , p_commit                      => p2_commit
                                                  , p_init_msg_list               => p2_init_msg_lst
                                                  , p_validation_level            => p2_validation_level
                                                  , p_relationship_tbl            => x_relationship_tbl
                                                  , p_txn_rec                     => x2_txn_rec
                                                  , x_return_status               => x2_return_status
                                                  , x_msg_count                   => x2_msg_count
                                                  , x_msg_data                    => x2_msg_data
                                                   );

      IF x2_return_status != apps.fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line ('failed. printing error msg...');
         DBMS_OUTPUT.put_line (apps.fnd_msg_pub.get (p_msg_index                   => apps.fnd_msg_pub.g_last
                                                   , p_encoded                     => apps.fnd_api.g_false
                                                    )
                              );
         v_success                                                := 'F';
         ROLLBACK;
         RETURN;
      ELSE
         DBMS_OUTPUT.put_line ('Inserted Install base Relationship data');
         DBMS_OUTPUT.put_line (' The instance Id is#: ' || v_instance_id);
         COMMIT;
      END IF;
   END IF;
END;
/