Dereferencing an Oracle Object in ADO

From: Og Maciel <ogmaciel_at_yahoo.com>
Date: 20 May 2002 07:51:10 -0700
Message-ID: <9117be4c.0205200651.72579a8@posting.google.com>

To whom this may concern;

I'm a VB programmer and I need to be able to fetch and deref an Oracle Object from a function in the database. The object is as follow:

create or replace type PLATE_OBJECT as object (

• Author : OMACIEL
• Created : 5/20/2002 9:55:59 AM
• Purpose : TO HOLD GENERAL INFORMATION FOR AN ASSAY PLATE

  gn_aplate_code             number(5),
  gv_created_by              varchar2(12),
  gv_creation_date           varchar2(10),
  gv_analysis_by             VARCHAR2(12),
  gv_analysis_date           varchar2(10),
  gv_comments                varchar2(4000),
  gn_parent_aplate_code      number(10),
  gv_runset_code             varchar2(12),
  
  gv_plate_bogused           varchar2(1),
  gn_plate_size              number,
  gv_plate_id                varchar2(30),
  gv_assay_type              varchar2(6),
  gn_z_prime                 number

)

and the function:

create or replace function getMyPlateObj(n_aplate_code in number) return plate_object
is

  my_object plate_object :=
plate_object(null,null,null,null,null,null,null,null,null,null,null,null,null);

  begin

   select distinct

       ap.aplate_code,
       nvl(ap.created_by,' '),
       nvl(to_char(ap.creation_date,'DD-MM-YYYY'),' '),
       nvl(ap.analysis_by,' '),
       nvl(to_char(ap.analysis_date,'DD-MM-YYYY'),' '),
       nvl(ap.comments,' '),
       nvl(ap.runset_code,' '),
       nvl(ap.parent_aplate_code,0),
       nvl(pr.assay_type,' '),
       nvl(pharma.isplatebogused(ap.aplate_code),'N'),
       nvl(round(aps.z_prime,2),0),
       nvl(pp.plate_id,' '),
       nvl(pp.plate_size,0)
  into my_object.gn_aplate_code,
       my_object.gv_created_by,
       my_object.gv_creation_date,
       my_object.gv_analysis_by,
       my_object.gv_analysis_date,
       my_object.gv_comments,
       my_object.gv_runset_code,
       my_object.gn_parent_aplate_code,
       my_object.gv_assay_type,
       my_object.gv_plate_bogused,
       my_object.gn_z_prime,
       my_object.gv_plate_id,
       my_object.gn_plate_size
  from pharma.p_aplate ap,
       pharma.p_aplate_stat aps,
       pharma.p_awell aw,
       pharma.p_results pr,
       cims.p_plate pp
  where
       ap.aplate_code = n_aplate_code

  and pp.plate_code(+) = ap.parent_aplate_code   and aps.aplate_code(+) = ap.aplate_code   and aw.aplate_code = ap.aplate_code
  and pr.result_code = aw.result_code;

  return my_object;

  end;

I can write a SELECT statement in VB that returns me a PLATE object...  But how can I then dereference it so I can access its members in VB?

Any ideas?

Sincerely,
Og Maciel Received on Mon May 20 2002 - 09:51:10 CDT