DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: collections questions again

  1. #1
    Join Date
    Dec 2001
    Posts
    26

    Unhappy How to return table of records from stored proc to vb

    PL/SQL Collections question

    Here is another problem.

    I have a collection (indexed-by table) in a stored procedure such that each element of the collection is in fact of the type - tablename%ROWTYPE. I mean this collection stores a subset of a larger table.

    Now how do i access each field within each row of this table.

    I found out that I can access each row of this indexed table by - table(i) .. but i need the fields within this element. eg. something like tablename(4).phonenumber.

    I hope i am clear.

    please help

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    You right.

    Example:
    type tb_access_list is table of aaa.accsess_list%rowtype index by binary_integer;

    ...


    AL_stack tb_access_list;


    ...

    if AL_stack.count > 0 then

    for i in AL_stack.first..AL_stack.last loop

    insert into slidenew.accsess_list( role_id ,

    resource_id ,

    key_id ,

    accsess_id ,

    accsess_value)

    values ( AL_stack(i).role_id ,

    AL_stack(i).resource_id ,

    AL_stack(i).key_id ,

    AL_stack(i).accsess_id ,

    AL_stack(i).accsess_value);

    end loop;

    end if;


  3. #3
    Join Date
    Dec 2001
    Posts
    26

    Thumbs up Good

    That sounds good. will try that but ... i have another problem that is bugging me now.

    i think perhaps posting the entire code will make sense but i know it cumbersome to read.

    i ll describe the error i am getting when VB gets to the execute statement:

    'GET_INDICES' is not a procedure or is undefined.

    and this is briefly the relevant function spec :


    FUNCTION Get_Indices(
    CLUID NUMBER,
    TUID NUMBER) RETURN Return_Table_Typ IS

    /*
    where Return_Table_Typ is a Table of records created by this -
    TYPE Index_Rec IS RECORD(
    FormUID number,
    FAName varchar2(255),
    RTName Varchar2(255),
    FATP number,
    RTTP number,
    Variation varchar2(5),
    Status varchar2(50));
    TYPE Return_Table_typ IS TABLE OF Index_rec INDEX BY BINARY_INTEGER;
    */

    this function is inside a package and from vb i am calling as :

    command.type = adCmdStoredProc
    command.text = "SP_Load_index.Get_Indices"

    and so on wiht appending parameters.

    finally since this is a function i am calling:

    Set rsLoadResult = .Execute

    this is where the error occurs. Help!
    thanks beforehand.


Posting Permissions

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


Click Here to Expand Forum to Full Width