Oracle procedure to vertica compatible code (with complex business logic)

Moderator: NorbertKrupa

Post Reply
vert_coder
Newbie
Newbie
Posts: 2
Joined: Thu Jun 02, 2016 7:19 am

Oracle procedure to vertica compatible code (with complex business logic)

Post by vert_coder » Thu Jun 02, 2016 7:47 am

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 .

Canarchiste
Newbie
Newbie
Posts: 7
Joined: Mon Aug 22, 2016 6:24 am

Re: Oracle procedure to vertica compatible code (with complex business logic)

Post by Canarchiste » Mon Dec 05, 2016 9:40 am

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 :P !

Canarchiste
Newbie
Newbie
Posts: 7
Joined: Mon Aug 22, 2016 6:24 am

Re: Oracle procedure to vertica compatible code (with complex business logic)

Post by Canarchiste » Mon Dec 19, 2016 4:41 am

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.

Post Reply

Return to “Vertica Database Development”