Page 1 of 2
Ora 00911
Posted: Thu Aug 06, 2009 2:10 am
by vijay.barani
HI,
I have an issue.When i use Oracle OCI instead of DRS i am getting the error "ORA 00911,Invalid character".I observed that this is only because of the following code.
Code: Select all
AND (PS_JRNL_HEADER.LASTUPD_EW_DTTM > %DateTimeIn('#LastModifiedDateTime#')
OR PS_JRNL_HEADER.LASTUPD_EW_DTTM Is Null
OR PS_JRNL_LN.LASTUPD_EW_DTTM > %DateTimeIn('#LastModifiedDateTime#')
OR PS_JRNL_LN.LASTUPD_EW_DTTM Is Null)
May I know where I am going wrong.
THe same code If i write in DRS,It so OK.The error is coming only if I use Oracle OCI..
Posted: Thu Aug 06, 2009 2:19 am
by Sainath.Srinivasan
Maybe you need a To_Date rather than %DateTimeIn
Posted: Thu Aug 06, 2009 2:49 am
by vijay.barani
Hi Sainath,
Thanks for the response,Now I got another issue :
ORA 01861 : Literal does not match format string...
Posted: Thu Aug 06, 2009 2:50 am
by DS_SUPPORT
For TO_DATE, what is the format you have given, and in what format, you are getting the Date.
If i remeber correctly, In PSFT the LastModifiedDatetime will be passed in YYYY-MM-DD HH24:MI:SS. So give the format string like this.
Posted: Thu Aug 06, 2009 2:58 am
by vijay.barani
HI DS,
I have given the same way.But I wonder why the issue coming .I had thrice and also cheked datatype also.
Posted: Thu Aug 06, 2009 3:04 am
by DS_SUPPORT
Can you paste the SQL, and the value for LastModifiedDateTime.
Posted: Thu Aug 06, 2009 3:19 am
by vijay.barani
DS_SUPPORT wrote:Can you paste the SQL, and the value for LastModifiedDateTime.
Please find the last part of the query..I hope this would sufficient.
Code: Select all
,.............LTRIM(RTRIM(PS_JRNL_HEADER.LEDGER)),TO_CHAR(PS_JRNL_HEADER.UNPOST_JRNL_DATE, 'YYYY-MM-DD HH24:MI:SS'),LTRIM(RTRIM(PS_JRNL_HEADER.JRNL_HDR_STATUS)),TO_CHAR(PS_JRNL_HEADER.POSTED_DATE, 'YYYY-MM-DD HH24:MI:SS'),RESOURCE_TYPE,RESOURCE_CATEGORY FROM #$OWS_SCHEMA#PS_JRNL_LN PS_JRNL_LN,#$OWS_SCHEMA#PS_JRNL_HEADER PS_JRNL_HEADER WHERE PS_JRNL_HEADER.BUSINESS_UNIT = PS_JRNL_LN.BUSINESS_UNIT
AND PS_JRNL_HEADER.SRC_SYS_ID = PS_JRNL_LN.SRC_SYS_ID
AND PS_JRNL_HEADER.JOURNAL_ID = PS_JRNL_LN.JOURNAL_ID
AND PS_JRNL_HEADER.JOURNAL_DATE = PS_JRNL_LN.JOURNAL_DATE
AND PS_JRNL_HEADER.UNPOST_SEQ = PS_JRNL_LN.UNPOST_SEQ
AND (PS_JRNL_HEADER.LASTUPD_EW_DTTM > %DateTimeIn('#LastModifiedDateTime#')
OR PS_JRNL_HEADER.LASTUPD_EW_DTTM Is Null
OR PS_JRNL_LN.LASTUPD_EW_DTTM > %DateTimeIn('#LastModifiedDateTime#')
OR PS_JRNL_LN.LASTUPD_EW_DTTM Is Null)
Value is which i have entered is 1900-01-01 00:00:00
Posted: Thu Aug 06, 2009 3:34 am
by DS_SUPPORT
If you are using OCI stage directly, instead of
Code: Select all
%DateTimeIn('#LastModifiedDateTime#')
you have to use
Code: Select all
To_Date('#LastModifiedDateTime#',"YYYY-MM-DD HH24:MI:SS")
Posted: Thu Aug 06, 2009 3:35 am
by Sainath.Srinivasan
And where is the to_date ?
Posted: Thu Aug 06, 2009 5:53 am
by chulett
Assuming all of your parameters are properly resolving, the 'illegal character' is the percent sign. What is
%DateTimeIn() supposed to be, a stored function?
Posted: Thu Aug 06, 2009 6:01 am
by vijay.barani
Sainath.Srinivasan wrote:And where is the to_date ? ...
Hi Sainath and DS,
This is my mistake.Anyhow I got a new error again...
"Oracle Error - OCI_INVALID_HANDLE"
How may i handle this.
Posted: Thu Aug 06, 2009 6:04 am
by vijay.barani
chulett wrote:Assuming all of your parameters are properly resolving, the 'illegal character' is the percent sign. What is
%DateTimeIn() supposed to be, a stored function?
...
Yeah, '%DateTimeIn()' is a stored function
Posted: Thu Aug 06, 2009 6:26 am
by chulett
OK, not syntax I've ever seen before. What happens if you remove the "%" from the query?
Posted: Thu Aug 06, 2009 6:37 am
by Sainath.Srinivasan
vijay.barani wrote:Sainath.Srinivasan wrote:And where is the to_date ? ...
Hi Sainath and DS,
This is my mistake.Anyhow I got a new error again...
"Oracle Error - OCI_INVALID_HANDLE"
How may i handle this.
Without you posting the full SQL it will not be possible for anyone to assist you efficiently. All will be guessing.
It is more like "guess what I am thinking" game.
You need to provide
1.) The SQL
2.) Parameters, their datatypes, values passed
3.) Stage used (assuming it is only OCI now)
4.) Error received
Posted: Thu Aug 06, 2009 6:54 am
by chulett
What, you don't like the "guess what I am thinking" game?
So many opportunities to play it here.
