Re: Function Help

A copy of this was sent to Dan White <Dan_member_at_newsguy.com> (if that email address didn't require changing) On 2 Nov 1999 08:47:22 -0800, you wrote:

>PLease help I have a function That is a stand alone function ( ie not part of a
>package and I am getting an error
>
>SQL> select format_name(name,1) from emp2;
>Select format_name(name,1) from emp2
> *
>ERROR at line 1:
>ORA-06571: Function FORMAT_NAME does not guarantee not to update database
>I looked up the error and I says that I need a pragma if it part of a package(
>it is not ) or if the function is a part of an update procedure I can not use it
>( i think )
>here is the source code
>
>set serveroutput on
>create or replace FUNCTION FORMAT_NAME(p_name in varchar2,P_PARAM in number)
>RETURN VARCHAR2
>IS
>v_comma number;
>v_first_name varchar2(15);
>v_last_name varchar2(20);
>v_first_name_temp varchar2(15);
>v_first_name_end number;
>v_prefix varchar2(3);
>v_full_name varchar2(25);
>V_MIDDLE_INI VARCHAR2(1);
>v_mi_count number;
>BEGIN
>
> v_comma:=(instr(p_name,',')+2);
> V_LAST_NAME:=SUBSTR(p_NAME,1,(V_COMMA-3));
> V_FIRST_NAME_TEMP:=SUBSTR(ltrim(P_NAME,' '),(INSTR(P_NAME,',')+1),15);
> v_mi_count:=(instr(ltrim(v_first_name_temp,' '),' ')+1);
> v_first_Name:=substr(v_first_name_temp,1,(v_mi_count-1));
> v_middle_ini:=substr(ltrim(v_first_name_temp,' '),v_mi_count,1);
> v_prefix:=SUBSTR(P_NAME,(LENGTH(P_NAME)-1),LENGTH(P_NAME));
> if p_param=1 then
>v_full_name:=(v_last_name||','||(rtrim(v_first_name,' '))||' '||v_middle_ini||'
>'||v_prefix);
> do.pl(v_full_name);
> elsif P_param=2 then
> v_full_name:=(v_prefix||v_first_name||' '||v_middle_ini||' '||v_last_name);
> do.pl(v_full_name);
> else
> do.pl('The only parameters that you can use are 1 and 2');
> end if;
>RETURN V_FULL_NAME;
>END;
>/
><PRE>

what is the purity specified on the package DO and the procedure PL contained within? The function format_name must call only 'pure' objects, packaged functions and procedure must use the pragma to assert their purity -- you are probably missing it on the referenced package itself.

>Dan White
>Programer/analyst
>dwhite_at_hotmail.com

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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 Nov 02 1999 - 13:43:01 CST