Thursday, December 19, 2013

API Script to Update Extensible Attributes for a instance in Install Base

DECLARE
   x_instance_rec                                    csi_datastructures_pub.instance_rec;
   p_ext_attrib_values                               csi_datastructures_pub.extend_attrib_values_tbl;
   p_party_tbl                                       csi_datastructures_pub.party_tbl;
   p_account_tbl                                     csi_datastructures_pub.party_account_tbl;
   p_pricing_attrib_tbl                              csi_datastructures_pub.pricing_attribs_tbl;
   p_org_assignments_tbl                             csi_datastructures_pub.organization_units_tbl;
   p_asset_assignment_tbl                            csi_datastructures_pub.instance_asset_tbl;
   p_txn_rec                                         csi_datastructures_pub.transaction_rec;
   x_instance_id_lst                                 csi_datastructures_pub.id_tbl;
   x_return_status                                   VARCHAR2(2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2(2000);
   x_msg_index_out                                   NUMBER;
   t_output                                          VARCHAR2(2000);
   t_msg_dummy                                       NUMBER;
   p_validation_level                                NUMBER;
   p_commit                                          VARCHAR2(5);
   p_init_msg_lst                                    VARCHAR2(500);
BEGIN
   x_instance_rec.instance_id                     := 4004190;
   x_instance_rec.object_version_number           := 7;
   p_txn_rec.transaction_id                       := fnd_api.g_miss_num;
   p_txn_rec.transaction_date                     := SYSDATE;
   p_txn_rec.source_transaction_date              := SYSDATE;
   p_txn_rec.transaction_type_id                  := 1;
   p_ext_attrib_values(1).attribute_value         := 'TEST_1';
   p_ext_attrib_values(1).attribute_value_id      := 1747998;
   p_ext_attrib_values(1).instance_id             := 4004190;
   p_ext_attrib_values(1).object_version_number   := 2;
   csi_item_instance_pub.update_item_instance(p_api_version                 => 1.0
                                            , p_commit                      => p_commit
                                            , p_init_msg_list               => p_init_msg_lst
                                            , p_validation_level            => 1
                                            , p_instance_rec                => x_instance_rec
                                            , p_ext_attrib_values_tbl       => p_ext_attrib_values
                                            , p_party_tbl                   => p_party_tbl
                                            , p_account_tbl                 => p_account_tbl
                                            , p_pricing_attrib_tbl          => p_pricing_attrib_tbl
                                            , p_org_assignments_tbl         => p_org_assignments_tbl
                                            , p_asset_assignment_tbl        => p_asset_assignment_tbl
                                            , p_txn_rec                     => p_txn_rec
                                            , x_instance_id_lst             => x_instance_id_lst
                                            , x_return_status               => x_return_status
                                            , x_msg_count                   => x_msg_count
                                            , x_msg_data                    => x_msg_data
                                             );
   COMMIT;

-- Output the results
   IF x_msg_count > 0
   THEN
      FOR j IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get(j
                       , fnd_api.g_false
                       , x_msg_data
                       , t_msg_dummy
                        );
         t_output                                       :=('Msg' || TO_CHAR(j) || ': ' || x_msg_data);
         DBMS_OUTPUT.put_line(SUBSTR(t_output
                                   , 1
                                   , 255
                                    ));
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line('x_return_status = ' || x_return_status);
   DBMS_OUTPUT.put_line('x_msg_count = ' || TO_CHAR(x_msg_count));
   DBMS_OUTPUT.put_line('x_msg_data = ' || x_msg_data);
   COMMIT;
END;

API Script to Create Extensible Attributes for a instance in Install Base

DECLARE
   x_instance_rec                                    csi_datastructures_pub.instance_rec;
   p_ext_attrib_values                               csi_datastructures_pub.extend_attrib_values_tbl;
   p_party_tbl                                       csi_datastructures_pub.party_tbl;
   p_account_tbl                                     csi_datastructures_pub.party_account_tbl;
   p_pricing_attrib_tbl                              csi_datastructures_pub.pricing_attribs_tbl;
   p_org_assignments_tbl                             csi_datastructures_pub.organization_units_tbl;
   p_asset_assignment_tbl                            csi_datastructures_pub.instance_asset_tbl;
   p_txn_rec                                         csi_datastructures_pub.transaction_rec;
   x_instance_id_lst                                 csi_datastructures_pub.id_tbl;
   x_return_status                                   VARCHAR2(2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2(2000);
   x_msg_index_out                                   NUMBER;
   t_output                                          VARCHAR2(2000);
   t_msg_dummy                                       NUMBER;
   p_validation_level                                NUMBER;
   p_commit                                          VARCHAR2(5);
   p_init_msg_lst                                    VARCHAR2(500);
BEGIN
   x_instance_rec.instance_id                     := 4004190;
   x_instance_rec.object_version_number           := 7;
   p_txn_rec.transaction_id                       := fnd_api.g_miss_num;
   p_txn_rec.transaction_date                     := SYSDATE;
   p_txn_rec.source_transaction_date              := SYSDATE;
   p_txn_rec.transaction_type_id                  := 1;
   p_ext_attrib_values(1).instance_id             := 4004190;
   p_ext_attrib_values(1).attribute_id            := 31000;
   p_ext_attrib_values(1).attribute_value         := 'TEST_5';
   csi_item_instance_pub.update_item_instance(p_api_version                 => 1.0
                                            , p_commit                      => p_commit
                                            , p_init_msg_list               => p_init_msg_lst
                                            , p_validation_level            => 1
                                            , p_instance_rec                => x_instance_rec
                                            , p_ext_attrib_values_tbl       => p_ext_attrib_values
                                            , p_party_tbl                   => p_party_tbl
                                            , p_account_tbl                 => p_account_tbl
                                            , p_pricing_attrib_tbl          => p_pricing_attrib_tbl
                                            , p_org_assignments_tbl         => p_org_assignments_tbl
                                            , p_asset_assignment_tbl        => p_asset_assignment_tbl
                                            , p_txn_rec                     => p_txn_rec
                                            , x_instance_id_lst             => x_instance_id_lst
                                            , x_return_status               => x_return_status
                                            , x_msg_count                   => x_msg_count
                                            , x_msg_data                    => x_msg_data
                                             );
   COMMIT;

-- Output the results
   IF x_msg_count > 0
   THEN
      FOR j IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get(j
                       , fnd_api.g_false
                       , x_msg_data
                       , t_msg_dummy
                        );
         t_output                                       :=('Msg' || TO_CHAR(j) || ': ' || x_msg_data);
         DBMS_OUTPUT.put_line(SUBSTR(t_output
                                   , 1
                                   , 255
                                    ));
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line('x_return_status = ' || x_return_status);
   DBMS_OUTPUT.put_line('x_msg_count = ' || TO_CHAR(x_msg_count));
   DBMS_OUTPUT.put_line('x_msg_data = ' || x_msg_data);
   COMMIT;
END;

Wednesday, December 18, 2013

Storing Images in Oracle tables



Images and Oracle BLOB data are easy to add to a Oracle table. There are two ways to load BLOBs and CLOBs into the database. The first method uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures. The second uses the Oracle Call Interface (OCI) to perform the same function. Let's look at the first method.

To load external LOB data into internal LOB storage using PL/SQL alone you must first use a table containing BFILE locators for the files to allow Oracle to access them.

create table xx_images_tbl (
  file_id number,
  file_desc varchar2(30),
  file_loc bfile,
  file_type varchar2(15))
 /
Listing 3: Example Use of BFILE datatype in a Table

Notice in the above code that no LOB storage clause is specified. This is because all that is stored in the database is a locator value for the BFILE consisting of an internal DIRECTORY specification and a file name. The BFILE locators are loaded into the table using the BFILENAME function and a standard insert statement.
An example of this process is shown here in Listing 4.

INSERT INTO xx_images_tbl VALUES(1,'Test_file',bfilename('XX_DIR_NAME','TEST.JPG'),'JPEG');

commit;

Listing 4: Example Set of INSERT Commands to Load BFILE Locators Manually
TIP:
Using a host command to perform a single column directory listing into a file (for example on NT: dir /B >file.lis), then using the UTL_FILE package to read the contents of the created file into the DBMS_SQL package to build the INSERT commands on the fly, an entire directory of LOB datafiles can be loaded at one time into a BFILE table and then on into the internal LOB storage table. For an example see Listing 5.

CREATE OR REPLACE PROCEDURE xx_insert_images_proc(
   bfile_dir                  IN             VARCHAR2
 , bfile_lis                  IN             VARCHAR2
 , bfile_int_dir                             VARCHAR2
)
AS
   cur                                               INTEGER;
   bfile_int                                         VARCHAR2(100);
   sql_com                                           VARCHAR2(2000);
   file_proc                                         INTEGER;
   file_hand                                         UTL_FILE.file_type;
   file_buff                                         VARCHAR2(1022);
   file_type                                         VARCHAR2(4);
BEGIN
   bfile_int                                      := UPPER(bfile_int_dir);
   file_hand                                      := UTL_FILE.fopen(bfile_dir
                                                                  , bfile_lis
                                                                  , 'R'
                                                                   );

   LOOP
      BEGIN
         UTL_FILE.get_line(file_hand
                         , file_buff
                          );
         cur                                            := DBMS_SQL.open_cursor;
         file_type                                      := SUBSTR(file_buff
                                                                , INSTR(file_buff
                                                                      , '.'
                                                                       ) + 1
                                                                , 3
                                                                 );
         file_type                                      := UPPER(file_type);

         IF file_type = 'GIF'
         THEN
            file_type                                      := 'GIF';
         ELSIF file_type = 'JPG'
         THEN
            file_type                                      := 'JPEG';
         END IF;

         sql_com                                        :=
               'INSERT INTO xx_images_tbl '
            || CHR(10)
            || 'VALUES (xx_images_tbl_seq.NEXTVAL,'
            || CHR(39)
            || CHR(39)
            || ', bfilename('
            || CHR(39)
            || bfile_int
            || CHR(39)
            || ','
            || CHR(39)
            || file_buff
            || CHR(39)
            || ') ,'
            || CHR(39)
            || file_type
            || CHR(39)
            || ')';
         DBMS_OUTPUT.put_line(sql_com);
         DBMS_SQL.parse(cur
                      , sql_com
                      , DBMS_SQL.v7
                       );
         file_proc                                      := DBMS_SQL.EXECUTE(cur);
         DBMS_SQL.close_cursor(cur);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   UTL_FILE.fclose(file_hand);
END;
/
Listing 5: Example Procedure for Loading BFILE Locators based on an External File List
Once the BFILE locators are set in the BFILE table we can use the DBMS_LOB package to read the external LOB (BFILE) into an internal LOB (BLOB, CLOB or NCLOB). This is shown in Listing

 6. The SELECT from the TEMP_BLOB table initializes the internal LOB values so they can be used, otherwise an error will be returned.

CREATE OR REPLACE PROCEDURE load_lob
AS
   ID                                                NUMBER;
   image1                                            BLOB;
   LOCATOR                                           BFILE;
   bfile_len                                         NUMBER;
   bf_desc                                           VARCHAR2(30);
   bf_name                                           VARCHAR2(30);
   bf_dir                                            VARCHAR2(30);
   bf_typ                                            VARCHAR2(4);
   ctr                                               INTEGER;

   CURSOR get_id
   IS
      SELECT file_id
           , file_desc
           , file_type
        FROM xx_images_tbl;
BEGIN
   OPEN get_id;

   LOOP
      FETCH get_id
       INTO ID
          , bf_desc
          , bf_typ;

      EXIT WHEN get_id%NOTFOUND;
      DBMS_OUTPUT.put_line('ID: ' || TO_CHAR(ID));

      SELECT file_loc
        INTO LOCATOR
        FROM xx_images_tbl
       WHERE file_id = ID;

      DBMS_LOB.filegetname(LOCATOR
                         , bf_dir
                         , bf_name
                          );
      DBMS_OUTPUT.put_line('Dir: ' || bf_dir);
      DBMS_LOB.fileopen(LOCATOR
                      , DBMS_LOB.file_readonly
                       );
      bfile_len                                      := DBMS_LOB.getlength(LOCATOR);
      DBMS_OUTPUT.put_line('ID: ' || TO_CHAR(ID) || ' length: ' || TO_CHAR(bfile_len));

      SELECT temp_blob
        INTO image1
        FROM temp_blob;

      bfile_len                                      := DBMS_LOB.getlength(LOCATOR);
      DBMS_LOB.loadfromfile(image1
                          , LOCATOR
                          , bfile_len
                          , 1
                          , 1
                           );

      INSERT INTO xx_images_tbl
           VALUES (ID
                 , bf_desc
                 , image1
                 , bf_typ
                  );

      DBMS_OUTPUT.put_line(bf_desc || ' Length: ' || TO_CHAR(bfile_len) || ' Name: ' || bf_name || ' Dir: ' || bf_dir || ' ' || bf_typ);
      DBMS_LOB.fileclose(LOCATOR);
   END LOOP;
END;
/
/
Listing 6: Example Procedure to Load BFILE values into Internal LOBs
By enforcing a naming standard on the external LOB files the loading procedure in Listing 6.6 could be modified to place BLOB types into BLOBs, CLOB types into CLOBs and NCLOB types into NCLOBs based on the file type values that are parsed from the file extensions in Listing 5. Between the procedure in Listing 5 and the one shown in Listing 6 the example tables in Listings 1 and 3 are populated with LOB values located in a specific directory.