Advertisements
RSS

Tag Archives: SQL

Delete all artifacts in an Oracle database schema

In our DEV and TST environment we sometimes want to perform a fresh deployment of a database release. Since we got our schemas from the DBA team, but can’t drop/create them ourselves, we found the script below to remove all artifacts from the schema. Packages, tables, sequences, etc. nothing is safe from this armageddon script.

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;

elsif c.object_type = 'TYPE' then
stringa := 'drop type ' || c.object_name;
EXECUTE immediate stringa;

end if;

 exception
when others then
null;
end;
end loop;
-- PURGE recyclebin
end;

Way to valuable not to store, so here it is.
All credits go to Massimo @ Oracle OTN Community for his code.

Advertisements
 
Leave a comment

Posted by on 29-08-2013 in Database, Oracle

 

Tags: ,

Transformation on XMLType in Oracle XML DB

When using a XMLType table/column in Oracle XML DB to store structured data you are able to perform transformations with the XMLTRANSFORM command. We have a requirement to remove all namespaces for the inserted XML content and decided to implement this mechanism in the database.

Probably piece of cake for a Oracle XML DB guru, I needed Google ;-)

CREATE TABLE XMLSTYLESHEETS
(
CODE VARCHAR2(6 CHAR) NOT NULL,
STYLESHEET "XMLTYPE" NOT NULL,
);

We insert the XSLT stylesheet for removing namespaces in the table:

INSERT INTO XMLSTYLESHEETS (CODE, STYLESHEET)
values ('REMNAM' ,'
<xsl:stylesheet version="1.0" xmlns:xsl=http://www.w3.org/1999/XSL/Transform>
<xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@* | node()"/>
</xsl:element>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{local-name()}">
<xsl:value-of select="."/>
</xsl:attribute>
</xsl:template>
<xsl:template match="comment() | text() | processing-instruction()">
<xsl:copy></xsl:copy>
</xsl:template>
</xsl:stylesheet>');

Then we create a trigger to

create or replace
trigger MYTRIGGER
BEFORE INSERT OR UPDATE ON MYTABLEWITHXML
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
-- remove namespaces from XMLTYPE input
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
SELECT XMLTRANSFORM(:new.data_xml, x.stylesheet)
INTO :new.data_xml
FROM XMLSTYLESHEETS x
WHERE x.CODE = 'REMNAM';
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
END;

Let’s test the trigger:

Insert into MYTABLEWITHXML(MYDATAXML)
values ('
<bk:book xmlns:bk="urn:loc.gov:books" xmlns:isbn="urn:ISBN:0-395-36341-6">
<bk:title>Cheaper by the Dozen</bk:title>
<isbn:number>1568491379</isbn:number>
</bk:book>');

When enabling the DBMS_OUTPUT lines we are able to validate the result of the trigger.
JDeveloper DBMS output

 
Leave a comment

Posted by on 13-08-2013 in Oracle, XMLDB, XSLT

 

Tags: , , , ,

JDeveloper and the lost trigger when clause

Once upon a time when creating a lot of tables, types and triggers we ran into a weird “thingie” with Oracle JDeveloper PS6 where the WHEN statement of the trigger “disappered” in the code view.

The script (for one of our many, many, many triggers):

</pre>
--------------------------------------------------------
 -- DDL for Trigger ZMS_DMT_BRI
 --------------------------------------------------------

CREATE OR REPLACE TRIGGER "ZMS"."ZMS_DMT_BRI" BEFORE INSERT ON ZMS.ZMS_DOCUMENTEN
 REFERENCING OLD AS old NEW AS new
 FOR EACH ROW
 WHEN (new.ID IS NULL) BEGIN
 SELECT ZMS.ZMS_DMT_ID_SEQ.NEXTVAL
 INTO :new.ID
 FROM dual;
 END;
 /
 ALTER TRIGGER "ZMS"."ZMS_DMT_BRI" ENABLE;
<pre>

After compilation and testing our project we discovered the trigger was not working correctly. So we checked out our code and discovered this:

Result

The WHEN statements were suddenly gone from all our triggers. However when inspecting the triggers closely with the EDIT view we could see they were still there:

trigger2

However 1 trigger was (probably) accidentally recompiled from the source view, and JDeveloper then makes the WHEN statement disappear. By manually returning the WHEN clause (in EDIT view or in code and recompile)  the issue was fixed.

Still it’s “strange” in our opinion that the statement is not viewable from normal source code view of the artefact. We are not sure if this is a bug, a hidden feature, or we expecting something stupid ;-)

 
Leave a comment

Posted by on 24-06-2013 in JDeverloper, Oracle

 

Tags: ,