Ora 00911

[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..

[Sainath.Srinivasan]

Maybe you need a To_Date rather than %DateTimeIn

[vijay.barani]

Hi Sainath,
Thanks for the response,Now I got another issue :
ORA 01861 : Literal does not match format string...

[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.

[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.

[DS_SUPPORT]

Can you paste the SQL, and the value for LastModifiedDateTime.

[vijay.barani]

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

[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")

[Sainath.Srinivasan]

And where is the to_date ?

[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?

[vijay.barani]

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.

[vijay.barani]

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

[chulett]

OK, not syntax I've ever seen before. What happens if you remove the "%" from the query?

[Sainath.Srinivasan]

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

[chulett]

What, you don't like the "guess what I am thinking" game? So many opportunities to play it here.