API Script to add the responsibilities to a user

DECLARE

   CURSOR c_resp

   IS

      SELECT fav.application_short_name

           , fav.application_name

           , frv.responsibility_key

           , frv.responsibility_name

        FROM fnd_application_vl fav

           , fnd_responsibility_vl frv

       WHERE frv.application_id = fav.application_id

         AND frv.responsibility_name IN ( 'Application Developer');

 l_user_name             VARCHAR2 (15) := '&USER_NAME';

 l_appl_short_name       fnd_application_vl.application_short_name%TYPE;

 l_resp_name             fnd_responsibility_vl.responsibility_name%TYPE;

 l_resp_key              fnd_responsibility_vl.responsibility_key%TYPE;

 l_description           VARCHAR2 (100) := 'Adding Responsibility to the user using script';

BEGIN

   FOR resp_rec IN c_resp

   LOOP

      l_appl_short_name    := resp_rec.application_short_name;

      l_resp_key           := resp_rec.responsibility_key;

      l_resp_name          := resp_rec.responsibility_name;

      BEGIN

         fnd_user_pkg.addresp (username          => l_user_name

                             , resp_app          => l_appl_short_name

                             , resp_key          => l_resp_key

                             , security_group    => 'STANDARD'

                             , description       => l_description

                             , start_date        => SYSDATE

                             , end_date          => NULL

                              );

         COMMIT;

         DBMS_OUTPUT.put_line ('The responsibility ' || l_resp_name || ' is added to the user ' || l_user_name);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (   'Responsibility '

                                  || l_resp_name

                                  || ' IS NOT added to the user '

                                  || l_user_name

                                  || ' due to '

                                  || SQLCODE

                                  || '; '

                                  || SUBSTR (SQLERRM, 1, 250)

                                 );

            DBMS_OUTPUT.put_line ('');

            ROLLBACK;

      END;

   END LOOP;

END;

API script to Disable the enabled Responsibilities for a User

DECLARE

   --cursor to get all inactive users

   CURSOR cur_inactive_user

   IS

      SELECT fu.user_id

           , fd.responsibility_id

           , fd.responsibility_application_id

           , fd.security_group_id

           , fd.start_date

           , fd.end_date

        FROM fnd_user fu

           , fnd_user_resp_groups_direct fd

       WHERE fu.user_id = fd.user_id

         AND (   fu.end_date <= SYSDATE

              OR fu.end_date IS NOT NULL)

         AND fd.end_date IS NOT NULL

         AND fu.user_id = :p_user_id;

BEGIN

   FOR rec_inactive_user IN cur_inactive_user

   LOOP

--checking if the responsibility is assigned to the user

 IF (fnd_user_resp_groups_api.assignment_exists (rec_inactive_user.user_id, rec_inactive_user.responsibility_id, rec_inactive_user.responsibility_application_id, rec_inactive_user.security_group_id))
     THEN

         -- Call API to End date the responsibility

         fnd_user_resp_groups_api.update_assignment

        (user_id           => rec_inactive_user.user_id

       , responsibility_id => rec_inactive_user.responsibility_id

       , responsibility_application_id => rec_inactive_user.responsibility_application_id

       , security_group_id => rec_inactive_user.security_group_id

       , start_date        => rec_inactive_user.start_date

       , end_date          => NULL

       , description       => NULL);

         COMMIT;

      END IF;

   END LOOP;

END;

API script to Enable the Disabled Responsibilities for a User

DECLARE
   --cursor to get all inactive users
   CURSOR cur_inactive_user
   IS
      SELECT fu.user_id
           , fd.responsibility_id
           , fd.responsibility_application_id
           , fd.security_group_id
           , fd.start_date
           , fd.end_date
        FROM fnd_user fu
           , fnd_user_resp_groups_direct fd
       WHERE fu.user_id = fd.user_id
         AND fu.user_id = :p_user_id;
BEGIN
   FOR rec_inactive_user IN cur_inactive_user
   LOOP
      fnd_user_resp_groups_api.update_assignment(
        user_id           => rec_inactive_user.user_id
      , responsibility_id => rec_inactive_user.responsibility_id
      , responsibility_application_id =>                rec_inactive_user.responsibility_application_id
    , security_group_id          => rec_inactive_user.security_group_id
    , start_date                 => rec_inactive_user.start_date
    , end_date                   => NULL
    , description                => NULL );
      COMMIT;
   END LOOP;
END;

Query to get the Menu,Submenu and function details For Oracle Menus

  SELECT second.application_id "App ID"
       , second.application_name "App Name"
       , second.responsibility_id "Resp ID"
       , second.responsibility_name "Responsibility"
       , second.menu_id "Menu ID"
       , second.user_menu_name "Main Menu Name"
       , second.entry_sequence "Seq"
       , second.prompt "Prompt"
       , second.function_id "Function ID"
       , second.user_function_name "Function"
       , second.func_descrip "Function Descrip"
       , second.sub_menu_id "SubMenu ID"
       , second.sub_menu_name "SubMenu Name"
       , second.sub_seq "Sub Seq"
       , second.sub_prompt "SubPrompt"
       , second.sub_func_id "SubFunction ID"
       , second.sub_func "SubFunction"
       , second.sub_func_descrip "SubFunction Descrip"
       , second.sub_sub_menu_id "Sub-SubMenu ID"
       , second.grant_flag "Grant Flag"
       , second.resp_end_date "Resp End Date"
       , DECODE( exc.rule_type
               , 'F', ( SELECT 'Ex F: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.function_id = exc.action_id ) )
            excluded_function
       , DECODE( exc.rule_type
               , 'F', ( SELECT 'Ex SF: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.sub_func_id = exc.action_id ) )
            excluded_sub_function
       , DECODE( exc.rule_type
               , 'M', ( SELECT 'Ex M: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.menu_id = exc.action_id ) )
            excluded_menu
       , DECODE( exc.rule_type
               , 'M', ( SELECT 'Ex SM: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.sub_menu_id = exc.action_id ) )
            excluded_sub_menu
       , DECODE( exc.rule_type
               , 'M', ( SELECT 'Ex SSM: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.sub_sub_menu_id = exc.action_id ) )
            excluded_sub_sub_menu
    FROM (  SELECT FIRST.application_id
                 , FIRST.application_name
                 , FIRST.responsibility_id
                 , FIRST.responsibility_name
                 , FIRST.end_date AS resp_end_date
                 , FIRST.menu_id
                 , FIRST.user_menu_name
                 , FIRST.entry_sequence
                 , FIRST.prompt
                 , FIRST.function_id
                 , ffft.user_function_name
                 , ffft.description AS func_descrip
                 , FIRST.sub_menu_id
                 , fmv2.user_menu_name AS sub_menu_name
                 , fme2.entry_sequence AS sub_seq
                 , fmet2.prompt AS sub_prompt
                 , fme2.function_id AS sub_func_id
                 , ffft2.user_function_name AS sub_func
                 , ffft2.description AS sub_func_descrip
                 , fme2.sub_menu_id AS sub_sub_menu_id
                 , FIRST.grant_flag
              FROM (  SELECT fat.application_id
                           , fat.application_name
                           , fr.responsibility_id
                           , frt.responsibility_name
                           , fr.end_date
                           , fr.menu_id
                           , fmv.user_menu_name
                           , fme.entry_sequence
                           , fmet.prompt
                           , fme.sub_menu_id
                           , fme.function_id
                           , fme.grant_flag
                        FROM apps.fnd_application_tl fat
                           , apps.fnd_responsibility fr
                           , apps.fnd_menus_vl fmv
                           , apps.fnd_responsibility_tl frt
                           , apps.fnd_menu_entries fme
                           , apps.fnd_menu_entries_tl fmet
                       --joins and constant selection
                       WHERE fat.application_id = fr.application_id(+)
                         AND fr.menu_id = fmv.menu_id(+)
                         AND fr.responsibility_id = frt.responsibility_id(+)
                         AND fr.menu_id = fme.menu_id(+)
                         AND fme.menu_id = fmet.menu_id(+)
                         AND fme.entry_sequence = fmet.entry_sequence(+)
                         AND fmet.language = 'US'
                         --------------------------------------
                         -- add specific selection criteria  --
                         --------------------------------------
                         --and   fat.application_id = 840
                         --for DEVL  19080 rows
                         --and fr.responsibility_id = 51856
                         AND fat.application_id = &appid
                    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST ---for application, responsibility and main menu info
                 , apps.fnd_menus_vl fmv2                                                                 ---for submenu info
                 , apps.fnd_menu_entries fme2
                 , apps.fnd_menu_entries_tl fmet2
                 , apps.fnd_form_functions_tl ffft                                                       ---for function info
                 , apps.fnd_form_functions_tl ffft2                                                   ---for subfunction info
             --left outer joins keep original records and add any sub menu and function info
             WHERE FIRST.function_id = ffft.function_id(+)
               AND FIRST.sub_menu_id = fmv2.menu_id(+)
               AND FIRST.sub_menu_id = fme2.menu_id(+)
               AND fme2.menu_id = fmet2.menu_id(+)
               AND fme2.entry_sequence = fmet2.entry_sequence(+)
               AND fme2.function_id = ffft2.function_id(+)
          ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21) second -- adds any sub menu and function info
         LEFT OUTER JOIN apps.fnd_resp_functions exc                                                        ---for exclusions
            ON ( second.application_id = exc.application_id
            AND second.responsibility_id = exc.responsibility_id
            AND ( second.function_id = exc.action_id
              OR second.sub_func_id = exc.action_id
              OR second.menu_id = exc.action_id
              OR second.sub_menu_id = exc.action_id
              OR second.sub_sub_menu_id = exc.action_id ) )
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21;

Script to Get the Nth Position String within a String

CREATE OR REPLACE PACKAGE xx_delimited_string
AS

-- p_str - the string to get the i'th word from
-- p_str can be xyz,yxz,zyx,zxy and you want to get the zxy as return ---- Value
-- p_n - the word to get from the string -- an index into the string
-- p_enclosed_by - what the words might be wrapped in.  In the above -- -- example, chr(34) is a double quote
-- p_terminated_by - what separates the words.  In the above example, ---- chr(44) is a comma.
  

  
   FUNCTION nth_word(
      p_str                      IN             VARCHAR2
    , p_n                        IN             VARCHAR2
    , p_enclosed_by              IN             VARCHAR2 DEFAULT ''''
    , p_separated_by             IN             VARCHAR2 DEFAULT ','
   )
      RETURN VARCHAR2;

   PRAGMA RESTRICT_REFERENCES(nth_word, WNDS, RNDS);
END xx_delimited_string;
/

CREATE OR REPLACE PACKAGE BODY xx_delimited_string
AS
   TYPE vcarray IS TABLE OF VARCHAR2(2000)
      INDEX BY BINARY_INTEGER;

   g_words                                           vcarray;
   g_empty                                           vcarray;
   g_last_string                                     VARCHAR2(4096);

   FUNCTION de_quote(
      p_str                      IN             VARCHAR2
    , p_enc_by                   IN             VARCHAR2
   )
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN REPLACE(LTRIM(RTRIM(p_str
                               , p_enc_by
                                )
                         , p_enc_by
                          )
                   , p_enc_by || p_enc_by
                   , p_enc_by
                    );
   END de_quote;

   PROCEDURE parse_string(
      p_str                      IN             VARCHAR2
    , p_delim                    IN             VARCHAR2
    , p_sep                      IN             VARCHAR2
   )
   IS
      l_n                                               NUMBER DEFAULT 1;
      l_in_quote                                        BOOLEAN DEFAULT FALSE;
      l_ch                                              CHAR(1);
      l_len                                             NUMBER DEFAULT NVL(LENGTH(p_str)
                                                                         , 0
                                                                          );
   BEGIN
      IF (l_len = 0)
      THEN
         RETURN;
      END IF;

      g_words                                        := g_empty;
      g_words(1)                                     := NULL;

      FOR i IN 1 .. l_len
      LOOP
         l_ch                                           := SUBSTR(p_str
                                                                , i
                                                                , 1
                                                                 );

         IF (l_ch = p_delim)
         THEN
            l_in_quote                                     := NOT l_in_quote;
         END IF;

         IF (    l_ch = p_sep
             AND NOT l_in_quote)
         THEN
            l_n                                            := l_n + 1;
            g_words(l_n)                                   := NULL;
         ELSE
            g_words(l_n)                                   := g_words(l_n) || l_ch;
         END IF;
      END LOOP;

      FOR i IN 1 .. l_n
      LOOP
         g_words(i)                                     := de_quote(g_words(i)
                                                                  , p_delim
                                                                   );
      END LOOP;
   END parse_string;

   FUNCTION nth_word(
      p_str                      IN             VARCHAR2
    , p_n                        IN             VARCHAR2
    , p_enclosed_by              IN             VARCHAR2 DEFAULT ''''
    , p_separated_by             IN             VARCHAR2 DEFAULT ','
   )
      RETURN VARCHAR2
   IS
   BEGIN
      IF (   g_last_string IS NULL
          OR p_str <> g_last_string)
      THEN
         g_last_string                                  := p_str;
         parse_string(p_str
                    , p_enclosed_by
                    , p_separated_by
                     );
      END IF;

      RETURN g_words(p_n);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
   END nth_word;
END xx_delimited_string;
/


How to Test 

SELECT xx_delimited_string.nth_word(:p_str
                         , 4
                         , CHR(34)
                         , CHR(44)
                          )

  FROM DUAL

Store timestamp with time zone in VARCHAR2 column and convert it into Date or timestamp Format

Format Timestamp with time zone ('YYYY-MM-DDTHH:MI:SS.FF+TZ')  if stored into VARCHAR2 column and needs to convert into 'YYYY-MM-DD HH:MI:SS' then use the Below select statement for conversion.


SELECT CAST (
          TO_TIMESTAMP_TZ ('2014-02-12T23:02:14.000+0000',
                           'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM')
             AT TIME ZONE DBTIMEZONE AS DATE)
  FROM DUAL

Way to search a String for LONG data type In oracle using Object types and Pipeline Functions

CREATE OR REPLACE TYPE XX_LONG_DATA_REC_TYPE AS OBJECT(OBJECT_NAME VARCHAR2(240), LONG_DATA_TEXT CLOB);
/

CREATE OR REPLACE TYPE XX_LONG_DATA_TBL AS TABLE OF XX_LONG_DATA_REC_TYPE
/

CREATE OR REPLACE FUNCTION XX_SEARCH_STRING( P_SEARCH_STRING VARCHAR2 )
   RETURN XX_LONG_DATA_TBL
   PIPELINED
IS
   XX_LONG_DATA_REC            XX_LONG_DATA_REC_TYPE;
BEGIN
   FOR Z IN ( SELECT VIEW_NAME, TEXT
                FROM DBA_VIEWS
               WHERE ROWNUM <=10 )
   LOOP
      IF ( UPPER(Z.TEXT) LIKE UPPER(P_SEARCH_STRING) )
      THEN
         XX_LONG_DATA_REC         := XX_LONG_DATA_REC_TYPE( Z.VIEW_NAME, Z.TEXT );
         PIPE ROW ( XX_LONG_DATA_REC );
      END IF;
   END LOOP;
END;
/

Script to test the same 

select * from table(xx_search_string('%SELECT%'));



Another way without creating any DB object

DECLARE

   L_CLOB_STRING                 CLOB;

   L_LONG_STRING                 LONG;

   L_OBJECT_NAME                 VARCHAR2( 240 );

BEGIN

   SELECT VIEW_NAME, TEXT

     INTO L_OBJECT_NAME, L_LONG_STRING

     FROM DBA_VIEWS

    WHERE ROWNUM = 1;

   L_CLOB_STRING              := L_LONG_STRING;

   IF UPPER( L_CLOB_STRING ) LIKE '%SELECT%'

   THEN

      DBMS_OUTPUT.PUT_LINE( 'OBJECT NAME : ' || L_OBJECT_NAME );

   ELSE

      DBMS_OUTPUT.PUT_LINE( 'SELECT SEARCH STRING NOT FOUND IN THE OBJECT NAME :  ' || L_OBJECT_NAME );

   END IF;

END;

Function to get the list of Sting with covered with Quotes for individual columns in the List.

CREATE OR REPLACE FUNCTION get_not_in_clause (
   p_list_of_columns                    IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   ---------------------------------------------------------------------------------
   -- Created By : Vijaykumar
   -- Creation Date :  15-Feb-2014
   -- purpose : purpose of this function is to split the string which is comma or any other
   --           separator and return the string with inverted quote string for each column in
   --          parameter list.
   --          ex :- 'CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY'
   --          the output of the this function will as below
   --          'CREATED_BY','CREATION_DATE','LAST_UPDATE_DATE','LAST_UPDATED_BY'
   -------------------------------------------------------------------------------
   l_count                                      NUMBER;
   l_string                                     VARCHAR2 (50);
   l_concatenated_string                        VARCHAR2 (2000);
BEGIN
   ------------------------------------------------------------------------
   --- below select statement is used to  Get the total count of Commas in
   --- the parameter string and adding 1 to consider the last column in the
   --- parameter list
   ------------------------------------------------------------------------
   BEGIN
      SELECT (LENGTH (REGEXP_REPLACE (p_list_of_columns, '[^,]')) / LENGTH (',')) + 1
        INTO l_count
        FROM DUAL;
   END;

   DBMS_OUTPUT.put_line ('Count Of Comma occurenece : ' || l_count);

   IF l_count IS NULL
   THEN
      l_concatenated_string                          := '''' || p_list_of_columns || '''';
   ELSE
      l_count                                        := l_count;
      DBMS_OUTPUT.put_line ('Count Of Comma occurenece 1: ' || l_count);

      FOR i IN 1 .. l_count
      LOOP
         DBMS_OUTPUT.put_line ('i : ' || i);

         SELECT REGEXP_SUBSTR (p_list_of_columns
                             , '[^,]+'
                             , 1
                             , i
                              )
           INTO l_string
           FROM DUAL;

         DBMS_OUTPUT.put_line ('String : ' || l_string);

         IF l_concatenated_string IS NULL
         THEN
            l_concatenated_string                          := '''' || l_string || '''';
         ELSE
            l_concatenated_string                          := l_concatenated_string || ',' || '''' || l_string || '''';
         END IF;
      END LOOP;
   END IF;

   RETURN l_concatenated_string;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('UNexpected Error : ' || SUBSTR (SQLERRM
                                                           , 1
                                                           , 250
                                                            ));
      RETURN NULL;
END;

Scrpt to get the Nth Occurrence of a string in the String

User below script to get the Nth occurrence of the string within a string separated by any string separator.

Ex.. i have a string like 'CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY' and within this string if i want to split the string for Individual column then use the below script.


SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

Please find below the use and output for the above select statement.

SQL> SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL;

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 1

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUB

----------

CREATED_BY

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 2

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR

-------------

CREATION_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 3

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('C

----------------

LAST_UPDATE_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 4

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('

---------------

LAST_UPDATED_BY

SQL> 

How to split comma separated string and pass to IN clause of select statement/ Create Comma Separated String using Select statement

SELECT   COLUMN_NAME
       , COLUMN_ID
    FROM DBA_TAB_COLUMNS
   WHERE TABLE_NAME = :P_TABLE_NAME
     AND OWNER = :P_OWNER
     AND COLUMN_NAME NOT IN (SELECT REGEXP_SUBSTR (:L_STRING
                                                     , '[^,]+'
                                                     , 1
                                                     , LEVEL
                                                      )
                                   FROM DUAL
                             CONNECT BY REGEXP_SUBSTR (:L_STRING
                                                     , '[^,]+'
                                                     , 1
                                                     , LEVEL
                                                      ) IS NOT NULL)
ORDER BY COLUMN_ID ASC

WHERE AS THE l_STRING CAN BE PASSED AS 'COLUMN1,COLUMN2,COLUMN3'

Use Below SQL to create comma separated string for multiple rows.
Limitations : The below SQL has String length Constraint. If the length of number of rows in a column getting converted into one row searating each other by using any separator, in that case the length should not exceed 2000 characters. 

SELECT SUBSTR(SYS_CONNECT_BY_PATH(''''||lookup_code||''''
                                    , ',')
                , 2)
              lookup_code
      FROM (SELECT lookup_code
                 , ROW_NUMBER() OVER (ORDER BY lookup_code) rownumber
                 , COUNT( *) OVER () cunt
              FROM fnd_lookup_values
             WHERE lookup_type = 'ITEM_TYPE'
               )
     WHERE rownumber = cunt
START WITH rownumber = 1

CONNECT BY rownumber = PRIOR rownumber + 1

How to Search a String for a LONG/CLOB datatype column

CREATE OR REPLACE FUNCTION apps.xx_search_long(
   p_obj_type                 IN             VARCHAR2
 , p_obj_name                 IN             VARCHAR2
 , p_owner                    IN             VARCHAR2
 , p_search_str               IN             VARCHAR2
)
   RETURN VARCHAR2
IS
   temporary_varchar                                 VARCHAR2(31000);
   v1                                                LONG;
   v_src_code                                        CLOB;
   v_length                                          NUMBER;
BEGIN
   BEGIN
      SELECT DBMS_METADATA.get_ddl(p_obj_type
                                 , p_obj_name
                                 , p_owner
                                  )
        INTO v_src_code
        FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line('Exception : ' || SUBSTR(SQLERRM
                                                     , 1
                                                     , 150
                                                      ));
   END;
   v_length                                       := DBMS_LOB.getlength(v_src_code);
   DBMS_OUTPUT.put_line('Length of CLOB - ' || v_length);
   IF DBMS_LOB.INSTR(v_src_code
                   , p_search_str
                    ) > 0
   THEN
      DBMS_OUTPUT.put_line('Found ....');
      RETURN('FOUND');
   ELSE
      DBMS_OUTPUT.put_line('Not Found ....');
      RETURN('NOT FOUND');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN('NOT_FOUND');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line('Error .' || SQLERRM);
      RETURN('EXCEPTION' || SUBSTR(SQLERRM
                                 , 1
                                 , 150
                                  ));
END;
/