ORA-06571, fn not guarantee not to update database 1

[Chao]

Hi,

I encountered the above error when attempting to call a function in a SQL statement. That called function in turn calls some other functions depending on the transaction type.

According to the documentation for the error message, I need to define Pragma Restrict_Refeerences. I have attempted to move the stand alone functions into a package and define a Pragma Restrict_References(fn_1, WNDS), however, I got a compilation error, PLS-00452: Subprogram fn_1 violates its associated pragma.

Can anyone provide me with some pointer as to how I may be able to resolve this issue?

The following are additional information:

Select A, B, C,
pkg_test.fn_1(TransType, ID)
From ( Select A, B, C TransType, ID
From Secondary_Table
where condition );

create or replace package pkg_test as
function fn_2 (i_key in Objects.ID%Type) return varchar2
v_result varchar2(50);
is
select 'Data_1: ' || field_1 || 'Data_2: ' || Field_2
into v_result
from Table_2
where ID = i_Key;
return v_result;
end fn_2;

function fn_3(i_key in Objects.id%Type) return varchar2
v_result varchar2(50);
v_type_ID Table_3.TypeID%type;
v_name Table_3.name%Type;
is
select name, type_id INTO v_Name, v_Type_ID
from Table_3
where ID = i_key;
v_Result := 'Label: ' || v_Name || ' Type name: ' ||
LookupTypeName(v_Type_ID);

--
-- LookupTypeName is a stand alone function
-- that contains a select ... INTO ... statement
-- and returns a varchar2 data type.
return v_result;
end fn_3;

FUNCTION fn_1 (
i_trans_type in Types.Data_Type%Type,
i_Primary_Key In Objects.ID%Type) Return Varchar2
is
if i_trans_type = 1 then
Return fn_2(i_Primary_Key);
else
Return fn_3(i_Primary_key);
end if;
end fn_1;
END;

 

[jimbopalmer]

here is a sample function from my package header file


function scheduled_time
(
TA_PERSON_ID IN number,
TA_DATE1 IN date,
TA_DATE2 IN date )
Return number;
PRAGMA RESTRICT_REFERENCES (scheduled_time, WNDS); I tried to remain child-like, all I acheived was childish.

 

[sem]

PRAGMA is used in package spec, because the body of function(implementation) is hidden, so there's no way to predict its behaviour. If a standalone function may update database, you can not change this by hiding it into package, because pragma is verified during compile time. In your case fn_1 calls "packaged" functions with unknown purity level, so you should specify purity level of CALLED functions rather than place a CALLING one to the package.

 

[Chao]

What I am trying to do is have a front end call a stored procedure that will return a result set (via SELECT ...). In this stored procedure, the select statement will called a function (say GetData) to query the related data depending on the transaction type. Everything compile fine until I tried to test the stored procedure via a front end. I get the famous Oracle ODBC error ORA-06571 GetData not guarantee not to update database.
Following the documentation, I attempt to put all of the referenced functions into a package and provide the Pragma Restrict_References(DEFAULT, WNDS). This time during compilation, I got the
PLS-00452: Subprogram fn_1 violates its associated pragma.
There are no updates, inserts or deletes in any of the functions refereced. All of them perform lookup (SELECT ...). Can you please let me know what am I doing wrong?

This stored procedure called by the front end looks like this:

Function GetTransList(...) Return Ref Cursor
IS
...
Begin
Open curRefCursor For
Select trans_type,
trans_id,
GetData(trans_type, Trans_ID) as Data
From Transactions
Where condition;
Return curRefCursor;
End GetTransList;

The following functions are stand alone functions:

Function GetType1Data(
i_trans_ID IN Transactions.trans_id%type)
RETURN VARCHAR2
IS
V_RESULT VARCHAR2(50);
BEGIN
Select 'First Name: ' || FName ||
'Last Name: ' || LName
INTO v_Result
FROM Type1_Table
WHERE Trans_ID = i_trans_ID;
Return v_Result;
END GetType1Data;

Function GetType2Data(
i_trans_ID IN Transactions.trans_id%type)
RETURN VARCHAR2
IS
V_RESULT VARCHAR2(50);
BEGIN
Select 'SSN: ' || to_char(SSN) INTO v_Result
FROM Type2_Table
WHERE Trans_ID = i_trans_ID;
Return v_Result;
END GetType2Data;

Function GetType3Data(
i_trans_ID IN Transactions.trans_id%type)
RETURN VARCHAR2
IS
V_RESULT VARCHAR2(50);
BEGIN
Select 'Address: ' || Address INTO v_Result
FROM Type3_Table
WHERE Trans_ID = i_trans_ID;
Return v_Result;
END GetType3Data;

Function GetData(
i_trans_type IN Transactions.Trans_type%Type,
i_trans_ID IN Transactions.Trans_ID%Type)
RETURN VARCHAR2
IS
BEGIN
If i_trans_type
= GetType1_ID(pkg_type_data.type1)then
Return GetType1Data(i_Trans_ID);
elsif i_trans_type
= GetType2_id(pkg_type_data.type1)then
Return GetType2Data(i_trans_ID);
else
Return GetType3Data(i_trans_id);
end if;
-- where pkg_type_data is a package containing the
-- constants declarations
END GetData;

 

[sem]

Please read my previous post CAREFULLY. Procedures in package are not SEEN by sql engin, so everything it knows about their "internal" behaviour MUST be located in package specification. To engine it know that function DOES NOT CHANGE DATA, pragma is used. So, even if YOU know tha function doesn't change database, you SHOULD use pragma to SHARE your knowledge with sql engine. Contrary to the packaged function, standalone function body is not hidden, so its purity level may be chacked directly.

Purity level of calling function apparantly can not EXCEED levels of called function. So if the called function is not pure enough (or is NOT KNOWN as being pure enough)- the calling function inherits its "unknown" (the lowest) purity level.

Placing a function into the package DOES NOT change its purity level, but rather allows COMPILER to check it during DESIGN TIME. So if compiler says, that function violates its pragma , you may beleive it and should DECLARE purity levels of called functions.

 

[Chao]

Thanks sem. I did not understand the usage of the restrict_reference very well. With your comment in mind, I put all the functions and constants into one stand alone function unit and it worked great. After that I started to replacing the calls to the local function with calls to other stand alone functions and it works too. So I knew that my problem is my reference to constants declared inside other packages. Thanks again for your pointers.