Home » RDBMS Server » Security » ORA-28112: failed to execute policy function (Oracle Release 10.2.0.3.0 - 64bit)
ORA-28112: failed to execute policy function [message #415909] Wed, 29 July 2009 14:20 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Hi,

I ran below code

SQL> set serveroutput on time on timing on;
14:58:46 SQL> DECLARE 
14:58:53   2    NAMESPACE VARCHAR2(32767);
14:58:53   3    ATTRIBUTE VARCHAR2(32767);
14:58:53   4    VALUE VARCHAR2(32767);
14:58:53   5    USERNAME VARCHAR2(32767);
14:58:53   6    CLIENT_ID VARCHAR2(32767);
14:58:53   7  
14:58:53   8  BEGIN 
14:58:53   9    NAMESPACE := NULL;
14:58:53  10    ATTRIBUTE := NULL;
14:58:53  11    VALUE := NULL;
14:58:53  12    USERNAME := NULL;
14:58:53  13    CLIENT_ID := NULL;
14:58:53  14  
14:58:53  15    --SYS.DBMS_SESSION.SET_CONTEXT ( NAMESPACE, ATTRIBUTE, VALUE, USERNAME, CLIENT_ID );

14:58:53  16    dbms_session.set_context( 'CLIENTCONTEXT', 'WORKSPACE', TO_CHAR('null') );
14:58:53  17    COMMIT; 
14:58:53  18  END; 
14:58:54  19  /

PL/SQL procedure successfully completed.


and after running this when selecting data from table getting below error.

ERROR at line 1:
ORA-28112: failed to execute policy function

Anyone having any idea?

Regards
Re: ORA-28112: failed to execute policy function [message #415910 is a reply to message #415909] Wed, 29 July 2009 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
28112, 00000, "failed to execute policy function"
// *Cause: The policy function has one or more error during execution.
// *Action: Check the trace file and correct the errors.
Re: ORA-28112: failed to execute policy function [message #415916 is a reply to message #415909] Wed, 29 July 2009 14:45 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

I checked the trace file and error is coming at below code
188 SELECT COUNT(*)
189 INTO v_view
190 FROM USER wu
191 WHERE id = v_id
192 AND user = v_user
193 AND EXISTS (
194 SELECT 1
195 FROM ROLE_MAT
196 WHERE ro_id = wu.role_id
197 AND r_id = g_view
198 );
199 
200 IF v_view = 1 THEN
201 
202 RETURN '1=1';
203
204 ELSE
205
206 RETURN
207 'EXISTS (
208 SELECT 1
209 FROM PERM_VW
210 WHERE c_id = DOC_VW.c_id
211 AND d_id = DOC_VW.d_id
212 AND id = sys_context( ''CLIENT'', ''USER'')
213 )';
214
215 END IF;


And trace file error comes as

Policy function execution error:
Logon user : USER
Table/View : DOC_VW
Policy name : DOC_VW_POLICY
Policy function: VPD_PKG.DOC
ORA-01722: invalid number
ORA-06512: at "VPD_PKG", line 188
ORA-06512: at line 1
Re: ORA-28112: failed to execute policy function [message #415918 is a reply to message #415909] Wed, 29 July 2009 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Logon user : USER
I find this to be curious.
Since I don't know VPD, I decline to speculate further.
Re: ORA-28112: failed to execute policy function [message #415919 is a reply to message #415909] Wed, 29 July 2009 14:57 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

It is USER_VPD. I missed it during copy paste.
Re: ORA-28112: failed to execute policy function [message #416009 is a reply to message #415919] Thu, 30 July 2009 02:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
What is the value of g_view?
Try to run the particular sql separately.

By
Vamsi
Re: ORA-28112: failed to execute policy function [message #424957 is a reply to message #416009] Tue, 06 October 2009 13:31 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

I tried running from SQL Plus and it ran fine without any error. But from application it is throwing the error.

Re: ORA-28112: failed to execute policy function [message #425005 is a reply to message #424957] Wed, 07 October 2009 02:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Check the code in VPD_PKG.DOC
Quote:
14:58:53 16 dbms_session.set_context( 'CLIENTCONTEXT', 'WORKSPACE', TO_CHAR('null') );
I really doubt you want to sent null as a string, which is having 4 characters.
I think you may want to send TO_CHAR(NULL). Cross check.

By
Vamsi
Re: ORA-28112: failed to execute policy function [message #425026 is a reply to message #415909] Wed, 07 October 2009 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since null defaults to char type the to_char is redundant as well.
Re: ORA-28112: failed to execute policy function [message #425094 is a reply to message #425026] Wed, 07 October 2009 08:20 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Yes thats true but when i added an exception and captured the variable values they are not NULL. Although the values are correct and present in database.

Re: ORA-28112: failed to execute policy function [message #425212 is a reply to message #425094] Thu, 08 October 2009 01:35 Go to previous message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
amorphous4u wrote on Wed, 07 October 2009 18:50
Yes thats true but when i added an exception and captured the variable values they are not NULL. Although the values are correct and present in database.


New code will help us to analyze.

By
Vamsi
Previous Topic: Remote Login question
Next Topic: Manage password for users
Goto Forum:
  


Current Time: Thu Mar 28 07:50:55 CDT 2024