Make Custom User Authorization Schemes. Oracle Apex. Part-1

How To Use DBMS_METADATA To Get DDL For Objects

 

The DBMS_METADATA package is a powerful tool for obtaining a complete definition of a schema object. It enables you to get all the qualities of an object in one pass. The object is described as DDL which can be used to create (re). The GET_DDL function is used to fetch DDL for all tables in the current schema, filtering nested tables and overflow segments. SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM equals handle value which means "for current session") is used to specify that storage clauses will not be returned to SQL DDL. Next, the session-level transform parameters are reset to their defaults. Once set, the transform parameter values ​​remain valid until they are specifically reset to the default.

Get DDL for all tables:- 

SELECT DBMS_METADATA.GET_DDL ('TABLE', table_name) 
    FROM USER_TABLES;

Get DDL for all Index- 

SELECT DBMS_METADATA.GET_DDL('INDEX', index_name)
      FROM USER_INDEXES;

Get DDL for All View 

SELECT DBMS_METADATA.get_ddl ('VIEW', view_name) 
         FROM all_views;

Get DDL for All Tablespace 

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
  FROM dba_tablespaces

Get DDL FOR All Constraints 

SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner)
  FROM all_constraints

Get DDL FOR All Sequences 

SELECT DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner)
  FROM all_sequences

Get DDL FOR All DB Links 

SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link, owner) 
    FROM dba_db_links

Get DDL FOR All SYSTEM_GRANT 

SELECT DBMS_METADATA.get_granted_ddl ('SYSTEM_GRANT', '[Owner_Schema]')
  FROM DUAL

How to Delete All Tables, Packages, Functions Using an SQL Script Oracle Database?

         Most Of The Time We Need To Delete All The Tables, Packages, Functions Of Oracle Database. Using The Following SQL Code You Can Delete All The Tables, Packages, Functions Of The Database Using A Script. If There Is Any Problem In Using The Code, You Must Comment Or Message, I Will Be With Your Overall Cooperation Inshallah.

DECLARE
    stringa   VARCHAR2 (100);
    CURSOR cur IS SELECT * FROM user_objects;
BEGIN
    FOR c IN cur
    LOOP
        BEGIN
            stringa := '';
            IF c.object_type = 'VIEW'
            THEN
                stringa := 'drop view ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'TABLE'
            THEN
                stringa :=
                    'drop table ' || c.object_name || ' cascade constraints';
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'SEQUENCE'
            THEN
                stringa := 'drop sequence ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'PACKAGE'
            THEN
                stringa := 'drop package ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'TRIGGER'
            THEN
                stringa := 'drop trigger ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'PROCEDURE'
            THEN
                stringa := 'drop procedure ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'FUNCTION'
            THEN
                stringa := 'drop function ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'SYNONYM'
            THEN
                stringa := 'drop synonym ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'INDEX'
            THEN
                stringa := 'drop index ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'PACKAGE BODY'
            THEN
                stringa := 'drop PACKAGE BODY ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'DATABASE LINK'
            THEN
                stringa := 'drop database link ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            END IF;
        EXCEPTION
            WHEN OTHERS
            THEN
                NULL;
        END;
    END LOOP;
END;

 

🔗 Demo Application-
            URL- Demo Application
            Username - demo, Pass- demo
 

I hope everyone will like it. Please watch the full video,
Comment on any of your problems, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable. Visit my blog site, new technology related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
Please stay tuned by subscribing to the YouTube channel, and encourages new videos to be uploaded.
=================
Visit my site to get more collaborative posts about Oracle Apex and subscribe to my YouTube channel. Thanks.
Comment on any of your issues, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable.
Visit my blog site, new technology-related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
==============================

🙍🏾‍ Md jABER HOSSEN
📲 Mobile- +8801760688286
📨 Email- jaberit786@gmail.com
🌐 FB- facebook.com/mdjaber.hossen1
Please Subscribe to My Channel

Many thanks for visiting the site.

Then Enjoy.........................