call stored program, register out parameter

[hmmftg]

Hi we're using your awesome package in our product and we're thankful for your good work here as our developers try to call an stored program with out parameter, we face the same issue when we forget to set string size like this:

​        ​name​ ​=​ ​strings​.​Repeat​(​" "​, ​200​)

this approach is the only way to set output parameter size? is there any way to pass length or something more standard? I think you've done something like that in last clob issue, if I got it right

another question: we have a huge table(with 300 columns😅) and our db is 11g, which approach is better if we want to write a plsql function and call it to insert into this table? 1- pass one json object and deserialize it to rowtype inside plsql function then insert(might have performance issue on high load). 2- have a parameter for each column(we test it with c code and it was ok, but code looks so dirty) 3- create a new type which is like rowtype and pass it to plsql and convert it to rowtype(as it does not neet to desrialize, theorically should be faster)

thanks for your support

[hmmftg]

for 2nd question, approach 3, I mean using user defined types

[sijms]

HI @hmmftg for first issue do you try something like this

// not use database/sql interface
stmt.AddParam("1", data, 200, go_ora.Output)

there is another solution = create new type go_ora.OutputPar instead of sql.Output and pass whatever information we need to create a intended parameter

[sijms]

second problem can you give me a simple example

[hmmftg]

second problem can you give me a simple example

for example:

create table huge_tab( col_001 varchar2(10), ... col_300 varchar2(10) );

create procedure register_record_multi_param( p_001 varchar2, ..., p_300 varchar2 ) as begin insert into huge_tab vlaues( p_001, ..., p_300 ); end;

create or replace type my_type as object( col_001 varchar2(10), ... col_300 varchar2(10) );

create procedure register_record_with_type( param my_type ) as rec huge_tab%rowtype; begin rec := convert (param); insert into huge_tab vlaues rec; end;

[sijms]

you can create a custom type in oracle then use it in the package easily see example UDT for more information

but note that UDT is tested in SELECT not INSERT I will test it in insert and inform you

[hmmftg]

the old way was a huge string formatted as tlv, with a parser to serialize and deserialize it in plsql and c it's problem was performance issue in high-load we're using the 300 parameters method currently in c codes

[sijms]

Hi @hmmftg I make a commit that introduce the following: 1- Support for user define type UDT as input parameter (still output not tested) 2- Add new structure go_ora.Out that contain Size member for setting output parameter size

you can see an example UDT_PARS for more help

please test it and inform me for any problems

[sijms]

tested oracle versions 12c and 19c

[sijms]

I am fixing some bugs now

[sijms]

I fix bug: when you have large UDT object will fail

[hmmftg]

Hi thanks for your support I'll test new version with 11g database and inform you

[sijms]

now UDT is supported as input and output parameters

[sijms]

add support for nullable parameters