Hello. I have started evaluating AEP Enterprise. It looks like a comprehensive tool that may provide my group with a cost-effective ETL solution. Thank you for making the full version of AEP Enterprise available for an evaluation.
I wanted to share with you some SQL errors I encountered in setting up a repository for AEP Enterprise in an Oracle 11gR2 database.
Here are the errors I encountered, and how I fixed them:
1. For this PK create statement:
Code:
ALTER TABLE QUEUE_HISTORY ADD
CONSTRAINT PK_QUEUE PRIMARY KEY
(
QUEUE_ID
)
I got an "ORA-00955: name is already used by an existing object" error.
I checked and found that the PK constraint name was already used for the QUEUE table.
To fix this problem, I issued the following SQL:
Code:
ALTER TABLE QUEUE_HISTORY ADD
CONSTRAINT PK_QUEUE_HISTORY PRIMARY KEY
(
QUEUE_ID
)
2. I had a similar problem with this PK create statement:
Code:
ALTER TABLE QUEUE_ACTIONS_HISTORY ADD
CONSTRAINT PK_QUEUE_ACTIONS PRIMARY KEY
(
QUEUE_ACTIONS_ID
)
The PK constraint name was already used for the QUEUE_ACTIONS table.
To fix this problem, I issued the following SQL:
Code:
ALTER TABLE QUEUE_ACTIONS_HISTORY ADD
CONSTRAINT PK_QUEUE_ACTIONS_HISTORY PRIMARY KEY
(
QUEUE_ACTIONS_ID
)
3. The following FK create statement failed, as a result of the failure to create a PK for table QUEUE_HISTORY:
Code:
ALTER TABLE QUEUE_ACTIONS_HISTORY ADD
CONSTRAINT FK_QUEUE_ACTIONS_H_QUEUE_H FOREIGN KEY
(
QUEUE_ID
) REFERENCES QUEUE_HISTORY (
QUEUE_ID
) ON DELETE CASCADE
The error was "ORA-02270: no matching unique or primary key for this column-list".
Once I ran the two updated SQL statements above, I was able to re-issue this FK create statement successfully.
4. After finishing creating the repository, I created an Oracle DB connection and then I created a simple transformation that selected 1000 rows from an Oracle table and wrote the rows out to a CSV file. I ran this transformation successfully.
I saved the transformation to the repository, also successfully.
I then tried to use Version Control to check in the transformation. I clicked the Check In button, and provided a comment. When I clicked the OK button, I got a dialog with an error about "ORA-01400: cannot insert NULL into OBJECTS_HISTORY.CHANGED".
I reviewed the OBJECTS_HISTORY table structure and noted that CHANGED column had a NOT NULL constraint on it.
To fix this, I altered the column definition to set a DEFAULT value of "SYSDATE" for this column. I could have instead removed the NOT NULL constraint from the column, but I was afraid that AEP might rely on the column having a value in it.
I went to check in the transformation again. This time, after clicking OK on the Check In dialog, I got another error of "ORA-01400: cannot insert NULL into OBJECTS_HISTORY.CHANGED_BY".
I noted that the CHANGED_BY column in table OBJECTS_HISTORY also had a NOT NULL constraint on it.
To fix this problem, I set a DEFAULT value for this column. I used a text string of 'CHI-USER', which means something to me.
Finally, I tried to check in the transformation. This time it worked.
If you need any more information from me about these errors, please let me know.
So far, I am impressed with what AEP offers and the functionality that it provides. I have some further questions about AEP, but I will create another topic to ask them.
Thank you.
Pete
