Re: Oracle7.1 - Functions in select columns

From: Michael P. Vergara <vergara_at_nosc.mil>
Date: Tue, 13 Dec 1994 16:08:49 GMT
Message-ID: <1994Dec13.160849.12418_at_nosc.mil>

In article <3chk9c$h19_at_due.uninett.no>, Erik Lie-Nielsen <Erik.Lie-Nielsen_at_mfr.uib.no> wrote:
>
>Hi,
>We have just been upgrading our Oracle7 to ver. 7.1.3 and have been
>looking forward to see the new features. The problem is I have no
>manual yet and I need the feature to call PL/SQL functions in sql
>select statements.
>
>I have tried a simple packaged function like this:
>
>function get_more (id in number) return char is
> somedata varchar := '';
> cursor more(fid number) is select data from child_table where id=fid;
>begin
> for childrec in more(id) loop
> somedata:=somedata || childrec.data
> end loop;
>end get_more;
>
>(The contents of the function don't matter at all..)
>
>When I try to call my function (the way I guess it should be):
>
> select something,more_data(id) from main_table;
>
>I get the following runtime exception:
>
> ERROR at line 1:
> ORA-06571: Function MORE_DATA does not guarantee not to update database.
>

Yes, the answer is RTM, but...even though I think I figured this out once before, I could not find the answer in the manual yesterday when I tried to tell a co-worker how to solve this very problem!

I created a function to convert a time string (formated HH:MI:SS.decimal) into the number of elapsed seconds, but not limited to 24 hours. This function doesn't update the database, either, and I also received this very irritating message. Here is a skeletonized listing of the PL/SQL script I used to create the function, with just the important parts:

• Begin PL/SQL Example ============================

create function ICONV (timex varchar2)

                       return number is
  (PL/SQL declarations)

	.
	.

  bad_param exception;
  PRAGMA EXCEPTION_INIT(bad_param,-1722);

--	The number specified in the EXCEPTION_INIT line is a standard
--	ORA-prefixed error messgae number.  Error message ORA-01722
--	is for 'Invalid Number'
begin
	.
	.
  (PL/SQL code here)
	.
	.

  if error_condition = TRUE then
    raise bad_param;
  end if;

	.
	.
  (more PL/SQL code, etc)
	.
	.

--
  return(numsecs);
--
exception
  when bad_param then
    return(NULL);
end ICONV;
/
============================== End PL/SQL Example =============================


HTH

--
============================================================================
Mike Vergara           |   Be good...and you will be lonesome
vergara_at_nosc.mil       |                                       Jimmy Buffett
Opinions expressed are not necessarily those of anyone else but me.  So there.

Received on Tue Dec 13 1994 - 17:08:49 CET