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;
/
Dan White
Programer/analyst
dwhite@hotmail.com
Received on Tue Nov 02 1999 - 10:47:22 CST
