The Vertica Database Forums

Hi All,
I have few oracle procedures with complex business logic (like lot of loops ,cursors ,merger, many updates,delete ) .
These procedure called via extjs ( and java) from UI dashboard and lot of oltp operations are performed from UI to existing oracle db like multiple updates/delete/merge .
Business requirement is to migrate these oracle procs to vertica compatible sql's with existing functionality means update /merge/insert/delete oprations on vertica DB with UI calls .

can you please help me how to convert typical oracle loops /cursors and in/out parameters to vertica (any kind of pseudo code will be helpful).

can we write all business logic in vsql and call via UI ?

i have tried vertica insert/update operations via java procedure but performance is very poor .

any idea how can we implement this scenario ?

Thanks in advance .

Facing the same situation, basically it seems like there is only two solutions :
-Java/C++. It is lame because perf are bad, but Vertica doesn't support any stored procedure and cursor are not supported by external procedures. And even Java doesn't support fully cursors..
-Keep Oracle server and execute your procedures on Oracle side, then load all the data on Vertica side. Take care of licences.

Until Vertica support higher level of procedure, it seems to be the only way to do this.

But if someone got a solution which doesn't imply using multiple technologies and loosing any gain of performance you can get with Vertica, that would be great !

Hi again,

For the Java, as far as Insert and Update queries are REALLY slow, I suggest you to write the result into .csv or .txt files then insert into your Vertica database using a COPY (through Java or Through another VSQL script).
This makes the whole thing kinda complex, but this should bypass the performance issue.