Oracle + Clob > 4000 Chars

General Discussion
1

Hi,

i’ve tried to insert a 8000 chars long String into a clob column and always get the error: ORA-01704: string literal too long

It does not work with an insert changeset or a load changeset.
The column type in the changeset i have tested are clob and computed.

I do not want to use stored procedures except it will not work without.

Thanks for any thoughts.

Liquibase Version is 2.0 RC7

The oracle driver is

    com.oracle     ojdbc14     10.2.0.3.0
2

It is a limitation with oracle and passing in everything as an SQL statement.  Currently we don’t use stored procs or prepared statements, just straight SQL to make outputting the SQL to executed easier.  Oracle doesn’t let their SQL be longer than 4000 chars, though. 

You could write a custom change that would let you specify the prepared statement and a file with the values to insert, and the change would loop though the file and exectue the prepared statement with the values from the file.  http://liquibase.org/extensions explains the process of creating a custom change.

Otherwise, if it is just a single row you could break it up into a smaller insert statement and them a couple update statementsthat append to the existing value.  Ugly, but it would work around it.

Nathan

3

Hi,

thanks for the reply.

I have solved my problem by defining a bind variable and then executing the statement with this variable.
The following snippet is saved in a seperate file e.g.: clobtest.sql

declare
myClobVar varchar2(32767) := ‘string with size up to 32000 chars’;
begin
insert into tb_config values (‘clobtable_test’, myClobVar);
end;

Then i execute the file with the liquibase tag:

It not the nicest way, but a quick one.

Writing an extension would be nice and i will try it.

4

so, i wrote an extension to process prepared statements.

my problem now is that the extension processes the statement in the “generateSql” method of the custom generator and the method always  returns null to avoid executing any other statements by the executor.
I dont see another solution to execute prepared statements without changing liquibase-core. is this right?

    public Sql[] generateSql(PreparedStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) throws RuntimeException { DatabaseConnection connection = database.getConnection(); try { java.sql.PreparedStatement pStatement = ((JdbcConnection) connection).getUnderlyingConnection().prepareStatement( statement.getPreparedStatement()); 
    		for (int i = 0; i < statement.getColumnValues().size(); i++) {
			{
				Object element = statement.getColumnValues().get(i);
				pStatement.setString(i + 1, element.toString());
			}
		}
		pStatement.executeUpdate();
	} catch (SQLException e) {
		throw new RuntimeException(msg);
	}

	return null;
}</ol>
5

Yes, that is the way you would want to do it. 

How difficult did you find it to create the plugin?  I’d like to improve the documentation, and it would help to have real-world suggestions.

Nathan

6

I think it is not too difficult to write an extension. The structure is done very well and i like this approach of extending liquibase a lot.
Maybe starting is a little bit complex.

However here are my experiences:

The first steps are quite hard because the aktuell documentation does not contain a step by step example. So I looked mainly at the given extensions to understand how it is done.
A Step By Step real world example would be very nice to get started.

Maybe I mini tutorial describing how to extend the XML structure would be nice. I had starting problems to get the custom tags working (setting correct namespace, the need of defining the extension xsd, etc.) But maybe thats just me :slight_smile:

When writing the extension I also looked quite often at the core code, if something is not working like expected.
For example: The first idea was to create the “preparedStatement” tag like the “loadData” tag in order to reuse the “column” child tag.
This does not work because of a class cast exception: ColumnConfig -> LoadDataColumnConfig
Why does this work for the “loadData” tag? Looking deaper iat the core I discovered that the problem is

    if (change instanceof LoadDataChange) {   column = new LoadDataColumnConfig(); }
in the class XMLChangeLogSAXHandler.java

So I think to avoid that extension developer have to look  (too much) at the core code, a documentation of limitations of extensions and what can be done with extensions would be useful.

Conclusion:
After some starting problems and reducing the extension to basics (reading the cvs and inserting only Strings into the prepared statement) it worked with passable effort. Now I can improve the extension to a useful level.

Once you have understand the structure i think writing an extension is very easy and done quickly!

edit: Today i added a custom column subtag by implementing the needed create method in the changeclass. It works pretty good :slight_smile:
You can find out how it works, but it would be very helpful, if this mechanism is documented.

7

Thanks for the info.  I’ll work at getting that better documented.  A tutorial and starting guide is definitely needed.

Nathan