Re: questions on record types in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 13 Mar 1999 00:03:55 GMT
Message-ID: <36e9ab34.2081613@192.86.155.100>

A copy of this was sent to Srinivas Ajjarapu <srinivas_at_synopsys.com> (if that email address didn't require changing) On Fri, 12 Mar 1999 11:41:02 -0800, you wrote:

>I am new to PL/SQL programming. I would appreciate if someone
>can answer the following 2 questions for me.
>
>1. Is it allowed to use record types in a implicit cursor?
>
> Here is what I mean:
>
> Let us say "dept" is a table:
>
> DECLARE dept_rec dept%ROWTYPE;
> select * INTO dept_rec from dept where empno = emp_id;
> .......
>
> Is this a valid code in PL/SQL? If record types are not allowed
> in select, are they allowed in any other implicit cursors?
>
>

Yes:
SQL> declare
  2 emp_rec emp%rowtype;
  3 begin

  4          select * into emp_rec from emp where rownum = 1;
  5          dbms_output.put_line( emp_rec.ename );

  6 end;
  7 /
smith

PL/SQL procedure successfully completed.

>2. Can the return type of a function be a record type?
>

Yes:

SQL> create or replace function get_emp_rec return emp%rowtype   2 as
  3 emp_rec emp%rowtype;
  4 begin

  5          select * into emp_rec from emp where rownum = 1;
  6          return emp_rec;

  7 end;
  8 /

Function created.

SQL> declare
  2 emp_rec emp%rowtype;
  3 begin

  4          emp_rec := get_emp_rec;
  5          dbms_output.put_line( emp_rec.ename );

  6 end;
  7 /
smith

PL/SQL procedure successfully completed.

note however that a plsql record can only be returned to a plsql routine -- there are no ways to bind to a C struct for example.
>
>Please send your answers to: srinivas_at_synopsys.com
>
>Thanks,
>Srinivas
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Mar 12 1999 - 18:03:55 CST