PLS-00306: wrong number or types of arguments in call to

[Xenocide]

I'm trying to implement a virtual private database. Right now I'm simply trying to insert a static condition into the "where" cluase.

Scipt for the policy creation:

create or replace function VPD_TEST_USERAUTH_TESTCCH
return varchar2
as

Out_String varchar2(100) default '1=1';

begin
Out_String := 'USER_PW_DATE >'|| TO_DATE(12/31/1999);

return Out_String;

end;
/


The next is to apply the Policy:

begin
dbms_rls.add_policy('lcch','USER_AUTH','VPD_TEST_POLICY','lcch','VPD_TEST_USERAUTH_TESTCCH',sec_rele vant_cols =>'USER_PW_DATE');
end;
/

This is a describe on the table I'm trying to implement this on:

SQL> desc lcch.user_auth
Name Null? Type
----------------------------------------- -------- -----------------

USER_LOGON NOT NULL VARCHAR2(8)
USER_LNM NOT NULL VARCHAR2(20)
USER_FNM VARCHAR2(15)
USER_SSN VARCHAR2(9)
USER_ROLE VARCHAR2(10)
USER_PW_DATE DATE
USER_III_ORI VARCHAR2(10)
USER_III_ROLE_NAME VARCHAR2(10)

Explantion of terms.

Errors:

SQL> select * from lcch.user_auth;
select * from lcch.user_auth
*
ERROR at line 1:
ORA-28112: failed to execute policy function

From udump file:


----------------------------------------------------------
Policy function execution error:
Logon user : LCCH
Table/View : LCCH.USER_AUTH
Policy name : VPD_TEST_POLICY
Policy function: LCCH.VPD_TEST_USERAUTH_TESTCCH
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
*** 2006-04-14 16:41:22.556
----------------------------------------------------------
Policy function execution error:
Logon user : LCCH
Table/View : LCCH.USER_AUTH
Policy name : VPD_TEST_POLICY
Policy function: LCCH.VPD_TEST_USERAUTH_TESTCCH
ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'VPD_TEST_USERAUTH_TEST
CCH'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
*** 2006-04-14 16:41:22.556
----------------------------------------------------------
Policy function execution error:
Logon user : LCCH
Table/View : LCCH.USER_AUTH
Policy name : VPD_TEST_POLICY
Policy function: LCCH.VPD_TEST_USERAUTH_TESTCCH
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



I'm very new to all of this so any explaination of anything would be appreciated and as "dumb" as possible.

[Xenocide]

I've gotten a little further and this is now my current error:

Policy function execution error:
Logon user : LCCH
Table/View : LCCH.USER_AUTH
Policy name : VPD_TEST_POLICY
Policy function: LCCH.VPD_TEST_USERAUTH_TESTCCH
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "LCCH.VPD_TEST_USERAUTH_TESTCCH", line 10
ORA-06512: at line 1

new policy build:

create or replace function VPD_TEST_USERAUTH_TESTCCH
(schema in varchar2, tab in varchar2)
return varchar2
as

Out_String varchar2(100) default '1=1';


begin
Out_String := 'USER_PW_DATE >'|| TO_DATE('12/31/1999', 'mm/dd/yyy');

return Out_String;

end;
/

[jmodic]

Your TO_DATE format mask is too short, your year's format picture is 'yyy', while it should be 'yyyy'.

Change this:

Out_String := 'USER_PW_DATE >'|| TO_DATE('12/31/1999', 'mm/dd/yyy');

into this:

Out_String := 'USER_PW_DATE >'|| TO_DATE('12/31/1999', 'mm/dd/yyyy');