Re: PL/SQL Problem

A copy of this was sent to "Sydney" <sydney_at_i-france.com> (if that email address didn't require changing) On Tue, 11 May 1999 18:59:04 +0200, you wrote:

>Hi
>
>> No, you do not have to use it. Just put anything you want to declare
>between
>> the AS (or IS) and BEGIN:
>>
>> create or replace function get_allcmd(pnumcom commande.numcom%TYPE)
>return tcmd as
>> type tcmd is record
>> ( tnumtable numtable%TYPE,
>> tnumserv numserv%TYPE,
>> tnomserv nomserv%TYPE,
>> tdatecom DATE,
>> tmontant montant%TYPE);
>> rcmd tcmd
>> begin
>> ...
>> end;
>
>It doesn't work. Here is the error message.
>Can't use a type that is declared after.
>Any idea?
>
>Sydney
>

The type must be declared outside of the function for the simple reason that if you didn't -- no one would be able to CALL your function. No one would ever be able to declare a record of your type and call you. You would have created a function that could never be invoked.

A solution is as follows:

create package types
as

       type tcmd is record
         ( tnumtable numtable%TYPE,
           tnumserv numserv%TYPE,
           tnomserv nomserv%TYPE,
           tdatecom DATE,
           tmontant montant%TYPE);

create or replace function get_allcmd(pnumcom commande.numcom%TYPE) return types.tcmd
as

    rcmd types.tcmd
begin

     ...
end;

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue May 11 1999 - 12:59:29 CDT