Bookshelf Home | Contents | Index | Search | PDF

Siebel Data Warehouse Installation and Administration Guide > DAC Functional Reference > About the DAC Design View >

Using SQL Files as an Execution Type in the DAC

---

There are two types of custom SQL files that can be executed through the DAC: XML formatted .xml files, and plain text .sql files. For examples of XML and SQL files, go to the DAC\CustomSQL folder.

XML Formatted Files

An XML file consists of a set of SQL statements for which the name, type, and Continue on Fail option are defined using XML attributes. The set of SQL statements is in a CDATA section which allows for special characters (like <, >, \) to be used without breaking the XML structure. A CDATA section looks like the following:

<![CDATA[this is a place for a SQL statement]]>

An example of an XML file follows:

<?xml version="1.0"?>

<CustomSQLs>

<sql name="Create Temp" type="SQL" continueOnFail="true">

<![CDATA[CREATE TABLE w_etl_temp (row_wid varchar(50))]]>

</sql>

<!-- This is how a comment section looks in XML -->

<!-- It will be ignored-->

<sql name="Update Temp" type="SQL">

<![CDATA[UPDATE w_etl_temp SET row_wid = 'qwe' WHERE row_wid LIKE '5*']]>

</sql>

<sql name="Drop Temp" type="SQL">

<![CDATA[DROP TABLE w_etl_temp]]>

</sql>

</CustomSQLs>

This example consists of three SQL statements: Create Temp, Update Temp, and Drop Temp. These names will be used in DAC run task detail description to give details of the errors encountered during execution (if any). In this particular example all three statements are of type SQL. Regular update SQL statements and the Stored Procedure type can also be used.

If the Continue on Fail option is set to True for a given statement, the execution will continue even if the task fails. If the Continue on Fail option is set to False for a given statement, or is omitted, the subsequent statements will not be executed and the Task Run status will be Failed.

Plain Text SQL Files

Plain text SQL files consist of a set of SQL statements (no stored procedure calls). The SQL statements are separated by a semicolon (;), and comment tags are allowed (//, /* comment */, --). If any of the SQL statements fail, the Task Run status will be Failed.

An example of a plain text SQL file follows:

CREATE TABLE w_etl_temp (name varchar(50))

;

UPDATE w_etl_temp

SET name = 'that''s right' //this line demonstrates the use of ' in a text area

WHERE name LIKE 'gone fishing%';

/*

*some

*query

*statement

*/

SELECT * FROM w_etl_temp

;

DROP TABLE w_etl_temp

;

/*end of file*/

Bookshelf Home | Contents | Index | Search | PDF