?
Solved

DBMS_SESSION.set_context

Posted on 2010-04-08
20
Medium Priority
?
717 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:anumoses
  • 10
  • 10
20 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 30115119
put in a variable instead of a constant wherever you are invoking that
0
 
LVL 6

Author Comment

by:anumoses
ID: 30115250
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 30115425
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 30115645
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
 
LVL 6

Author Comment

by:anumoses
ID: 30115802
Date format is 04/06/2010
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 30115968
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
 
LVL 6

Author Comment

by:anumoses
ID: 30116010
date  in the format dd/mm/rrrr
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 30116166
"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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 30116296
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
 
LVL 6

Author Comment

by:anumoses
ID: 30119408
I get not a valid date error

ORA-01843: not a valid month
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 30119716
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
 
LVL 6

Author Comment

by:anumoses
ID: 30120690
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 30120762
you changed the view to dd/mm/yyyy

but the package is dd-mon-yyyy?

0
 
LVL 6

Author Comment

by:anumoses
ID: 30120861
Sorry it was my mistake (typo)

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

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 30121034
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
 
LVL 6

Author Comment

by:anumoses
ID: 30121094
I am getting the error in the view, when I run my form with that date
0
 
LVL 6

Author Comment

by:anumoses
ID: 30121164
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 30121682
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
 
LVL 6

Author Comment

by:anumoses
ID: 30121694
I changed the view and its working now. Thanks
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 31712293
Thanks a lot
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question