Hello!
I currently have an OCI application that makes thousands of stored procedure calls per second. The stored procedure performs some business logic of which I want my client application to remain unaware. I want to reduce database chat, and instead of calling the stored procedure thousands of times per second, call the stored procedure (say) once per second, but with a thousand values.
I have used OCIBindArrayOfStruct successfully to perform bulk inserts, where my OCI application performs an INSERT INTO () operation using the "iters" function, and it might be possible to do something similar with the stored procedure, but I want to do something slightly different, because the stored procedure does something like this:
* If a particular condition is set, call a secondary stored procedure
* If another particular condition is set, insert a row into a certain table
What I was therefore wanting to do was to pass an array (or a series of arrays) into the stored procedure, and then for the stored procedure to use whatever optimal methods are available, such as FORALL, to perform each of the tasks in the most performant manner.
I was thinking therefore of creating a type, e.g. as a TABLE OF VARCHAR2, or as a VARRAY, and having parameters to the stored procedure defined according to that type, something like:
create or replace TYPE "NTT_VARCHAR2" AS TABLE OF VARCHAR2(32700);
create or replace FUNCTION bulk_process_values (
p_error_message OUT NOCOPY VARCHAR2,
p_module_context_name OUT NOCOPY VARCHAR2,
p_sql_error OUT NOCOPY VARCHAR2,
p_count IN NUMBER,
p_non_array_var_1 IN VARCHAR2,
p_non_array_var_2 IN VARCHAR2,
p_array_var_1 IN NTT_VARCHAR2,
p_array_var_2 IN NTT_VARCHAR2,
p_array_var_3 IN NTT_VARCHAR2
<etc. for all array variables>)
RETURN NUMBER
...but I am struggling to find an example of how to call the "bulk_process_values" stored procedure, and indeed whether I should be using a TABLE OF VARCHAR2, or a VARRAY as the parameter type.
I have searched the Internet, and found this: - and Binding array of structs (OCIBindArrayOfStruct) example code
- but they take different approaches, and a little confusing (the second example introduces talk of the OTT).
I think that the first approach is more likely to be the solution that I need - but I cannot help thinking - this is an awful lot of stuff to go through if it does not end up significantly improving the performance!
My questions therefore are:
* Is moving from a call-SP-thousands-of-times-per-second to call-SP-fewer-times-but-with-array-parameters sensible?
* Should the array parameters be VARRAY, TABLE OF VARCHAR2, or something else?
* Do I need to use the OTT for my case?
* The examples I found are both quite old - and the second example hints that the optimal approach evolves with OCI releases - I am on 11.2, what is the optimal approach these days?
* <added afterwards> Why are there no useful official examples?!
Thank you very much
Much appreciated.
https://community.oracle.com/message/4528528#4528528 did in fact work, with VARRAY, with a proof of concept. Now I just need to shake it about a little bit and then get my PL/SQL experts to provide optimal SP.
I think I was really daunted by the lack of official documentation, with the (really fabulous) community being the only way to get things done.