Drop all user objects in schema

SQL*Plus script:

set head off
set pagesize 0
set verify off
set feedback off

select 'drop ' || Object_Type || ' ' || Object_Name ||
       decode(Object_Type, 'TABLE', ' CASCADE CONSTRAINTS;' , ';')
from User_Objects;

purge recyclebin;

spool drop_objects.sql
/
spool off
@drop_objects.sql

…or just run the select in the middle to generate the drop statements, run those, then purge recyclebin.

Extract query result as XML

This example is for the play script database that Chris devised. Theoretically it should work in any DBMS that supports the SQL/XML spec (https://en.wikipedia.org/wiki/SQL/XML).

select
    xmlelement(
        "Script",  
        xmlelement("name", Scr.Name),  
        xmlelement("author", Scr.Author),  
        xmlelement("copyright_date", Scr.Copyright_Date),  
        (   select xmlagg(
                xmlelement(
                    "Act",
                    xmlelement("number", A.Act),  
                    (   select xmlagg(
                            xmlelement(
                                "Scene",  
                               xmlattributes(S.Scene AS "number")  
                            )
                            order by S.Scene
                        )
                        from Scene S  
                        where
                            S.Script = Scr.Name and
                            S.Act = A.Act
                    )
                )
            )  
            from Act A  
            where A.Script = Scr.Name
        )  
    ).getstringval()  
from Script Scr;