VIRTUAL Column - Function [message #651021] |
Tue, 10 May 2016 03:55 |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
I created the below function and table with virtual column which will call a function.
when i create the table am getting below error
create table vir_test
( party_key varchar2(100),
employee_key VARCHAR2(150 BYTE) GENERATED ALWAYS AS (w2_derived_value('E',party_key)) VIRTUAL VISIBLE
)
Error starting at line 1 in command:
create table vir_test
( party_key varchar2(100),
employee_key VARCHAR2(150 BYTE) GENERATED ALWAYS AS (w2_derived_value('E',party_key)) VIRTUAL VISIBLE
)
Error at Command Line:3 Column:56
Error report:
SQL Error: ORA-30553: The function is not deterministic
30553. 00000 - "The function is not deterministic"
*Cause: The function on which the index is defined is not deterministic
*Action: If the function is deterministic, mark it DETERMINISTIC. If it
is not deterministic (it depends on package state, database state,
current time, or anything other than the function inputs) then
do not create the index. The values returned by a deterministic
function should not change even when the function is rewritten or
recompiled.
create or replace
function w2_derived_value (v_exec_flg varchar2, v_exec_value varchar2)
return varchar2
AS
v_ret_value varchar2(100);
Begin
if ( v_exec_flg = 'E' ) then
Begin
select rm into v_ret_value from ss_repo.party_info where party_key = trim(v_exec_value) ;
Exception when no_data_found then
v_ret_value := null;
return v_ret_value;
End;
Return v_ret_value;
End if;
End w2_derived_value;
|
|
|
Re: VIRTUAL Column - Function [message #651022 is a reply to message #651021] |
Tue, 10 May 2016 04:11 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The error is clear: your fuction is not deterministic, because it depends on database state. What you trying to do is logically impossible.
It seems to me that what you need is a view that joins your new table to party_info.
|
|
|
Re: VIRTUAL Column - Function [message #651023 is a reply to message #651022] |
Tue, 10 May 2016 04:21 |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
Its working if i change this function into DETERMINISTIC
create or replace
function w2_derived_value (v_exec_flg varchar2, v_exec_value varchar2)
return varchar2 DETERMINISTIC
iS
v_ret_value varchar2(100);
Begin
if ( v_exec_flg = 'E' ) then
Begin
select rm into v_ret_value from ss_repo.party_info where party_key = trim(v_exec_value) ;
Exception when no_data_found then
v_ret_value := null;
return v_ret_value;
End;
Return v_ret_value;
End if;
End w2_derived_value;
create table vir_test
( party_key varchar2(100),
employee_key VARCHAR2(4000 BYTE) GENERATED ALWAYS AS (ss_repo.w2_derived_value('E',party_key)) VIRTUAL VISIBLE
)
I dont know how far its efficient
|
|
|
|
|
Re: VIRTUAL Column - Function [message #651029 is a reply to message #651028] |
Tue, 10 May 2016 05:12 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Never, never label a function as deterministic if it isn't, and anything that contains a select statement isn't.
Doing so will result in you getting the wrong answer sooner or later as oracle wrongly believes (cause you've lied to it) that the function will always give the same result for the same parameters.
As John says - what you are attempting isn't logically possible and you need to use a view instead.
|
|
|
|
Re: VIRTUAL Column - Function [message #651038 is a reply to message #651035] |
Tue, 10 May 2016 07:31 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:alue dynamically at the time of inserting into table
The value "in" (rather "of") the virtual column is NOT the one when you insert but when you query. This is why a RELA deterministic function is needed to have the same value in a column.
What would you think about a real column would not return the same value each time you query it, without any UPDATE on it?
Quote:s there any way of doing like parameterised views
Yes, just search for this term.
|
|
|