Search Siebel Repository Scripts Using a PL/SQL Query

Fast Keyword Search in Siebel Scripts with PL/SQL

Siebel developers frequently need to locate specific keywords within scripts, such as Server Scripts, Browser Scripts, or Business Service scripts. Typically, you would use Siebel Tools to perform this search, but it can be slow and cumbersome. Thankfully, with a PL/SQL query, you can directly search for any keyword within Siebel scripts without ever opening Siebel Tools.

This blog post will walk you through a PL/SQL query that is designed specifically to search within Siebel Scripts. It offers a faster, more efficient way to get a list of all objects where the keyword appears, streamlining your development and troubleshooting process.

Note: A separate blog post will cover how to search keywords in workflows using PL/SQL.

Why Use PL/SQL to Search Siebel Scripts?

• Speed: Database queries execute faster than the Siebel Tools interface, reducing time spent searching for keywords in scripts.
• Convenience: PL/SQL queries can be run directly from your preferred SQL tool, bypassing the need for Siebel Tools.
• Detailed Results: The query will give a comprehensive list of script objects where the keyword is found, across all script types (server scripts, browser scripts, business service scripts, etc.).

The PL/SQL Query for Searching Siebel Scripts:

The following query searches for the specified keyword across various script objects in the Siebel repository. It looks in tables that store script data, such as s_buscomp_script and s_applet_script. Modify the keyword as per your search requirements.

declare
    string_to_be_searched_for varchar2 (255) default 'Your Keyword';      -- here include the string you are searching for
    loopnumber     integer;

    cursor cur_loop
    is
        select 'Business Component script' ObjType,
               bc.name businesscomponent,
               sc.name scriptname,
               sc.script
          from siebel.s_buscomp_script sc, siebel.s_buscomp bc, siebel.s_repository r
         where     sc.buscomp_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Applet script' ObjType,
               bc.name businesscomponent,
               sc.name scriptname,
               sc.script
          from siebel.s_applet_script sc, siebel.s_applet bc, siebel.s_repository r
         where     sc.applet_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Application script' ObjType,
               bc.name businesscomponent,
               sc.name scriptname,
               sc.script
          from siebel.s_appl_script sc, siebel.s_application bc, siebel.s_repository r
         where     sc.application_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Applet Browser script' ObjType,
               bc.name businesscomponent,
               sc.name || '.' || sc.procedure_name scriptname,
               sc.script
          from siebel.s_aplt_brsscrpt sc, siebel.s_applet bc, siebel.s_repository r
         where     sc.applet_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Application Browser script' ObjType,
               bc.name businesscomponent,
               sc.name || '.' || sc.procedure_name scriptname,
               sc.script
          from siebel.s_appl_brsscrpt sc, siebel.s_application bc, siebel.s_repository r
         where     sc.application_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Applet Web script' ObjType,
               bc.name businesscomponent,
               sc.name scriptname,
               sc.script
          from siebel.s_appl_webscrpt sc, siebel.s_applet bc, siebel.s_repository r
         where     sc.applet_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Business Component Browser script' ObjType,
               bc.name businesscomponent,
               sc.name || '.' || sc.procedure_name scriptname,
               sc.script
          from siebel.s_bc_brs_scrpt sc, siebel.s_buscomp bc, siebel.s_repository r
         where     sc.buscomp_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Class script' ObjType,
               bc.name businesscomponent,
               sc.name || '.' || sc.procedure_name scriptname,
               sc.script
          from siebel.s_class_scrpt sc, siebel.s_class bc, siebel.s_repository r
         where     sc.class_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Business Service script' ObjType,
               bc.name businesscomponent,
               sc.name scriptname,
               sc.script
          from siebel.s_service_scrpt sc, siebel.s_service bc, siebel.s_repository r
         where     sc.service_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository'
        union all
        select 'Business Service Browser script' ObjType,
               bc.name businesscomponent,
               sc.name || '.' || sc.procedure_name scriptname,
               sc.script
          from siebel.s_svc_brs_scrpt sc, siebel.s_service bc, siebel.s_repository r
         where     sc.service_id = bc.row_id
               and r.row_id = bc.repository_id
               and r.name = 'Siebel Repository';

    posizione      integer (10);
begin
    loopnumber  := 0;

    for crs_loop in cur_loop
    loop
        posizione   := instr (crs_loop.script, string_to_be_searched_for);
        loopnumber  := loopnumber + 1;

        if posizione > 0
        then
            dbms_output.put_line (substr (   crs_loop.ObjType
                                          || ';'
                                          || crs_loop.businesscomponent
                                          || ';'
                                          || crs_loop.scriptname
                                          || ';'
                                          || posizione, 1, 255));
        end if;
    end loop;

    dbms_output.put_line (substr (to_char (loopnumber) || ' loops', 1, 255));
end;

How the Query Works:

• Keyword Input: You can specify the keyword you want to search by updating the ‘Your Keyword’ placeholder.
• Tables Involved: The query searches the s_buscomp_script , s_applet_script, s_appl_script, s_aplt_brsscrpt,s_appl_brsscrpt, s_appl_webscrpt,s_bc_brs_scrpt tables, which store script data for Business Components, Applets, Business Services, and Browser Scripts.
• Union Query: The UNION clause combines the results from different script types, providing a consolidated output.
• Results: The output lists the object type (e.g., Business Component Script, Applet Script, Business Service Script), object name, and script name where the keyword is found.

Key Benefits Over Using Siebel Tools:

• Faster Performance: Running a PL/SQL query directly on the database yields results faster, especially when dealing with a large number of scripts.
• Single Interface: By using a SQL client, you can stay in one environment instead of toggling between multiple tools.
• Customizable: The query can be expanded to include additional objects, or adjusted to search for specific script types based on your needs.

This PL/SQL query provides a faster, more efficient way to search for keywords within Siebel Scripts, reducing the need to rely on Siebel Tools. By running this query directly in your SQL environment, you can quickly locate where the keyword is used across Business Component, Applet, Business Service, and Browser Scripts.

Stay tuned for the next blog post where I will share a PL/SQL query to search keywords in Siebel Workflows.

---

Call to Action:

If you found this query helpful, share it with your fellow Siebel developers. For more tips and tricks on using SQL with Siebel, subscribe to our blog and get notified about upcoming posts.

---

This format not only helps streamline the process for Siebel developers but also makes the content easy to understand and apply.