How to make the workaround for ORA-01841 work?

[heleenw]

Hi everyone,

I am running into the ORA-01841 error. It says: ORA-01841 (full) year must be between -4713 and +9999, and not be 0

A formatting issue, I suspect. Hot fix applying takes time.

So meanwhile I started figuring out if I can apply the workaround.

One could use DBCONDITION=

That is described in

61621 - Performance issues or an ORA-01841 error might occur when querying a DBMS table (sas.com)

I am looking up if I can find, anywhere, some example code of someone who has succesfully used the DBCONDITION= option in order to get rid of the ORA-0141 error. I couldn't find any code anywhere except here on the viya 3.5 forum - bit weird since I am using EG v7 but anyhow here on the viya 3.5 forum I found example-code using DBCONDITION=:

SAS Help Center: DBCONDITION= Data Set Option

Does anybody know more example-code ? Thanks so much - it may save time...

Best regards,

heleen.

[ballardw]

It never hurts to show the code generating any error messages, along with all the notes and messages from a procedure call.

Since this is an Oracle error you might also want to include the connection settings you are using (xxx out any password and id values).

[jimbarbour]

That DBCONDITION option looks very interesting.  It seems to be making the query be a partially implicit pass through and a partially explicit pass through type query.

One option, if you're using a Libname and SAS SQL that is being implicitly passed through to Oracle is to convert it to an entirely explicit pass through query, which would be coded in Oracle's syntax, not SAS's.

However, for starters, in  addition to @ballardw's suggestions, perhaps you could turn on the following options, re-run, and post the log?

OPTIONS 	SASTRACE		=	",,,ds";
OPTIONS 	SASTRACELOC		=	SASLOG;
OPTIONS 	NOSTSUFFIX;
OPTIONS 	MsgLevel		=	I;
OPTIONS 	DEBUG			=	DBMS_SELECT;

Jim

[heleenw]

I had a bit of luck and a bit of help here...
Sorry for not posting the code, I need to ask the person who wrote this, if I can...
But, gentlemen; I changed my prompt in the EG-PromptManager to a timestamp instead date which it previously had, and now I have output instead of having my ORA001841 error. Thanks ever so much...
Of course I am still curious to see/know if any person(s) have succesfully applied the DBCONDITION=
And what that would look like.
What I think is if you use DBCONDITION (which probably I will not need to do, but let's see since my prompt of course got changed and prompt-values also get used elsewhere so I will need to thoroughly test) then you need to really use oracle-syntax at the very least inside the (dbcondition= WHERE part) and I don't even know if Oracle should compare a date from within a table, against a prompt-value generated by SAS - in this case I also have doubts how I could succesfully refer to that prompt-value in oracle-syntax.
Anyhow - now that I changed the prompt - it does complete the comparison without error for now so I am quite happy.

[jimbarbour · Accepted solution]

If you're concerned about the validity of things like date-time values (a very valid concern), you might try what I suggested in my earlier reply:  Switching to an explicit pass through form of SQL.  With explicit pass through, you have a lot more control over what is being passed to the database.

Regarding datetime stamps, I seem to recall something about a DATETIME2=YES setting that specifies a datetime precision.

Jim