How to use Limits for Bulk Collect (PL/SQL Collections)

DECLARE
   CURSOR c_emp_cur
   IS
      SELECT * FROM emp;

   TYPE emp_cur_tbl_typ IS TABLE OF c_emp_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   emp_cur_tbl                                  emp_cur_tbl_typ;
BEGIN
   OPEN c_emp_cur;

   LOOP
      FETCH c_emp_cur BULK COLLECT INTO emp_cur_tbl LIMIT 100;

      EXIT WHEN c_emp_cur%NOTFOUND;

      FOR indx IN 1 .. emp_cur_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line( emp_cur_tbl( indx));
      END LOOP;
   END LOOP;

   CLOSE c_emp_cur;
END ;

How to assign the values to a DB Collection (Array ) When it is defined as Out Parameter in the Procedure/Functions

-- Define the Object Types , Collections as Below 

CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_OBJ AS OBJECT(
   SERIAL_NUMBER                   VARCHAR2(240)
 , STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SERIAL_NUMBER_OBJ
IS
   STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ
   IS
   BEGIN
      RETURN XX_SERIAL_NUMBER_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_TBL AS TABLE OF XX_SERIAL_NUMBER_OBJ;
/
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , SERIAL_NUMBER           VARCHAR2(240)
 , PROCESS_STATUS       VARCHAR2(40)
 , ERROR_ID               VARCHAR2(240)
 , ERROR_MESSAGE           VARCHAR2(2000)
 , STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_ORG_REASSIGN_STATUS_OBJ
IS
   STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ
   IS
   BEGIN
      RETURN XX_ORG_REASSIGN_STATUS_OBJ(NULL
                                        , NULL
                                        , NULL
                                        , NULL
                                        , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_TBL AS TABLE OF XX_ORG_REASSIGN_STATUS_OBJ;

--Define Procedure to assign the values to the Collection type

PRocedure xx_test_proc (
  p_serial_number_tbl IN xxrb_serial_number_tbl
, x_org_reassignment_status_tbl OUT NOCOPY xxrb_org_reassign_status_tbl)
IS 
--Define Local variable for the Object Type as below
org_reassign_status_obj xx_org_reassign_status_obj;

--Define Local variable for the Collection Type as below
xx_org_reassign_sts_tbl xx_org_reassign_status_tbl;
BEGIN
-- Initialize the local object type and DB table type 
org_reassign_status_obj := 

xx_org_reassign_status_obj.org_reassign_status_obj;

xx_org_reassign_sts_tbl:= xx_org_reassign_status_tbl( org_reassign_status_obj);

For i in p_serial_number_tbl.FIRST..p_serial_number_tbl.LAST 
LOOP

-- Write your core logic which will derive the values for the Columns which needs to assign to the object type as shown below
org_reassign_status_obj.org_id       := 102;
org_reassign_status_obj.serial_number:= 'XYZ';
org_reassign_status_obj.process_status := 'S';
org_reassign_status_obj.error_id       := 'ORA-123';

-- Use the extend Function foe the Array as shown below
 
xx_org_reassign_sts_tbl.EXTEND;

-- Assign the Local record type declared in the declare section as -- below 

xx_org_reassign_sts_tbl( xx_org_reassign_sts_tbl.COUNT) := org_reassign_status_obj;

END LOOP;

x_org_reassignment_status_tbl :=
xx_org_reassign_sts_tbl

EXCEPTION
WHEN OTHERS 
THEN
DBMS_OUTPUT.PUT_LINE('UNEXP_ERROR : '||SUBSTR(SQLERRM, 1, 250));
END;

How to Invoke the Database Object Types in Anonymous Blocks

 -- Create DB Object Types and Collections

CREATE OR REPLACE TYPE XX_SYNC_ORG_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJECT
      RETURN XX_CC_SYNC_ORG_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SYNC_ORG_OBJ
IS
   STATIC FUNCTION GET_OBJECT
      RETURN XX_SYNC_ORG_OBJ
   IS
   BEGIN
      RETURN XX_SYNC_ORG_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SYNC_ORG_TBL AS TABLE OF XX_SYNC_ORG_OBJ;
/
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_EBS_SYNC_OBJ AS OBJECT(
   INSTANCE_REFERENCE       VARCHAR2(50)
 , SERIAL_NUMBER            VARCHAR2(240)
 , SERVICE_START_DATE       DATE
 , SERVICE_END_DATE         DATE
 , SUPPORT_LEVEL            VARCHAR2(50)
 , PARTNER                  VARCHAR2(1)
 , NODE_CATEGORY            VARCHAR2(50)
 , ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_EBS_SYNC_OBJ
IS
   STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ
   IS
   BEGIN
      RETURN XX_EBS_SYNC_OBJ(NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_EBS_SYNC_TBL AS TABLE OF XX_EBS_SYNC_OBJ;
/
--  Invoke it in anonymous Block...

DECLARE
   p_cc_org_tbl   xx_cc_sync_org_tbl := xx_cc_sync_org_tbl();
   x_ebs_sync_tbl xx_ebs_cc_sync_tbl;
   l_index           NUMBER := 1;
BEGIN
   -- Use Below Method When Using Hard coding
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 1):= xx_cc_sync_org_obj( '1212121');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 2):= xx_cc_sync_org_obj( '123123213213');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 3):= xx_cc_sync_org_obj( '1231232132131');

   -- Use Below Method When Using Dynamic fetch
    FOR i IN (SELECT DISTINCT attribute21
               FROM csi_item_instances
              WHERE NVL(active_end_date, SYSDATE) >= SYSDATE
                AND attribute21 IS NOT NULL)
   LOOP
     p_cc_org_tbl.EXTEND;
     p_cc_org_tbl( l_index) := xx_cc_sync_org_obj( i.attribute21);
     l_index                                   := l_index + 1;
   END LOOP;

   DBMS_OUTPUT.put_line(   'Table Count : '
                        || p_cc_org_tbl.COUNT);
 
   ebs_ks_cc_sync(p_cc_org_tbl, x_ebs_cc_sync_tbl);
END;

Efficient SQL Statements

Driving Tables (RBO Only)

The structure of the FROM and WHERE clauses of DML statements can be tailored to improve the performance of the statement. The rules vary depending on whether the database engine is using the Rule or Cost based optimizer. The situation is further complicated by the fact that the engine may perform a Merge Join or a Nested Loop join to retrieve the data. Despite this, there are a few rules you can use to improve the performance of your SQL.

Oracle processes result sets a table at a time. It starts by retrieving all the data for the first (driving) table. Once this data is retrieved it is used to limit the number of rows processed for subsequent (driven) tables. In the case of multiple table joins, the driving table limits the rows processed for the first driven table. Once processed, this combined set of data is the driving set for the second driven table etc. Roughly translated into English, this means that it is best to process tables that will retrieve a small number of rows first. The optimizer will do this to the best of it's ability regardless of the structure of the DML, but the following factors may help.

Both the Rule and Cost based optimizer select a driving table for each query. If a decision cannot be made, the order of processing is from the end of the FROM clause to the start. Therefore, you should always place your driving table at the end of the FROM clause. Subsequent driven tables should be placed in order so that those retrieving the most rows are nearer to the start of the FROM clause. Confusingly, the WHERE clause should be written in the opposite order, with the driving tables conditions first and the final driven table last. i.e.

FROM  d, c, b, a

WHERE a.join_column = 12345

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

AND   c.join_column = d.join_column;

If we now want to limit the rows brought back from the "D" table we may write the following.

FROM  d, c, b, a

WHERE a.join_column = 12345

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

AND   c.join_column = d.join_column

AND   d.name = 'JONES';

Depending on the number of rows and the presence of indexes, Oracle my now pick "D" as the driving table. Since "D" now has two limiting factors (join_column and name), it may be a better candidate as a driving table so the statement may be better written as follows.

FROM  c, b, a, d

WHERE d.name = 'JONES'

AND   d.join_column = 12345

AND   d.join_column = a.join_column

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

This grouping of limiting factors will guide the optimizer more efficiently making table "D" return relatively few rows, and so make it a more efficient driving table.

Remember, the order of the items in both the FROM and WHERE clause will not force the optimizer to pick a specific table as a driving table, but it may influence it's decision. The grouping of limiting conditions onto a single table will reduce the number of rows returned from that table, and will therefore make it a stronger candidate for becoming the driving table.

Build IN Clause dynamically and use it in SELECT statement

CREATE OR REPLACE TYPE xx_string_tbl_typ AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION xx_build_dynamic_in_clause( p_in_clause_strg IN VARCHAR2)
   RETURN xx_string_tbl_typ
AS
   xx_string_tbl   xx_string_tbl_typ := xx_string_tbl_typ();
   l_in_clause     VARCHAR2(4000) :=    p_in_clause_strg || ','; 
-- length can be increase till 32767
   i                                            NUMBER;
BEGIN
   LOOP
      i                                         :=
         INSTR(l_in_clause
             , ',');
      EXIT WHEN NVL(i, 0) = 0;
      xx_string_tbl.EXTEND;
      xx_string_tbl( xx_string_tbl.LAST)        :=
         TRIM(SUBSTR(l_in_clause
                   , 1
                   , i - 1));
      l_in_clause                               :=
         SUBSTR(l_in_clause
              , i + 1);
   END LOOP;

   RETURN xx_string_tbl;
END;
/
--- Test Script to test the Function
SELECT *
  FROM mtl_system_items_b
 WHERE organization_id = &organization_id
   AND inventory_item_id IN (SELECT * FROM TABLE( xx_build_dynamic_in_clause( '&1,&2,&3')))

-- Another method by using PIPELINED function

CREATE OR REPLACE FUNCTION xx_build_dynamic_in_clause( p_in_clause_strg IN VARCHAR2)
   RETURN xx_string_tbl_typ
   PIPELINED
AS
l_in_clause  VARCHAR2(4000):=    p_in_clause_strg || ',';                            -- length can be increase till 32767
   i  NUMBER;
BEGIN
   LOOP
      i                                         :=
         INSTR(l_in_clause
             , ',');
      EXIT WHEN NVL(i, 0) = 0;
      PIPE ROW (TRIM(SUBSTR(l_in_clause
                          , 1
                          , i - 1)));
      l_in_clause                               :=
         SUBSTR(l_in_clause
              , i + 1);
   END LOOP;

   RETURN;
END;
/

--- Test Script to test the Function
SELECT *
  FROM mtl_system_items_b
 WHERE organization_id = &organization_id
   AND inventory_item_id IN (SELECT * FROM TABLE( xx_build_dynamic_in_clause( '&1,&2,&3')))

API to get the On hand quantity details for Item in Inveontory

PROCEDURE get_onhand_quantities(
   p_item_name              IN            VARCHAR2
 , p_organziation_id        IN            NUMBER
 , p_subinv                 IN            VARCHAR2 DEFAULT NULL
 , l_qty_oh                    OUT NOCOPY NUMBER
 , l_qty_res_oh                OUT NOCOPY NUMBER
 , l_qty_res                   OUT NOCOPY NUMBER
 , l_qty_sug                   OUT NOCOPY NUMBER
 , l_qty_att                   OUT NOCOPY NUMBER
 , l_qty_atr                   OUT NOCOPY NUMBER)
IS
   CURSOR c_get_item_id(
    c_item_name                                   VARCHAR2
  , c_organization_id                             NUMBER)
   IS
      SELECT inventory_item_id
           , organziation_id
        FROM mtl_system_items_b
       WHERE segment1 = c_item_name
         AND organization_id = c_organization_id;

   l_api_return_status                          VARCHAR2(1);
   l_qty_oh                                     NUMBER;
   l_qty_res_oh                                 NUMBER;
   l_qty_res                                    NUMBER;
   l_qty_sug                                    NUMBER;
   l_qty_att                                    NUMBER;
   l_qty_atr                                    NUMBER;
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2(250);
   x_return_msg                                 VARCHAR2(4000);
   l_msg_index_out                              NUMBER;
   l_organziation_id                            NUMBER;
   l_item_id                                    NUMBER;
BEGIN
   OPEN c_get_item_id(p_item_name
                    , p_organziation_id);

   LOOP
      FETCH c_get_item_id
         INTO l_item_id
            , l_organziation_id;

      EXIT WHEN c_get_item_id%NOTFOUND;
      apps.inv_quantity_tree_grp.clear_quantity_cache;

      apps.inv_quantity_tree_pub.query_quantities(
         p_api_version_number                      => 1.0
       , p_init_msg_lst                            => apps.fnd_api.g_false
       , x_return_status                           => l_api_return_status
       , x_msg_count                               => l_msg_count
       , x_msg_data                                => l_msg_data
       , p_organization_id                         => l_organziation_id
       , p_inventory_item_id                       => l_item_id
       , p_tree_mode                               => apps.inv_quantity_tree_pub.g_transaction_mode
       , p_onhand_source                           => 3
       , p_is_revision_control                     => FALSE
       , p_is_lot_control                          => FALSE
       , p_is_serial_control                       => FALSE
       , p_revision                                => NULL
       , p_lot_number                              => NULL
       , p_subinventory_code                       => p_subinv
       , p_locator_id                              => NULL
       , x_qoh                                     => l_qty_oh
       , x_rqoh                                    => l_qty_res_oh
       , x_qr                                      => l_qty_res
       , x_qs                                      => l_qty_sug
       , x_att                                     => l_qty_att
       , x_atr                                     => l_qty_atr);

      IF l_api_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line(   'Total on hand quantity : '
                              || l_qty_oh
                              || ' Quantity res oh :'
                              || l_qty_res_oh
                              || ' Quantity reserved :'
                              || l_qty_res
                              || ' Quantity sug :'
                              || l_qty_sug
                              || ' Quantity Available to transat :'
                              || l_qty_att
                              || ' Quantity available to reserve :'
                              || l_qty_atr);
      ELSE
         FOR i IN 1 .. l_msg_count
         LOOP
            fnd_msg_pub.get(p_msg_index                               => i
                          , p_encoded                                 => fnd_api.g_false
                          , p_data                                    => l_msg_data
                          , p_msg_index_out                           => l_msg_index_out);

            IF x_return_msg IS NULL
            THEN
               x_return_msg                              :=
                  SUBSTR(l_msg_data
                       , 1
                       , 250);
            ELSE
               x_return_msg                              :=
                     x_return_msg
                  || ','
                  || SUBSTR(l_msg_data
                          , 1
                          , 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line(   ' QUERY_QUANTITIES API Failure : '
                              || x_return_msg);
      END IF;
   END LOOP;

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

Script to Remove the Duplicate Strings from a String and return Unique String (Comma Separated)

Below script can be used to remove the repeating (Duplicate String ) String from a string and return unique string Concatenated by comma (replace comma in below example with any string separator)


FUNCTION remove_duplicate_strings( p_string IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_temp_string                                VARCHAR2(4000);
   l_loop_cnt                                   NUMBER := 0;
   l_string                                     VARCHAR2(4000);
   l_final_sting                                VARCHAR2(4000) := ' ';

   TYPE string_tbl_typ IS TABLE OF VARCHAR2(4000)
      INDEX BY VARCHAR(4000);

   string_tbl                                   string_tbl_typ;
BEGIN
   l_temp_string                             :=
      REGEXP_REPLACE(
         p_string
       , '[,]+'
       , ',');

   LOOP
      l_loop_cnt                                := l_loop_cnt + 1;
      l_string                                  :=
         REGEXP_SUBSTR(
            p_string
          , '[^,]+'
          , 1
          , l_loop_cnt);

      EXIT WHEN l_string IS NULL;

      IF NOT string_tbl.EXISTS( l_string)
      THEN
         string_tbl( l_string)                     := l_string;
         l_final_sting                             :=
               l_final_sting
            || ','
            || l_string;
      END IF;
   END LOOP;

   l_final_sting                             := TRIM( BOTH ',' FROM TRIM( l_final_sting));

   RETURN l_final_sting;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            SQLERRM
         || CHR( 10)
         || DBMS_UTILITY.format_error_backtrace);
      RETURN p_string;
END;

sys_refcursor to dynamic record/table type

DECLARE
   l_refcursor                          SYS_REFCURSOR;
   l_cols                        NUMBER;
   l_desc                        DBMS_SQL.desc_tab;
   l_curs                        INTEGER;
   l_varchar                     VARCHAR2(4000);

   FUNCTION xx_ref_cursor
      RETURN SYS_REFCURSOR
   IS
      l_refcursor                   SYS_REFCURSOR;
   BEGIN
      OPEN l_refcursor FOR
         SELECT inventory_item_id, segment1, description
           FROM mtl_system_items_b
          WHERE ROWNUM <= 10;

      RETURN l_refcursor;
   END;
BEGIN
   l_refcursor := xx_ref_cursor;
   l_curs                                              := DBMS_SQL.to_cursor_number( l_refcursor);
   DBMS_SQL.describe_columns(
      c                               => l_curs
    , col_cnt                         => l_cols
    , desc_t                          => l_desc);

   FOR i IN 1 .. l_cols
   LOOP
      DBMS_SQL.define_column(
         l_curs
       , i
       , l_varchar
       , 4000);
   END LOOP;

   WHILE DBMS_SQL.fetch_rows( l_curs) > 0
   LOOP
      FOR i IN 1 .. l_cols
      LOOP
         DBMS_SQL.COLUMN_VALUE(
            l_curs
          , i
          , l_varchar);
         DBMS_OUTPUT.put_line(
               'Row Number :'
            || DBMS_SQL.last_row_count
            || ': '
            || l_desc( i).col_name
            || ' = '
            || l_varchar);
      END LOOP;
   END LOOP;

   DBMS_SQL.close_cursor( l_curs);
END;
/

Script to Create Excel using UTL_FILE

DECLARE
   v_fh                          UTL_FILE.file_type;
   v_dir                         VARCHAR2 (30) := 'XX_INTF_DIR';
   v_file                        VARCHAR2 (30) := 'custom_value_sets.xls';

   PROCEDURE run_query (p_sql IN VARCHAR2)
   IS
      v_v_val                       VARCHAR2 (4000);
      v_n_val                       NUMBER;
      v_d_val                       DATE;
      v_ret                         NUMBER;
      c                             NUMBER;
      d                             NUMBER;
      col_cnt                       INTEGER;
      f                             BOOLEAN;
      rec_tab                       DBMS_SQL.desc_tab;
      col_num                       NUMBER;
   BEGIN
      c                               := DBMS_SQL.open_cursor;
      -- parse the SQL statement
      DBMS_SQL.parse (c, p_sql, DBMS_SQL.native);
      -- start execution of the SQL statement
      d                               := DBMS_SQL.execute (c);
      -- get a description of the returned columns
      DBMS_SQL.describe_columns (c, col_cnt, rec_tab);

      -- bind variables to columns
      FOR j IN 1 .. col_cnt
      LOOP
         CASE rec_tab (j).col_type
            WHEN 1
            THEN
               DBMS_SQL.define_column (c,
                                       j,
                                       v_v_val,
                                       4000);
            WHEN 2
            THEN
               DBMS_SQL.define_column (c, j, v_n_val);
            WHEN 12
            THEN
               DBMS_SQL.define_column (c, j, v_d_val);
            ELSE
               DBMS_SQL.define_column (c,
                                       j,
                                       v_v_val,
                                       4000);
         END CASE;
      END LOOP;

      -- Output the column headers
      UTL_FILE.put_line (v_fh, '<ss:Row>');

      FOR j IN 1 .. col_cnt
      LOOP
         UTL_FILE.put_line (v_fh, '<ss:Cell>');
         UTL_FILE.put_line (v_fh,
                               '<ss:Data ss:Type="String">'
                            || rec_tab (j).col_name
                            || '</ss:Data>');
         UTL_FILE.put_line (v_fh, '</ss:Cell>');
      END LOOP;

      UTL_FILE.put_line (v_fh, '</ss:Row>');

      -- Output the data
      LOOP
         v_ret                           := DBMS_SQL.fetch_rows (c);
         EXIT WHEN v_ret = 0;
         UTL_FILE.put_line (v_fh, '<ss:Row>');

         FOR j IN 1 .. col_cnt
         LOOP
            CASE rec_tab (j).col_type
               WHEN 1
               THEN
                  DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell>');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="String">'
                                     || v_v_val
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
               WHEN 2
               THEN
                  DBMS_SQL.COLUMN_VALUE (c, j, v_n_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell>');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="Number">'
                                     || TO_CHAR (v_n_val)
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
               WHEN 12
               THEN
                  DBMS_SQL.COLUMN_VALUE (c, j, v_d_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell ss:StyleID="OracleDate">');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="DateTime">'
                                     || TO_CHAR (v_d_val, 'YYYY-MM-DD"T"HH24:MI:SS')
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
               ELSE
                  DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
                  UTL_FILE.put_line (v_fh, '<ss:Cell>');
                  UTL_FILE.put_line (v_fh,
                                        '<ss:Data ss:Type="String">'
                                     || v_v_val
                                     || '</ss:Data>');
                  UTL_FILE.put_line (v_fh, '</ss:Cell>');
            END CASE;
         END LOOP;

         UTL_FILE.put_line (v_fh, '</ss:Row>');
      END LOOP;

      DBMS_SQL.close_cursor (c);
   END;

   --
   PROCEDURE start_workbook
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '<?xml version="1.0"?>');
      UTL_FILE.put_line (v_fh,
                         '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
   END;

   PROCEDURE end_workbook
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '</ss:Workbook>');
   END;

   --
   PROCEDURE start_worksheet (p_sheetname IN VARCHAR2)
   IS
   BEGIN
      UTL_FILE.put_line (v_fh,
                            '<ss:Worksheet ss:Name="'
                         || p_sheetname
                         || '">');
      UTL_FILE.put_line (v_fh, '<ss:Table>');
   END;

   PROCEDURE end_worksheet
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '</ss:Table>');
      UTL_FILE.put_line (v_fh, '</ss:Worksheet>');
   END;

   --
   PROCEDURE set_date_style
   IS
   BEGIN
      UTL_FILE.put_line (v_fh, '<ss:Styles>');
      UTL_FILE.put_line (v_fh, '<ss:Style ss:ID="OracleDate">');
      UTL_FILE.put_line (v_fh, '<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
      UTL_FILE.put_line (v_fh, '</ss:Style>');
      UTL_FILE.put_line (v_fh, '</ss:Styles>');
   END;
BEGIN
   v_fh                            :=
      UTL_FILE.fopen (UPPER (v_dir),
                      v_file,
                      'w',
                      32767);
   start_workbook;
   set_date_style;
   start_worksheet ('CUSTOM_VALUESET');
   run_query ('SELECT ffvs.flex_value_set_id,
       ffvs.flex_value_set_name,
       ffvt.value_column_name,
       ffvt.meaning_column_name,
       ffvt.id_column_name,
       ffvt.application_table_name,
       ffvt.additional_where_clause
  FROM fnd_flex_value_sets ffvs, fnd_flex_validation_tables ffvt
 WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id
     AND UPPER (ffvs.flex_value_set_name) LIKE ''XX%''') ;
   end_worksheet;
/*When needs to create Multiple Sheets in a workbook*/
--   start_worksheet ('DUAL');
--   run_query ('SELECT SYSDATE FROM DUAL');
--   end_worksheet;
   end_workbook;
   UTL_FILE.fclose (v_fh);
END;