Oracle Sentence - JDBC Sender - TO_Date

[Former Member]

Hi. Dear Experts

I have an Scenario.  JDBC (Oracle)- Proxy.

     On Sender CC I have an SQL Sentence like. 

       Select  Field1,Field2,Field3 from Tabla1 Where Date_F <= TO_DATE(sysdate,'yyyy/mm/dd') and Flag = 1

     With the above sentence PI doesn't select any records. but I tested the same query on oracle client. and return 3 records.

      Is there any problem with TO_DATE sentence?

Regards.

[baskar_gopalakrishnan2 · Accepted solution]

To better understand the syntax for different date values , please check the following site

http://www.dba-oracle.com/f_to_date.htm

in your case this syntax might work...

ex:

to_date('2007/05/31','YYYY/MM/DD')

Also go to jdbc channel and in the advanced mode set the following

logSQLStatement = true

and see the log for this statement during the test.

[Former Member]

Hi.

The problem is solved..

According sintax , is no necessary to convert sysdate with to_date is implicit.

Former Member I set the  logSQLStatement = true but I don't see any logs on Sender JDBC Channel , only works for me on JDBC Receiver, is it ok?

Regards.

[nabendu_sen]

Hi Fabrizio,

Can you please check like this 'TO_DATE('20080118','YYYYMMDD').

[Former Member]

Hi.

I tried with that but is not working.

Regards

[nabendu_sen]

Hi Fabrizio,

There may be some issue with the spaces in the SELECT query of your PI adapter.

[Former Member]

syntax is correct, check the log for exact which date it is passing to oracle in sysdate (if required increase trace level), also check, if oracle has date and time both in the same field, it may create issue.

[Former Member]

Hi. SAP Tech

No exist log for Adapter Engine because. The sentence select does't pick up any records so the AE don't process anything is like similar flat file if directory is empty no exis any process.

[Former Member]

Oracle has the facility of logging incoming sql , please ask your DB guy to check that, which exact sql it is coming from PI...