Page History


Nigel Stanger edited this page on 9 Sep 2020

Clone this wiki locally

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

…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 (

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