Recompiling Invalid/All schema objects in the database

-
Identifying Invalid Objects: Use all_objects table to identity invalid schema objects. Oracle has given the default pkg to recompiling invalid/all schema objects in the database.

 SELECT owner,
       object_type,
       object_name,
       status
  FROM all_objects
 WHERE status = 'INVALID'
 ORDER BY owner,
          object_type,
          object_name;

 Custom Script: When you have to compile "N" no of invalid objects in one go, the best approach is to write custom script as below,

 BEGIN
  FOR cur IN(SELECT object_name,
                    object_type,
                    owner
               FROM sys.all_objects
              WHERE object_type   = 'MY_OBJECTS'
                AND owner         = 'MY_SCHEMA'
                AND status        = 'INVALID' -- For all objects, it should be excluded
             )
  LOOP
    BEGIN
      IF cur.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'alter ' || cur.object_type || ' "' || cur.owner || '"."' || cur.object_name || '" compile body';
      ELSE
        EXECUTE IMMEDIATE 'alter ' || cur.object_type || ' "' || cur.owner || '"."' || cur.object_name || '" compile';
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
    END;
  END LOOP;

END;

Manual Execution: Manual execution is sufficient, when you have to compile small no of objects.
  • ALTER PROCEDURE <<my_procedure>> COMPILE;
  • ALTER FUNCTION <<my_function>> COMPILE;
  • ALTER PACKAGE <<my_package>> COMPILE;
  • ALTER PACKAGE <<my_package>> COMPILE BODY;
  • ALTER VIEW <<my_view>> COMPILE;
  • ALTER TRIGGER <<my_trigger>> COMPILE;
DBMS_DDL.alter_compile: An alternative approach is to use the DBMS_DDL pkg,
  • exec dbms_ddl.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
  • exec dbms_ddl.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
  • exec dbms_ddl.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
  • exec dbms_ddl.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
  • exec dbms_ddl.alter_compile('VIEW', 'MY_SCHEMA', 'MY_VIEW');
  • exec dbms_ddl.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
DBMS_UTILITY.compile_schema: The DBMS_UTILITY.compile_schema procedure used to compiles all procedures, functions, packages, views, and triggers in the specified schema.

exec dbms_utility.compile_schema(SCHEMA => 'FXO', compile_all => FALSE);


Comments

Post a Comment

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

-
Image
Oracle APEX Interactive Report has default search option, where we can search the values which are exists in the report. Here I am giving an elegant method to highlight the cell of interactive report based on search criteria. Step 1:  Create a new blank page. Step 2:  Create a new Interactive Report region to the page. Step 3:  Set  CSS Classes  "data-highlighter" to the region. Fig 1: Set CSS Classes Step 4: Create a class by adding below CSS to the page in inline CSS section. It will change the style of report cells. Go to   Page => CSS => Inline . highlight- data  {     font-weight: bold !important ;      background -color: #ffecb4 !important ; } Step 5: Get a class for IR search component by following below steps. Place a mouse cursor on IR search component. Right click a mouse (The right mouse button is often used to open contextual menus, which are pop-up menus that change depending where you click) and select the menu " Inspect " . DevTools pop-up come
Read more

Querying Data From Flat Files in Oracle

-
Image
Couple of days before i had an interesting discussion with my client related to  Oracle external tables , Client: Is there any way to read the data from flat file(s) which are stored outside the database? Raja: Could you please explain in more detail? Client: Sure. We have data analytics team, what they will do is, they will export the file(s) (.csv/.txt) from their system and push it to our file system ( Oracle directory ).  They have a job to push their files to our file system, which will run in predefined interval. Once it's uploaded we should read the file(s) data and display it in our system. Raja: Yes! it's very much possible, if we go with O racle external table . Client: What is that? Raja:  It can be used for query, join and sort operations. External tables allow oracle to query data that is stored outside the database in flat files. Once it's running we can create views against it. Client: Oh! Sounds interesting. That's what i needed. Is there any limit
Read more

Number of Tables by The Number of Rows in Oracle Database

-
Image
Here I am giving an example to find out number of tables by the number of rows in oracle database. Query 1: Number of all tables accessible to the current user in Oracle database by the number of rows grouped into predefined intervals . SELECT  row_interval ,         count ( * )   AS  no_of_tables    FROM   ( SELECT   owner ,                table_name ,                num_rows ,                 CASE                   WHEN  num_rows >  1000000000                   THEN   '1b rows and more'                   WHEN  num_rows >  1000000                   THEN   '1m - 1b rows'                   WHEN  num_rows >  1000                   THEN   '1k - 1m rows'                   WHEN  num_rows >  100                   THEN   '100 - 1k rows'                   WHEN  num_rows >  10                   THEN   '10 - 100 rows'                   ELSE   '0 - 10 rows'                 END   AS  row_interval            FROM  sys . all_tables
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

-
Image
Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename
Read more

Highlight Data That Meet Certain Criteria in Oracle APEX Report

-
Image
  Here I am giving an example to highlight data when it meets certain criteria in oracle apex report. Step 1:  Create a new blank page. Step 2: Function Call:  Create a function  get_data_highlighter.  If you want to render it in multiple places, it would be good practice to put this code in a package and call it in your query. Sample function in a package called  FXGN_GENERAL create   or   replace   FUNCTION  get_data_highlighter (       p_value  IN   NUMBER )      RETURN   VARCHAR2    IS     l_return            VARCHAR2   ( 4000 );    BEGIN      IF  p_value    >=  95   THEN   -- Very Strong       l_return               :=  '<span class="t-Badge t-Badge--basic t-Badge--small is-success w100p">Very Strong</span>' ;      elsif  p_value >=  66   THEN   -- Strong       l_return               :=  '<span class="t-Badge t-Badge--basic t-Badge--small is-success w100p">Strong</span>' ;      elsif  p_value >=  33   THEN   -- M
Read more

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

-
Image
Here I am giving an example to Return a custom validation message using JavaScript if the Oracle Application Express Item is Empty. Step 1:  Create a new blank page. Note:  Mine was page 15. You will need to update reference to "P15" with your page number if it's different. Step 2: Create a new HTML region to the page. Step 3:  Create a new item to the region. Set name to P15_ATTRIBUTE_1 and leave the default of Text Field Step 4:  Create a new item to the region. Set name to  P15_ATTRIBUTE_2  and leave the default of  Text Field Step 5:  Create a new button to the region. Set name to  SUBMIT  and action to Defined Dynamic Action Step 6:  Create a new dynamic action that fires when the button is clicked. The easiest way to do this is to right click the button and select  Create Dynamic Action  Set name to  Not Null Check Validation Step 7:  Set action to Execute JavaScript Code and copy and paste the following code into the JavaScript Code section. var attribute1 = $(&
Read more

Remove X (Close Mark) from Oracle APEX Modal Dialog

-
Image
  Here I am giving an example to remove X (Close Mark) from the Oracle APEX modal dialog. Step 1:  Get a class for X (Close Mark) by following below steps. Place a mouse cursor on Close Mark (X). Right click a mouse (The right mouse button is often used to open contextual menus, which are pop-up menus that change depending where you click) and select the menu "Inspect" . DevTools pop-up comes up when we click on Inspect menu, from there we can get a class (JQuery Selector) for the particular components. Step 2:  Copy and paste below code in page JavaScript (Execute when Page Loads) var button = parent.$('. ui-dialog-titlebar-close ');  button.hide();  Output: That's it. The close mark (X) has been removed. Happy APEXing!!!... Reference: Oracle APEX hide the x on the modal dialog
Read more

Display User Rating Icons Dynamically in Oracle APEX Report

-
Image
Here I am giving an example to display user rating icons dynamically in oracle apex report. Step 1:  Create a new blank page. Step 2: Function Call:  Create a function  get_user_ratings.  If you want to render it in multiple places, it would be good practice to put this code in a package and call it in your query. Sample function in a package called  FXGN_GENERAL create   or   replace   FUNCTION  get_user_ratings ( p_value  IN   NUMBER )      RETURN   VARCHAR2    IS     l_return      VARCHAR2   ( 4000 );     l_checked     VARCHAR2 ( 240 )  :=  '<span class="fa fa-star" style="color:orange;"></span>' ;     l_un_checked  VARCHAR2 ( 240 )  :=  '<span class="fa fa-star" style="color:grey;"></span>' ;     l_max_value   NUMBER         :=  5 ;     l_remaining   NUMBER ;    BEGIN      IF  p_value  BETWEEN  1  AND   5   THEN   -- Check given values are in the range       l_remaining := l_max_value - p_value ;       
Read more

Printing Page Numbers in RTF Template [Oracle BI Publisher]

-
Image
Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
Read more

Find Where Specific Table or View is Used in Oracle Database

-
Image
Here I am giving an example to find where specific table or view is used in oracle database Query 1: All objects where specific table or view accessible to the current user is used in Oracle database. SELECT  referenced_owner        ||  '.'        || referenced_name  AS  table_name ,        referenced_type     AS   TYPE ,         owner        ||  '.'        ||  NAME   AS  referencing_object ,         TYPE      AS  referencing_type    FROM  sys . all_dependencies   WHERE  referenced_type  IN   ( 'TABLE' ,   'VIEW' )     AND  referenced_name    =  'FXGN_USERS'   -- put your table/view name here     --AND referenced_owner = 'SCHEMA_NAME'     AND   owner   NOT   IN   ( 'ANONYMOUS' , 'CTXSYS' , 'DBSNMP' , 'EXFSYS' ,                         'MDSYS' ,   'MGMT_VIEW' , 'OLAPSYS' , 'OWBSYS' ,                       'ORDPLUGINS' ,   'ORDSYS' ,   'SI_INFORMTN_SCHEMA&#
Read more