Re: Want to do "TYPE mytype IS tablename%ROWTYPE"....

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/17
Message-ID: <8fspme$gtc$1@nnrp1.deja.com>#1/1

In article <8fs01s$j9f$1_at_nnrp1.deja.com>,   dejadon_at_simonindustries.cc wrote:
> I'm hoping that there's something obvious I'm just missing, but have a
> feeling, I may be mis-understanding the whole Packages/Types thing.
>
> Trying to define a package that will allow a VB developer to interact
> with our Oracle DB, to access a set of tables, without allowing the VB
> app to execute SQL against the tables.
>
> One of the first tasks I've started on is defining some data types.
> I'd like them to automagically match my table definitions, but so far
> I'm only able to define variables as records like the table, not to
> create a TYPE. Is what I'm trying to do unsupported?
>
> Table 1:
>
> CREATE TABLE TNInfo (
> TN VARCHAR2(10) NOT NULL,
> CustID NUMBER(38),
> ModBy VARCHAR2(10) NOT NULL,
> ModDate DATE
> );
>
> ----
>
> CREATE OR REPLACE PACKAGE mypack IS
>
> type TNInfoType IS TNInfo%ROWTYPE;
>
> ----
>
> But of course this fails. The closest I can get is
>
> type TNInfoType IS RECORD (tninfo TNInfo%ROWTYPE);
>
> MyTNRec1 TNInfoType;
> MyTNRec2 TNInfoType;
> ---
>
> But that means the VB app would have to do an extra level of
 derefernce,
> ie. MyTNRec1.tninfo.Modby
>
> Is there anyway to end up with a TYPE definition that matches the
> table, without having to manually code that record defintion?
>
> Thanx,
>
> Don Simon
> dejadon_at_simonindustries.cc
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

well, you can use a subtype (but you don't need to). consider:

ops$tkyte_at_8i> create table test ( x int, y int );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package my_pkg   2 as
  3 subtype myrecord is test%rowtype;   4

  5          a myrecord;
  6          b test%rowtype;

  7 end;
  8 /

Package created.

I prefer to just use table%rowtype instead of subtyping. Its about as clear as you can get (it is wholly unambigous where the record gets its definition from)....

My question back to you is -- how do you plan on using a plsql record type with VB. PLSQL record types are good inside of PLSQL only -- VB cannot create a record and bind to it in plsql -- it can bind scalar types only. You'll need a formal named parameter for each column -- not a procedure that takes a record type as input, if VB is to be calling this procedure...

--
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.

Received on Wed May 17 2000 - 00:00:00 CDT