DSXchange

Posted: **Thu Aug 06, 2009 7:17 am**

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

Craig,

You are either very happy today or in verge of a total breakdown.

Posted: **Thu Aug 06, 2009 11:52 pm**

Hi Sainath,
Please find the full query which i an using

SELECT LTRIM(RTRIM(PS_JRNL_LN.BUSINESS_UNIT)),LTRIM(RTRIM(PS_JRNL_LN.JOURNAL_ID)),TO_CHAR(PS_JRNL_LN.JOURNAL_DATE, 'YYYY-MM-DD HH24:MI:SS'),PS_JRNL_LN.UNPOST_SEQ,PS_JRNL_LN.JOURNAL_LINE,LTRIM(RTRIM(PS_JRNL_LN.LEDGER)),LTRIM(RTRIM(PS_JRNL_LN.SRC_SYS_ID)),LTRIM(RTRIM(PS_JRNL_LN.ACCOUNT)),LTRIM(RTRIM(PS_JRNL_LN.ALTACCT)),LTRIM(RTRIM(PS_JRNL_LN.DEPTID)),LTRIM(RTRIM(PS_JRNL_LN.OPERATING_UNIT)),LTRIM(RTRIM(PS_JRNL_LN.PRODUCT)),LTRIM(RTRIM(PS_JRNL_LN.FUND_CODE)),LTRIM(RTRIM(PS_JRNL_LN.CLASS_FLD)),LTRIM(RTRIM(PS_JRNL_LN.PROGRAM_CODE)),LTRIM(RTRIM(PS_JRNL_LN.BUDGET_REF)),LTRIM(RTRIM(PS_JRNL_LN.AFFILIATE)),LTRIM(RTRIM(PS_JRNL_LN.AFFILIATE_INTRA1)),LTRIM(RTRIM(PS_JRNL_LN.AFFILIATE_INTRA2)),LTRIM(RTRIM(PS_JRNL_LN.CHARTFIELD1)),LTRIM(RTRIM(PS_JRNL_LN.CHARTFIELD2)),LTRIM(RTRIM(PS_JRNL_LN.CHARTFIELD3)),LTRIM(RTRIM(PS_JRNL_LN.PROJECT_ID)),LTRIM(RTRIM(PS_JRNL_LN.BOOK_CODE)),LTRIM(RTRIM(PS_JRNL_LN.GL_ADJUST_TYPE)),LTRIM(RTRIM(PS_JRNL_LN.SCENARIO)),LTRIM(RTRIM(PS_JRNL_LN.CURRENCY_CD)),LTRIM(RTRIM(PS_JRNL_LN.STATISTICS_CODE)),PS_JRNL_LN.MONETARY_AMOUNT,PS_JRNL_LN.STATISTIC_AMOUNT,LTRIM(RTRIM(PS_JRNL_LN.FOREIGN_CURRENCY)),PS_JRNL_LN.FOREIGN_AMOUNT,LTRIM(RTRIM(PS_JRNL_LN.JRNL_LINE_SOURCE)),LTRIM(RTRIM(PS_JRNL_LN.LINE_DESCR)),LTRIM(RTRIM(PS_JRNL_HEADER.DESCR254)),LTRIM(RTRIM(PS_JRNL_HEADER.SOURCE)),LTRIM(RTRIM(PS_JRNL_HEADER.ADJUSTING_ENTRY)),PS_JRNL_HEADER.FISCAL_YEAR,PS_JRNL_HEADER.ACCOUNTING_PERIOD,LTRIM(RTRIM(PS_JRNL_HEADER.LEDGER_GROUP)),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') 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  > To_Date('#LastModifiedDateTime#','YYYY-MM-DD HH24:MI:SS')
OR   PS_JRNL_HEADER.LASTUPD_EW_DTTM  Is  Null 
OR   PS_JRNL_LN.LASTUPD_EW_DTTM  > To_Date('#LastModifiedDateTime#','YYYY-MM-DD HH24:MI:SS')
OR   PS_JRNL_LN.LASTUPD_EW_DTTM  Is  Null)

The stage which I am using is Oracle OCI.
The error i got is

Code: Select all

J_Fact_PS_F_JOURNAL_E_TEST1..IPC_SRC.IDENT1: Oracle Error - OCI_INVALID_HANDLE

Posted: **Fri Aug 07, 2009 12:32 am**

Hi,

Can you post in some more information of the error surrounding it?

This is could be a problem of the datatype from the source.

Posted: **Fri Aug 07, 2009 1:28 am**

SHARAD123 wrote:Hi,

Can you post in some more information of the error surrounding it?

This is could be a problem of the datatype from the source.

Code: Select all

Item #: 240
   Event ID: 239
   Message: J_Fact_PS_F_JOURNAL_E_TEST1..OCI_PS_JRNL_LN: SELECT LTRIM(RTRIM(PS_JRNL_LN.BUSINESS_UNIT)), LTRIM(RTRIM(PS_JRNL_LN.JOURNAL_ID)), TO_CHAR(PS_JRNL_LN.JOURNAL_DATE, 'YYYY-MM-DD HH24:MI:SS'), PS_JRNL_LN.UNPOST_SEQ, PS_JRNL_LN.JOURNAL_LINE, LTRIM(RTRIM(PS_JRNL_LN.LEDGER)), LTRIM(RTRIM(PS_JRNL_LN.SRC_SYS_ID)), LTRIM(RTRIM(PS_JRNL_LN.ACCOUNT)), LTRIM(RTRIM(PS_JRNL_LN.ALTACCT)), LTRIM(RTRIM(PS_JRNL_LN.DEPTID)), LTRIM(RTRIM(PS_JRNL_LN.OPERATING_UNIT)), LTRIM(RTRIM(PS_JRNL_LN.PRODUCT)), LTRIM(RTRIM(PS_JRNL_LN.FUND_CODE)), LTRIM(RTRIM(PS_JRNL_LN.CLASS_FLD)), LTRIM(RTRIM(PS_JRNL_LN.PROGRAM_CODE)), LTRIM(RTRIM(PS_JRNL_LN.BUDGET_REF)), LTRIM(RTRIM(PS_JRNL_LN.AFFILIATE)), LTRIM(RTRIM(PS_JRNL_LN.AFFILIATE_INTRA1)), LTRIM(RTRIM(PS_JRNL_LN.AFFILIATE_INTRA2)), LTRIM(RTRIM(PS_JRNL_LN.CHARTFIELD1)), LTRIM(RTRIM(PS_JRNL_LN.CHARTFIELD2)), LTRIM(RTRIM(PS_JRNL_LN.CHARTFIELD3)), LTRIM(RTRIM(PS_JRNL_LN.PROJECT_ID)), LTRIM(RTRIM(PS_JRNL_LN.BOOK_CODE)), LTRIM(RTRIM(PS_JRNL_LN.GL_ADJUST_TYPE)), LTRIM(RTRIM(PS_JRNL_LN.SCENARIO)), LTRIM(RTRIM(PS_JRNL_LN.CURRENCY_CD)), LTRIM(RTRIM(PS_JRNL_LN.STATISTICS_CODE)), PS_JRNL_LN.MONETARY_AMOUNT, PS_JRNL_LN.STATISTIC_AMOUNT, LTRIM(RTRIM(PS_JRNL_LN.FOREIGN_CURRENCY)), PS_JRNL_LN.FOREIGN_AMOUNT, LTRIM(RTRIM(PS_JRNL_LN.JRNL_LINE_SOURCE)), LTRIM(RTRIM(PS_JRNL_LN.LINE_DESCR)), LTRIM(RTRIM(PS_JRNL_HEADER.DESCR254)), LTRIM(RTRIM(PS_JRNL_HEADER.SOURCE)), LTRIM(RTRIM(PS_JRNL_HEADER.ADJUSTING_ENTRY)), PS_JRNL_HEADER.FISCAL_YEAR, PS_JRNL_HEADER.ACCOUNTING_PERIOD, LTRIM(RTRIM(PS_JRNL_HEADER.LEDGER_GROUP)), 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') FROM epsysadm.PS_JRNL_LN PS_JRNL_LN,epsysadm.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  > To_Date('1900-01-01','YYYY-MM-DD HH24:MI:SS')

OR   PS_JRNL_HEADER.LASTUPD_EW_DTTM  Is  Null 

OR   PS_JRNL_LN.LASTUPD_EW_DTTM  > To_Date('1900-01-01','YYYY-MM-DD HH24:MI:SS')

OR   PS_JRNL_LN.LASTUPD_EW_DTTM  Is  Null)

   Item #: 241
   Event ID: 240
   Message: Attempting to Cleanup after ABORT raised in stage J_Fact_PS_F_JOURNAL_E_TEST1..HASH_PS_BU_LED_GRP_TBL_F_JOURNAL.IDENT2

   Item #: 242
   Event ID: 241
   Message: J_Fact_PS_F_JOURNAL_E_TEST1..HASH_PS_D_PATTERN_DAY_F_JOURNAL_E.DRS_PS_D_PATTERN_DAY: Write link private caching enabled, cache size (512MB)

   Item #: 243
   Event ID: 242
   Message: J_Fact_PS_F_JOURNAL_E_TEST1..IPC_SRC.IDENT1: Oracle Error - OCI_INVALID_HANDLE

   Item #: 244
   Event ID: 243
   Message: Attempting to Cleanup after ABORT raised in stage J_Fact_PS_F_JOURNAL_E_TEST1..IPC_SRC.IDENT1

   Item #: 245
   Event ID: 244
   Message: J_Fact_PS_F_JOURNAL_E_TEST1..OCI_PS_D_JRNL_SOURCE: Using NLS map MS1252

Posted: **Fri Aug 07, 2009 4:24 am**

Try running this query in SQL+ or any SQL client, and see if you are able to execute it.
And if any other job is working fine using OCI stage.

Posted: **Fri Aug 07, 2009 4:35 am**

Did you search?

Are you using a LongVarchar or LOB ?

Are there any special characgers in the data?

Posted: **Fri Aug 07, 2009 5:16 am**

DS_SUPPORT wrote:Try running this query in SQL+ or any SQL client, and see if you are able to execute it.
And if any other job is working fine using OCI stage.

The query runs good in SQL,And i had successfully used OCI Stage in other jobs also.

Posted: **Fri Aug 07, 2009 5:21 am**

Sainath.Srinivasan wrote:Did you search?

Are you using a LongVarchar or LOB ?

Are there any special characgers in the data? ...

Hi Sainath,
No LongVarchar or LOB are being used
No special characters in the data.

Posted: **Fri Aug 07, 2009 5:44 am**

As noted, this can be a problem when sourcing any kind of LOB field or a LONG from a database that still uses that evil data type. If you are using none of them, then perhaps it is a bug that was reported as fixed in the 7.5.3 release - check with your official support provider:

ORACLE OCI stage problem (ecase 80361)
------------------------
ORACLE OCI stage - jobs abort with error "Oracle Error - OCI_INVALID_HANDLE'
when using LONGVARCHAR type.

Anything in your job declared as a LONGVARCHAR? If yes, what kind of column are you using it for if not a CLOB, BLOB or LONG?

Posted: **Fri Aug 07, 2009 6:35 am**

Remove all your columns returned from Select and do a

Select 1 x
FROM ........

and see what happens.

Build the columns from there. Or do a viceversa.

Posted: **Tue Aug 11, 2009 4:40 am**

hi,
Very strangly, I got the issue resolved.I had increased(double the size) the datatype size and ran the job,then it went successfully.Now again i placed original values and found no warning or error.

Finally i found there must be an issue with datatype size,As our Sainath told earlier.

Thank you

DSXchange

Ora 00911