Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Passing a single value parameter along with a 2nd parametr that is an array of values

    Hi all

    I am using ODP.NET (oracle db provider for .NET) with Oracle Database.
    I don't know whether my problem can be solved by manipulating the .NET program or the PL/SQL procedures. So I will try to explain my problem.

    Basically I will need to pass single value parameter along with other parameters that may is an array of values. for example

    procedure (id_in in number, names_in in varchar2, addresses_in in varchar2) etc

    Where names_in and addresses_in are arrays of names and addresses and id_in is a single value. I will need to perform insertion of names and addresses into a table based on the id_in parameter.

    another word

    i first do a select based on the id to retrieve extra information. based on this information, i will insert the names and addresses along with other values retrieved from the select statement. function1 has an extra id_in, where as function2 doesn't have a single value parameter id_in. function 1 will not work correctly because id_in is treated as an array parameter along with the other array parameters thus returning a cast error. function2 works fine because all the parameters are arrays.

    The question is thus, how do I pass parameters to a procedure using ODP.NET in such a way that the parameters may be a mix of single value and array parameters. There is a way to get around this by not using procedures, instead directly use "insert into .... " as the CommandText, this works but the logic that i am implementing is rather complex and requires a great deal of processing which i think can only be done within a procedure.

    e.g.

    procedure function1 (id_in in number, names_in in varchar2, addresses_in in varchar2)
    as
    suburb varchar2;
    begin
    select suburb_name into suburb from table1 where id = id_in;

    insert into table2
    (name, address, suburb_name)
    values
    (names_in, addresses_in);

    end;

    procedure function2(names_in in varchar2, addresses_in in varchar2)
    as
    begin

    insert into table2
    (name, address)
    values
    (names_in, addresses_in);

    end;


    Any help is greatly appreciated.

    James : )

  2. #2
    Join Date
    Aug 2003
    Posts
    111

    Re: Passing a single value parameter along with a 2nd parametr that is an array of values

    If anyone know how to do the equivalent in any other language other than .NET I would be happy to see how you would do it.

    James : )

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Passing a single value parameter along with a 2nd parametr that is an array of values

    Well, I don't know how .NET would handle this, but this is how to use arrays in PL/SQL:

    create type names_array_t is table of varchar2(30);
    create type addr_array_t is table of varchar2(40);

    procedure function1 (id_in in number, names_in in names_array_t, addresses_in in addr_array_t)
    ...

    I don't know how .NET could populate the arrays before calling the procedure, but I imagine it is possible. If it were not possible, you could avoid it by building up the arrays inside a PL/SQL package with calls like this:

    pkg.initialise_names;
    pkg.add_name('John');
    pkg.add_name('Jane');
    pkg.add_name('Arthur');
    pkg.process_names;

    That wouldn't be very efficient though!

  4. #4
    Join Date
    Aug 2003
    Posts
    111
    Thank you for your reply.

    I have resorted to doing multiple calls to insert the array of information I wanted to pass in a single go.
    Couldn't find any documentation on how it could be done. Hopefully the next version of Oracle Provider for MS .NET would fix this deficiency.

    James

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •