Re: VERY quick question for the masters...

From: <saks1000_at_my-deja.com>
Date: Wed, 21 Jul 1999 20:25:00 GMT
Message-ID: <7n5aab$74i$1@nnrp1.deja.com>

SQL> create or replace type dept as object (code varchar(20), name varchar(20))
  2 /

Type created.

SQL> create table depts of dept
  2 /

Table created.

SQL> insert into depts values('code1', 'dept1')   2 /

1 row created.

SQL> insert into depts values('code2', 'dept2')   2 /

1 row created.

SQL> insert into depts values('code3', 'dept3')   2 /

1 row created.

SQL>
SQL> create or replace function get_dref (name IN VARCHAR) RETURN REF dept IS
  2 d_ref REF dept;
  3 cursor c IS
  4 select REF(d) from depts d where d.name = name;   5 begin
  6 Open C; Fetch C into d_ref; Close C; Return d_ref;   7 end get_dref;
  8 /

Function created.

SQL>
SQL> select DEREF(get_dref('dept3')) from dual   2 /

DEREF(GET_DREF('DEPT3'))(CODE, NAME)

---

---

DEPT('code1', 'dept1')

SQL>
SQL> It should show info for dept3. How do I do that?

In article <7n4qnj$knk$1_at_nntp.csufresno.edu>,   stevec_at_zimmer.csufresno.edu (Steve Cosner) wrote:
> In article <7n2sll$a76$1_at_nnrp1.deja.com>, <saks1000_at_my-deja.com>
wrote:
> >How can I make a function out of this statement?
> >select REF(d) from departments d where d.Name = 'Computer Science'
>
> What is REF(d)?
>
> >PS This doesn't work:
> >create or replace function get_dref (name IN VARCHAR) RETURN REF
> >department IS
> > d_ref REF department;
> >begin
> > select REF(d) into d_ref from departments d where d.Name = name; 5
> >end get_dref;
>
> Create or replace function get_dref(p_Name varchar2)
> return varchar2 is
> dref departments.ref%type;
> cursor c is
> select d.Ref from Departments d where d.Name = p_Name;
> Begin
> Open C; Fetch C into dref; Close C; Return dref;
> End;
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jul 21 1999 - 15:25:00 CDT