[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-sqlplus-l
Subject: RE: Q about SQL Developer ...
From: "Michelle, Suzanne" <Suzanne.Michelle () nyct ! com>
Date: 2011-11-11 0:16:59
Message-ID: 0B9952A150DC7F4D9341F981BDD9B41F6F7985 () NYCTEXVS06 ! transit ! nyct ! com
[Download RAW message or body]
The Get_a_Value function does a variety of things ... and tracing them
all out to be sure I don't break something _else_ is not in current
timeline. The function gets anything from anywhere in the database ...
it started life requesting any of 3 fields from each of three different
tables ... where the "use-this-key" value was analyzed to determine from
which table to retrieve the requested field ... and none of those fields
were date fields.
get_a_value(Use_this_key, 'Get_this_field')
Later on, we adapted it to have more parameters, and a 2nd part, that
builds an even more dynamic string than the first part ... and handle a
multi-part keys (and select from far more than the original 3 tables)
...
It gets used in places where we don't want to break stride for a select
statement (hand me a ... oh yeah, that wrench over there ...). And,
since it is all dynamic, it doesn't cause mutation problems since it is
not related to anything ...
We have one master audit table per sub-master section of our database
... when we throw child records in there, it turns out to be useful to
grab select real master's date fields, so when the child is reviewed,
relevant stub data is on that record (e.g., the master's status and
in-effect dates at the time the child audit record got created).
I had assumed the grab and enter was working (and, as I said, from SQL
plus, no prob) ... but then I went to adjust some test data (for a
complete other purpose) in the "new table" with the newest trigger ...
and ... bbbblllaaatttt, date err!!!
Thanks for your time and suggestions. I consider the problem solved and
at least 2 lessons learned.
Suzanne (@ 2Bwy A13.32)
desk: 646-252-8663, cell: 347-907-1125
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Justin Cave
(DDBC)
Sent: Thu, Nov 10, 2011 18:59
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
How is the GET_A_VALUE function declared? I'm assuming that the second
parameter is declared as a VARCHAR2 in which case the symptoms you're
describing would make sense. If you want to be able to pass a DATE to
that function, it would generally make sense to declare the second
parameter to the function as a DATE or, if you want to pass either a
string or a date, to overload the function so that there is one version
that accepts a string and one that accepts a date (note that overloading
requires a package)
CREATE PACKAGE package_name
FUNCTION get_a_value( p_key IN NUMBER, p_val IN DATE )
RETURN varchar2;
FUNCTION get_a_value( p_key IN NUMBER, p_val IN VARCHAR2 )
RETURN varchar2
END;
At a minimum, this would mean that you'd only have to put the to_char
call in one place (the date overload version of the function). If
GET_A_VALUE returns a DATE if it is passed in a DATE, it would be even
better to declare the date-overloaded method to return a DATE so that
you don't have to wrap the call in a TO_DATE, i.e.
CREATE PACKAGE package_name
FUNCTION get_a_value( p_key IN NUMBER, p_val IN DATE )
RETURN DATE;
FUNCTION get_a_value( p_key IN NUMBER, p_val IN VARCHAR2 )
RETURN varchar2
END;
Justin
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Michelle,
Suzanne
Sent: Thursday, November 10, 2011 6:49 PM
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
Ah! Yes, well ... I found the problem! Even better, my test fix worked!
The code was saying this ...
to_date(Get_a_value(Use_this_key, 'Get_this_field'),'outer_date_format')
And the problem lies with our embedded function "Get_a_value" ... it was
fine, so long as 'Get_This_field' was _NOT_ a date ... if it was a date,
it would return the date as text in the current NLS format. AND THEN ...
if the 'outer_date_format' did not agree with it, Bob's yer uncle,
there's the error message.
SOoooo ... what my code needs to say when a DATE is being requested ...
to_date(Get_a_value(Use_this_key,
'to_char(Get_this_field,''inner_date_format'')'),'outer_date_format')
... and make the inner format match the outer format ... and no more SQL
Dev error.
I didn't see the error from SQL plus, because the whole deal had the
_same_ format at that level.
But in SQL Dev, where I had changed the default NLS for display purposes
... now they were different.
I didn't see the error _before_ because I didn't consider looking for
it.
But fortunately ... it is only in new code since this June and easily
fixed.
OTOH ... a whole new chasm of potential errors has just opened up at my
feet ... so it goes.
Suzanne (@ 2Bwy A13.32)
desk: 646-252-8663, cell: 347-907-1125
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Justin Cave
(DDBC)
Sent: Thu, Nov 10, 2011 10:53
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
Doesn't the error stack tell you where the exception is being thrown?
Or do the deeper levels of code have exception handlers that are hiding
where the actual exception is coming from? If that's the case, you may
want to eliminate those exception handlers (either permanently or just
temporarily to debug) so that you get the complete stack trace.
Justin
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Michelle,
Suzanne
Sent: Thursday, November 10, 2011 10:23 AM
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
Hmmm ... that's what I thought, Justin, thanks.
I checked, and so far as I can find, anywhere I am assigning a string to
a date, I seem to include a format mask ... there must be something I
missed somewhere at a deeper level (in the case of this code, thing X
calls thing Y (which may or may not call thing Z) and the audit trail
records result of thing X)
(but error could have occurred further down (at Y or Z) and propagated
up ... I have used these levels in other code, and it ran fine, or so I
thought, but I also never "immediately" had reason to check it with SQL
Developer).
I will keep digging.
Suzanne (@ 2Bwy A13.32)
desk: 646-252-8663, cell: 347-907-1125
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Justin Cave
(DDBC)
Sent: Wed, Nov 09, 2011 16:43
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
There is no problem assigning a date to a date-- that's what you should
be doing. The problem comes when you assign a string to a date and
Oracle has to implicitly convert the string to a date that NLS settings
come in to play.
In my example, I was assigning a string (i.e. '01-FEB-11') to a date
variable. That forced Oracle to do an implicit conversion which created
opportunities for NLS settings to create unexpected results or errors.
There would be no problem if I assigned a date (i.e. :new.dt := sysdate)
to the date variable. There would have been no problem if I had
explicitly converted the string to a date (i.e. :new.dt := to_date(
'01-FEB-11', 'DD-MON-RR')). There would have been no problem if I had
use a date literal rather than a string (i.e. :new.dt := date
'2011-02-01'). It is only when I assigned a string to a date that I ran
into issues.
You should not convert a date to a string and back to a date before
assigning it to a date variable-- that's just more work and more
opportunities to make a typo that will cause problems.
Justin
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Michelle,
Suzanne
Sent: Wednesday, November 09, 2011 4:33 PM
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
Thanks, Justin, for taking the time to answer.
Well, sort of yes, some implicit dates ... but the code "was the same"
...
So, are you saying I should _NOT_ do something like this ...
My_Date Date := sysdate;
But rather ... I should always do something like ...
My_Date := to_date(to_char(sysdate,[date format of choice]),[same
format]);
... so that the NLS translation never interferes?
I guess I had always thought that if you were assigning date information
to dates, it would just "be a date," irrespective of translation into
anything ... it was only when I _examined_ the actual value of the date,
that it mattered ... (and I have no idea if that is an odd assumption to
make ... I just always have assumed this).
Thanks again.
Suzanne (@ 2Bwy A13.32)
desk: 646-252-8663, cell: 347-907-1125
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Justin Cave
(DDBC)
Sent: Wed, Nov 09, 2011 16:10
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
Are you doing any implicit string to date conversions in your code? If
so, the code in your trigger will use whatever the current session's
NLS_DATE_FORMAT happens to be. That can easily lead to different users,
different sessions, and different tools getting different results and
behaviors because they happen to have different NLS settings. If you
always do explicit conversions, you shouldn't have these problems.
For example, if I have a simple trigger that sets a DATE column using
implicit string to date conversion, you can get *very* unexpected
results if the session's NLS_DATE_FORMAT happens not to match what the
trigger was expecting. In this example, the trigger assumes that the
NLS_DATE_FORMAT is DD-MON-RR while the actual date format is YYYY-MM-DD.
That causes the date that is inserted to be Feb 11, 0001 rather than Feb
1, 2011.
SQL> alter session set nls_date_format = 'YYYY-MM-DD';
Session altered.
SQL> create table t( col1 number, dt date );
Table created.
SQL> create trigger trg_t
2 before insert on t
3 for each row
4 declare
5 begin
6 :new.dt := '01-FEB-11';
7 end;
8 /
Trigger created.
SQL> insert into t( col1 ) values( 1 );
1 row created.
SQL> select * from t;
COL1 DT
---------- ----------
1 0001-02-11
You can also end up with odd errors if the conversion doesn't succeed.
But then when you change the NLS_DATE_FORMAT, the same insert seems to
work.
SQL> alter session set nls_date_format = 'DD-MON-RR' ;
Session altered.
SQL> drop table t;
Table dropped.
SQL> create table t( col1 number, dt date );
Table created.
SQL> create trigger trg_t
2 before insert on t
3 for each row
4 declare
5 begin
6 :new.dt := '11-11-11';
7 end;
8 /
Trigger created.
SQL> insert into t( col1 ) values( 1 );
insert into t( col1 ) values( 1 )
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at "SCOTT.TRG_T", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_T'
SQL> alter session set nls_date_format = 'RR-MM-DD';
Session altered.
SQL> insert into t( col1 ) values( 1 );
1 row created.
If the code was always doing an explicit TO_DATE (or if it was using
date literals rather than strings), the session's NLS_DATE_FORMAT would
be irrelevant and the code would work for everyone. That's what you
realistically want to happen.
Justin
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Michelle,
Suzanne
Sent: Wednesday, November 09, 2011 3:53 PM
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: RE: Q about SQL Developer ...
HC said this ...
I don't understand the question. The trigger is running in the database,
right?
It's not running from sqlplus etc.
so you mean you are manipulating the data from sqlplus,sql developer in
such a way that it causes the trigger to react and fail?
.... and the answer is, yes ... behavior for same code was different in
SQLplus and in SQL Developer.
(Data entry would also succeed from Oracle form on top of a view ... ISO
trigger on view fired (1), attempted entry of data into table (2),
before trigger fired and set footprint details (3), record entered (4),
after trigger fired and wrote audit trail record (5), end of story ...
entering from SQL plus, straight into table starts at (2), and would
succeed. Attempted to enter data in table via SQLDeveloper, the after
trigger would generate a date error, and all would fail.)
When I went to preferences in SQL Developer, changed my NLS setting back
to default (Tools, preferences, Default Values button) ... then the code
worked in SQLDev.
It was a very confusing hour. SQL Developer said there was a date error,
but yet a brute force "Insert X Y Z into table Q Values (1,2,3)" at SQL
plus would succeed with no errors.
I am wondering why SQL Developer would respond this way?
Where my code had date entries, it had date formats.
What I have _not_ done yet is test other entry points on other tables
w/views and similar ISO triggers, in a similar manner to see if the NLS
stuff affected them all along, and I just never noticed before.
Suzanne (@ 2Bwy A13.32)
desk: 646-252-8663, cell: 347-907-1125
-----Original Message-----
From: odtug-sqlplus-l-bounce@fatcity.com
[mailto:odtug-sqlplus-l-bounce@fatcity.com] On Behalf Of Michelle,
Suzanne
Sent: Tue, Nov 08, 2011 19:53
To: ODTUG-SQLPLUS-L@fatcity.com
Subject: Q about SQL Developer ...
I am using this version ...
Java(TM) Platform 1.6.0_11
Oracle IDE 3.0.04.34
Versioning Support 3.0.04.34
... I realize there are a zillion things to do with this tool, but I use
it as my library, my inspection agent, my data reviewer ...
I put a trigger on a table. The trigger executes perfectly from SQLplus.
Not so from SQL Developer ... it kept giving me a "not a valid date"
error.
... until I set my "preferences" with respect to NLS back to defaults
(as a coworker suggested).
Then it worked ...
Why is that? In the trigger code, various dates are bought and sold,
always with a format ... I never had a trigger do this before.
I had set my dates a certain way, just so I could see them in that way
on the screen.
Is there a way to change screen display of dates so I can see them as I
like them (American style mm/dd/yyyy) ...
but not have NLS problems with date entry?
Thanks.
Suzanne Michelle
New Yawk, New Yawk
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic