How to Rollback When an Error Occurs While Executing SQL Loader Command

SQL LOADER GIVING ERROR

Add the log,bad and discard options to your command and look what's in it.

#>sqlldr userid=username/password control=yourcontrolfile.ctl data=yourdata.csv log=yourlogfile.log bad=yourbadfile.bad  discard=yourdiscardfile.disc

I use a controlfile for csv files created from excell like this:

options (skip =6)
load data
append
into table table_name
fields terminated by "," optionally enclosed by '"'
(column_name1
,column_name2
,etc.
)

Skip = 6 is to skip the headers from the file

sqlldr return codes - ex_warn

You can have the situation where SQL Loader inserted (and committed) some rows out of a data file but failed to reach the end of that file (ie there could have been more records after the failure point which would have otherwise succeeded).

I'd opt for an external table over SQL Loader, using an INSERT INTO dest_table ... SELECT * FROM external_table. That would be an atomic operation and there's a (generally small) chance that it will fail if you have insufficient undo for rollback (since you are not using intermediate commits).

I'd also minimise the possibilities of rejections in the external table / SQL Loader layer by treating everything as generic text until it is loaded into the database. Then I'd apply the structure and use DML error logging to handle anything irregular. That way you have clear access to the rejected data and the reason for the rejection in the database.

Liquibase rollback from command line not working

The problem was the path to the change log file. Liquibase stores somewhere the path to the chengeLog and comapres it to the given change log at calling. If they are not the same Liquibase will just go on without a changeLog and warning.

So in my case when calling Liquibase from the tomcat app the path was: database\master.xml and when calling it from the console I gave the path C:\myProject\src\main\resources\database\master.xml in the comand line. This caused the method to return null although Liquibase knew the path to the changeLog. So that was the reason it did not worked.

A workaround is to call liquibase from the command line from the same folder as the application did using a relative path.

Technical reason:
After debuggin LB I found this. The Method DatabaseChageLog.getChangeSet() is returning null althoug the chanegeLog path is correct. This happens when creating the ChangeLogIterator for runnign the RollbackVisitor in the method Liquibase.rollback(int changesToRollback, Contexts contexts, LabelExpression labelExpression). This do not happens with the ValidationVisitor becasuse the ChangeLogIterator is created differently, that is why I did not get any error\warnings

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Your table is already locked by some query. For example, you may have executed "select for update" and have not yet committed/rollbacked and fired another select query. Do a commit/rollback before executing your query.

---

Related Topics