Ora 00911

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

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

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

:lol:
Top
vijay.barani
Participant
Posts: 78
Joined: Wed Jun 04, 2008 2:59 am

Post by vijay.barani »

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

Code: Select all

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
Warm Regards,
Vijay
Top
SHARAD123
Premium Member
Premium Member
Posts: 54
Joined: Wed Jan 09, 2008 12:05 am

Post by SHARAD123 »

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.
222102
Top
vijay.barani
Participant
Posts: 78
Joined: Wed Jun 04, 2008 2:59 am

Post by vijay.barani »

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
Warm Regards,
Vijay
Top
DS_SUPPORT
Premium Member
Premium Member
Posts: 232
Joined: Fri Aug 04, 2006 1:20 am
Location: Bangalore

Post by DS_SUPPORT »

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.
Top
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

Did you search?

Are you using a LongVarchar or LOB ?

Are there any special characgers in the data?
Top
vijay.barani
Participant
Posts: 78
Joined: Wed Jun 04, 2008 2:59 am

Post by vijay.barani »

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.
Warm Regards,
Vijay
Top
vijay.barani
Participant
Posts: 78
Joined: Wed Jun 04, 2008 2:59 am

Post by vijay.barani »

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.
Warm Regards,
Vijay
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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? :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

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.
Top
vijay.barani
Participant
Posts: 78
Joined: Wed Jun 04, 2008 2:59 am

Post by vijay.barani »

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
Warm Regards,
Vijay
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”