Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10

    Unanswered: how load data using stored procedures?

    Hi,

    I want to load into a table a lot of records using stored procedures. For example I have this table:

    test_table (id number, name varchar2(24), address varchar2(64))

    The procedure is something like that:

    Create procedure load_data(collection) is

    Begin
    for each data from collection loop
    Insert into test_table values(sequence.nextval, collection.name, collection.adress)
    end loop;
    commit;
    End;
    /


    It is possible to have a stored procedure witch have as parameter a collection or something like this.? I what to call this procedure from Visual Basic and I want to parsing more than one name and address for each call.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Yes you can... It sounds to me that you need to use VARRAYS in the procedure ... BUT ...

    Why not use SQLLDR (sql loader) ... It is made to load alot of data .. You can still use rownum as a sequence ...

    HTH
    Gregg

  3. #3
    Join Date
    Feb 2003
    Location
    Romania
    Posts
    10
    Thanks for answer gbrabham…..

    The requirement is to don’t use external utility like sqlloader. My solutions are unreliability?.
    Can you be more explicitly how to use VARRAYS using my example? The parameter for procedure must by VARRAYS type?

    Thanks a lot!

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    .. Create the varray type(s) (in this case 22 occurrances)

    create or replace type var_parameter_name as varray(22) of varchar2(5)
    /
    create or replace type var_final_value as varray(22) of number
    /

    ... Create procedure ... (in the declaration area)

    C_parameter_name var_parameter_name;
    C_final_value var_final_value;

    ...
    ...

    loop and insert ...

    FOR I IN 1 .. 22
    LOOP
    if c_parameter_name(I) is not null AND c_parameter_name(I) <> 'NULL' AND
    c_parameter_name(I) <> ' '
    then
    INSERT INTO tablename (PARAMETERNAME,...
    values (TRIM(C_PARAMETER_NAME(I)), ....

    HTH

    Read up on the uses of Varrays ...

    Gregg

Posting Permissions

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