About EasyMorph Tutorials & Examples Web-help

ORA-01013 Message


#1

Hello,

I try to select data from an Oracle database using an SQL-statement. The database is really big (34.459.000 data sets)
The statement is listed below.

Easymorph stops with errormessage ORA-01013 indicating timeout problems.

Have you got a clue where to fix this issue? I allready tried with increased timeout, but did not work.

Regards,
Michael

SELECT
r.rd AS VERMITTLERNUMMER_KOPF
,aa.VERMITTLERNUMMER
,aa.KOSTENSTELLE
,to_char(CAST(aa.ABRECHNUNGSDATUM AS Date),‘YYYY-MM-DD’) AS DATE_ABRECHNUNG
,COUNT(DISTINCT VERT_ID) AS cnt_VERT_ABGER
,SUM(ORG_BEWERTUNGSSUMME) AS sum_ORG_BWS

FROM FF_OWNER.S_VFFW_ABRECHNUNG_ARCHIC aa
LEFT JOIN FF_OWNER.V_FFREP_RDVB_LISTE r ON aa.vermittlernummer = r.vermittlernummer

WHERE 1=1
AND r.rd IS NOT NULL
AND aa.PROVISIONSART NOT IN ‘Saldo’
AND ABRECHNUNGS_POSITION IN (‘EA’,‘PB’)
AND aa.KOSTENSTELLE IN (2061,2062,2065)
AND aa.VERMITTLERNUMMER NOT IN (‘MAK75729’,‘MAK24698’)
AND CAST(aa.ABRECHNUNGSDATUM AS Date) >= TO_TIMESTAMP(COALESCE(’{PRM_DATE_FROM}’,to_char(TRUNC(CURRENT_DATE - 1),‘YYYY-MM-DD HH24:MI:SS’)),‘YYYY-MM-DD HH24:MI:SS’)
AND CAST(aa.ABRECHNUNGSDATUM AS Date) < TO_TIMESTAMP(COALESCE(’{PRM_DATE_TO}’,to_char(TRUNC(CURRENT_DATE),‘YYYY-MM-DD HH24:MI:SS’)),‘YYYY-MM-DD HH24:MI:SS’)

GROUP BY
r.rd
,aa.VERMITTLERNUMMER
,aa.KOSTENSTELLE
,to_char(CAST(aa.ABRECHNUNGSDATUM AS Date),‘YYYY-MM-DD’)

ORDER BY
r.rd
,aa.VERMITTLERNUMMER


#2

Hello Michael,

Can you please provide an actual error message?

Do you use EasyMorph’s Oracle connector or ODBC connector?

Just have to make sure that we are talking about “SQL SELECT timeout” setting in Advanced tab in connector configuration dialog? If yes - how many seconds did you tried to set there? And how much time passes before the query fails?


#3

Hello Andrew,

we are using the EM Oracle Connector.
SQL SELECT timeout is set to 3.600 seconds.

Actual message follows.

Regards,
Michael


#4

Michael, thanks for the details.

And how much time does it takes for query to fail?


#5

Hello Andrew,
it stops exactly after 1 hour … (Server and Professional)
Regards,
Michael


#6

Hello Michael,

It seems that it just takes more than 1 hour to execute this query and import all the results to EasyMorph.

Can you please try to run this query with a limited number of rows (100 and 100,000, for example) and measure approximate import time?

Do you know an approximate number of rows which this query should return?

Regards,
Andrew