About Oracle and Oracle ā'pěks (#orclapex)

   What is Oracle ACE Program? The  Oracle ACE Program  is designed to recognize and reward members of the Oracle Technology and Applications communities for their contributions. The Oracle ACE Program award is given for a  one year term  and past members are  Oracle ACE Alumni . I am happy to announce that I became an Oracle ACE Associate  in September 2020. Thanks Oracle  and Oracle ACE Program to honored me as an Oracle ACE Program Member and for this wonderful award. Hard work Pays off!!!.  I have received the Oracle ACE Associate Award (based on my significant contributions and activity in the oracle technical community) and my profile has been added in the Oracle ACE Directory . What else i need more? An award is a morale booster, An award gives you that high no drug in this world can give, today I am totally on a new high, My Oracle Ground Breakers award has been approved and I was on Cloud, but moments I realized that there is ...

  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     ...

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' ...

  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' ,                  ...

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'   --...

 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 ...

  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           ...