DBMS_SESSION.set_context

put in a variable instead of a constant wherever you are invoking that

DBMS_SESSION.set_context('mydate', 'given_date', 'p_date');
How do I refer to this variable when I call this package?
      my_pkg.test_context;

for example...

CREATE OR REPLACE PACKAGE my_pkg
IS      
    PROCEDURE test_ctx; 
END my_pkg;C

CREATE OR REPLACE PACKAGE BODY my_pkg IS
PROCEDURE test_ctx(p_date_string in varchar2) IS 
       BEGIN            
         DBMS_SESSION.set_context('mydate', 'given_date', p_date_string);
      END;
END my_pkg;

Select allOpen in new window

Date format is 04/06/2010

is that dd/mm/yyyy?  or mm/dd/yyyy?

also,  are you passing in a string or a date?  If a string you don't need to format it, it should already be formatted.  If a date, then simply modify the format in the example to whatever you want

date  in the format dd/mm/rrrr

"date  in the format dd/mm/rrrr"

that still doesn't answer the question of what you want as a parameter.
Rember,  dates don't have formats.  Strings have formats.

if you want strings to be your parameter then use my first example.

if you want dates to be your parameter then use my second example and put in the format you want

I get not a valid date error

ORA-01843: not a valid month

what are you doing?

which version are you using?  what are you passing in?  how are you using the context?  your previous question used it in a view with dd-mon-yyyy string format to convert to a date.  Have you changed formats?

As you told me I used the format dd/mm/yyyy in the view and changed the package accordingly.

PROCEDURE test_ctx(p_date in date) IS  
       BEGIN            
         DBMS_SESSION.set_context('mydate', 'given_date', to_char(p_date,'dd-mon-yyyy'));
      END;
-----------------------
I get ORA-01843: not a valid month (this error)

you changed the view to dd/mm/yyyy

but the package is dd-mon-yyyy?

Sorry it was my mistake (typo)

 DBMS_SESSION.set_context('mydate', 'given_date', to_char(p_date,'dd/mm/yyyy'));

I am getting the error in the view, when I run my form with that date

CREATE OR REPLACE VIEW QC_DAILY_VW
(DEPT_ID, QC_TYPE_ID, QC_SUBTYPE_ID, QC_DATES, CONTROL_NO)
AS
select a.dept_id,a.qc_type_id,
      a.qc_subtype_id,b.qc_dates,b.control_no
from qc_dept_subtypes a,qc_daily b
where a.dept_id = b.dept_id(+)
and a.qc_subtype_id = b.qc_subtype_id(+)
and a.qc_type_id = b.qc_type_id(+)
and b.qc_dates(+) = to_date(sys_context('mydate','given_date'),'dd-mon-yyyy')

so,  the view is dd-mon-yyyy and the package is dd/mm/yyyy ?

and, if the form is generating the error, are you sure you're getting the error in the view
and not in some before-trigger  that calls the package?

I changed the view and its working now. Thanks

Thanks a lot