Re: Technical OCI Question

In article <38FB84FF.8F1FE7F8_at_hasc.com>,   Sam Habbab <sam_at_hasc.com> wrote:
> Hi,
>
> I'm calling a stored procedure from my C program.
> At first everything works fine, but since I need to pass a varargs to
 my
> procedure I had to create a table, then insert key-value pairs into
 the
> table. Then I would pass the table as the only parameter to my
> procedure.
>
> This means that instead of having one call:
> execute procedureX(parameter);
>
> now I have the following:
>
> declare
> table tableType;
> begin
> table(1).key := 'key1';
> table(1).value := 'value1';
> table(2).key := 'key2';
> table(2).value := 'value2';
> .... other parameter setting.
> procedureX(table);
> end;
>
> Q1- Is there any other way to pass unlimited number of arguments
 without
> having to declare the function with tons of parameters (with DEFAULT
> value)?
>

take your example one logical step further. Make a package like:

create or replace package my_pkg
as

   type array is table of varchar2(255) index by binary_integer;

   procedure procedureX( names in array, vals in array ); end;
/

and in OCI, use a block:

begin my_pkg.procedureX( :names, :vals ); end;

and just bind 2 arrays to that statement -- don't build a unique block with hard coded values, just build one block -- bind c arrays of strings to it and execute it.

> Q2- Would calling a block of code like that create any extra network
> traffic? I.E.: Would the oracle oci pass the entire block to the
> database to be executed, or would it simply execute one line at a
 time?
>

oci would have to pass the entire block to the engine for compilation and execution.

> Thanks a lot in advance.
>
> S_at_M.
>
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.