Script to Compile all invalid Objects in the Data base.

DECLARE
   l_schema                                          VARCHAR2 (240) := '&Schema_name';
   l_include_java                                    INTEGER := 0;
   l_max_passes                                      INTEGER := 10;
   l_debug_flag                                      INTEGER := 0;
   -- Above four variables can be used as parameters if defining a procedure for the Anonymous block
   l_alter_string                                    VARCHAR2 (4000);
   c_cursor                                          INTEGER;
   l_ret_code                                        INTEGER;
   l_object_count                                    INTEGER;
   l_pass                                            INTEGER;
   l_message                                         VARCHAR2 (8000);
   l_status                                          NUMBER;

   CURSOR c_objects_dtls (
      c_schema                   IN             VARCHAR2
   )
   IS
      SELECT   owner
             , object_type
             , object_name
             , DECODE (object_type, 'TYPE', 1, 'OPERATOR', 2, 'PACKAGE', 3, 4) obj_type
          FROM all_objects
         WHERE (status = 'INVALID')
           AND (   c_schema IS NULL
                OR owner = UPPER (c_schema))
           AND object_type IN
                  ('DIMENSION', 'FUNCTION', 'INDEXTYPE', 'JAVA CLASS', 'JAVA SOURCE', 'MATERIALIZED VIEW', 'OPERATOR'
                 , 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER', 'TYPE', 'TYPE BODY', 'VIEW')
      ORDER BY obj_type;
BEGIN
   c_cursor                                                 := SYS.DBMS_SQL.open_cursor;
   l_pass                                                   := 1;

   LOOP
      l_object_count                                           := 0;

      FOR c_objects_dtls_rec IN c_objects_dtls (l_schema)
      LOOP
         IF (c_objects_dtls_rec.object_type = 'PACKAGE')
         THEN
            l_alter_string                                           :=
               'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name
               || '" COMPILE PACKAGE';
         ELSIF (c_objects_dtls_rec.object_type = 'PACKAGE BODY')
         THEN
            l_alter_string                                           :=
                'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE')
         THEN
            l_alter_string                                           :=
                  'ALTER TYPE "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE SPECIFICATION';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE BODY')
         THEN
            l_alter_string                                           :=
                   'ALTER TYPE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSE
            l_alter_string                                           :=
                  'ALTER '
               || c_objects_dtls_rec.object_type
               || ' "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE';
         END IF;

         IF (    l_include_java <> 1
             AND c_objects_dtls_rec.object_type LIKE '%JAVA%')
         THEN
            l_alter_string                                           := NULL;
         END IF;

         IF (l_alter_string IS NOT NULL)
         THEN
            l_object_count                                           := l_object_count + 1;
            l_message                                                :=
                  'CompileAllObjects pass '
               || TO_CHAR (l_pass, '99999')
               || ' processing object '
               || TO_CHAR (l_object_count, '99999')
               || ' ['
               || l_alter_string
               || ']';

            BEGIN
               DBMS_SQL.parse (c_cursor, l_alter_string, DBMS_SQL.native);
               l_ret_code                                               := DBMS_SQL.EXECUTE (c_cursor);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_message                                                :=
                                                                  l_message || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
                  NULL;
            END;

            IF (l_debug_flag <> 0)
            THEN
               DBMS_PIPE.pack_message (LENGTH (l_message));
               DBMS_PIPE.pack_message (l_message);
               l_status                                                 :=
                                                      DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
            END IF;
         END IF;
      END LOOP;

      EXIT WHEN l_object_count = 0
            OR l_pass >= l_max_passes;
      l_pass                                                   := l_pass + 1;
   END LOOP;

   DBMS_SQL.close_cursor (c_cursor);

   IF l_debug_flag <> 0
   THEN
      l_message                                                :=
            '*** The Invalid Object Compilation is Completed'
         || TO_CHAR (l_object_count)
         || ' objects in '
         || l_pass
         || ' passes ***';
      DBMS_PIPE.pack_message (LENGTH (l_message));
      DBMS_PIPE.pack_message (l_message);
      l_status                                                 :=
                                                       DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
   END IF;
END;
/

API scripts to Create/Update/delete the Category Set/Category Assignment for a Item

/*****************************************************************************
 Script to Create the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs.structure_id
                                FROM mtl_category_sets_b mcs
                               WHERE mcs.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_name>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   inv_item_category_pub.create_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_true
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => x_return_status
                                                   , x_errorcode                   => x_error_code
                                                   , x_msg_count                   => x_msg_count
                                                   , x_msg_data                    => x_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_id
                                                    );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to Update the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
   l_old_category_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs_b.structure_id
                                FROM mtl_category_sets_b mcs_b
                               WHERE mcs_b.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_id>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   SELECT mcb.category_id
     INTO l_old_category_id
     FROM mtl_system_items_b msi
        , mtl_item_categories mic
        , mtl_categories_b mcb
        , mtl_category_sets mcs
    WHERE 1 = 1
      AND mic.inventory_item_id = msi.inventory_item_id
      AND mic.organization_id = msi.organization_id
      AND mic.category_id = mcb.category_id
      AND mic.category_set_id = mcs.category_set_id
      AND mcb.structure_id = mcs.structure_id
      AND msi.inventory_item_id = c_inventory_item_id
      AND msi.organization_id = c_orgnaization_id
      AND mcs.category_set_id = c_category_set_id;

   inv_item_category_pub.update_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_false
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => l_return_status
                                                   , x_errorcode                   => l_error_code
                                                   , x_msg_count                   => l_msg_count
                                                   , x_msg_data                    => l_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_id
                                                   , p_old_category_id             => l_old_category_id
                                                    );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to delete the categroy assignment for a item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);

   CURSOR c_get_item_categories
   IS
      SELECT msi.segment1
           , msi.inventory_item_id
           , mcs.category_set_id
           , mcb.category_id
           , msi.organization_id
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.inventory_item_id = c_inventory_item_id
         AND msi.organization_id = c_orgnaization_id;
BEGIN
   FOR c_get_item_categories_rec IN c_get_item_categories
   LOOP
      inv_item_category_pub.delete_category_assignment (p_api_version                 => 1.0
                                                      , p_init_msg_list               => fnd_api.g_true
                                                      , p_commit                      => fnd_api.g_true
                                                      , x_return_status               => x_return_status
                                                      , x_errorcode                   => x_error_code
                                                      , x_msg_count                   => x_msg_count
                                                      , x_msg_data                    => x_msg_data
                                                      , p_category_id                 => c_get_item_categories_rec.category_id
                                                      , p_category_set_id             => c_get_item_categories_rec.category_set_id
                                                      , p_inventory_item_id           => c_get_item_categories_rec.inventory_item_id
                                                      , p_organization_id             => c_get_item_categories_rec.organization_id
                                                       );

      IF x_return_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index                   => i
                                , p_encoded                     => fnd_api.g_false
                                , p_data                        => x_msg_data
                                , p_msg_index_out               => l_msg_index_out
                                 );

            IF l_error_message IS NULL
            THEN
               l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
            ELSE
               l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
         DBMS_OUTPUT.put_line ('*****************************************');
      ELSE
         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line (   'Removed Category Assiginment from Item : '
                               || c_get_item_categories_rec.segment1
                               || ' Successfully'
                              );
         DBMS_OUTPUT.put_line ('*****************************************');
      END IF;
   END LOOP;
END;
/
********************************************************************
 Bulk Update script to Update the Category on Item
********************************************************************
DECLARE
   CURSOR c_category
   IS
      SELECT mcb.category_id
           , mcs.category_set_id
           , msi.organization_id
           , msi.inventory_item_id
           , msi.segment1
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.segment1 = :p_segment1
          OR msi.inventory_item_id = :p_inventory_item_id
         AND msi.organization_id = :p_organization_id
         AND mcs.category_set_id = :p_category_set_id
         AND mcb.category_id = :p_category_id;

   l_msg_index_out                              NUMBER;
   l_error_message                              VARCHAR2(2000);
   x_return_status                              VARCHAR2(80);
   x_error_code                                 NUMBER;
   x_msg_count                                  NUMBER;
   x_msg_data                                   VARCHAR2(250);
   l_category_id                                NUMBER;
   l_category_set_id                            NUMBER;
   l_inventory_item_id                          NUMBER;
   l_organization_id                            NUMBER;
   l_old_category_id                            NUMBER;
BEGIN
   fnd_global.apps_initialize( 12247, 20634, 401);
   COMMIT;

   FOR z IN c_category
   LOOP
      inv_item_category_pub.update_category_assignment(
         p_api_version                             => 1.0
       , p_init_msg_list                           => fnd_api.g_false
       , p_commit                                  => fnd_api.g_true
       , x_return_status                           => x_return_status
       , x_errorcode                               => x_error_code
       , x_msg_count                               => x_msg_count
       , x_msg_data                                => x_msg_data
       , p_category_id                             => :p_new_category_id                                                    --3372
       , p_category_set_id                         => z.category_set_id
       , p_inventory_item_id                       => z.inventory_item_id
       , p_organization_id                         => z.organization_id
       , p_old_category_id                         => z.category_id);

      IF x_return_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get(
               p_msg_index                               => i
             , p_encoded                                 => fnd_api.g_false
             , p_data                                    => x_msg_data
             , p_msg_index_out                           => l_msg_index_out);

            IF l_error_message IS NULL
            THEN
               l_error_message                                          := SUBSTR( x_msg_data, 1, 250);
            ELSE
               l_error_message                                          :=
                     l_error_message
                  || ' /'
                  || SUBSTR( x_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line( '*****************************************');
         DBMS_OUTPUT.put_line(
               'API Error : '
            || l_error_message);
         DBMS_OUTPUT.put_line( '*****************************************');
      ELSE
         DBMS_OUTPUT.put_line( '*****************************************');
         DBMS_OUTPUT.put_line(
               'Created Category Assiginment from Item id : '
            || l_inventory_item_id
            || ' Successfully');
         DBMS_OUTPUT.put_line( '*****************************************');
      END IF;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            'UNEXP_ERROR IN MAIN : '
         || SUBSTR( SQLERRM, 1, 250));
END;
/

How to Copy Any File using UTL_FILE

While UTL_FILE.FCOPY can be used to copy valid text files, it does not copy binary files or invalid text files.  An example of an invalid text file would be one that contains a character sequence that cannot be generated by the characters on your keyboard (i.e. the sequence of two CR's in a row on a Windows platform rather than CRLF, CRLF) and hence may be considered a binary file.

NOTE: As UTL_FILE first supported the reading and writing of binary files in 10gR1, this note can only be used with Oracle Database versions 10gR1 and later.

Solution

1. Create two directory objects for the IN and OUT file locations.

CREATE OR REPLACE DIRECTORY in_file_loc AS 'C:\temp\in';
CREATE OR REPLACE DIRECTORY out_file_loc AS 'C:\temp\out';

NOTE: You must have the CREATE ANY DIRECTORY privilege to execute these statements.

2. Create the procedure CopyFile.

-- Procedure to COPY a Text or Binary file

CREATE OR REPLACE PROCEDURE copyfile (
   in_filename                IN             VARCHAR2
 , out_filename               IN             VARCHAR2
)
IS
   in_file                                           UTL_FILE.file_type;
   out_file                                          UTL_FILE.file_type;
   buffer_size                              CONSTANT INTEGER := 32767;   -- Max Buffer Size = 32767
   buffer                                            RAW (32767);
   buffer_length                                     INTEGER;
BEGIN
   -- Open a handle to the location where you are going to read the Text or Binary file from
   -- NOTE: The 'rb' parameter means "read in byte mode" and is only available
   --       in the UTL_FILE package with Oracle 10g or later
   in_file                                                  :=
                                                              UTL_FILE.fopen ('IN_FILE_LOC', in_filename, 'rb', buffer_size);
   -- Open a handle to the location where you are going to write the Text or Binary file to
   -- NOTE: The 'wb' parameter means "write in byte mode" and is only available
   --       in the UTL_FILE package with Oracle 10g or later
   out_file                                                 :=
                                                            UTL_FILE.fopen ('OUT_FILE_LOC', out_filename, 'wb', buffer_size);
   -- Attempt to read the first chunk of the in_file
   UTL_FILE.get_raw (in_file, buffer, buffer_size);
   -- Determine the size of the first chunk read
   buffer_length                                            := UTL_RAW.LENGTH (buffer);

   -- Only write the chunk to the out_file if data exists
   WHILE buffer_length > 0
   LOOP
      -- Write one chunk of data
      UTL_FILE.put_raw (out_file, buffer, TRUE);

      -- Read the next chunk of data
      IF buffer_length = buffer_size
      THEN
         -- Buffer was full on last read, read another chunk
         UTL_FILE.get_raw (in_file, buffer, buffer_size);
         -- Determine the size of the current chunk
         buffer_length                                            := UTL_RAW.LENGTH (buffer);
      ELSE
         buffer_length                                            := 0;
      END IF;
   END LOOP;

   -- Close the file handles
   UTL_FILE.fclose (in_file);
   UTL_FILE.fclose (out_file);
EXCEPTION
   -- Raised when the size of the file is a multiple of the buffer_size
   WHEN NO_DATA_FOUND
   THEN
      -- Close the file handles
      UTL_FILE.fclose (in_file);
      UTL_FILE.fclose (out_file);
END;
/

3. Test procedure CopyFile with both Text and Binary files.

-- Test a TEXT file
EXEC CopyFile('original_textfilename.txt', 'new_textfilename.txt')

-- Test a BINARY File
EXEC CopyFile('original_binaryfilename.jpg', 'new_binaryfilename.jpg') 

Check the IN_FILE_LOC and OUT_FILE_LOC and verify that the files were successfully copied.  Compare the actual size in bytes to make sure they are an exact copy. 

Rate this document 

Top of Form

Bottom of Form