DSXchange

Hi,

I am calling an Oracle Stored Procedure from ODBC stage, i am getting the following error if SP runs more than 30 minutes.

ExtractPSFTServiceSeq..Transformer_Input_T1: ORA-03113: end-of-file on communication channel

Attempting to Cleanup after ABORT raised in stage ExtractPSFTServiceSeq..Transformer_Input_T1

ExtractPSFTServiceSeq..Transformer_Input_T1: ORA-03114: not connected to ORACLE

I called the same store procedure through unix script and working fine if SP runs more than 30 minutes. As well i called the Script through a sequencer and it works fine.

I checked the ODBC Config & ini files and didn't find anything wrong. The property LockTimeOut is set to -1 in the ini file. Please find the config entry for same from ini file.

QEWSD=37957
Driver=/datastage/Ascential/DataStage/branded_odbc/lib/VMora19.sl
Description=DataDirect Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=oracledb
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=scott
Password=tiger
PortNumber=2521
ProcedureRetResults=0
SID=oracledbsid
UseCurrentSchema=1

Please help me in resolving the issue

Please let me know the solution if someone knows.

They will. Me, I don't use ODBC so can't really help.

Or give Support a call and open a case.

Even in one of the other project in my site reporting the same issue. Need to check out the reason.

An exact match Search on -03113 got 13 hits. Perhaps one of these will help?

Few were towards some setting in database end, and few were towards network traffic. They would have probably tried those option discussed here. As mentioned, if the stored procedure is called via shell, it can running for a long hrs, and not via ODBC stage.
Kinda weired.

No evidence that they'd searched at all, so I had to point it out.

Its nothing wrong in searching again though.

I searched for the key but i didn't get any result.

I did search on -03113 , result was my message nothing else.

As noted, do an Exact Match search in All forums - you'll get almost a dozen other threads that mention your error.

Specifying '-' before search key word will function unexpectedly. As per Goolge standards, if you mention something preceeded by '-', it will produce the search result apart from the given key word.

I searched it but couldn't find what is required. Anyways thanks for your responses.

Hi All,

In my view this problem is occuring because the connection you are having with Oracle is getting timed out due inactivity. So, in order to get rid of this problem you have to get in touch with your DBA and increase the time-out time for your user name.

I was facing a problem like this with Sybase DB and i was using ODBC stage for accessing the DB.The above mentioned solution helped me in solving the issue.I hope it will be helpful to you also.

Correct me if i am wrong some where.

Thanks & Regards,
Punar Deep Singh

Thanks Punardeep, but here issue is different. I am able to execute same SP which runs for more than 30 minutes through Shell Script or from a Job Sequnce by Calling the scripts which calls SP but not directly from ODBC.