Posts

Showing posts from October, 2020

[Loner Tables] Find Tables Without Relationships in Oracle Database

Image
  Here I am giving an example to find out the tables without having relationships in oracle database. Tables that are not referencing and are not referenced by other tables. Something we called "Loner Tables" . Query 1: All tables accessible to the current user in Oracle database that don't have foreign keys and are not referenced by other tables with foreign keys. SELECT  t . owner   AS  schema_name ,        t . table_name ,         '>- No FKs'  fks    FROM  sys . all_tables t    LEFT   JOIN   ( SELECT   DISTINCT   owner   AS  schema_name ,                     table_name                 FROM  sys . all_constraints                WHERE  constraint_type =  'R'              )  fks      ON  t . owner       = fks . schema_name     AND  t . table_name = fks . table_name    LEFT   JOIN   ( SELECT   DISTINCT  cons . r_owner  AS  schema_name ,                     ref_cons . table_name    AS  table_name                 FROM  sys . all_constraints cons          

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

Find Recently Created Tables in Oracle Database

Image
  Here I am giving an example to fetch recently created tables in oracle database. Query 1: All tables accessible to the current user in Oracle database that were created within the last 30 days. SELECT   owner    AS  schema_name ,        object_name  AS  table_name ,        created    FROM  sys . all_objects   WHERE  object_type =  'TABLE'    -- excluding some Oracle maintained schemas     AND   owner   NOT   IN   ( 'ANONYMOUS' , 'CTXSYS' , 'DBSNMP' , 'EXFSYS' ,   'LBACSYS' ,                         'MDSYS' ,   'MGMT_VIEW' , 'OLAPSYS' , 'OWBSYS' , 'ORDPLUGINS' ,                         'ORDSYS' , 'OUTLN' ,   'SI_INFORMTN_SCHEMA' , 'SYS' , 'SYSMAN' ,                       'SYSTEM' ,   'TSMSYS' , 'WK_TEST' , 'WKSYS' ,   'WKPROXY' , 'WMSYS' ,                       'XDB' , 'APEX_040000' ,   'APE

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&#

Wrapping PL/SQL Code with DBMS_DDL Subprogram

Image
 Here I am giving an example to wrap oracle plsql code with dbms_ddl subprogram. Wrapping is the process of hiding PL/SQL source code. Wrapping helps to protect your source code by making it more difficult for others to view it. The DBMS_DDL package contains procedures for wrapping a single PL/SQL unit, such as a package specification, package body, function, procedure, type specification, or type body.  These overloaded subprograms provide a mechanism for wrapping dynamically generated PL/SQL units that are created in a database. Oracle has given few Guidelines for Wrapping as below: Wrap only the body of a package or object type, not the specification. This allows other developers to see the information they must use the package or type, but prevents them from seeing its implementation. Wrap code only after you have finished editing it. You cannot edit PL/SQL source code inside wrapped files. Either wrap your code after it is ready to ship to users or include the wrapping operation

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

Bootstrap Progress Bar in Oracle APEX Report

Image
  Here I am giving an example to display bootstrap progress bar in oracle apex report. Step 1:  Create a new blank page. Step 2:  Add CSS to the page in inline CSS section. It will make the magic happen. .progress-bar   {      display:   -ms-flexbox ;      display:   flex ;      -ms-flex-direction:   column ;      flex-direction:   column ;      -ms-flex-pack:   center ;      justify-content:   center ;      color:  #fff ;      text-align:   center ;      white-space:   nowrap ;      background-color:  #007bff ;      transition:   width   .6s   ease ; } .progress   {      display:   -ms-flexbox ;      display:   flex ;      height:   1rem ;      overflow:   hidden ;      font-size:   .75rem ;      background-color:  #cfdae4 ;      border-radius:   .25rem ; } Step 3: Function Call:  Create a function  get_progress_bar.  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