Re: VERY quick question for the masters...

The error is in the line:

    d.name = name

the 'name' which you think is the input variable has been captured by the object table as an attribute name, so your query simply the very first row from the table.

Change the input variable name to v_name (say), and adjust the cursor to match and your function will work.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

saks1000_at_my-deja.com wrote in message <7n5aab$74i$1_at_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?
>
>
Received on Thu Jul 22 1999 - 07:17:31 CDT