DD - xml to db (oracle)

Hi,

Hopefully an easy one...

I'm trying to push an xml file into an Oracle table.

The xml file has been pulled from another Oracle database using DD (db-to-xml).

What I'd like to do now is push that xml file back into an empty table. Is this possible?

I've set up the dd.cfg file like this:

<deployment name="xml-to-dbdoc">
<source>
<xml-formatted-data
file="$infile"
area="$area"
area-type="os-filesystem"
xml-type="interwoven"
options="full,wide">
<fields>
<field name="ID" element="ID" />
<field name="DOCUMENT_SIZE" element="DOCUMENT_SIZE" />
<field name="VERSION" element="VERSION" />
<field name="AUTHOR" element="AUTHOR" />
<field name="CREATION_DATE" element="CREATION_DATE" />
<field name="LOCKED_BY" element="LOCKED_BY" />
<field name="MODIFIED_DATE" element="MODIFIED_DATE" />
<field name="MODIFIED_BY" element="MODIFIED_BY" />
<field name="DESCRIPTION" element="DESCRIPTION" />
<field name="COMMENTS" element="COMMENTS" />
<field name="MIME_TYPE" element="MIME_TYPE" />
</fields>
</xml-formatted-data>
</source>
<destinations>
<database
db="$dbase2"
user="$dbuser2"
password="$dbpass2"
table="DOCUMENT_METADATA"
vendor="$db2">
<select>
<column name="ID" value-from-field="ID" data-type="VARCHAR2(254)" allows-null="no" />
</select>
<update>
<column name="DOCUMENT_SIZE" value-from-field="DOCUMENT_SIZE" data-type="NUMBER(15)" allows-null="no" />
<column name="VERSION" value-from-field="VERSION" data-type="NUMBER(15)" allows-null="no" />
<column name="AUTHOR" value-from-field="AUTHOR" data-type="VARCHAR2(50)" allows-null="yes" />
<column name="CREATION_DATE" value-from-field="CREATION_DATE" data-type="DATE" data-format="yyyy-MM-dd" />
<column name="LOCKED_BY" value-from-field="LOCKED_BY" data-type="VARCHAR2(50)" allows-null="yes" />
<column name="MODIFIED_DATE" value-from-field="MODIFIED_DATE" data-type="DATE" data-format="yyyy-MM-dd" />
<column name="MODIFIED_BY" value-from-field="MODIFIED_BY" data-type="VARCHAR2(50)" allows-null="yes" />
<column name="DESCRIPTION" value-from-field="DESCRIPTION" data-type="VARCHAR2(2000)" allows-null="yes" />
<column name="COMMENTS" value-from-field="COMMENTS" data-type="VARCHAR2(2000)" allows-null="yes" />
<column name="MIME_TYPE" value-from-field="MIME_TYPE" data-type="VARCHAR2(100)" allows-null="no" />
</update>
</database>
</destinations>
</deployment>

and when I run the deployment through the command line I get this:

/data/software/iw/iw-home/iw-perl/bin/iwperl /data/software/iw/iw-home/datadeploy/bin/iwdd.ipl cfg=dd.cfg deployment=xml-to-dbdoc area=/tmp infile=dbdoc-tupleTable-lite.xml db2=oracle dbase2=blah:1521:blah dbuser2=blah dbpass2=blah

DataDeploy Version 5.6.0 Build 19499 Interwoven 20030506
Database Vendor set to oracle
SQL datatype for column ID is VARCHAR2(254)
SQL datatype for column DOCUMENT_SIZE is NUMBER(15)
SQL datatype for column VERSION is NUMBER(15)
SQL datatype for column AUTHOR is VARCHAR2(50)
SQL datatype for column CREATION_DATE is DATE
SQL datatype for column LOCKED_BY is VARCHAR2(50)
SQL datatype for column MODIFIED_DATE is DATE
SQL datatype for column MODIFIED_BY is VARCHAR2(50)
SQL datatype for column DESCRIPTION is VARCHAR2(2000)
SQL datatype for column COMMENTS is VARCHAR2(2000)
SQL datatype for column MIME_TYPE is VARCHAR2(100)
Setting up consumer TDbAgent
>>> Loading drivers <<<
url : blah:1521:blah
login : blah
login timeout=3
Connecting to the database...
Try connecting using jdbc Smiley Surprised

racle:thin:@blah:1521:blah...

Connected to jdbc Smiley Surprised

racle:thin:@blah:1521:blah
Driver Oracle JDBC driver
Version 9.0.1.1.0
Connected to [blah:1521:blah]
SELECT * FROM USER_TABLES WHERE TABLE_NAME='IWOV_IDMAPS'
Building mappings cache...
Built mapping cache(s) successfully.
# column mappings: 0
# table mappings: 0
# view mappings: 0
# constraint mappings: 0
SELECT * FROM USER_TABLES WHERE TABLE_NAME='DOCUMENT_METADATA'
Table <DOCUMENT_METADATA> exists.
Running deployment [xml-to-dbdoc]
Xml source[dbdoc-tupleTable-lite.xml]
TUPLE={MODIFIED_BY=iwadmin, AUTHOR=iwadmin, DOCUMENT_SIZE=4623, MIME_TYPE=text/html, MODIFIED_DATE=11-11-01 00:00:00.0, ID=generated/content/editorial/news/april16_full.html, CREATION_DATE=19-01-01 00:00:00.0, COMMENTS=IWOV, VERSION=1}
TTableSchemaHelper not found in cache for [DOCUMENT_METADATA]. Creating new.
SELECT ID, DOCUMENT_SIZE,VERSION,AUTHOR,CREATION_DATE,LOCKED_BY,MODIFIED_DATE,MODIFIED_BY,DESCRIPTION,COMMENTS,MIME_TYPE FROM DOCUMENT_METADATA WHERE ID=?
TTableSchemaHelper object for [DOCUMENT_METADATA] found in cache.
WHERE clause parameter 1: generated/content/editorial/news/april16_full.html
Column: ID, field: ID, Index: 1,Converting 'generated/content/editorial/news/april16_full.html' to VARCHAR
Error occured in TDbAgent: Smiley Frustrated

elect()

*******************************************************
SQLException occured in TDbAgent Smiley Frustrated

elect()
Exception Message: ORA-00904: invalid column name

Vendor Error Code: 904
SQL state: 42000
*******************************************************

*******STACK TRACE*************
java.sql.SQLException: ORA-00904: invalid column name

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2117)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2331)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:366)
at com.interwoven.dd100.dd.TDbAgent.Select(TDbAgent.java:2476)
at com.interwoven.dd100.dd.TDbAgent.NarrowInsertUpdate(TDbAgent.java:2110)
at com.interwoven.dd100.dd.TDbAgent.WriteTuple(TDbAgent.java:3595)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumerInternal(TConsumerManager.java:398)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumers(TConsumerManager.java:387)
at com.interwoven.dd100.dd.TAgentClient.DoOneXmlSource(TAgentClient.java:362)
at com.interwoven.dd100.dd.TAgentClient.DoXmlSource(TAgentClient.java:290)
at com.interwoven.dd100.dd.TAgentClient.DoOneDeployment(TAgentClient.java:262)
at com.interwoven.dd100.dd.TAgentClient.Go(TAgentClient.java:188)
at com.interwoven.dd100.dd.IWDataDeploy.Go(IWDataDeploy.java:487)
at com.interwoven.dd100.dd.IWDataDeploy.runDeploy(IWDataDeploy.java:527)
at com.interwoven.dd100.dd.IWDataDeploy.main(IWDataDeploy.java:508)
Error occured in NarrowInsertUpdate: Select() returned null ResultSet
-- Failed
ERROR:WriteTuple failure to consumer[0]
ERROR:deployment [xml-to-dbdoc] FAILED
Error occured in area outside TDbAgent consumer (explicit rollback initiated)
Rollback database [btwpd4.devenv1.bt.co.uk:1521:btwpd4]...
Deployment[xml-to-dbdoc] Table[DOCUMENT_METADATA]
-----------------------------------
Tuples inserted : 0
Tuples updated : 0
Tuples deleted : 0
Tuples failed : 0
Tuples rolled back : 0
Tuples skipped : 0
-----------------------------------
Started : Wed Jun 11 11:05:51 BST 2003
Finished: Wed Jun 11 11:05:54 BST 2003

### IWDataDeploy FAILED ###

Any help, thoughts, suggestions will be gratefully received.

Cheers
Stuart Rosewall
Technical Consultant

Find more posts tagged with

Comments

srosewall

Just working this through - I see I've fallen into the trap of thinking that DD could do an sql insert somehow, but it can't.

I've tried setting up a user-action sql insert command in the dd.cfg, but I'm not sure I can get the values from the XML file into the command.

So I'm beginning to think I need to investigate Perl DBI to do the insert...

If I get anywhere interesting I'll report back, but if anyone has any comments etc. feel free to post.

Cheers
Stuart Rosewall

Adam Stoller

If you're copying data from one Oracle DB to another Oracle DB - doesn't Oracle provide commands for doing that?

Or did I miss some special custom thing going on between the extraction and insertion steps - for which Perl would be useful/required?

--fish
(Interwoven Senior Technical Consultant)

Migrateduser

Do you have ID column in table DOCUMENT_METADATA??