DBMS_SESSION.set_context

DBMS_SESSION.set_context('mydate', 'given_date', '06-apr-2010');
Instead of hardcoding the last parameter the date as 06-apr-2010, I want this to be passed as parameter for any date. How to do that. Now it pulls data only for 6th april, but I need to get data for any date that I give as input.
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
put in a variable instead of a constant wherever you are invoking that
0
anumosesAuthor Commented:
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;
0
sdstuberCommented:
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;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
Don't put your procedure parameter in quotes, that would make it a string literal.

Also, I've assumed you wanted your parameter to be a string in the dd-MON-yyyy format.
if you want your parameter to be a DATE then modify the procedure something like this...


PROCEDURE test_ctx(p_date in date) IS  
       BEGIN            
         DBMS_SESSION.set_context('mydate', 'given_date', to_char(p_date,'dd-mon-yyyy'));
      END;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Date format is 04/06/2010
0
sdstuberCommented:
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
0
anumosesAuthor Commented:
date  in the format dd/mm/rrrr
0
sdstuberCommented:
"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
0
sdstuberCommented:
also, I recommend NOT using the rrrr format, but instead use yyyy.

if you have 4 digit years then rrrr doesn't give you any advantage

if you have 2 digit years then rrrr is simply a hack for the y2k bug and you should force your users to use 4 digit years.
0
anumosesAuthor Commented:
I get not a valid date error

ORA-01843: not a valid month
0
sdstuberCommented:
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?

0
anumosesAuthor Commented:
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)
0
sdstuberCommented:
you changed the view to dd/mm/yyyy

but the package is dd-mon-yyyy?

0
anumosesAuthor Commented:
Sorry it was my mistake (typo)

 DBMS_SESSION.set_context('mydate', 'given_date', to_char(p_date,'dd/mm/yyyy'));
0
sdstuberCommented:
are you getting the error when you query the view?

or are you getting the error when you call the test_ctx procedure?

if the former, please repost the view so I can compare to your latest version of the package.
if the latter, then how are you calling the procedure?  are you passing it in a date? or are you passing it in a string?

I'm guessing it's the latter, and you are probably passing in a string when it expects a date.

'04/06/2010'  is not a date,  '08-apr-2010' isn't a date either.  those are both strings.
0
anumosesAuthor Commented:
I am getting the error in the view, when I run my form with that date
0
anumosesAuthor Commented:
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')
0
sdstuberCommented:
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?
0
anumosesAuthor Commented:
I changed the view and its working now. Thanks
0
anumosesAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.